How to Solve MySQL's 'Row size too large' Error?

Addressing Row Size Limit in MySQL with "Row size too large" Error

The "Row size too large" error in MySQL arises when a database table's row exceeds the allowable limit. To resolve this issue, it's crucial to understand the underlying factors.

In the given table, the MySQL error indicates that the row size is greater than 8126 bytes, possibly due to several text columns (TEXT or BLOB) with large data. To address this, consider converting certain columns to text or blob data types or utilizing row format optimization techniques.

However, the error message also suggests that inline storage of BLOB prefixes may contribute to the problem. To overcome this:

1. Leverage Barracuda File Format:

This file format in InnoDB stores only a 20-byte pointer to text data instead of the initial 768 bytes, effectively eliminating the row size limitation. To activate Barracuda:

• Add innodb_file_per_table=1 and innodb_file_format = Barracuda to the my.cnf file.
• Alter the table to use ROW_FORMAT=COMPRESSED, which can shrink row sizes even further.

2. Utilize MyISAM Engine (Temporary Solution):

Unfortunately, the Barracuda-based solution may not always resolve the issue due to an InnoDB engine bug. In such cases, you can fallback to the MyISAM engine as a temporary storage option by adding internal_tmp_disk_storage_engine=MyISAM to your my.cnf file.

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