MySQL "Row size too large" when saving many text fields

Using MySQL? InnoDB table type? Got a table with several TEXT or BLOB fields? Getting a "Row size too large" error when saving a row with lots of text in those TEXT fields? Confused, because you thought the whole point of TEXT fields was that they stored the text off-table? Well, read on....

disclaimer: if you're storing many text fields in a relational database table, you might want to look again at whether that's the right place and method for storing that data - if it looks like a document and quacks like a document, then hey, maybe a document store would be more appropriate? But that's a whole other topic...

The detail is in the MySQL docs 14.4.5. How InnoDB Stores Variable-Length Columns , but I'll give a quick summary here. It hinges on the file format of your InnoDB engine.

As the docs say:

Early versions of InnoDB used an unnamed file format (now called Antelope) for database files. With that format, tables were defined with ROW_FORMAT=COMPACT (or ROW_FORMAT=REDUNDANT) and InnoDB stored up to the first 768 bytes of variable-length columns (such as BLOB and VARCHAR) in the index record within the B-tree node, with the remainder stored on the overflow pages.

(emphasis mine)

So with the Antelope file format, it's perfectly possible to store a single TEXT field up to 2GB without encountering the "Row size too large" error, but it's not possible to store 10 x 1k TEXT fields - because InnoDB will store the first 768 bytes of each TEXT field on the record itself, and exceed the row size limit of 8192 bytes.

The solution is straightforward (well, mostly :) - change your innodb_file_format variable to Barracuda, and alter the table to use the DYNAMIC or COMPRESSED row_format. This will store the entire contents of the TEXT fields "off-page" -

SET GLOBAL innodb_file_format=Barracuda; SET GLOBAL innodb_file_per_table=ON; ALTER TABLE (your table) ROW_FORMAT=COMPRESSED;

- and you're good to go.

The one complication is if you're running on Amazon RDS, in which case you'll get an error saying you don't have SUPER privileges. If that's the case, you just need to set the innodb_file_format parameter in your RDS instance parameter group, and allow a few seconds for it to propagate to all your instances.