How to Disable Strict SQL Mode in MySQL 5.7 and Ubuntu 16.04
If your app was written for older versions of MySQL and is not compatible with strict SQL mode in MySQL 5.7, you can disable strict SQL mode. For example, apps such as WHMCS 6 and Craft 2 do not support strict SQL mode.
To disable strict SQL mode, SSH in to your server as root and create this file:
Open the file and enter these two lines:
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 re-enable it by deleting that file and restarting MySQL again.
What Strict SQL Mode Errors Look Like
If your app isn't compatible with strict SQL mode, you'll often see SQL errors such as:
SQLSTATE: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'yourdbname.tblannouncements.date' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
Last updated: October 06, 2016
Comments area, use one of your social accounts to log-in and post a comment.
Responses to the post
- The Best Lightweight Linux Distributions For Older PC's
- How to write real client IP address in error Log with Varnish 4 and Apache 2.4 in Ubuntu 16.04
- How to Configure the Mod_Security Core Ruleset in Ubuntu
- Install Shotcut Video Editor in Ubuntu 16.04, 16.10
- How to Install Syncthing on Ubuntu 16.04
- Setup FTP server with VSFTPD in Ubuntu 16.04
- How to install and setup Varnish in Ubuntu 16.04
- Install Apache, MariaDB and PHP7 on Ubuntu 16.04
- How to force screen/s resolution in Ubuntu and make it permanent
- Install Memcache in Ubuntu 16.04
- Ubuntu servers security: 25 security tools to armor your system
- MySql: How to build a performant search engine