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.

53 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

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

Anonymous 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

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

Unknown 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

Unknown said...

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


VMware Training in Chennai

Unknown 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

Anonymous 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

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

service care said...

I am obliged to you for sharing this piece of information here and updating us with your resourceful guidance. Hope this might benefit many learners. Keep sharing this gainful articles and continue updating for us.
moto service centre chennai
moto service center
motorola service center

cdcdcdcd said...


Thanks for sharing this Post, Keep Updating such topics.

Best Ice Fishing Gloves Best Ice Fishing Gloves Best Ice Fishing Gloves


htop said...

Thanks for sharing blog artice. Here i like to share your thought about deveops training
devops training in omr
best devops training in chennai

best devops training institute in omr
best devops training institute in sholinganallur

Training for IT and Software Courses said...

Very useful and information content has been shared out here, Thanks for sharing it.microsoft azure training in bangalore

svrtechnologies said...

Pretty article! I found some useful information in your blog, it was awesome to read, thanks for sharing this great content to my vision, keep sharing...

oracle training

svrtechnologies said...

Whatever we gathered information from the blogs, we should implement that in practically then only we can understand that exact thing clearly, but it’s no need to do it, because you have explained the concepts very well. It was crystal clear, keep sharing..

oracle cloud tutorial

Rashika said...

Wow!! Really a nice Article about Data Science. Thank you so much for your efforts. Definitely, it will be helpful for others. I would like to follow your blog. Share more like this. Thanks Again.
Java training in chennai | Java training in annanagar | Java training in omr | Java training in porur | Java training in tambaram | Java training in velachery

Anonymous said...

Right here is the right site for anybody who would like to understand this topic. You understand so much its almost tough to argue with you (not that I actually would want to…HaHa). You definitely put a brand new spin on a subject that's been discussed for ages. KBC Winner Wonderful stuff, just great!

nisha said...

Excellent blog. the blog is really very impressive every concept of this blog is neat and very clear in the manner.

Data Science Training Course In Chennai | Data Science Training Course In Anna Nagar | Data Science Training Course In OMR | Data Science Training Course In Porur | Data Science Training Course In Tambaram | Data Science Training Course In Velachery

subha said...

this page fully contain mysql coding...it may help beginner for sql keep update lots of details..relaed to my sql
AngularJS training in chennai | AngularJS training in anna nagar | AngularJS training in omr | AngularJS training in porur | AngularJS training in tambaram | AngularJS training in velachery

Unknown said...

Thank you for excellent article.You made an article that is interesting.

SAP HR Online Training

SAP HR Classes Online

SAP HR Training Online

Online SAP HR Course

SAP HR Course Online

Unknown said...

Very interesting blog Thank you for sharing such a nice and interesting blog and really very helpful article.

sap pm training in bangalore

sap pm class in bangalore

learn sap pm in bangalore

places to learn sap pm in bangalore

sap pm schools in bangalore

sap pm school reviews in bangalore

sap pm training reviews in bangalore

sap pm training in bangalore

sap pm institutes in bangalore

sap pm trainers in bangalore

learning sap pm in bangalore

where to learn sap pm in bangalore

best places to learn sap pm in bangalore

top places to learn sap pm in bangalore

sap pm training in bangalore india

radhika said...

Wow it is really wonderful and awesome thus it is very much useful for me to understand many concepts and helped me a lot.
AWS training in Chennai

AWS Online Training in Chennai

AWS training in Bangalore

AWS training in Hyderabad

AWS training in Coimbatore

AWS training

harshni said...

Thank you for sharing such a great information.Its really nice and informative.hope more posts from you. I also want to share some information recently i have gone through and i had find the one of the best
Artificial Intelligence Training in Chennai | Certification | ai training in chennai | Artificial Intelligence Course in Bangalore | Certification | ai training in bangalore | Artificial Intelligence Training in Hyderabad | Certification | ai training in hyderabad | Artificial Intelligence Online Training Course | Certification | ai Online Training | Blue Prism Training in Chennai | Certification | Blue Prism Online Training Course

Jayalakshmi said...

Thanks for sharing this blog. This very important and informative blog Learned a lot of new things from your post! Good creation.
hadoop training in chennai

hadoop training in tambaram

salesforce training in chennai

salesforce training in tambaram

c and c plus plus course in chennai

c and c plus plus course in tambaram

machine learning training in chennai

machine learning training in tambaram

deiva said...

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

web designing training in omr

digital marketing training in chennai

digital marketing training in omr

rpa training in chennai

rpa training in omr

tally training in chennai

tally training in omr

praveen said...

Wow very informative blog,
Thanks and keep more updates,

oracle training in chennai

oracle training in porur

oracle dba training in chennai

oracle dba training in porur

ccna training in chennai

shiny said...

Nice Post. Thanks for sharing with us....

hadoop training in chennai

hadoop training in annanagar

salesforce training in chennai

salesforce training in annanagar

c and c plus plus course in chennai

c and c plus plus course in annanagar

machine learning training in chennai

machine learning training in annanagar

jeni said...

I simply wanted to thank you so much again. I am not sure the things that I might have gone through without the type of hints revealed by you regarding that situation.
hadoop training in chennai

hadoop training in velachery

salesforce training in chennai

salesforce training in velachery

c and c plus plus course in chennai

c and c plus plus course in velachery

machine learning training in chennai

machine learning training in velachery

vivekvedha said...

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

acte complaints

acte reviews

acte trainer complaints

acte trainer reviews

acte velachery reviews complaints

acte tambaram reviews complaints

acte anna nagar reviews complaints

acte porur reviews complaints

acte omr reviews complaints

Rajasthan Budget Tours said...

Thanks for sharing this blog. really nice and useful for me. I really appreciate your post and you explain each and every point very well. Rajasthan Budget Tours

OGEN Infosystem (P) Limited said...

Amazing Blog, thank you so much for sharing with us. Visit Ogen Infosystem for best Website Designing Company and SEO Services in affordable price.
Web Designing Company in Delhi

Sunder said...

Great content & Thanks for sharing with oflox, Website Designing Company In Dehradun

Python said...

Thanks for sharing this great article. It's really nice and useful for us.
Data Science Online Training
Python Online Training

Pathway for German Language said...

I am glad to post a worthy article about the German Language Course and IELTS Coaching from KCR consultants, this may change your career growth and language skill.
KCR-German Language
KCR-IELTS

saketh321 said...

I want to say thanks to you. I have bookmark your site for future updates. ExcelR Data Science Course In Pune

Anonymous said...

Thanks for the Valuable information.Really useful information. Thank you so much for sharing. It will help everyone.

SASVBA is one of the leading training providers in the country. We provide AI training Course in Delhi accordance with current industry standards, allowing students to find their dream job in the world's leading companies. SASVBA specializes in teaching services, artificial lighting training that imparts practical knowledge through live project-based learning.
FOR MORE INFO:

3RI Technologies said...

Informative,Thanks for sharing.
Data Science Training in Pune

Certified SDET Program said...

Software Development Engineer Training SDET is an emerging role in the present scenario, this role needs an IT professional that can handle development as well as testing effectively. The entire software development process is handled from developing to the testing phase. So this role has high priority in the industries.

Unknown said...

Thank you very much for sharing such a useful article. Will definitely saved and revisit your site business analytics course in kanpur

Back Links Only said...

Free Download Wifi Hacker Full Version For Windows 7 i was surfing net and thankfully came throughout this website and found intensely exciting stuff here. Its in endeavor of truth amusing to settlement. I enjoyed loads. thank you for sharing this super aspire.

Home Appliances PK said...

This very informative and interesting blog. I have read many blog in days but your writing style is very unique and understanding. if you are interested in home appliances then click below.

chiq air conditioner
buy dc inverter ac

TRONIX said...

Good blogger ever

Java course in Hyderabad

hubert said...

You can modify your pictures, you can use the tools provided by Photo IDimager Supreme.
https://thepcsoft.com/idimager-photo-supreme-crack/

MY LEARN NEST TRAINING said...

This application is one of the world’s most frequently utilized in virtually all sectors. One of the most essential modules in ERP, SAP PP or production planning, is the SAP PP module. Materials planning, capacity planning, execution of production orders, billing materials and products transportation are just a few of the planning operations that are handled by SAP PP. Because it controls and keeps records of manufacturing process flows, SAP PP training in Hyderabad is quite significant. Several additional SAP modules such as the SD, MM and QM modules as well as FICO and PM are fully connected with the PP module of SAP.
best-sap-pp-course-in-hyderabad/

vcube said...

informative article. Keep spreading such a helpful article.
best institute for python in kphb

Anonymous 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.
Python Training institute in Hyderabad