Row size too large when adding data to the MYSQL database

Overview

When adding data to the database, you may encounter an error message "Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline."

 

Solution

This issue was encountered after a record field was updated through a SQL statement.

The possible solutions for this issue can be found in the following articles:

https://mariadb.com/kb/en/troubleshooting-row-size-too-large-errors-with-innodb/

https://stackoverflow.com/questions/15585602/change-limit-for-mysql-row-size-too-large

 

t's important to note that even if you use TEXT or BLOB fields, your row size could still be over 8K (the limit for InnoDB) because it stores the first 768 bytes for each field inline in the page.

The simplest way to fix this is to use the Barracuda file format with InnoDB. This basically gets rid of the problem altogether by only storing the 20-byte pointer to the text data instead of storing the first 768 bytes.

OR change the row format to compressed by:

Add the following to the my.cnf file under [mysqld] section.

innodb_file_per_table=1
innodb_file_format = Barracuda
ALTER the table to use ROW_FORMAT=COMPRESSED.

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

 

There is a possibility that the above still does not resolve your issues. It is a known (and verified) bug with the InnoDB engine, and a temporary fix for now is to fallback to MyISAM engine as temporary storage. So, in your my.cnf file:

internal_tmp_disk_storage_engine=MyISAM

 

If the above does not work, please open a support ticket and provide the SQL statement you are using to update the data.

Comments

0 comments

Please sign in to leave a comment.