How to disable MySQL Strict Mode

Introduction

On MySQL 5.7, strict mode is enabled by default.  Often at time, there are some applications, such as WHMCS that would requires you to disable MySQL strict mode. 

Here are some of the most common errors related to MySQL strict mode:

This behavior is due to Strict Mode being enabled in your MySQL server configuration.

Please disable MySQL Strict Mode in your server configuration in order for WHMCS to function correctly.

Critical: MySQL enables "strict mode" by default as of version 5.7. Strict mode controls how MySQL handles invalid or missing values in data-change statements such as INSERT or UPDATE. Applications not built with strict mode enabled may cause undesired behavior; please verify applications using MySQL are compatible before upgrading. More information about strict mode is available here.

Below is the complete instruction on how to do so. 

Procedure

1. Log into your server with your favorite SSH client or you can access into WHM -> Terminal as root

2. You can check the sql mode by the following command:

# mysql -e 'select @@GLOBAL.sql_mode;'
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@GLOBAL.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 |
+-------------------------------------------------------------------------------------------------------------------------------------------+

3. If you see the option "STRICT_TRANS_TABLES", this means STRICT MODE is enabled. 

4. Open up /etc/my.cnf with your favorite text editor (VIM, NANO)

5. Look for sql_mode, if there is no sql_mode, add it at the end of the file

sql_mode = "NO_ENGINE_SUBSTITUTION" 

6. Restart MySQL service

/scripts/restartsrv_mysql --force