MySQL 5.7 introduced a new feature known as “Strict Mode” to ensure data integrity and to prevent certain data inconsistencies. However, this feature may cause compatibility issues with older applications designed for MySQL 5.6 or earlier. This article will guide you on how to revert MySQL 5.7 strict mode to 5.6 compatibility.
To revert MySQL 5.7 strict mode to 5.6 compatibility, you need to disable strict mode in MySQL 5.7 by creating a new configuration file and modifying it to disable strict mode settings. Restart MySQL to apply the changes.
Understanding MySQL Strict Mode
Strict mode controls how MySQL handles invalid or missing values in data-change statements such as INSERT or UPDATE. In MySQL 5.7, strict mode is enabled by default, and if a value is missing or invalid, MySQL returns an error and the statement fails. In contrast, MySQL 5.6 or earlier versions, not operating in strict mode, would adjust the value to fit and issue a warning.
Disabling Strict Mode
To revert to the MySQL 5.6 behavior, you need to disable strict mode in MySQL 5.7. Here’s how to do it:
Step 1: SSH into your server
First, you need to SSH into your server as root. The root user has all privileges and can perform any operation on the database.
Step 2: Create a new configuration file
Next, create a new configuration file named disable_strict_mode.cnf in the /etc/mysql/conf.d/ directory. This directory is used for user-defined configurations.
/etc/mysql/conf.d/disable_strict_mode.cnfStep 3: Modify the configuration file
Open the disable_strict_mode.cnf file and add the following lines under the [mysqld] section:
sql_mode=NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTIONThis line sets the sql_mode system variable, which controls the server SQL mode. The values specified here disable the strict mode settings STRICT_TRANS_TABLES and ONLY_FULL_GROUP_BY that were added in MySQL 5.7.
Step 4: Restart MySQL
Save the file and restart MySQL using the following command:
sudo service mysql restartThis command restarts the MySQL service, applying the changes you made to the configuration file.
Verifying the Changes
To confirm that strict SQL mode is disabled, run the following command as root:
sudo mysql -i -BN -e 'SELECT @@sql_mode' | grep -E 'ONLY_FULL_GROUP_BY|STRICT_TRANS_TABLES'If strict mode is disabled, you won’t see any output from this command.
Reverting the Changes
If you encounter any issues after disabling strict mode, you can enable it again by deleting the disable_strict_mode.cnf file and restarting MySQL.
Conclusion
This article has shown you how to revert MySQL 5.7 strict mode to 5.6 compatibility. Please note that these steps are specific to MySQL 5.7 and may not work for other versions. Always make sure to backup your data before making any changes to your database settings.
Strict mode in MySQL 5.7 controls how the database handles invalid or missing values in data-change statements. If strict mode is enabled, MySQL returns an error and the statement fails. If it is disabled, MySQL adjusts the value to fit and issues a warning.
You might need to revert MySQL 5.7 strict mode to 5.6 compatibility if you have older applications that were designed for MySQL 5.6 or earlier. These applications may not be compatible with the strict mode settings in MySQL 5.7, causing issues or failures in data-change statements.
To disable strict mode in MySQL 5.7, you need to create a new configuration file named disable_strict_mode.cnf in the /etc/mysql/conf.d/ directory. In this file, add the line sql_mode=NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION under the [mysqld] section. Finally, restart MySQL for the changes to take effect.
To verify if strict SQL mode is disabled in MySQL 5.7, you can run the command sudo mysql -i -BN -e 'SELECT @@sql_mode' | grep -E 'ONLY_FULL_GROUP_BY|STRICT_TRANS_TABLES' as root. If strict mode is disabled, you won’t see any output from this command.
If you encounter any issues after disabling strict mode, you can revert the changes by deleting the disable_strict_mode.cnf file that you created and then restarting MySQL. This will enable strict mode again.
These steps are specific to MySQL 5.7 and may not work for other versions. The configuration and steps may vary depending on the version of MySQL you are using. It’s always recommended to consult the official documentation or seek assistance from a MySQL expert when working with different versions.
