10

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?

34

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

  • 2
    This is a correct answer, and this should have been marked as accepted. – budhajeewa Oct 5 '16 at 4:53
  • Why does running the command mysql command returns: mysql: [ERROR] Found option without preceding group in config file /etc/mysql/conf.d/disable_strict_mode.cnf at line 1! ? – Abhishek Saini Nov 22 '16 at 17:40
  • 1
    @AbhishekSaini, take a look at this and this. – Bilal Nov 22 '16 at 18:00
  • 1
    This is a answer. Please refer this as a answer. – Lasitha Benaragama Feb 7 '18 at 10:38
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.