Mysql Row Size Limit

Question

How to change the limit

Line size is too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In the format of the current line, the BLOB prefix of 768 bytes is stored in the line.

Table:

 id int(11) No name text No date date No time time No schedule int(11) No category int(11) No top_a varchar(255) No top_b varchar(255) No top_c varchar(255) No top_d varchar(255) No top_e varchar(255) No top_f varchar(255) No top_g varchar(255) No top_h varchar(255) No top_i varchar(255) No top_j varchar(255) No top_title_a varchar(255) No top_title_b varchar(255) No top_title_c varchar(255) No top_title_d varchar(255) No top_title_e varchar(255) No top_title_f varchar(255) No top_title_g varchar(255) No top_title_h varchar(255) No top_title_i varchar(255) No top_title_j varchar(255) No top_desc_a text No top_desc_b text No top_desc_c text No top_desc_d text No top_desc_e text No top_desc_f text No top_desc_g text No top_desc_h text No top_desc_i text No top_desc_j text No status int(11) No admin_id int(11) No 

Answer 1

The question was asked on the server .

You might 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 the TEXT or BLOB fields, your row size can still exceed 8 KB (the limit for InnoDB), since it stores the first 768 bytes for each inline field on the page.

The easiest way to fix this is to use the Barracuda file format with InnoDB. This basically eliminates the problem by storing only a 20-byte pointer to text data instead of storing the first 768 bytes.

---

The method that worked for the OP there was:

1. Add the following to the my.cnf file in the [mysqld] section.

    innodb_file_per_table=1 innodb_file_format = Barracuda 

2. ALTER tables use ROW_FORMAT=COMPRESSED .

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

---

There is a possibility that the above still will not solve your problems. This is a known (and verified) bug in the InnoDB engine , and the interim fix is ​​currently rolling back to the MyISAM engine as a temporary storage. So in your my.cnf file:

 internal_tmp_disk_storage_engine=MyISAM 

Answer 2

I recently ran into this problem and solved it differently. If you are using MySQL version 5.6.20, a known error exists on the system. See MySQL Docs

Important Due to error # 69477, the rewriting log for large BLOB fields stored in the external block may overwrite the most recent checkpoint. To fix this error, the patch introduced in MySQL 5.6.20 limits the size of the replay log. BLOB writes 10% of the size of the replay log file. As a result of this limit, innodb_log_file_size should be set to a value that is 10 times the largest BLOB data size found in the rows of your table, plus the length of other fields of variable length (VARCHAR, VARBINARY and TEXT fields).

In my situation, the offending blob was about 16 MB. So the way I solved this was to add a line to my.cnf that would ensure that I had at least 10x that amount, and then a few:

innodb_log_file_size = 256M

Answer 3

If you can switch the ENGINE and use MyISAM instead of InnoDB, this should help:

ENGINE=MyISAM

MyISAM has two caveats (maybe more):

• You cannot use transactions.
• You cannot use foreign key constraints.

Answer 4

Install the following in the my.cnf file and restart the MySQL server.

 innodb_strict_mode = 0 

Answer 5

I would like to share an amazing answer, this may be helpful. Credits by Bill Carvin see here https://dba.stackexchange.com/questions/6598/innodb-create-table-error-row-size-too-large

They vary depending on the InnoDB file format. There are currently 2 formats called Antelope and Barracuda. A.

The central tablespace file (ibdata1) is always in Antelope format. If you use the file behind the table, you can make individual files using the Barracuda format by setting innodb_file_format = Barracuda to my.cnf.

Key points:

• One 16KB InnoDB data page must contain at least two rows of data. Plus, each page has a header and footer containing the checksums of the pages and the serial number of the journal, etc. This is where you get your limit a little less than 8 KB per line.

• On this primary data page, fixed data types of the type INTEGER, DATE, FLOAT, CHAR are stored and calculated in accordance with the row size limit.

• Variable-sized data types, such as VARCHAR, TEXT, BLOB, are stored on overflow pages, so they do not fully account for row size restrictions. In Antelope, up to 768 bytes of such columns are stored on the primary data page in addition to being stored on the overflow page. Barracuda supports the dynamic line format, so it can only store a 20-byte pointer on the primary data page.

• Variable-sized data types also have a prefix with 1 or more bytes for encoding lengths. And the InnoDB string format also has an array of field offsets. Thus, the internal structure is more or less documented on their wiki.

Barracuda also supports ROW_FORMAT = COMPRESSED to get additional storage efficiency for overflow data.

I also need to comment that I have never seen a well-designed table exceed the row size limit. It is a strong “smell of code” that you violate the condition of repeating groups of the first normal form.

Answer 6

After spending hours, I found a solution: just run the following SQL in your MySQL administrator to convert the table to MyISAM:

 USE db_name; ALTER TABLE table_name ENGINE=MYISAM; 

Answer 7

I ran into this problem when I was trying to restore a backup of the mysql database from another server. For me, this problem was solved by adding certain settings to my.conf (as in the questions above) and additionally changing the sql backup file:

Step 1: Add or edit the following lines in my.conf:

 innodb_page_size=32K innodb_file_format=Barracuda innodb_file_per_table=1 

Step 2 add ROW_FORMAT = DYNAMIC to the table creation statement in the sql backup file for the table that causes this error:

 DROP TABLE IF EXISTS 'problematic_table'; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE 'problematic_table' ( 'id' bigint(20) NOT NULL AUTO_INCREMENT, ... PRIMARY KEY ('id') ) ENGINE=InnoDB AUTO_INCREMENT=3 ROW_FORMAT=DYNAMIC; 

the important change above is ROW_FORMAT = DYNAMIC; (this was not included in the backup file of the original sql)

source that helped me solve this problem: MariaDB and InnoDB MySQL Row too large

Answer 8

I had the same problem, this solved it for me:

 ALTER TABLE 'my_table' ROW_FORMAT=DYNAMIC; 

From MYSQL documentation :

The DYNAMIC row format supports the efficiency of storing the entire row in the index node if it is suitable (as the COMPACT and REDUNDANT formats do), but this new format eliminates the problem of filling B-tree nodes with a large number of data bytes of long columns. The DYNAMIC format is based on the idea that if part of a long data value is stored off-page, it is usually most efficient to store all values ​​off-page. In the DYNAMIC format, shorter columns are likely to remain in the B-tree node, minimizing the number of overflow pages needed for any given row.

Answer 9

Other answers answer the question. I will focus on the main reason: poor circuit design.

Do not massage the array in columns. Here you have 3 * 10 columns that should be turned into 10 rows of 3 columns in a new table (plus id , etc.)

The Main table will only

 id int(11) No name text No date date No time time No schedule int(11) No category int(11) No status int(11) No admin_id int(11) No 

Your extra table ( Top ) would have

 id int(11) No -- for joining to Main seq TINYINT UNSIGNED -- containing 1..10 img varchar(255) No title varchar(255) No desc text No PRIMARY KEY(id, seq) -- so you can easily find the 10 top_titles 

In Top for each id will be 10 (or less?) Lines.

This fixes your original problem and clears the circuit. (This is not "normalization," as discussed in some comments.)

Do not switch to MyISAM; He's leaving.
Do not worry about ROW_FORMAT .

You will need to modify your code to make a JOIN , and handle multiple rows instead of multiple columns.

Answer 10

I also ran into the same problem. I solve the problem by running the following sql:

 ALTER ${table} ROW_FORMAT=COMPRESSED; 

But I think you should know about Row Storage .
There are two types of columns: a variable-length column (of type VARCHAR, VARBINARY, BLOB, and TEXT) and a fixed-length column. They are stored in different types of pages.

Variable-length columns are an exception to this rule. Columns such as BLOB and VARCHAR, which are too long to fit on a B-tree page, are stored on separate dedicated disk pages called overflow pages. We call such columns off-page. The values ​​of these columns are stored in singly linked lists of overflow pages, and each such column has its own list of one or more overflow pages. In some cases, all or prefix values ​​of a long column are stored in a B-tree to avoid losing storage and eliminating the need to read a separate page.

and when the goal of setting ROW_FORMAT is

When a table is created using ROW_FORMAT = DYNAMIC or ROW_FORMAT = COMPRESSED, InnoDB can store long values ​​of variable-length columns (for types VARCHAR, VARBINARY and BLOB and TEXT) completely off-page, and a clustered index record containing only a 20-byte pointer to the overflow page .

Want to know more about DYNAMIC AND COMPRESSED FORMS Row

Answer 11

If this happens in SELECT with many columns, the reason may be that mysql is creating a temporary table. If this table is too large to fit in memory, it will use the default temporary table format (InnoDB) for storage on disk. In this case, InnoDB size limits apply.

Then you have 4 options:

1. change the innodb line size limit as indicated in another post, which requires reinitializing the server.
2. change your query to include fewer columns, or do not create a temporary table (for example, by removing the order by and limit commands).
3. change max_heap_table_size to large so that the result fits in memory and does not need to be written to disk.

4. change the default temporary table format to MYISAM, this is what I did. Change in my.cnf:

    internal_tmp_disk_storage_engine=MYISAM 

Restart mysql, the query is working.