Hello,
I am encoutering the "Row size too large (> 8126)" errors on my Private Cloud instance.
This is because we have some objectifs with dozens of text area fields in them.
I cannot add any more fields (INTGXXX) to my tables because of it.
I noticed on the 4.2 patch that you have fixed this issue on Rollbase Hosted Cloud.
Could you tell me what you have done?
I'm trying to fix this error in my Private Cloud instance, and I want to be able to add some more columns afterwards.
I can't seem to be able to fix this issue.
We are using MySQL 5.5.25, with InnoDB and the Antelope file format.
I read that we need to use the Barracuda file format. However, we can't seem to be able to switch to Barracuda file format.
Is this what you had to do to fix the issue?
Here is what we have done so far:
SET GLOBAL innodb_file_format = barracuda;
SET GLOBAL innodb_file_format_max = barracuda;
ALTER TABLE rb_obj_data
ENGINE=InnoDB
ROW_FORMAT=COMPRESSED
KEY_BLOCK_SIZE=8;
The ALTER TABLE doesn't work; when trying it, we get the "Row size too large (> 8126)" error.
Thank you for your help.
Kind regards,
Romain.
Hello,
Do you have information on this matter?
Kind regards,
Hi,
If the error comes when the schema is being created , set the property innodb_strict_mode =OFF
else
the fix to this is to change row format from COMPACT to DYNAMIC so that 768 bytes of each BLOB column (TEXT column) is not counted against the row size limit.
But looks like it is not easy to do this in MySQL 5.6/5.5 as changing this needs us to move to Barracuda innodb file format from Antelope innodb file format.
Changing innodb file format is at tablespace level and not at table level.
We need to upgrade to MySQL 5.7.13 or above and change row format for this issue to go away.
Hello,
Thank you for your answer.
This is in adequation with what I thougt.
We are using MySQL 5.5.25 right now.
Do you think we can avoid the issue without having to upgrade MySQL?
How can we change innodb file format is at tablespace level?
Aren't the folowing request enough?
- SET GLOBAL innodb_file_format = barracuda;
- SET GLOBAL innodb_file_format_max = barracuda;
- ALTER TABLE rb_obj_data ROW_FORMAT=DYNAMIC;
Why am I getting "Row size too large (> 8126)" when doing the last request? (Alter table), after having changed to barracuda?
By the way, are there any "risks" when upgrading from MySQL 5.5 to MySQL 5.7?
Is this a long operation?
And would upgrading to MySQL 5.7 be enough to resolve our problem?
Can you give answers to my questions above please?
Thank you,
Romain.
Hi,
Sorry could'nt reply earlier. Setting the values will not be of any use. It is a known mySQL bug which is enhanced with 5.7.13.Let me get back to you with the exact answers by reproducing the scenario and applying a fix.Will reply in a day or two .
Hello,
Thank you,
Looking forward to it.
Kind regards,
Romain.
I dont understand the part where you say , I cant add fields .
How I reproduced and checked:
So I reproduced the issue in 5.5 using 200 textfields with data . When creating a new object with 200 fields filled with random text(100-150) characters we are getting "Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline." Is this the exact error you are getting .
This happens when row_format is compact (5.5/5.6)
From 5.7.13+ the default row_dormat is dynamic /compressed . So i again created 200 text fields with the same text and submitted the new object . We dont get the error
This is because the compressed row formats include compressed tables and off-page storage for long column data.
More detailed explanation:
When a table is created with ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED, long variable-length column values (for VARBINARY, VARCHAR, BLOB, and TEXT columns) are stored fully off-page, and the clustered index record contains only a 20-byte pointer to the overflow page.
Couple of questions to test the safety of the upgrade /time taken
What type of upgrade are you planning ?
In-place upgrade or dump restore(Logical Upgrade)
Best regards,
Srikanth
Hello,
Thank you very much for your time and answers.
We really appreciate it!
This is an important matter for us, so thank you again.
In-place upgrade seems easier to do than logical upgrade.
I believe we'll try this upgrade on our test server.
Or do you think it is better to do a logical upgrade?
We are using MySQL 5.5.25 for now.
I believe we will have to:
Do an in-place upgrade from 5.5.25 to 5.6.33
Do an in-place upgrade from 5.6.33 to 5.7.15
Kind regards,