8

I have a problem with MySQL, I have a table with many text fields. When I try to store some data I obtain this error.

Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. You have to change some columns to TEXT or BLOBs

The text that I store in each field is not too long, only a few paragraphs in each one.

What can I do?

9

Thanks for the people that answer. The links you posted were a very useful base to start learning.

Finally I found this page: http://download.oracle.com/docs/cd/E17952_01/refman-5.5-en/innodb-compression-usage.html

And I configured my.cnf adding these two lines in [mysqld] section:

innodb_file_per_table
innodb_file_format = Barracuda

Then I ALTER my table with this command through phpMyAdmin:

ALTER TABLE nombre_tabla
 ENGINE=InnoDB
 ROW_FORMAT=COMPRESSED 
 KEY_BLOCK_SIZE=8; 
 SHOW WARNINGS;

It is also possible use other settings that you can read in the link above, but these worked good for me.

| improve this answer | |
  • I didnt find much info on wahts the drawback for COMPRESSED and Barracuda. Why not always use that if that is better and solves this? – Ted Aug 6 '18 at 14:56
3

You may want to take a look at this article which explains a lot about MySQL row sizes. It's important to note that even if you use TEXT or BLOB fields, your row size could still be over 8K (limit for InnoDB) because it stores the first 768 bytes for each field inline in the page. The simplest way to fix this is to use the Barracuda file format with InnoDB. This basically gets rid of the problem altogether by only storing the 20 byte pointer to the text data instead of storing the firs 768 bytes.

| improve this answer | |
1

Well, do as mysql says: convert big fields from varchar to text or blob (ALTER TABLE). text fields behave much like varchar (indices needs prefix length, but nevertheless work), but are stored separately.

This reference tells you the exact limits of every stock mysql table engine:

http://dev.mysql.com/doc/refman/5.0/en/column-count-limit.html

And here's everything to know about text fields:

http://dev.mysql.com/doc/refman/5.0/en/blob.html

| improve this answer | |
  • Thanks @korkman for your answer, my fields were already defined as text when the problem ocurred. I'm going to watch your link suggestions. – Memochipan Nov 2 '11 at 0:18
0

Mostly cross-posting from Stack Overflow, but this is an open (and verified) bug in MySQL server's InnoDB engine. A temporary fix for now is to fallback to MyISAM engine as temporary storage. So, in your my.cnf file:

internal_tmp_disk_storage_engine=MyISAM
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.