Update SQL Mode In MySQL

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 purpose.

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.cnf, /etc/mysql/my.cnf, and /usr/etc/my.cnf. It may also be on the local path of the user ~/.my.cnf. On my system having Ubuntu 18.04 LTS and MySQL 8, I found this file at /etc/mysql/my.cnf.

On Linux Mint 18, the file location is /usr/my.cnf. You can also search the file using the command as shown below.

# 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 purpose.

# 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.