How to Solve MySQL Error 1118: 'Row size too large'?

MySQL Row Size Too Large Error: Addressing the 1118 Code

When creating a MySQL table with a large number of columns, you might encounter the error code 1118: "Row size too large." This error indicates that the combined size of the columns exceeds a limitation imposed by the default row format.

Understanding the Row Size Limit

In MySQL, the row size limit varies based on the row format. The default row format, Compact, has a size limit of 8126 bytes. Other row formats, such as Compressed and Dynamic, offer larger limits.

Optimizing Column Types

To resolve the 1118 error, consider optimizing the data types of your columns. For example, replacing VARCHAR columns with TEXT can significantly reduce row size. Additionally, consider using BLOB or LONG types for large data values.

Enabling Barracuda File Format

MySQL 5.7 introduced the Barracuda file format, which offers enhanced performance and flexibility. Enabling Barracuda can mitigate the row size issue by allowing for larger row storage. To enable Barracuda, add the following attributes to your my.ini file:

innodb_file_per_table=1
innodb_file_format=Barracuda
innodb_file_format_check = ON

Disabling Strict Mode

As a last resort, you can try disabling strict mode for MySQL. However, this may expose you to potential data corruption issues. To disable strict mode, add the following attribute to your my.ini file:

innodb_strict_mode = 0

This will allow MySQL to ignore the record size check and allow you to create tables with larger rows.

Note: Modifying server parameters like innodb_strict_mode should be done with caution and only after considering the potential risks and implications for your data.

The above is the detailed content of How to Solve MySQL Error 1118: 'Row size too large'?. For more information, please follow other related articles on the PHP Chinese website!