How to Fix MySQL error "Row size too large" Print

  • 0

MySQL error "Row size too large" usually occurs when the combined size of all columns in a table row exceeds the maximum allowed row size. The default maximum row size for the InnoDB engine is 65,535 bytes. To fix this issue, you can try the following steps:

Increase the InnoDB log file size:

Edit the MySQL configuration file, typically located at /etc/my.cnf or /etc/mysql/my.cnf. Open the file with your preferred text editor:

nano /etc/my.cnf
Under the [mysqld] section, add or modify the following lines:

innodb_log_file_size = 128M
innodb_log_buffer_size = 64M
Save and close the file.

Note: Increasing the log file size may require you to delete the existing log files. Please backup the log files before proceeding. The log files are usually located in the MySQL data directory (e.g., /var/lib/mysql) and have names like ib_logfile0 and ib_logfile1.

Increase the InnoDB page size:

Note that this option is only available in MySQL 5.7.7 or later. You should check your MySQL version first:

mysql --version
If your MySQL version is 5.7.7 or later, open the MySQL configuration file again as in step 1, and under the [mysqld] section, add or modify the following line:

innodb_page_size = 32k
Save and close the file.

Restart the MySQL service:

service mysqld restart
Or, depending on your system:

systemctl restart mysqld
Or for MariaDB:

systemctl restart mariadb
If the issue persists, consider restructuring your table schema by breaking it into multiple smaller tables, using vertical partitioning, or reducing the number of columns with large data types such as TEXT or BLOB.

After implementing these changes, the "Row size too large" error should be resolved. If you still experience the issue, double-check your MySQL configuration and ensure that you have followed the steps correctly.

Was this answer helpful?

« Back