Trying to disable MySQL strict mode

[DougK94]

I need to turn off MySQL strict mode for a couple of applications to work properly and have been unsuccessful. System is as follows:

WHM 11.28.52
Centos 5.5
MySQL 5.1.51

I have tried editing etc/my.cnf

The original is:

[mysqld]
set-variable = max_connections=500
safe-show-database
local-infile=0

I have tried adding each of the below individually and restarting MySQL after each

sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
sql-mode=""
sql-mode="TRADITIONAL"

Any ideas on what I am doing wrong?

[cPanelDavidN]

Hi Doug,

I was able to modify the /etc/my.cnf with the desired results.

My Scenario:
1)added to /etc/my.cnf

[mysqld]
sql_mode="TRADITIONAL,NO_AUTO_CREATE_USER"

2)verified that the mode was previously set

root@054 [~]# mysql -e 'select @@GLOBAL.sql_mode;'
+-------------------+
| @@GLOBAL.sql_mode |
+-------------------+
|                   | 
+-------------------+

3) restart mysql

root@054 [~]# /scripts/restartsrv mysql
Waiting for mysql to restart...............finished.

mysqld_safe (/bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/054.cpanel.test.pid) running as root with PID 26370
mysqld (/usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/054.cpanel.test.pid --skip-external-locking) running as mysql with PID 26394

mysql started ok

4) check mysql

root@054 [~]# mysql -e 'select @@GLOBAL.sql_mode;'
+-------------------------------------------------------------------------------------------------------------------------------+
| @@GLOBAL.sql_mode                                                                                                             |
+-------------------------------------------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER | 
+-------------------------------------------------------------------------------------------------------------------------------+
root@054 [~]#

The only thing that stands out as a possible oversight is to make sure that you added the variable within the [mysqld] section of the my.cnf

If your problem persists, you may want to open a ticket with cPanel Technical Support.

Regards,
-DavidN

[cPanelDavidN]

Oh also, make sure you use "sql_mode" and not "sql-mode" for the variable key name in /etc/my.cnf.

[DougK94]

The "_" was the major problem I think.

[juniorich]

This can be done in two ways...

1- Open your "my.ini" file within the MySQL installation directory, and look for the text "sql-mode".

Find:

# Set the SQL mode to strict sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

Replace with:

# Set the SQL mode to strict sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

Or

2- You can run an SQL query within your database management tool, such as phpMyAdmin:

SET @@global.sql_mode= '';