How Can I Fix MySQL's 'Row size too large' Error?

Adjusting MySQL Row Size Limit for Oversized Data

The error message "Row size too large (> 8126)" indicates that a row in a MySQL table exceeds the default 8KB limit. Several factors contribute to this issue, including the data types used in the table columns.

One potential solution is to change some of the columns to TEXT or BLOB data types, as these can accommodate larger amounts of data. Additionally, using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED can also help reduce the row size by storing data in a more efficient manner.

However, the provided error message suggests that even with these modifications, the row size may still be too large. This is because the InnoDB storage engine stores the first 768 bytes of BLOB data inline within the row, further limiting the row size.

To address this issue, consider implementing the following solutions:

1. Switch to the Barracuda File Format:

   • Add the following to the my.cnf file:

     • innodb_file_per_table=1

   - This file format removes the issue of storing BLOB data inline, allowing larger rows to be accommodated.

2. Use ROW_FORMAT=COMPRESSED:

   • Execute the following query to alter the table structure:

     • ALTER TABLE table_name
       ENGINE=InnoDB
       ROW_FORMAT=COMPRESSED

   - This setting reduces the row size by compressing non-key columns, potentially resolving the issue.

If the above solutions still fail to resolve the issue, consider temporarily switching to the MyISAM engine for storing temporary data by adding the following line to the my.cnf file:

internal_tmp_disk_storage_engine=MyISAM

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