Trying to disable MySQL strict mode

KLAMP

Member
Oct 22, 2013
5
0
1
cPanel Access Level
Website Owner
Hello, I can figure out how I'm getting the following warning




Strict SQL Mode Detected

Please disable strict SQL mode before continuing.

[Removed - Please paste in CODE tags or upload directly to the thread]
 
Last edited by a moderator:

cPanelMichael

Administrator
Staff member
Apr 11, 2011
47,880
2,256
463
Hello,

Could you elaborate on where you are receiving that warning message? Which entries are added to your /etc/my.cnf file?

Thank you.
 

musioc

Well-Known Member
Aug 4, 2011
127
1
68
Hi
It seams there is a problem in new cpanel or mysql version

We can not disable strict mode

Code:
[[email protected] /]# mysqladmin version
mysqladmin  Ver 8.42 Distrib 5.6.30, for Linux on x86_64
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Server version          5.6.30
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /var/lib/mysql/mysql.sock
Uptime:                 3 min 3 sec
mysql mode :
Code:
mysql> select @@GLOBAL.sql_mode;
+--------------------------------------------+
| @@GLOBAL.sql_mode                          |
+--------------------------------------------+
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+
1 row in set (0.00 sec)
changing my.cnf:
Code:
[mysqld]
default-storage-engine=MyISAM
innodb_file_per_table=1
performance-schema=0
local-infile=0       
max_connections = 750       
max_allowed_packet=268435456
open_files_limit=10000
sql_mode = ""
sql-mode = ""
check sql mode again:
Code:
[[email protected] /]# service mysql restart
Shutting down MySQL.... SUCCESS!
Starting MySQL. SUCCESS!
[[email protected] /]# mysql -e 'select @@GLOBAL.sql_mode;'
+--------------------------------------------+
| @@GLOBAL.sql_mode                          |
+--------------------------------------------+
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+
 

musioc

Well-Known Member
Aug 4, 2011
127
1
68
This problem exists in original cpanel mysql, original cpanel mariadb and cloudlinux mariadb (installed by mysqlgovernor)
 

rpvw

Well-Known Member
Jul 18, 2013
1,100
472
113
UK
cPanel Access Level
Root Administrator
MySQL / MariaDB reads the my.cnf configuration files in a preset order. See the SQL docs for full details

As well as an /etc/my.cnf , I found a /usr/my.cnf file that is read after the /etc/my.cnf

I added the line
Code:
sql_mode=""
as the last line of the /usr/my.cnf file and restarted sql and safe mode was disabled, and this configuration survives reboots and updates.
 

cPanelMichael

Administrator
Staff member
Apr 11, 2011
47,880
2,256
463
Hello,

Internal case CPANEL-5992 is open to address reports of user confusion regarding changes to the sql_mode MySQL configuration value when the /usr/my.cnf file is in-use. I'll update this thread with the outcome of this case once it's marked as resolved. In the meantime, you can manually modify the /usr/my.cnf to configure the value to your own preference.

Thank you.
 

musioc

Well-Known Member
Aug 4, 2011
127
1
68
MySQL / MariaDB reads the my.cnf configuration files in a preset order. See the SQL docs for full details

As well as an /etc/my.cnf , I found a /usr/my.cnf file that is read after the /etc/my.cnf

I added the line
Code:
sql_mode=""
as the last line of the /usr/my.cnf file and restarted sql and safe mode was disabled, and this configuration survives reboots and updates.
Thank you very much :)
This was my solution.
 

cPanelMichael

Administrator
Staff member
Apr 11, 2011
47,880
2,256
463
To update, the issue is addressed in cPanel 58 for new MySQL installations:

Fixed case CPANEL-6030: Update MySQL56 to 5.6.31-2.cp1156.

A change in this case will ensure no /usr/my.cnf file is created with the sql_mode directive set to "strict mode". Existing systems can manually modify the /usr/my.cnf to configure the value to your own preference.

Note that cPanel 58 is not yet released to production build tiers. You can review our versioning and release guide at:
cPanel & WHM Product Versions and the Release Process

Thank you.
 

sahostking

Well-Known Member
May 15, 2012
403
29
78
Cape Town, South Africa
cPanel Access Level
Root Administrator
Twitter
Thanks. This explains why we had 2 clients today asking us to disable it. We refused as we though it was standard with cPanel. Must be some change that occured or is it just coincidence ?

Update: Just noticed its only on our new servers with new cpanel install that it occurs on. The older ones don't have /usr/my.cnf and have the same setup.
 

cPanelMichael

Administrator
Staff member
Apr 11, 2011
47,880
2,256
463
Update: Just noticed its only on our new servers with new cpanel install that it occurs on. The older ones don't have /usr/my.cnf and have the same setup.
Yes, this affects new installations of cPanel with MySQL 5.6, as that's the version that enables strict mode by default.

Thank you.