Set variable innodb_strict_mode to OFF on target | How-to

Set variable innodb_strict_mode to OFF on target like a pro with help from Bobcares.

At Bobcares, we offer solutions for every query, big and small, as a part of our Server Management Service.

Let’s take a look at how our Support Team is ready to help customers set variable innodb_strict_mode to OFF on target.

How to set variable innodb_strict_mode to OFF on target

You may have come across the following error message during the course of a migration pre-check on the target server:

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

Upon investigation, our Support Team has come to the conclusion that this error is due to the default enabling of Strict mode in the newer versions of MySQL/MariaDB.

For Linux: Set variable innodb_strict_mode to OFF

1. First, we have to connect to the target server with the help of SSH.
2. Next, we add the innodb_strict_mode to MySQL by opening the /etc/my.cnf in a text editor.
3. Then, we have to add innodb_strict_mode=OFF under the [mysqld] section.
4. After that, save the changes and close the text editor.
5. Now, it is time to restart the MySQL service with the command:
6. # service mysql restart || service mariadb restart || service mysqld restart
7. Finally, it is time to proceed with the migration.

For Windows: Set variable innodb_strict_mode to OFF

1. First, we have to connect to the target server with the help of RDP.
2. Then, open %plesk_dir%Databases\MySQL\my.ini file in a text editor and add innodb_strict_mode=OFF under the [mysqld] section.
3. Next, save the changes and close the text editor.
4. After that, MySQL service with the help of Plesk Services Monitor (found in the system tray).
5. Finally, it is time to proceed with the migration.

[Looking for a solution to another query? We are just a click away.]

Conclusion

To sum up, our skilled Support Engineers at Bobcares demonstrated how to set variable innodb_strict_mode to OFF on target.