Is it a good idea to disable MySQL strict mode on the server?

Yes! Because many customers are facing with errors like “Field '<field_name>' doesn't have a default value” while managing some applications.

The root cause of this error is that some software applications like WHMCS do not require MySQL strict mode to be enabled on the server.

At Bobcares, we often get requests from our customers to disable MySQL strict mode as part of our Server Management Services.

Today, let’s get into the details on how our Support Engineers disable MySQL strict mode and fix the related errors.

Reasons for disabling MySQL strict mode

Strict mode controls how invalid or missing values are handled when creating or updating new records on a MySQL database.

By default, MySQL strict mode​ is active on the server. So, it may arise worrying warnings when a user tries to save records with blank values on certain fields.

As a result, it may not save/update the records which affect the normal operation of a website or web application.

Therefore, Disabling MySQL mode is necessary for certain applications for the proper working. When we disable the MySQL strict mode​ , it allows queries with missing or invalid values to be executed.

How we disable​ MySQL strict mode​

Here, let’s see how our Support Engineers disable​ MySQL strict mode​ on the server.

1. initially, we log into the server as a root user.

2. Then, we make sure that if strict mode is enabled on the MySQL server. For that, we log as mysql user.

mysql -u root -p

3. And, we run the following command on a terminal.

SHOW VARIABLES LIKE 'sql_mode';

4. A table with some sql mode values separated by commas will display on the screen as shown below. If it contains “STRICT_TRANS_TABLES” value, it means MySQL strict mode​ is active now.

5. If so, we create a new configuration file using nano or any other editor.

nano /etc/mysql/conf.d/disable_strict_mode.cnf

6. Next, we enter the below text into the file and save the changes.

[mysqld]
sql_mode=IGNORE_SPACE,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

7, Also, we restart MySQL service.

service mysql restart

8.  If strict mode is disabled, the output doesn’t contain a value like “STRICT_TRANS_TABLES” after running the below command.

SHOW VARIABLES LIKE 'sql_mode';

This is how we successfully disabled the MySQL strict mode​.

In addition, to disable strict mode in cPanel servers, we update the same code as above in /etc/my.cnf.

MySQL disable strict mode​ – How we fixed

At Bobcares, where we have more than a decade of expertise in managing servers, we see that many customers face problems while managing the MySQL server with certain applications.

Now, let’s see the major reasons for MySQL errors and how our Support Engineers fix this error.

Incompatible applications

Recently, One of our customers had a MySQL server problem with his web application. While updating some data to the MySQL server, he received an error like,

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of
SELECT list is not in GROUP BY clause and contains nonaggregated column
'yourdbname.tblannouncements.date' which is not functionally dependent on
columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

On checking, our Support Engineers found that the MySQL server was running in strict mode and the app wasn’t compatible with strict SQL mode too. So, we disabled the strict mode​ on the MySQL server.

This solved the error.

MySQL service

Similarly, many cPanel customers reported to us that the strict mode​ showed as enabled after adding the code to disable the strict mode. He added the code
sql_mode=IGNORE_SPACE,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION in the MySQL configuration file /etc/my.cnf.

Mainly, this occurs when the changes do not take effect after the code has been added to the MySQL configuration file. So, it is necessary to restart the MySQL service. Otherwise, it will only read the configuration files when it initially loads up.

By restarting the MySQL service, we are forcing MySQL to use the new configuration files. For that, we use the following command.

/scripts/restartsrv_mysql

That’s it.

[Need assistance disable strict mode​? We’ll help you.]

Conclusion

In short, some applications like WHMCS needs to disable MySQL strict mode to work properly. Today, we saw how our Support Engineers disabled strict mode for our customers and solved the related errors.