What's the correct way to revert Mysql 5.7 strict mode back to how it was in 5.6?

Question

I've upgraded our server to Ubuntu 16 which includes Mysql 5.7 and, by default, Strict Mode is enabled (although there is no entry for it in any of the configuration files).

We're having issues importing databases that were in production under mysql 5.6 and earlier, and it's because of the strict mode. By default, here is what's enabled:

STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY

How can I configure mysql 5.7 to work exactly as it did in 5.6 or so that databases from 5.6 are compatible with 5.7?

Answer 1

To disable strict SQL mode, SSH in to your server as root and create this 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 with this command:

sudo service mysql restart

This change disables two SQL mode settings, STRICT_TRANS_TABLES and ONLY_FULL_GROUP_BY, that were added in MySQL 5.7 and cause problems for some older applications.

Confirming Strict SQL Mode Is Disabled

You can confirm strict SQL mode is disabled by running this 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 that command.

If disabling strict mode causes any problems for you, you can enable it again by deleting that file and restarting MySQL again.

Source: How to Disable Strict SQL Mode in MySQL 5.7

Answer 2

Set sql_mode in /etc/mysql/mysql.conf.d/mysqld.cnf without STRICT_TRANS_TABLES.

Add under [mysqld]:

sql_mode = NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION