Meaning of 'In current row format, BLOB prefix of 768 bytes is stored inline.'

Question

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.

Answer 1

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 = Barracuda.