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

1. Connect to your server via SSH as the root user
2. Use the mysql command to get your current sql_mode value:

   [root@server ~]# mysql -e '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_ENGINE_SUBSTITUTION |
   +-----------------------------------------------------------------------------------------------------------------------+

3. Copy the return value and keep it accessible for later. Make whatever changes you need to the values here.
4. Using your preferred text editor, open the MySQL configuration file here:

   /etc/my.cnf

5. Place the modified sql_mode string from step 3 into the sql_mode value of the configuration file. If the value does not exist, you can create it:

   [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'

6. Save the file and restart the MySQL service:

   /scripts/restartsrv_mysql