MySQL: Error 1118 – Row size too large

I was working on an old project for a new requirement. Tried to add a new column in the database for additional descriptions of products. The table already had a lot of columns. Got the following error, on the MySQL server while performing the operation.

Error Number: 1118

Error Message: 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.

This means I have hit the limit of row size. Let’s dig in and see what the error is actually about and how to resolve it.

Take a look at the details of the error first.

Error Details

Reason

Let’s try to regenerate the issue.

Try to create a new table that has some very large VARCHAR columns. Use the following query:

CREATE TABLE products (
	col_1 VARCHAR(10000),
	col_2 VARCHAR(10000),
	col_3 VARCHAR(10000),
	col_4 VARCHAR(10000),
	col_5 VARCHAR(10000),
	col_6 VARCHAR(10000),
	col_7 VARCHAR(10000)
) ENGINE=INNODB CHARACTER SET latin1; 

You will get the same error. As the total size of a row, we are trying to allocate is more than 65,535 bytes.

Let’s check all the limits of MySQL row size.

• MySQL has a limit of 65,535 bytes for a row.
• For InnoDB, the maximum size of a row is half of the page size. Page size is defined in innodb_page_size setting.

You can find much more details here: https://dev.mysql.com/doc/mysql-reslimits-excerpt/8.0/en/column-count-limit.html#row-size-limits

MySQL error no. 1118 means you have hit one of these limits.

TEXT and BLOB fields are stored separately, so the full size of those fields does not affect the row size. Those fields need 9 to 12 bytes in the row, to store the field-related information.

That is why the error message suggests You have to change some columns to TEXT or BLOBs at the end.

Solutions

Try any of the following solutions:

1. Set the appropriate size for a column. Like, If there is a large VARCHAR field, and you think the length of the field can be reduced, then reduce the size of that VARCHAR field.
2. Use a TEXT field instead of a large VARCHAR field.
3. If both of the above solutions are not possible, then split the table into multiple tables.
4. If the issue is happening because of the page size of InnoDB, then try the following to resolve the issue.

SET GLOBAL innodb_strict_mode=OFF;

Or in the MySQL config file add the following line:

innodb_strict_mode = 0

Related Errors

Here is the list of related errors to MySQL Error: 1118. The error code is the same but the message can be different.