Update SQL Mode In MySQL
Shows how to update the SQL Mode in MySQL and preserve it on server restart. The sql_mode value should remain same on restarting the MySQL server.
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.
The default value of the variable sql_mode in MySQL 8 is as shown below.
# Default - sql_mode
In my case the option ONLY_FULL_GROUP_BY is breaking the code, hence I will remove it for demonstration
We can directly update the value of sql_mode using the commands as shown below.
mysql -u root -p
SET GLOBAL sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION";
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.
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"
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
sudo /etc/init.d/mysql restart
# Using service
sudo service mysql restart
# Using systemd
sudo systemctl restart mysqld.service
sudo systemctl restart mysql
In this tutorial, we have updated the value of the MySQL variable sql_mode and also preserved it permanently on the server restart.