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.


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:

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:

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 | | salud limpia

ananthi said...

this is very nice blog and you are updating wonderful information to us thanks for your attention to and keep going on to updating so many useful information.

Base SAS Training in chennai

vinothika said...

The blog is very interesting and will be much useful for use. Thanks for sharing with your blog. Please keep on updating.

Bigdata Training in Chennai

sharath said...

Think this blog is very useful and informative. Thank you so much.

MSBI Training in Chennai
Informatica Training in Chennai
Data Warehousing Training in Chennai

jaya prakash said...

This is very nce one.. really spend time with good thing.... i will share this to my frends...thank you so much for this post....

Best Training Institute in chennai

chenna sankar said...

Thank you for taking the time and sharing this information with us.

VMware Training in Chennai

Sulekha Nivas said...

The blog is very different and useful. The way of writing makes it more interesting.

Selenium Training in Chennai

Lucy Allison said...

Thanks for the informative article.This is one of the best resources I have found in quite some time.Nicely written and great info.I really cannot thank you enough for sharing.

Herbalife in Chennai
Subamwellness in Chennai
Weight Loss in Chennai
Weight Gain in Chennai

janani said...

I’ve desired to post about something similar to this on one of my blogs and this has given me an idea. Cool Mat.
Java training in Chennai | Java training in Omr

Oracle training in Chennai

Java training in Chennai | Java training in Annanagar

Java training in Chennai | Java training institute in Chennai | Java course in Chennai