Trying to disable MySQL strict mode

[KLAMP]

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]

 

[cPanelMichael]

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]

Hi
It seams there is a problem in new cpanel or mysql version

We can not disable strict mode

[[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 :

mysql> select @@GLOBAL.sql_mode;
+--------------------------------------------+
| @@GLOBAL.sql_mode                          |
+--------------------------------------------+
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+
1 row in set (0.00 sec)

changing my.cnf:

[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:

[[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]

This problem exists in original cpanel mysql, original cpanel mariadb and cloudlinux mariadb (installed by mysqlgovernor)

 

[rpvw]

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

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]

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]

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

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]

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]

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]

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.