How to Disable MySQL Strict Mode in CentOS 7.X ?

MySQL’s, and MariaDB’s, strict mode controls how invalid or missing values in data changing queries are handled; this includes INSERT, UPDATE, and CREATE TABLE statements. With MySQL strict mode enabled, which is the default state, invalid or missing data may cause warnings or errors when attempting to process the query.

When strict mode is disabled the same query would have its invalid, or missing, values adjusted and would produce a simple warning.

SQL_MODE is a string with different options separated by commas (‘,’) without spaces. The options are case insensitive.

Kindly follow the below procedure to Disable MySQL Strict Mode.

Strict Mode Enabled :-

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

Strict Mode Disabled :-

sql_mode=”NO_ENGINE_SUBSTITUTION”

Step : 1

2. Home > SQL Services > phpMyAdmin

3. Go to Variables

4. Choose sql mode

5. Change the sql mode to sql_mode=”NO_ENGINE_SUBSTITUTION”

6. Click on Save

Step : 2

Now login to SSH,

2. edit the “my.cnf” file

vi /etc/my.cnf

3. sql_mode=”NO_ENGINE_SUBSTITUTION”

4. Restart the MySQL Service

service mysqld stop
service mysqld start

5. Verify strict mode has been disabled:

Command :

mysql -e “SELECT @@sql_mode;”

Result :
+————————+
| @@sql_mode |
+————————+
| NO_ENGINE_SUBSTITUTION |
+————————+

That’s all, Strict Mode has been disabled succesfully !!