Row size too large. The maximum row size for the used table type....

3 months 1 week ago #323677 by autobahn
I apologise for revisiting this question which I know you have addressed many times before - I have read all the previous posts but an still unsure what to do next.

I was trying to add a new field to one of the tables in our live Joomla database (a single-select dropdown field) using CB Field Management. I haven’t had a problem doing the exact same thing on my local clone (in fact I’ve added 27 new fields). There are currently 91 columns on our comprofiler table.

But I got this error:
Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

I searched the forum and found your post for the same error where you stated that the correct MySQL setting are:
phpmyadmin settings:
Storage Engine: InnoDB
ROW_FORMAT: DYNAMIC

database settings:
innodb_large_prefix = 1
innodb_file_per_table = 1
innodb_file_format = Barracuda

Our hosting provider has implemented MariaDB 10.3. I have done a lot of research and asked a lot of questions before posting this and I have discovered that:

phpmyadmin settings:
Storage Engine: InnoDB – I'm told this is not really relevant and the correct storage engine is used depending on the data format
ROW_FORMAT: DYNAMIC – this is the default value in MariaDB 10.3 (held as innodb_default_row_format) but as I do not have appropriate access rights I can’t see if it is being applied or if an older, deprecated format is in use

database settings:
innodb_large_prefix = 1 – deprecated in MariaDB 10.3 and so has no actual value, but I understand it is set to 1 for compatibility
innodb_file_per_table = 1 – is set ON in phpMyAdmin/Server Variables, which usually means it is 1
innodb_file_format = Barracuda – there is no value against this Server Variable in phpMyAdmin. Barracuda is the default file format in MariaDB 10.3, but again as I do not have appropriate access rights I can’t see if it is being applied.

I suspect the problem lies with either the row_format or the file-format of the table(s) in question. I set up our site 7-8 years ago and I keep Joomla, CB and CBSubs up-to-date, but the site itself has never been re-installed. Is it possible that some of the tables are using an old row or file format and this is causing the error?

If so, would the problem be solved if I re-installed the site from backup?

Thank you

Please Log in to join the conversation.

3 months 1 week ago - 3 months 1 week ago #323687 by krileon
Please see the below topic for a detailed post regarding MariaDB settings.

www.joomlapolis.com/forum/153-professional-member-support/242484-installation-cb-connect-failed?start=12#318734

Your issue is probably caused by the innodb_page_size setting. Older versions of MariaDB set this below MySQL default value. It should be at minimum 16384.

I suspect the problem lies with either the row_format or the file-format of the table(s) in question. I set up our site 7-8 years ago and I keep Joomla, CB and CBSubs up-to-date, but the site itself has never been re-installed. Is it possible that some of the tables are using an old row or file format and this is causing the error?

It's possible you may need to rebuild your database to generate it's storage again. Believe database repair/optimize within phpmyadmin should handle that for you, but be sure to make a backup before running either tool. Alternative is to export the tables and import into a new database.

Note this problem won't exist in CB 3.x. We'll be redesigning how fields store to completely eliminate this issue.


Kyle (Krileon)
Community Builder Team Member
Before posting on forums: Read FAQ thoroughly + Read our Documentation + Search the forums
CB links: Documentation - Localization - CB Quickstart - CB Paid Subscriptions - Add-Ons - Forge
--
If you are a Professional, Developer, or CB Paid Subscriptions subscriber and have a support issue please always post in your respective support forums for best results!
--
If I've missed your support post with a delay of 3 days or greater and are a Professional, Developer, or CBSubs subscriber please send me a private message with your thread and will reply when possible!
--
Please note I am available Monday - Friday from 8:00 AM CST to 4:00 PM CST. I am away on weekends (Saturday and Sunday) and if I've missed your post on or before a weekend after business hours please wait for the next following business day (Monday) and will get to your issue as soon as possible, thank you.
--
My role here is to provide guidance and assistance. I cannot provide custom code for each custom requirement. Please do not inquire me about custom development.

Please Log in to join the conversation.

3 months 1 week ago #323696 by autobahn
Thank you for your quick (as always :) ) response.

The link you provided is very useful and I wish I'd found it on my own forum searches.

The innodb_page_size is indeed 16384, so it looks like a database rebuild is in order. Since I always do a backup (and download it) before touching anything on the live site I could probably achieve the same result by restoring the (Akeeba) backup using Kickstart, which drops existing tables and rebuilds them in the course of database restoration. But I will also take a backup using phpMyAdmin.

I apologise again for revisiting this error, but hopefully my experience (and your answer) will be of use to anyone who encounters the same problem.

Thank you.

Please Log in to join the conversation.

3 months 1 week ago #323697 by krileon
Hopefully the rebuild solves the issue. I'm not a database expert so it's possible there's other configuration settings I'm not aware of that could be a problem.


Kyle (Krileon)
Community Builder Team Member
Before posting on forums: Read FAQ thoroughly + Read our Documentation + Search the forums
CB links: Documentation - Localization - CB Quickstart - CB Paid Subscriptions - Add-Ons - Forge
--
If you are a Professional, Developer, or CB Paid Subscriptions subscriber and have a support issue please always post in your respective support forums for best results!
--
If I've missed your support post with a delay of 3 days or greater and are a Professional, Developer, or CBSubs subscriber please send me a private message with your thread and will reply when possible!
--
Please note I am available Monday - Friday from 8:00 AM CST to 4:00 PM CST. I am away on weekends (Saturday and Sunday) and if I've missed your post on or before a weekend after business hours please wait for the next following business day (Monday) and will get to your issue as soon as possible, thank you.
--
My role here is to provide guidance and assistance. I cannot provide custom code for each custom requirement. Please do not inquire me about custom development.
The following user(s) said Thank You: autobahn

Please Log in to join the conversation.

Moderators: beatnantkrileon
Time to create page: 0.432 seconds

Facebook Twitter LinkedIn