Detailed explanation of MySQL ERROR1118 error report

created at 10-28-2021 views: 5

two types of ERROR1118

1.

ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

The maximum record length of a line is 65535 (defined to this length will also report an error, and the maintenance of the line itself will also occupy bytes). It is recommended to use text or blobs.

2.

ERROR 1118 (42000): Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline.

A record is too long and exceeds 8126 bytes. It is recommended to use text or blob types for some columns.

Seeing these two error messages, I feel that the descriptions are somewhat conflicting. One says that the maximum length of a record does not exceed 65535 bytes, and the other says that the length cannot exceed 8126 bytes.

First look at the description of the official document:

mysql docs

The MySQL maximum row size limit of 65,535 bytes is demonstrated in the following InnoDB and MyISAM examples. The limit is enforced regardless of storage engine, even though the storage engine may be capable of supporting larger rows.

InnoDB restricts row size (for data stored locally within the database page) to slightly less than half a database page for 4KB, 8KB, 16KB, and 32KB innodb_page_size settings, and to slightly less than 16KB for 64KB pages.

summary:

The maximum length of a record of 65535 bytes is the limit of the MySQL database Server level.

By default, Innodb page size is 16KB, so

The storage length of a record in the page cannot exceed 8126 bytes

This is a limitation of the Innodb storage engine.

There may be some questions here. Usually, the varchar(10000) type field is created, which has exceeded 8126, but this error has not been reported. This is related to the format of Innodb's storage of a record.

The official document explains the storage format:

mysql docs

Fixed-length columns greater than or equal to 768 bytes are encoded as variable-length columns, which can be stored off-page

That is to say, a varhcar (10000) type field is created, and 10000 bytes of data are written at the same time. In fact, only 768 bytes are stored on the data page, and the remaining bytes are stored on the overflow page.

Why Innodb storage engine, each storage page, there must be at least two records,

mysql

This is if each page can only store one record, the table stores [1, 2, 3, 4] 4 records B+ tree structure diagram,

If the data volume of a page cannot store 2 records, the B+ tree cannot be called a B+ tree, because it cannot serve as an index. In fact, it is a doubly linked list, but it occupies a lot more space than a doubly linked list.

If two records cannot be stored, then this B+ tree is meaningless and cannot form an effective index.

Summarize:

There are two restrictions when creating a table and writing data. One is the restriction at the server level. The maximum length of a record cannot exceed 65535 (the length of the actual record cannot be 65535, because the record itself also needs some bytes to maintain)

The other is a limitation at the Innodb level. The length of a record stored in a page cannot exceed 8126 bytes.

experiment:

experiment 1

Create a table t, the total length of records exceeds 65535. The default character set is latin1, one character occupies one byte, if utf8 is used, one character occupies 3 bytes. To multiply the defined varchar field type by 3 is the number of bytes occupied.

Looking at the t2 table, the varchar type is 5000, and the longest record is 35000 bytes, which does not reach the limit at the server level.

The 768 bytes of each field are stored on the InnoDB page, and the rest of the data is stored on the overflow page.

The t2 table has a total of 7 fields. Only the first 768 bytes of each field are stored on the Innodb page, 7*768=5376 bytes, which does not reach the 8126 limit of the Innodb storage engine and will not report an error, so the creation is successful.

mysql>  CREATE TABLE t (a VARCHAR(10000), b VARCHAR(10000),
    ->        c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),
    ->        f VARCHAR(10000), g VARCHAR(6000)) ENGINE=InnoDB CHARACTER SET latin1;
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

mysql> create table t1(a varchar(10000),b varchar(10000),c varchar(2000)) character set utf8;ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs 

mysql> CREATE TABLE t2 (a VARCHAR(5000), b VARCHAR(5000),    ->       c VARCHAR(5000), d VARCHAR(5000), e VARCHAR(5000),    ->       f VARCHAR(5000), g VARCHAR(5000)) ENGINE=InnoDB CHARACTER SET latin1;Query OK, 0 rows affected (0.01 sec)

experiment 2

Create the t4 table and use the char(255) fixed-length string type. No matter how much content is written in the char type (of course, it must be less than or equal to 255), it will occupy 255 bytes in actual storage.

A total of 33 fields, each field has a fixed length of 255 bytes, 33*255=8415, and the maximum length of each record is 8145 bytes, which is within the limit of the Server layer, so the 65535 error is not reported.

But a record exceeded the 8126 limit when stored in the Innodb page, so the Innodb storage engine reported an error.

mysql>  CREATE TABLE t4 (
    ->        c1 CHAR(255),c2 CHAR(255),c3 CHAR(255),
    ->        c4 CHAR(255),c5 CHAR(255),c6 CHAR(255),
    ->        c7 CHAR(255),c8 CHAR(255),c9 CHAR(255),
    ->        c10 CHAR(255),c11 CHAR(255),c12 CHAR(255),
    ->        c13 CHAR(255),c14 CHAR(255),c15 CHAR(255),
    ->        c16 CHAR(255),c17 CHAR(255),c18 CHAR(255),
    ->        c19 CHAR(255),c20 CHAR(255),c21 CHAR(255),
    ->        c22 CHAR(255),c23 CHAR(255),c24 CHAR(255),
    ->        c25 CHAR(255),c26 CHAR(255),c27 CHAR(255),
    ->        c28 CHAR(255),c29 CHAR(255),c30 CHAR(255),
    ->        c31 CHAR(255),c32 CHAR(255),c33 CHAR(255)
    ->        ) ENGINE=InnoDB ROW_FORMAT=COMPACT DEFAULT CHARSET latin1;
ERROR 1118 (42000): Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.

experiment 3

Modify the t4 table to varhcar(255). Try the variable length field. When the varchar field type is actually stored on the page, it is not stored in fixed length, but how many bytes are written and how many bytes are stored.

As you can see, there is no problem in creating a table in this way, and there is no problem if the number of bytes written is less than 8126 bytes.

However, if the number of written bytes exceeds 8126, an error will still be reported due to the limitations of the Innodb storage engine.

mysql>  CREATE TABLE t4 (
    ->        c1 VARCHAR(255),c2 VARCHAR(255),c3 VARCHAR(255),
    ->        c4 VARCHAR(255),c5 VARCHAR(255),c6 VARCHAR(255),
    ->        c7 VARCHAR(255),c8 VARCHAR(255),c9 VARCHAR(255),
    ->        c10 VARCHAR(255),c11 VARCHAR(255),c12 VARCHAR(255),
    ->        c13 VARCHAR(255),c14 VARCHAR(255),c15 VARCHAR(255),
    ->        c16 VARCHAR(255),c17 VARCHAR(255),c18 VARCHAR(255),
    ->        c19 VARCHAR(255),c20 VARCHAR(255),c21 VARCHAR(255),
    ->        c22 VARCHAR(255),c23 VARCHAR(255),c24 VARCHAR(255),
    ->        c25 VARCHAR(255),c26 VARCHAR(255),c27 VARCHAR(255),
    ->        c28 VARCHAR(255),c29 VARCHAR(255),c30 VARCHAR(255),
    ->        c31 VARCHAR(255),c32 VARCHAR(255),c33 VARCHAR(255)
    ->        ) ENGINE=InnoDB ROW_FORMAT=COMPACT DEFAULT CHARSET latin1;
Query OK, 0 rows affected (0.01 sec)

Test 1: The scenario where the length of the written data is less than 8126, 240*33=7920, can be successful.

insert into t4 (c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16,c17,c18,c19,c20,c21,c22,c23,c24,c25,c26,c27,c28,c29,c30,c31,c32,c33)
values(repeat('a',240),repeat('a',240),repeat('a',240),repeat('a',240),repeat('a',240),repeat('a',240),repeat('a',240),repeat('a',240),repeat('a',240),repeat('a',240),repeat('a',240),repeat('a',240),repeat('a',240),repeat('a',240),
repeat('a',240),repeat('a',240),repeat('a',240),repeat('a',240),repeat('a',240),repeat('a',240),repeat('a',240),repeat('a',240),repeat('a',240),repeat('a',240),repeat('a',240),repeat('a',240),repeat('a',240),repeat('a',240),repeat('a',240),repeat('a',240),repeat('a',240),repeat('a',240),repeat('a',240))
Query OK, 1 row affected (0.01 sec)

test 2: The scenario where the length of the written data is greater than 8126, 255*33=8415, directly report an error.

mysql> insert into t4 (c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16,c17,c18,c19,c20,c21,c22,c23,c24,c25,c26,c27,c28,c29,c30,c31,c32,c33)
    -> values(repeat('a',255),repeat('a',255),repeat('a',255),repeat('a',255),repeat('a',255),repeat('a',255),repeat('a',255),repeat('a',255),repeat('a',255),repeat('a',255),repeat('a',255),repeat('a',255),repeat('a',255),repeat('a',255),
    -> repeat('a',255),repeat('a',255),repeat('a',255),repeat('a',255),repeat('a',255),repeat('a',255),repeat('a',255),repeat('a',255),repeat('a',255),repeat('a',255),repeat('a',255),repeat('a',255),repeat('a',255),repeat('a',255),repeat('a',255),repeat('a',255),repeat('a',255),repeat('a',255),repeat('a',255));
ERROR 1118 (42000): Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.
mysql> 
created at:10-28-2021
edited at: 10-28-2021: