1

I encounter below issue on MySQL v5.6.16:

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.

The corresponding table has several columns of type VARCHAR(4000).

I understand that here the record size exceeds the row size limit (8126 bytes), have also read 'Overview of InnoDB Row Storage'; but I do not understand what the following part of the error message means: In current row format, BLOB prefix of 768 bytes is stored inline.

When searching for this error, I see fixes being suggested but I'm interested on understanding what exactly causes this error. I would really appreciate your help in understanding the cause of this error.

Thank you.

0

If your ROW_FORMAT is REDUNDANT/COMPACT, the prefix (upto 768 bytes) of every blob/varchar/text is stored in the index records and remaining bytes are stored in Off-pages. So there is limitation on No of variable length columns in a table (upto 10 can be created in utf8 file format). Whether in DYNAMIC/COMPRESSED row formats, these fields are fully off-paged (prefix 0 byte). So the limitation is relaxed so that upto ~196 Text/Blob can be created in a single table.

Note : Verify whether the table row format is DYNAMIC/COMPRESSED

SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESWHERENAME='dbName/tableName'\G

And verify FILE_FORMAT = BARACUDA.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.