I'm currently working on a project where users can create a quite extensive profile. This contains about 20 free text fields.
When the profiles get too big InnoDB might show the following error:
What seemed to be an easy to fix problem took me quite some time to figure out.
I was working with an unmodified MySQL 5.5.28 on Debian Wheezy with the default configuration and had the same problem for Ubuntu 12.10.
The error message is misleading
First up: The error message is a little misleading. You would expect that converting some of your columns to
TEXT (asuming this makes sense) would solve the problem – but it does not. The online documentation supports this assumption by stating
The maximum row length, except for variable-length columns (
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.
In the default configuration for Debian and Ubuntu this is not true.
TEXT columns also count onto the limit! (Update: The mysqlperformanceblog explains the internals. Check the link below)
The reason is that the "old" Antelope file format is used by default which is not able to compress the rows.
Warning: Even though these steps seem to work without loosing data, you should always have a backup of all your tables.
1. Enable Barracuda as file format for InnoDB
To do that, just add the following lines to
The innodb_file_per_table is a required option for Barracuda. You should espacially read up on the disadvantages of this option.
After the change you need to restart the MySQL server.
2. Use the compressed row format
Now you have to use the compressed row format for all possibly affected tables:
This worked for me on-the-fly even though it took some time with a few hundred thousand records. Anyway: Make sure you have a backup!
And that's it. We have modified our MySQL configuration to actually comply with the documentation. ;)
Peter Zaitsev explains the internal handling of
TEXT on the mysqlperformanceblog. A really nice read that explains why having lots of
TEXTfields in one table with Antelope file format causes the problem above and how Barracuda fixes this.
The first 768 Bytes of a
TEXT (aka "long columns") are always stored in the row even if the content is stored on an external page. With more than 10 filled long columns the 8126 Bytes are completely exhausted.