Knowledge Base

Plesk pre-migration check finishes with an error: Set variable innodb_strict_mode to OFF on target to avoid the migration errors

Symptoms

One of the following errors appear during a migration pre-check on the target (destination) server:

Failed to check MySQL InnoDB strict mode.
Command execution failed on the source server 'source' (203.0.113.2) with non-zero exit code.
command: mysql -h localhost -P 3306 -uadmin -p'***hidden***' --silent --skip-column-names -e 'SHOW VARIABLES LIKE '"'"'innodb_strict_mode'"'"''
exit code: 1
stdout:
stderr: ERROR 1045 (28000): Access denied for user 'admin'@'localhost' (using password: YES)

In InnoDB 5.6 (both in MySQL 5.6 and MariaDB 10.0/10.1) server has variable innodb_strict_mode=0 by default.
So you can create table with wrong ROW_FORMAT option and warning will be reported.
In InnoDB 5.7 (both in MySQL 5.7 and MariaDB 10.2) server has variable innodb_strict_mode=1 by default.
Migration from source, hosted with previous version of InnoDB will not be possible to target
with current version of InnoDB and error will be issued if innodb_strict_mode is set to default.
Set variable innodb_strict_mode to OFF on target to avoid the migration errors.
Here is the link describing the problem
https://jira.mariadb.org/browse/MDEV-11305

Here is a list of affected subscriptions:
- example.com

Cause

Strict mode is enabled on the newer versions of MySQL or MariaDB by default (those on the target server) and this causes incompatibility with older versions of MySQL or MariaDB (those on the source server)

Resolution

In order to resolve the issue, you must disable strict mode for the database server that resides on the target (destination server) by following these steps:

For Linux

1. Connect to the target (destination) server via SSH

2. Edit the MySQL configuration file /etc/my.cnf (or /etc/mysql/my.cnf on Debian-based operating systems) with your favorite command-line text editor.

3. Add innodb_strict_mode=OFF under the [mysqld] section (create the section if necessary):

[mysqld]
innodb_strict_mode=OFF

4. Save the changes and close the file.

5. Restart the MySQL service by executing the following command:

# service mysql restart || service mariadb restart || service mysqld restart

4. Proceed with the migration.

Note: Once the migration will finish, it is safe to enable back the innodb_strict_mode option by removing the lines that were added.

For Windows

Connect to the target server via RDP.
Open MySQL configuration file %plesk_dir%DatabasesMySQLmy.ini in a text editor.
2.1 Add innodb_strict_mode=OFF under the [mysqld] section:
[mysqld]
innodb_strict_mode=OFF

2.3. Save the changes and close the file