Change SQL mode in MySQL

If your app was written for older versions of MySQL and is not compatible with the strict SQL mode defaults in MySQL 5.7+, you can change the SQL mode in MySQL.

App compatibility issues related to the stricter SQL mode settings are usually caused by the ONLY_FULL_GROUP_BY and STRICT_TRANS_TABLES options being enabled by default in the sql_mode of MySQL 5.7+.

For apps that are not compatible with the SQL mode defaults in MySQL 5.7+, your apps may have SQL errors such as the following.

SQLSTATE[42000]: 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

Disable strict SQL mode

To disable strict SQL mode, SSH into your server as root and create the following file.

/etc/mysql/conf.d/disable_strict_mode.cnf

Add the contents below to the file.

[mysqld]
sql_mode=IGNORE_SPACE,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

Finally, restart MySQL.

sudo service mysql restart

The configuration above does not enable the setting NO_AUTO_CREATE_USER which is rarely needed and has been removed from MySQL 8.0.

View the sql_mode setting

You can see the current value MySQL is using for the sql_mode setting by running the following command as root.

sudo mysql -i -BN -e 'SELECT @@sql_mode'