MYSQL - How to workaround the row size limit of 66 KBytes

Question

I have a mysql db. I use innodb. 0ne of my tables contains a little more than 10 columns. The last column has a type of LONGTEXT and it is supposed to contain html code. The problem is, for each record, that field does not conatin the full code and it stops always after the same amount of characters. The weight of the html files I try to insert is around 60KO. So I guess each of my record exceeds the row size limit of mysql (66KO). What I would like to know is if there are some ways to extand that limit. Any workaround would be much appreciated. Thanks in advance for the inputs. Cheers. Marc

Answer 1

The accepted answer is wrong (or at least pretty opinionated) - I don't personally want data stored outside of my database, as it creates complications in terms of backup procedures and transactional queries.

As others have pointed out, the manual repeatedly states that BLOB and TEXT columns do not count towards the total row-size, but unfortunately, with the default configuration settings, that's not true, and you end up getting this error-message. (The error-message doesn't make any sense, because it's telling you to use TEXT instead of VARCHAR to address the issue - which you already are.)

The reason for this limitation is the default storage mechanism, Antelope, which stores the first 768 bytes of variable-length columns in the row - and a possible solution is to use INNODB and switch your storage mechanism to the alternative Barracuda storage mechanism:

SET GLOBAL innodb_file_format=Barracuda;

This will have no immediate effect, because this setting is a default for new database files - so you will need to drop and recreate your entire database.

Alternatively, switch to Barracuda (as above) and then (in addition) switch to the file-per-table strategy:

SET GLOBAL innodb_file_per_table=ON;

Again, this will have no immediate effect, because both settings are defaults for new tables - so again, you will need to drop and recreate the table.

If you look in the MySQL data folder after doing this, you can confirm that separate files were created, e.g. for a database named "data", and a table named "test", you should see a file named "data/test/bigtable.ibd".

If you dislike changing the global settings in MySQL, try SET SESSION instead of SET GLOBAL, e.g. immediately before running your CREATE TABLE statements.

Answer 2

Values for (LONG)TEXT (and BLOB) are not stored "in the row" but outside of it. Therefor the size of your HTML does not contribute to the size of the individual rows.

From the manual:

The internal representation of a table has a maximum row size of 65,535 bytes, even if the storage engine is capable of supporting larger rows. This figure excludes BLOB or TEXT columns, which contribute only 9 to 12 bytes toward this size

For BLOB and TEXT data, the information is stored internally in a different area of memory than the row buffer.

(emphasis mine)

http://dev.mysql.com/doc/refman/5.5/en/storage-requirements.html

Answer 3

When you say "that field does not conatin the full code and it stops always after the same amount of characters", how are you determining what the field contains? I suspect what you're viewing has been truncated by the max_allowed_packet variable.

As stated in the MySQL manual:

The maximum size of a BLOB or TEXT object is determined by its type, but the largest value you actually can transmit between the client and server is determined by the amount of available memory and the size of the communications buffers. You can change the message buffer size by changing the value of the max_allowed_packet variable, but you must do so for both the server and your client program. For example, both mysql and mysqldump enable you to change the client-side max_allowed_packet value. See Section 8.11.2, “Tuning Server Parameters”, Section 4.5.1, “mysql — The MySQL Command-Line Tool”, and Section 4.5.4, “mysqldump — A Database Backup Program”. You may also want to compare the packet sizes and the size of the data objects you are storing with the storage requirements, see Section 11.5, “Data Type Storage Requirements”

Answer 4

There is no way to extend this limit, as it is not dependent on the storage engine but it is a hard limit on the server:

Every table (regardless of storage engine) has a maximum row size of 65,535 bytes. Storage engines may place additional constraints on this limit, reducing the effective maximum row size.

In this case the solutions should revolve around deferring the storage of HTML to some other place - on the filesystem or on the cloud (S3) and then refer to the file name in the table column.

Answer 5

We resolved it with following below steps.

Step-1: Run below queries into MySql to set global variables.

• SET GLOBAL innodb_file_format=Barracuda;
• SET GLOBAL innodb_file_per_table=ON;

Step-2: Select table from which the error occur when you trying to save large data into single row.

Step-3: Go to Operations

Step-4: Select COMPRESSED from ROW_FORMAT. (By default COMPACT and REDUNDANT will be there when you set global variables innodb_file_format and innodb_file_per_table you can find another options like COMPRESSED and DYNAMIC)

Step-5: Click Go.

:)