Get the Best WordPress Backup
& Migration Plugin Today
Get Duplicator Now

Duplicator Documentation

Documentation, Reference Materials, and Tutorials for Duplicator

How to fix database write issues?

Issue AWrite Errors
If the destination server is on MySQL 5.7+ and the Duplicator log shows errors such as: **ERROR** database error write ‘…’then you may have issues when running on MySQL in strict mode. If this is the case try the following options:

Common Error Messages

– DB ERROR: Could not get the CREATE query for the table wp_XYZ. Incorrect information in file: ‘./database_store/wp_XYZ.frm’
– DATABASE ERROR: This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging…
– Tablespace for table xxx exists. Please DISCARD the tablespace before IMPORT

Solution 1For general write errors there are a few options to consider. Try these in the order they are presented.

The quickest solution is OPTION A, however for the option to be permanent on the server consider OPTION B.

  1. Duplicator Lite
    Installer Step 2 > Options Section > Mode > Custom > enter NO_ENGINE_SUBSTITUTION in the text box.
    Duplicator Pro
    Installer Step 1 > Advanced Mode > Options > Database > Mode > Custom > Add NO_ENGINE_SUBSTITUTION.
  2. Continue with and complete the install process.
  3. If this does not work see OPTION 2

If OPTION A does not work try the following:

  1. Open this file in notepad: C:\wamp64\bin\mysql\mysql5.7.9\my.ini
  2. Add the final line to the file:[mysqld]
    port = 3306
  3. Restart the MySQL Server (budget hosts may need to contact hosting provider)

If OPTIONS A/B do not work, do the following:

  1. SSH into your server as root and create the file /etc/mysql/conf.d/disable_strict_mode.cnf
  2. Open the file and enter these two lines:[mysqld]
  3. Restart MySQL by executing: :$ sudo service mysql restart

The above procedure is from

Solution 2Resolve write issues for create queries related to the following error message:
EXCEPTION message: DB ERROR: Could not get the CREATE query for the table wp_XYZ. Incorrect information in file: './database_store/wp_XYZ.frm'

  1. Run a database repair on your current WordPress site.
  2. On Installer Step 2 > Tables Tab > Uncheck the Import flag for the table generating the issue
  3. Alternatively When creating a package on Step 1 exclude the table in question from being added

Solution 3Resolve write issues for functions related to the following error message:DATABASE ERROR: This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the lesssafe log_bin_trust_function_creators variable)Execute the following in the MySQL console OR add the following to the mysql.ini configuration file:SET GLOBAL log_bin_trust_function_creators = 1;log_bin_trust_function_creators = 1The setting relaxes the checking for non-deterministic functions. Non-deterministic functions are functions that modify data (i.e. have update, insert or delete statement(s)).For more info, see here. For complete details seethis article.

If your hosting provider does not allow the settings above then contact them directly or consider working with the developer who wrote the Functions and have them work properly with the database engines requirements.

Solution 4During an install if you receive a message such as the one shown below, its likely you may need to delete one of the tablename.ibd files, that did not properly get deletedduring the database table removal process.Tablespace for table xxx exists. Please DISCARD the tablespace before IMPORTFor a full run-down of all the possible solutions for this issue seethis stack-overflow article.

Issue BDatabase Creation Failure
A message such as “Unable to create database…” is recevied with trying to use the create database action.

Solution 1“Unable to create database…” typically means your host does not support creating databases through PHP code. In this case you will need to create a database through the tools they provide.On most hosts you can do this through the cpanel. Please contact your host for how to create a new database. Duplicator Pro users should be able to use the cPanel connection tab tologin to there cPanel accounts and create the database through the cPanel installer interface. Alternatively you can use the Import or Overwrite modes.

Issue C Compatibility Issues
In order to avoid database incompatibility issues make sure the database versions between the build and installer servers are as close as possible. If the package was created on a newer database version than where it is being installed then you might run into issues.

It is best to make sure the server where the installer is running has the same or higher version number than where it was built. If the major and minor version are thesame or close for example [5.7 to 5.6], then the migration should work without issues. A version pair of [5.7 to 5.1] is more likely to cause issues unless you have avery simple setup. If the versions are too far apart work with your hosting provider to upgrade the MySQL engine on this server.

Solution 1UTF8MB4 FORMAT:
If the current MySQL server version detected is below 5.5.3 (release on April 8th 2010) then support for utf8mb4 tables will not work. The utf8mb4 format is only supportedin MySQL server 5.5.3+. It is highly recommended to upgrade your version of MySQL server on this server to be more compatible with recent releases of WordPress and avoid issueswith install errors.

Solution 2UTF8 INPUTS:
If non ASCII characters are detected as part of the database connection string and MySQLserver is not configured correctly then you will not be able to connect. This issue can be resolved by updating the MySQL my.ini configuration.Add the setting ‘character_set_server=utf8’ under [mysqld] and restart the database server. This value may just need to be uncommented.

If a version of 10.N.N shows then the database distribution is a MariaDB flavor of MySQL. While the distributions are very close there are some subtle differences.Some operating systems will report the version such as “5.5.5-10.1.21-MariaDB” showing the correlation of both. Please visit the onlineMariaDB versus MySQL – Compatibility page for more details.

Please note some messages are simply notices. It is highly recommended to continue with the install process and closely monitor the installer-log.txt filealong with the install report found on step 3 of the installer. Be sure to look for any notices/warnings/errors in these locations to validate the install processdid not detect any errors. If any issues are found please visit the FAQ pages and see the questionHow to resolve database errors or general warnings on the install report?

Creating tables across MySQL and MariaDB are very close however there are some small differences that can sometimes (not common) cause issues. To visit the different between eachsee the two sets of documentation here:

As you can see they are not a one-to-one match (MariaDB has options that MySQL does not), so when you try to run a CREATE query that worked on MariaDB, but has some table option thatMySQL does not support, it may fail. The current fix is to remove the offending table options from the DB dump by doing a manual search-and-replace on the file.

Issue DTable Collation Issues
How to resolve compatibility mode & ‘Unknown collation’ errors?

There are 4 ways to solve this issue. Option 1 is the quickest, easiest and safest way to resolve this issue

Unknown Collation Overview:

An ‘Unknown collation’ error means the MySQL server being installed on (target server) is older than the MySQL engine youcreated the package on. An error message such as:

**ERROR** database error write ‘Unknown collation: ‘utf8mb4_unicode_ci’

will be triggered because the MySQL version being used does not support this table format. You can view the MySQL server version comparison by clicking on the ‘Test Connection’button in the installer or by viewing the installer-log.txt file. The output will look something like:

MYSQL VERSION: This Server: 5.4.22 — Build Server: 5.7.10

Option 1

Update Database Software

The cleanest and safest way to fix ‘Unknown Collation’ issue is to update your target MySQL server to the same or newer version than where thepackage was built. The most common format errors include:

  • utf8mb4_unicode_ci: Mysql server 5.5.3+ is required.
  • utf8mb4_unicode_520_ci: Mysql server 5.6+ is required.
Best Fix:The best and easiest way to solve this issue is to ask you’re host to move your account to a MySQL server that supports the collation type youneed (see example above). Simply give them the error message your getting and they can help move you to the correct server.

Most host will not upgrade the current server your site is on, however they should be able to easily move your account to a newer server.This is a very common practice and a good host should be able to help out pretty easily.

If you’re running any version lower than 5.5.3 then your using a very outdated version of MySQL server. 5.5.3 was released in 2010 which means the version of MySQL server you’re using is even older. We strongly encourage users to work with their hosts to use up-to-date software for improved security and performance and to stay in compliance with updates from WordPress core. Don’t let a crappy host use outdated software on your site.

Option 2

Enable Legacy Check

On the installer in step 2 under options is a setting titled “Legacy” and labeled “Apply legacy collation fall-back support for unknown collations types”.Check this check box to apply a fall back legacy collation and continue with the install.

When creating a database table, the Mysql version being used may not support the collation type of the Mysql version where the table was created.In this scenario, the installer will fall-back to a legacy collation type to try and create the table. This value should only be checked if you receive anerror when installing the database.

For example, if the package was built on MySQL 5.7 with a table collation type of ‘utf8mb4_unicode_520_ci’ and your runing the installeron an older MySQL 5.5 engine that does not support that type then an error will be thrown. If this option is checked then the legacy setting will try touse ‘utf8mb4_unicode_520’, then ‘utf8mb4’, then ‘utf8’ and so on until it runs out of options. While this option does work on most setups, option 1 aboveis still the recommended and safest option.

Option 3

Run Compatibility Mode

Mysqldump Compatibility mode helps produce a database sql script output that is more compatible with other database systems or with older MySQL servers.The Duplicator supports several compatibility modesall of which are only available when mysqldump mode is enabled in the plugin. Compatibility mode can be enabled when building a package using themysqldump option.

The Duplicator will only allow compatibility mode during the creation process. It is not allowed when creating templates or with schedules.The reason for this setup is to discourage using these settings and fix the real issue which is to update your database server.

Database Upgrades:Compatibility Mode should only be used as an last resort. The recommended approach to fixing database issues is to makesure that both database servers are up-to date and very close in version numbers (see OPTION 1). For example if the database server where youcreate the package (database) is 5.7.5 then the database server where you install the package should be at least in the 5.7.N range.Ask your host or server admin to upgrade the mysql server to a newer version if possible. If your host will not upgrade to a recent upgradeask them to upgrade to as high of a version as possible.

Data Preservation:While there are several work-a-rounds on the internet (Option 4) explaining how you can modify the current SQL file or use mysql dump compatibility mode, the mainunderlying issue is that you stored data in one format and are now going to convert it. These techniques can lead to data loss and incompatibility issueslater down the road. The correct approach is to upgrade the MySQL software.

Consider trying both Modes:

Duplicator currently supports two different modes. Mysqldump and PHP Mode. See Settings > Packages option. If you are running into character issues and running out of optionsyou may consider trying to build the database in both modes to see if one or the other solves the issues.

Option 4

Manual Configurations

These options are for advanced users and should be performed with caution. The options require updating the database.sql script orupdating the source database table collation types. Below are three different approaches but only one option is required to address the issue.

Update database.sql:
For ‘unknown collation’ issues you can try the follow the steps below:

  1. Extract the archive zip file manually
  2. Open the database.sql file with notepad and Search & Replace on the collations for example you might :
    • replace: ‘utf8mb4’ with ‘utf8’
    • replace: ‘utf8_unicode_520_ci’ with ‘utf8_general_ci’
    • Always try these options in a sandbox first and never on a production site until your content with the results
  3. Then browse to the installer.php file on the server and choose Advanced & check ‘Manual Extract’
  4. This can also be attempted with other collations i.e. replace ‘collation_type_1’ with ‘collation_type_2’

Update Source Database with Code:
This options requires that you really understand your system and have made backups ahead of time. The script will allow you to alter the collationon the build server so that it can be supported by the target server. The full script can be seen on stackoverflow at:

Update Source Database with IDE:
If you don’t feel comfortable running the code above you can also use an IDE or GUI tool that will allow you to manually change the tables collationeither with SQL statements or through and interface. Again these options requires that you really understand your system and have made backups ahead of time.Below are some easy to follow step by step for updating the collations via an IDE such as phpmyadmin:

Also see:
Recommended hosting providers for Duplicator?

Was this article helpful?

Related Articles