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.
|Message||Row size too large. The maximum row size for the used table type, not counting BLOBs, is %ld. |
This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs.
|Reason||The total size of the row has exceeded the max allowed size of 65,535 bytes|
|Error Type||Server-Side Error|
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
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.
Try any of the following solutions:
- Set 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.
- Use a TEXT field instead of a large VARCHAR field.
- If both of the above solutions are not possible, then split the table into multiple tables.
- 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
Here is the list of related errors to MySQL Error: 1118. The error code is the same but the message can be different.
|Error Number||Error Symbol||SQLSTATE||Message||Error Type|
|1118||ER_TOO_LONG_STRING||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.||Server-Side|