MYSQL - How to Bypass 66KB String Size Limit

Question

I have mysql db. I am using innodb. One of my tables contains just over 10 columns. The last column is of type LONGTEXT and should contain html code. The problem is that for each record this field does not encode the full code and it always stops after the same number of characters. The weight of the html files I'm trying to insert is around 60KO. Therefore, I think that each of my entries exceeds the mysql row size limit (66KO). I would like to know if there are any ways to expand this limit. Any workaround would be greatly appreciated. Thanks in advance for the input. Greetings. Mark

Answer 1

It is not possible to expand this limit because it is independent of the storage mechanism, but this is a hard limit on the server :

Each table (regardless of storage mechanism) has a maximum row size of 65,535 bytes. Storage engines may create additional restrictions for this limitation, reducing the effective maximum row size.

In this case, decisions should revolve around deferring HTML storage elsewhere - in the file system or in the cloud (S3), and then refer to the file name in the table column.

Answer 2

The accepted answer is incorrect (or, at least, rather self-confident) - I personally do not need data stored outside my database, as this creates difficulties in terms of backup procedures and transactional requests.

As others have pointed out, the manual repeatedly states that the BLOB and TEXT columns are not taken into account in relation to the total row size, but unfortunately with the default configuration settings this is not true and you get an error message, (The error message makes no sense because it tells you to use TEXT instead of VARCHAR to solve the problem - you are already.)

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

SET GLOBAL innodb_file_format=Barracuda; 

This will not have an immediate effect, because this option is standard for new database files, so you will need to drop and recreate the entire database.

Alternatively, switch to Barracuda (as described above) and then (optional) switch to the file strategy at the table:

 SET GLOBAL innodb_file_per_table=ON; 

Again, this will not have an immediate effect, because both settings are the default values ​​for new tables - so you will need to drop and recreate the table.

If you look into the MySQL data folder after that, you can confirm that separate files were created, for example. for a database named "data" and a table named "test" you should see a file called "data / test / bigtable.ibd".

If you don't like changing global settings in MySQL, try SET SESSION instead of SET GLOBAL , for example. just before running your CREATE TABLE statements.

Answer 3

Values ​​for (LONG) TEXT (and BLOB) are not stored "on line", but outside it. Therefore, the size of your HTML does not affect the size of individual lines.

From the manual:

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

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

(emphasis mine)

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

Answer 4

When you say: “this field does not encode the full code and it always stops after the same number of characters,” how do you determine what the field contains? I suspect that what you are viewing has been truncated by the variable max_allowed_packet .

As stated in the MySQL manual :

The maximum size of a BLOB or TEXT object is determined by its type, but the largest value that you can really transfer between the client and server is determined by the amount of available memory and the size of communication buffers. You can change the size of the message buffer by changing the value of the max_allowed_packet variable, but you must do this both for the server and for your client program. For example, both mysql and mysqldump allow you to change the value of max_allowed_packet on the client side. See Section 8.11.2, “Configuring Server Settings,” Section 4.5.1, “ mysql - MySQL Command Line Tool” and Section 4.5.4, “ mysqldump - Database Backup Utility” . You can also compare the packet sizes and the size of the data objects that you store with storage requirements, see Section 11.5, “Data Warehouse Requirements”

Answer 5

We resolved it by following these steps.

Step-1: Follow the below queries in MySql to set global variables.

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

Step-2: select the table from which the error occurs when trying to save large data in one row.

Step 3: go to operations

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

Step 5: Click "Go."

:)