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.

25 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

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

sai said...

It is amazing and wonderful to visit your site.Thanks for sharing this information,this is useful to me...
python Online training in chennai
python Online training in bangalore
python interview question and answers

Nila shri said...

Great Article… I love to read your articles because your writing style is too good, its is very very helpful for all of us and I never get bored while reading your article because, they are becomes a more and more interesting from the starting lines until the end.
Data Science course in kalyan nagar | Data Science Course in Bangalore
Data Science course in OMR | Data Science Course in Chennai
Data Science course in chennai | Best Data Science training in chennai
Data science course in velachery | Data Science course in Chennai
Data science course in jaya nagar | Data Science course in Bangalore
Data Science interview questions and answers

gowsalya said...

This is a nice article here with some useful tips for those who are not used-to comment that frequently. Thanks for this helpful information I agree with all points you have given to us. I will follow all of them.
Best Devops Training in pune
excel advanced excel training in bangalore

jeeva said...

Good Post! Thank you so much for sharing this pretty post, it was so good to read and useful to improve my knowledge as updated one, keep blogging.
rpa training in Chennai | rpa training in bangalore | best rpa training in bangalore | rpa course in bangalore | rpa training institute in bangalore | rpa training in bangalore | rpa online training

sathya said...

This blog is the general information for the feature. You got a good work for these blog.We have a developing our creative content of this mind.Thank you for this blog. This for very interesting and useful.

angularjs Training in chennai

angularjs Training in chennai

angularjs-Training in tambaram

angularjs-Training in sholinganallur

angularjs-Training in velachery

angularjs-Training in pune

tamilsasi said...

I was recommended this web site by means of my cousin.
I am now not certain whether this post is written through him as nobody else recognise such precise about my difficulty. You're amazing! Thank you!

selenium training in Chennai
selenium training in Tambaram
selenium training in Velachery
selenium training in Omr
selenium training in Annanagar

saranyaregan said...

Your good knowledge and kindness in playing with all the pieces were very useful. I don’t know what I would have done if I had not encountered such a step like this.
microsoft azure training in bangalore
rpa training in bangalore
best rpa training in bangalore
rpa online training

siva said...

All are saying the same thing repeatedly, but in your blog I had a chance to get some useful and unique information,
I love your writing style very much, I would like to suggest your blog in my dude circle, so keep on updates.
Java training in Btm layout
Java training in Jaya nagar
Java training in Electronic city
Java training in Chennai
Java training in USA