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

How to Modify MySQL Row Size Limit

When encountering the error "Row size too large (> 8126)", it indicates that the maximum row size permissible by MySQL has been exceeded. To resolve this issue, certain table design considerations and database configuration adjustments can be implemented.

Recommendations

The suggested solution proposed on Stack Exchange involves modifying settings in the my.cnf file:

1. Enable Barracuda File Format: Configure InnoDB to use the Barracuda file format by setting innodb_file_format=Barracuda. This eliminates the need to store a portion of BLOB data inline, reducing row size.
2. Use ROW_FORMAT=COMPRESSED: Change the table's row format to ROW_FORMAT=COMPRESSED. This format reduces the size of BLOB data by applying compression.

Example Command:

ALTER TABLE table_name
ENGINE=InnoDB
ROW_FORMAT=COMPRESSED
KEY_BLOCK_SIZE=8;

3. Fallback to MyISAM: In case the above measures don't resolve the issue, consider using MyISAM as the temporary storage engine for internal data. Add the following line to the my.cnf file:

   internal_tmp_disk_storage_engine=MyISAM

Considerations

1. Review Table Schema: Examine the table's columns and their data types. Consider changing columns that contain excessive data to TEXT or BLOB.
2. Use Dynamic or Compressed Row Format: The ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED options for the table can help optimize space utilization for data-heavy rows.
3. Consider Sharding: If the table contains a significant amount of data, consider sharding it across multiple databases or tables. This will reduce the row size in each table.

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