Friday, January 04, 2013

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.

9 comments:

Joseph D. Purcell said...

A fantastic article! Thank you for writing it! Two notes worth mentioning:

1. DYNAMIC vs COMPRESSED: the key difference between the two is COMPRESSED also uses zlib compression on the data. In cases where storage space is not a constraint, using "ROW_FORMAT=COMPRESSED" will avoid the unnecessary CPU load of compressing data. References:

http://dev.mysql.com/doc/innodb/1.1/en/innodb-compression-syntax-warnings.html

http://venublog.com/2008/04/25/innodb-plugin-row-format-performance/

2. IO concerns: for those who aren't aware of what the "innodb_file_per_table" flag does, it's at least important to know that this could significantly increase IO depending on the behavior of your data, i.e. foreign key constraints or many JOIN queries. References:

http://umangg.blogspot.com/2010/02/innodbfilepertable.html

http://dba.stackexchange.com/questions/16208/why-using-innodb-file-per-table

Alistair Davidson said...

Thanks Joseph, useful reference articles

Aldo said...

Every few weeks/months I end up here...cheers Al!

Guru said...

thank you

Guru said...

thank you

Guru said...

thank you

Pradeep Yadav said...

Thanks a lot for nice tutorials

aashi said...

I do trust all of the concepts you’ve presented on your post. They’re really convincing and will definitely work. Still, the posts are too brief for newbies. May you please extend them a little from subsequent time?Also, I’ve shared your website in my social networks.
Informatica Training in Chennai
Java Training in Chennai
Oracle Apps Training in Chennai

KarlDeville said...

Thank you for taking the time and sharing this information with us. It was indeed very helpful and insightful while being straight forward and to the point.
mcdonalds.gutscheine | startlr.com | salud limpia