This article provides detailed instructions on resolving various MySQL database issues including write errors, database creation failures 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

The above procedure is from serverpilot.io.

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.

Tablespace for table xxx exists. Please DISCARD the tablespace before IMPORT

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.

Also see:
Recommended hosting providers for Duplicator?
http://dev.mysql.com/downloads/mysql
https://make.wordpress.org/core/2015/04/02/the-utf8mb4-upgrade