MySQL Error – “Row size too large” while Restoring a Backup

While restoring a backup on another website, an error may occur stating:

“Could not restore query. MySQL has returned the error code 1118, with message ‘Row size too large (> 8126)'”

Or alternatively,

“Your server reached the maximum row size of the table”

This issue is caused by InnoDB having reached the maximum row size and cannot store any more data on it.

Root Cause

The root cause of this issue is that the MySQL/MariaDB database engine (InnoDB) has a maximum row size limit. When this limit is exceeded, the database engine is unable to store any more data in that row. This limit is typically encountered when trying to import large datasets or when the table structure includes large VARCHAR fields.

General Steps to Solve the Row Size Too Large Error

Generally, the steps to fix this are:

  1. Change table fields with TEXT or BLOB instead of using large VARCHAR.
  2. Change table ROW_FORMAT to DYNAMIC.
  3. Set the innodb_strict_mode to off.

As these steps involve server configuration changes and altering the WordPress table structure, this is generally not recommended due to the potential for data loss and other issues.

Use a Filter to Make the Backup Restore Work

We aim to import the SQL data from the backup file to the current site. To achieve this objective, we set the innodb_strict_mode off during the restore process.

When this error occurs, you can add the code below into a Must-Use plugin (MU-plugin) to turn off the innodb_strict_mode:

PHP
<?php
add_filter('wpstg.backup.restore.innodbStrictModeOff', '__return_true');

Step-by-Step Instructions

  1. Connect to your WordPress site via FTP or File Manager in your hosting control panel.
  2. Navigate to the directory: wp-content/mu-plugins. If the mu-plugins directory does not exist, create it.
  3. Inside the mu-plugins directory, create a new PHP file. You can name it anything you like, for example, disable-innodb-strict-mode.php.
  4. Edit the newly created PHP file and add the following code:
PHP
<?php
add_filter('wpstg.backup.restore.innodbStrictModeOff', '__return_true');
  1. Save and close the file. Now, this code will run on every page load, ensuring that the innodb_strict_mode is turned off during the restore process.
  2. Retry the restore process from your backup plugin. The error should no longer occur.

Important Note

This is a workaround to bypass the MySQL row size limit during the restore process. It is not a permanent solution to the problem, as having the innodb_strict_mode turned off can lead to potential data integrity issues. After the restoring process, consider removing the MU-plugin or disabling the filter, and consult with a database expert for a more permanent solution to the row size limit problem.

Related Articles

Updated on July 19, 2023