During the backup installation or restoration, the installer report may include errors and warnings related to the database. These errors are typically associated with data from the installer-data.sql dump file that fails to be inserted into the new database.

This article explains the database (SQL) errors that can occur during installation and offers a step-by-step guide on how to resolve them.

Write Errors

Queries that error during the installation are recorded in the installer log file. To review these errors, open the log file and search for the section titled “DATABASE RESULTS”. Errors will be marked with “**ERROR**”. The most common causes include:

1. Database Versions: Incompatibilities may occur if the MySQL versions differ significantly across servers. To minimize issues, ensure the destination server’s MySQL version is similar to or higher than the build server’s version.
2. MySQL Mode: The MySQL Mode can affect how the MySQL engine processes queries. The installer supports applying NO_ENGINE_SUBSTITUTION and other modes. We recommend initially setting NO_ENGINE_SUBSTITUTION if many database errors occur. To do so
   • On Step 1 of the installer under Advanced options, locate the “MySQL Mode” setting.
   • Select the ‘Custom’ radio button and enter NO_ENGINE_SUBSTITUTION.
     
   • Continue with the installation.
   • After the install completes, check the installer log (link available in the upper right-hand corner) for errors.

Experiment with various combinations to determine what works best for your server. Once you identify a working configuration, work with your host or server admin to set these values permanently in the my.ini file. A quick example of this process is provided here.

Important: If you see the text “database error write ‘Invalid default value…” in the installer-log.txt, it is a clear indication that you need to set the MySQL mode to NO_ENGINE_SUBSTITUTION.

Query Size Limit errors

If you encounter errors related to the ‘Query size limit’, it means the data payload exceeds what is allowed by your MySQL configuration. You will need to either manually post the values or update your MySQL server’s max_allowed_packet setting to handle larger payloads. If you are on a hosted server, contact your server administrator. More details can be found in the MySQL packet size documentation.

To resolve this yourself, edit your my.cnf (or my.ini) configuration file and add or update the setting max_allowed_package_size=2M. If this setting already exists, double its current value. Be sure to restart the MySQL server after making these changes.

Installer Report Overview

The installer report on the final step is designed to highlight potential issues with the installation. Note that in some cases, you might receive errors or notices that do not affect your site’s overall functionality. If your site is stable and the Deploy/Update result count is low (under 10), you can typically ignore these minor errors or notices.

1. Update Errors: These are basic query statements that encountered issues during execution on the new server. They may be caused by data integrity problems or incompatibilities between different MySQL versions. If errors appear here, inspect the query and debug the migration issue.
2. Table Key Notices: These notices occur when a table’s new URL value is detected but cannot be updated because there is no corresponding table key. Such values must be located and updated manually.
3. Serialization Notices: PHP serialization issues can occur when internal values change. Although Duplicator attempts to repair these serialized strings, in some rare cases they are not updated correctly.
   Serialization errors can happen for a variety of reasons. To fix a serialized string manually:
   1. Locate the problematic serialized string using the query provided in the installer report.
   2. Visit http://unserialize.onlinephpfunctions.com/.
   3. On the page, you might see a sample serialized string such as a:1:{s:4:"Test";s:17:"unserialize here!";}. Notice the segment s:4:"Test"; – it denotes a string with 4 characters. If you change “Test” to “Tes” and execute, it will generate an error. This error occurs because the numeric character count is out of sync with the actual string length.
   4. Adjust the string’s character count to match its actual length to fix the issue. This method works if the string wasn’t corrupted by another form of encoding. If it was, further encoding evaluation may be necessary.

Quick Check

If you receive any errors on the installer report, follow these steps:

1. Verify that your site is functioning properly. If your site is stable and the error count is low, you may simply need to re-save settings for a particular theme, plugin, or widget.
2. If you remain concerned, run the SQL queries provided in the installer report and review the error log to assess problematic queries. You can also perform this check using phpMyAdmin.

Also see:
How to fix database write issues?
How to fix database connection issues?
Recommended hosting providers for Duplicator?