How to change the SQL mode in MySQL or MariaDB

Introduction

To ensure compatibility with your applications, you may need to change the SQL mode.

 

Procedure

First, find your current SQL Mode

Connect to your server via SSH as root, then execute the following command.

 

# mysql

 

You will be presented with a new prompt. From here, you can list your current SQL Mode string.

 

mysql> SELECT @@sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

 

Copy this string, then exit the MySQL prompt.

mysql> exit;
Bye

 

Now, ensure the SQL Mode is set permanently

Add or remove the SQL modes from the string you gathered in the last step in your favorite text editor. You’ll need to edit /etc/my.cnf and restart MySQL or MariaDB to ensure the change takes effect.

 

# nano /etc/my.cnf

---

[mysqld]
...
sql-mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

 

Save this file, then restart the SQL service.

 

# /scripts/restartsrv_mysql