MySQL Bugs: #63507: Error Code 1118: Row size too large

Bug #63507	Error Code 1118: Row size too large - even though it is not.
Submitted:	30 Nov 2011 13:36	Modified:	11 Dec 2012 22:05
Reporter:	Floris Steenkamp	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	5.5.18, 5.1.61, 5.0	OS:	Any (Windows 7 and Vista)
Assigned to:		CPU Architecture:	Any
Tags:	InnoDB;Row size too large;BLOB

Description:
This problem is very easy to repeat by running the scripts in "How to repeat" below from, say, MySQL Workbench.

Create a table with (at least) 10 BLOB fields and another (dummy) field. Add one row.

Now update the table so each BLOB field in that row contains (at least) 800 bytes. This works fine as it should. Each row now contains about 8000 bytes, all of which basically resides in the BLOB fields so that the actual row size is in effect very  small. 

The moment you now try to do another simple update an error occurs as follows:
"Error Code 1118: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. You have to change some columns to TEXT or BLOBs".

Note that the storage engine is InnoDB - it does not occur with MyISAM. Clearly the problem must be that contrary to what the error says "...not counting BLOBs" it IS actually counting the BLOBs! When the same scripts are run with BLOBs having length 400 instead of 800 the error does not occur!

Curiously enough, when the table contains less than 10 BLOB fields the error also does not occur.

I am running MySQL Community Server 5.5.18 and get the error on both Win7 x64 AND Vista x86.

Can anyone solve this puzzle?

How to repeat:
Simply run the attached scripts from top to bottom:
Note: The BLOB fields are updated in the scripts so that each BLOB field contains (at least) 800 bytes.

Suggested fix:
The suggested fix is clear from the context.

MySQL Script to repeat problem

Attachment: Bug.txt (text/plain), 8.37 KiB.

Thank you for the report.

Verified as described.

Manual says at http://dev.mysql.com/doc/refman/5.5/en/innodb-restrictions.html:

----<q>----
#

The maximum row length, except for variable-length columns (VARBINARY, VARCHAR, BLOB and TEXT), is slightly less than half of a database page. That is, the maximum row length is about 8000 bytes. LONGBLOB and LONGTEXT columns must be less than 4GB, and the total row length, including BLOB and TEXT columns, must be less than 4GB.

If a row is less than half a page long, all of it is stored locally within the page. If it exceeds half a page, variable-length columns are chosen for external off-page storage until the row fits within half a page, as described in Section 13.3.12.2, “File Space Management”.
----</q>----

I think this explains why you get this error, but you should not, because you store data in BLOB columns.

Workaround: use new InnoDB file format Barracuda and row_format=DYNAMIC

Another workaround: switch data type for a1 from CHAR to VARCHAR

Added to changelog for 5.1.66, 5.5.29, 5.6.7: 

The error message was improved for the case where an UPDATE failed
because the row included several BLOB values greater than 768 bytes
each, causing the size of a row to exceed half the page size. The old
message, was misleading; it suggested using BLOBs, when the 768-byte
prefix for each BLOB column was the cause of the limit error:

Error Code 1118: Row size too large. The maximum row size for the used
table type, not counting BLOBs, is 8126. You have to change some
columns to TEXT or BLOBs

A workaround for the problem was to create
the table with the ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED
clause, which is now suggested in the message.