Disable MySQL Strict mode

Posted on October 23rd, 2015

To disable strict mode on cpanel servers in mysql 5.6

edit /etc/my.cnf

add in under [mysqld]

sql_mode=NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

for example

[mysqld]
default-storage-engine=MYISAM
local-infile=0
innodb_file_per_table=1
max_allowed_packet=268435456
query_cache_limit=4M
query_cache_size=128M
query_cache_type=1
innodb_flush_log_at_trx_commit=2
innodb_lock_wait_timeout=200
max_connections=500
max_user_connections=45

# innodb_force_recovery = 6
open_files_limit=10000
sql_mode=NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

restart mysql with /etc/init.d/mysql restart

To confirm test with the following command:

mysql> SELECT @@sql_mode;
+—————————————————————————————————-+
| @@sql_mode |
+—————————————————————————————————-+
| NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+—————————————————————————————————-+
1 row in set (0.00 sec)

If you see STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION strict mode is still enabled.

Leave a Reply