4

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.

Improve this question
3
  • Is this InnoDB or MyISAM? Commented Aug 10, 2012 at 10:57
  • InnoDB (in subject heading). Commented Aug 13, 2012 at 9:54
  • Can you post the output of SHOW CREATE TABLE tablename so we can try and replicate the problem at our machines? Commented Aug 13, 2012 at 10:03

1 Answer 1

Reset to default
3

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”.

Improve this answer