How to Disable MySQL Strict Mode on Centos 7

Strict Mode in MySQL controls how MySQL handles invalid or missing values in statements such as INSERT and UPDATE. If strict mode is not in effect, MySQL inserts adjusted values for invalid/missing values and generates a warning. When strict mode is enabled, an error will be produced on invalid/missing values and no data will be inserted.

To best understand it, consider this example. When strict mode enabled and you create a key that exceeds maximum key length, the key will not be inserted in its field and an error will be displayed. When strict mode is not enabled, a truncated value will be inserted and a warning displayed instead.

For more information on MySQL Strict Mode, please visit the MySQL Manual on Strict Mode

To disable strict mode, follow the steps below.

1. Login to MySQL as root. You wont be able to siable Strict Mode without enough user permissions.

[root@vps657040 ~]# mysql -uroot -p

Enter password:

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MariaDB connection id is 10225

Server version: 10.1.38-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

1. Login to MySQL as root. You wont be able to siable Strict Mode without enough user permissions.

[root@vps657040 ~]# mysql -uroot -p

Enter password:

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MariaDB connection id is 10225

Server version: 10.1.38-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

2. Verify which SQL Modes are running

MariaDB [(none)]> SHOW VARIABLES LIKE 'sql_mode';

3. Disable Strict Mode

MariaDB [(none)]> SET GLOBAL sql_mode = 'NO_ENGINE_SUBSTITUTION';

4. Verify that Strict Mode is disabled

MariaDB [(none)]> SELECT @@GLOBAL.sql_mode;

5. Restart mysql

[root@vps657040 ~]# systemctl restart mysqld

You can also disable it via the my.cnf file as follows:

1. Locate my.cnf file. Common locations include /etc/my.cnf , /etc/mysql/my.cnf and /usr/my.cnf

2. Once you access the my.cnf file, look for [mysqld] section and navigate to the following line sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

3. Change the line in step 2 above to the following:

sql_mode=NO_ENGINE_SUBSTITUTION

If [mysqld] section isnt present, create it and add the code in step 3 just below [mysqld]

4. Restart mysql

[root@vps657040 ~]# systemctl restart mysqld