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:

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

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

Limits on InnoDB Tables

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)

Solution

The reason is that the "old" Antelope file format is used by default which is not able to compress the rows.

Switching to the newer Barracuda file format grants a few more features like the compressed row format that acts just like the MySQL documentation states on the 1118 Row size too large error.

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 /etc/mysql/my.cnf

[mysqld]
# [...]
    innodb_file_per_table
    innodb_file_format = Barracuda

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:

ALTER TABLE mytable ROW_FORMAT = COMPRESSED;

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. ;)

Update 2013-10-31

Peter Zaitsev explains the internal handling of BLOBs and TEXT on the mysqlperformanceblog. A really nice read that explains why having lots of BLOBor TEXTfields in one table with Antelope file format causes the problem above and how Barracuda fixes this.

Short version

The first 768 Bytes of a BLOB or 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.