MySQL - ERROR 1118 (42000) Row size too large

How does this error occur? How does InnoDB handle overflow?

Row overflow

We know that a column of type VARCHAR(M) can occupy up to 65535 bytes. If we use the ASCII character set, one character represents one byte. Let’s see if VARCHAR(65535) works.

1
2
3
4
mysql> CREATE TABLE varchar_size_demo(
-> c VARCHAR(65535)
-> ) CHARSET=ascii ROW_FORMAT=Compact;
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

As we see from the error message, MySQL has a limitation on the maximum storage space occupied by a single record. Except columns with the type BLOB or TEXT, the sum of all other columns (excluding hidden columns and header) cannot add up to more than 65535 bytes. Therefore MySQL suggests that we change the storage type to BLOB or TEXT.

In addition to the actual data, the 65535 bytes also includes other storage overhead. For example, in order to store a column of type VARCHAR, InnoDB makes use of 3 storage spaces.

  1. actual data
  2. bytes occupied by the actual data
  3. NULL attributes

If the VARCHAR column does not have a NOT NULL attribute, then it can store up to 65532 bytes. 2 bytes to store the length of VARCHAR, and 1 byte to indicate if it is null.

If the VARCHAR column has a NOT NULL attribute, then it is able to store 65533 bytes. It is not necessary to indicate whether it is null or not.

How to handle row overflow?

A page is typically 16KB in size, which is 16384 bytes. How does InnoDB handle VARCHAR(65532)?

In situations where a column occupies a large storage space, COMPACT and REDUNDANT manage to store only a part of the data in the current page, and dispersedly store the remaining data in other pages. To locate the pages, both formats use 20 bytes from the actual data section (refer to this post) to store the addresses pointing to these locations.

DYNAMIC and COMPRESSED

They are similar to the COMPACT format, except the way they deal with row overflow. They manage to store all the data in other pages, and only have the addresses pointing to these locations.

References

  1. High Performace MySQL
  2. MySQL 5.7 Reference Manual
  3. MySQL Internals Manual