How to disable MySQL Strict Mode

Introduction

In some versions of MySQL and MariaDB Strict Mode is enabled by default. There are some applications, such as WHMCS, that require you to disable MySQL strict mode. 

Strict Mode is described in the MySQL 8 documentation as follows:

MySQL :: MySQL 8.0 Reference Manual :: Server SQL Modes - Strict Mode

Strict mode controls how MySQL handles invalid or missing values in data-change statements such as INSERT or UPDATE. A value can be invalid for several reasons. For example, it might have the wrong data type for the column, or it might be out of range. A value is missing when a new row to be inserted does not contain a value for a non-NULL column that has no explicit DEFAULT clause in its definition. (For a NULL column, NULL is inserted if the value is missing.) Strict mode also affects DDL statements such as CREATE TABLE.

Strict SQL mode is in effect if either STRICT_ALL_TABLES or STRICT_TRANS_TABLES is enabled.

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.

Is it possible to change Strict Mode for a single user or database?

It is technically possible to change the sql_mode on a per session basis, meaning that the sql_mode would only be customized for the duration that the client has logged into the database. Modifying the sql_mode on a per session basis is not advisable or practical except for advanced applications where the systems administrator or developer implementing this type of usage is well versed in database administration.

Changing the sql_mode should generally be done on a server-wide basis to avoid complications and difficult to resolve problems. The method to modify the sql_mode server-wide is outlined in the below procedure.

Procedure

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

2. Check the current sql mode with the following command:

mysql -e 'select @@GLOBAL.sql.mode;'

For example:

# 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", or "STRICT_ALL_TABLES" strict mode is enabled. Copy the existing SQL Mode configuration that you find in step 2 above.

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. When updating or adding the sql_mode, use the sql_mode configuration you copied in step 3, but be sure to remove the option that was enabling strict mode.
For example:

sql_mode = "ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" 

6. Restart MySQL service

/scripts/restartsrv_mysql --force

7. Check the newly specified sql_mode again to be sure your change worked:

mysql -e 'select @@GLOBAL.sql.mode;'