This article provides detailed instructions on resolving various MySQL database issues including write errors, database creation failures, compatibility concerns, and table collation errors.
Database Write 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 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 1
For 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.
OPTION A
- Enable the Advanced mode of the installer.
- Under Options » Database » Extraction Setings, select Custom for the Mode settings, and enter NO_ENGINE_SUBSTITUTION in the text box.
- Continue with and complete the install process.
OPTION B
- Open this file in notepad: C:\wamp64\bin\mysql\mysql5.7.9\my.ini
- Add the final line to the file:
[mysqld]
port = 3306
sql_mode=NO_ENGINE_SUBSTITUTION - Restart the MySQL Server (budget hosts may need to contact their hosting provider).
If OPTIONS A/B do not work, do the following:
- SSH into your server as root and create the file /etc/mysql/conf.d/disable_strict_mode.cnf.
- Open the file and enter these two lines:
[mysqld]
sql_mode=IGNORE_SPACE, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION - Restart MySQL by executing:
$ sudo service mysql restart
Solution 2
Resolve 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’
- Run a database repair on your current WordPress site.
- On Installer Step 2 » Tables Tab » Uncheck the Import flag for the table generating the issue.
- Alternatively, when creating a backup on Step 1, exclude the table in question from being added.
Solution 3
Resolve 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)
Or, Add the following to the mysql.ini configuration file:
Execute the following in the MySQL console:
SET GLOBAL log_bin_trust_function_creators = 1;Or, Add the following to the mysql.ini configuration file:
log_bin_trust_function_creators = 1
The 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 see this 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 re-evaluated to work properly with the database engine’s requirements.
Solution 4
During an install if you receive a message such as the one shown below, it’s likely you may need to delete one of the tablename.ibd files, that did not properly get deleted during the database table removal process.
For a full run-down of all the possible solutions for this issue see this stack-overflow article.
Database Creation Failure
A message such as “Unable to create database…” is received when trying to use the create database action.
Solution
“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 instructions on how to create a new database. Duplicator Pro users should be able to use the cPanel connection tab to login to their cPanel accounts and create the database through the cPanel installer interface. Alternatively you can use the Import or Overwrite modes.
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 backup 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 versions are the same 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 a very simple setup. If the versions are too far apart work with your hosting provider to upgrade the MySQL engine on this server.
Solution 1: UTF8MB4 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 supported in 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 issues with install errors.
Solution 2: UTF8 INPUTS
If non-ASCII characters are detected as part of the database connection string and MySQL server 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.
Solution 3: MARIA-DB SETUPS
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 online MariaDB 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 file along 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 that the install process did not detect any errors. If any issues are found please visit the FAQ pages and see the question “How to resolve database errors or general warnings on the install report?”
Solution 4: CREATE TABLES
Creating tables across MySQL and MariaDB are very close; however, there are some small differences that can sometimes (not common) cause issues. To view the differences between each, see 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 that MySQL 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.
Table 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 you created the backup 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 the “Unknown Collation” issue is to update your target MySQL server to the same or a newer version than where the backup 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 your host to move your account to a MySQL server that supports the collation type you need (see example above). Simply give them the error message you’re getting and they can help move you to the correct server.
Most hosts 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 you’re 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 – Default Charset and Collation
On the installer in step 2 under options, the ‘Legacy’ checkbox no longer exists. However, it is now detected and set automatically. If the charset and collation are not explicitly defined, a notice will appear stating that the default charset and collation will be used.
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 automatically fallback collation type to try and create the table.
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 modes, all of which are only available when mysqldump mode is enabled in the plugin. Compatibility mode can be enabled when building a backup using the mysqldump 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 a last resort. The recommended approach to fixing database issues is to make sure that both database servers are up to date and very close in version numbers (see Option 1). For example, if the database server where you created the backup is 5.7.5 then the database server where you install the backup 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 upgrade, ask them to upgrade to as high of a version as possible.
Data Preservation: While there are several work-arounds on the internet (Option 4) explaining how you can modify the current SQL file or use mysqldump compatibility mode, the main underlying 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 issues later 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 » Backups option).
- If you are running into character issues and running out of options, you 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 or updating the source database table collation types. Below are three different approaches; only one option is required to address the issue.
Update database.sql
For “unknown collation” issues you can try the following steps:
- Extract the archive zip file manually.
- 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 you are comfortable with the results.
Then browse to the installer.php file on the server and choose Advanced & check “Manual Extract”.
This can also be attempted with other collations (i.e. replace ‘collation_type_1’ with ‘collation_type_2’).
Update Source Database with Code
This option requires that you really understand your system and have made backups ahead of time. The script will allow you to alter the collation on the build server so that it can be supported by the target server. The full script can be seen on Stack Overflow at: http://stackoverflow.com/a/29939906/2808424
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 table collations either with SQL statements or through an interface. Again, these options require that you really understand your system and have made backups ahead of time.
Below are some easy to follow step by step instructions for updating the collations via an IDE such as phpMyAdmin:
Also see:
Recommended hosting providers for Duplicator?
http://dev.mysql.com/downloads/mysql
https://make.wordpress.org/core/2015/04/02/the-utf8mb4-upgrade