error 1118: row size too large. utf8 innodb

Question

I have tried to find a solution guys, and I am guessing that someone has met this before. But I cannot get hits down below 5000... most of which don't match my question.

Using MySQL 5.6 - windows (both XP and Win7 - same issue)

I have a utf8 mysql schema with the following structure (field names removed) The schema was built just using standard tool - I did not choose compression nor anything else.

f1  varchar(5) 
f2  varchar(255)
f3  varchar(255)
f4  varchar(255)
f5  varchar(255)
f6  datetime
f7  datetime
f8  longtext
f9  varchar(255)
f10 varchar(255)
f11 longtext
f12 varchar(255)
f13 varchar(255)
f14 varchar(255)
f15 longtext
f16 longtext
f17 longtext
f18 longtext
f19 longtext
f20 longtext
f21 longtext
f22 longtext
f23 varchar(255)
f24 varchar(255)
f25 varchar(255)
f26 longtext
f27 longtext
f28 varchar(255)
f29 varchar(255)
f30 longtext
f31 longtext
f32 longtext
f33 longtext
f34 longtext
f35 longtext
f36 longtext
f37 longtext
f38 longtext
f39 datetime
f40 datetime

This works fine, until I come to a record with a lot of text in a lot of the longtext fields. Say I have inserted values into 6 of the longtext fields, but when I try to paste in values into (say) the 7th, I get the 1118 Error.

Now, I am a bit puzzled as this data is going into a text field which should not contribute to the row length as it is stored outside the row (barring the 12 bytes used to define the entry). Using my best guess for the row length of the structure above it comes to only 11009 bytes (give or take), well within the 64K limit.

This is proving frustrating as I can't really understand why it fails. I could break the table down into multiple 1-to-1s, but that seems hackneyed, and I could take out the text fields and create a related table with foreign key and variable name and the associated text (using row modelling for this element) - but again, that is just rod for my back later.

Answer 1

There are two issues you need to look at

ISSUE #1 : Characters Vs Bytes

Since UTF-8 uses 3 bytes per characters, you may not have allocated enough for character fields.

ISSUE #2 : InnoDB Row Length

By default, an index key for a single-column index can be up to 767 bytes. The same length limit applies to any index key prefix. See Section 13.1.13, “CREATE INDEX Syntax”. For example, you might hit this limit with a column prefix index of more than 255 characters on a TEXT or VARCHAR column, assuming a UTF-8 character set and the maximum of 3 bytes for each character. When the innodb_large_prefix configuration option is enabled, this length limit is raised to 3072 bytes, for InnoDB tables that use the DYNAMIC and COMPRESSED row formats.

When you attempt to specify an index prefix length longer than allowed, the length is silently reduced to the maximum length. This configuration option changes the error handling for some combinations of row format and prefix length longer than the maximum allowed. See innodb_large_prefix for details.

The InnoDB internal maximum key length is 3500 bytes, but MySQL itself restricts this to 3072 bytes. This limit applies to the length of the combined index key in a multi-column index.

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 14.3.12.2, “File Space Management”.

Although InnoDB supports row sizes larger than 65,535 bytes internally, MySQL itself imposes a row-size limit of 65,535 for the combined size of all columns:

mysql> CREATE TABLE t (a VARCHAR(8000), b VARCHAR(10000), -> c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000), -> f VARCHAR(10000), g VARCHAR(10000)) ENGINE=InnoDB; ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs See Section E.10.4, “Table Column-Count and Row-Size Limits”.