MySQL has changed the default value of the variable sql_mode since previous releases which is breaking the existing code in several applications. This tutorial explains how to change the sql_mode according to the application features. It also provides the steps to preserve this value on the server restart.
Notes: You may also follow the MySQL tutorials - How To Install MySQL 8 on Ubuntu, How To Completely Remove MySQL From Ubuntu, and Learn Basic SQL Queries Using MySQL.
The default value of the variable sql_mode in MySQL 8 is as shown below.
# Default - sql_mode ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
In my case the option ONLY_FULL_GROUP_BY is breaking the code, hence I will remove it for demonstration
Update Directly
We can directly update the value of sql_mode using the commands as shown below.
# Login mysql -u root -p
# Globally SET GLOBAL sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION";
# Session
SET SESSION sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION";
The sql_mode value will be changed using the above-mentioned queries. The issue is that the variable gets back it's default value on server restart. We can preserve the value as shown in the next section.
Update Permanently
We can preserve the value of sql_mode by updating my.cnf file. The location of this file differs based on the server environment and version. The most common locations of this file include/etc/my.
On Linux Mint 18, the file location is /
# Search the default config mysqld --verbose --help | grep -A 1 "Default options"
# Output
Default options are read from the following files in the given order: /etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf
The MySQL server will look at the above-mentioned locations for the default configurations both at the system level and local level. Update my.cnf file as shown below. I have used the nano editor for demonstration
# Update my.cnf sudo nano /etc/mysql/my.cnf
# Scroll down and update the file as shown below
...
... # # The MySQL Server configuration file. # # For explanations see # http://dev.mysql.com/doc/mysql/en/server-system-variables.html # * IMPORTANT: Additional settings that can override those from this file! # The files must end with '.cnf', otherwise they'll be ignored. # !includedir /etc/mysql/conf.d/ !includedir /etc/mysql/mysql.conf.d/ [mysqld] sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"
Now save the file, and exit the editor. Make sure to include [mysqld] above the variable.
Restart the MySQL server to set the value of the variable sql_mode as configured by us in my.cnf file as shown above.
# Using init sudo /etc/init.d/mysqld restart # OR sudo /etc/init.d/mysql restart
# Using service sudo service mysql restart
# Using systemd sudo systemctl restart mysqld.service # OR sudo systemctl restart mysql
Summary
In this tutorial, we have updated the value of the MySQL variable sql_mode and also preserved it permanently on the server restart.