Skip to content

Instantly share code, notes, and snippets.

@cucxabeng

cucxabeng/disable_strict_MySQL.md

Last active March 17, 2017 10:03
Show Gist options
  • Save cucxabeng/eed25a77f051400c56b44043652c535d to your computer and use it in GitHub Desktop.
Save cucxabeng/eed25a77f051400c56b44043652c535d to your computer and use it in GitHub Desktop.
Download ZIP
Disable strict SQL mode
Raw
disable_strict_MySQL.md

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment