Skip to main content

How to change the SQL mode in MySQL or MariaDB

Alex Jankowiak
  • Updated

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'

 

If the server runs MySQL 8, you will need to adjust the mode not to include NO_AUTO_CREATE_USER as this is not supported on MySQL 8.

 

[mysqld]
...
sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'

 

Save this file, then restart the SQL service.

 

# /scripts/restartsrv_mysql

Related articles