SOLVED Mariadb 10.2 - Disabling Strict Mode

[dr_lucas]

I am wondering if disabling strict mode going to stick when cpanel/WHM auto updates itself?
If so, is it going to stick while upgrading minor MariaDB 10.2.x versions?

 

[rpvw]

When I upgraded from MySQL to MariDB 10.1 (I have not yet gone up to 10.2) I found the same strict mode issues.

On my install, 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 has survived all reboots and updates so far.

I probably should note that the answers regarding 10.2 that I have been reading suggest that a different format should be used to disable strict mode (that doesn't actually seem to exist any more) in the cnf files eg

sql_mode=NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

but I haven't tried it on 10.1 (if it isn't broken don't fix it)

This resource may be of some use SQL_MODE

 

[sparek-3]

My MariaDB 10.1 servers show an SQL mode of:

MariaDB [(none)]> SELECT @@GLOBAL.sql_mode;
+--------------------------------------------+
| @@GLOBAL.sql_mode |
+--------------------------------------------+
| NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+

I would assume then, upgrading to MariaDB 10.2, I would just set the sql_mode in /etc/my.cnf to NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION as well. Although that is not what I am reading across these forums.

I don't see where I have an sql_mode set on my MariaDB 10.1 servers, so I assumed that this setting was the default? But maybe not?

MariaDB 10.2 is going to add a strict mode to this by default, so for me the solution would be to just set the global sql_mode in my.cnf to the value previously used in MariaDB 10.1.

Edit: Server System Variables clears this up a bit.

 

[rpvw]

I would agree with @sparek-3

The relevant text seems to be

Since MariaDB 10.2.4, SQL_MODE is by default set to NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO.

In earlier versions of MariaDB 10.2, and since MariaDB 10.1.7, SQL_MODE is by default set to NO_ENGINE_SUBSTITUTION,NO_AUTO_CREATE_USER.

For earlier versions of MariaDB 10.1, and MariaDB 10.0 and before, no default is set.

so reversing the sql_mode in the cnf back to NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION would seem to exactly follow the advice I have been reading elsewhere

 

[dr_lucas]

Thanks.
I am still interested in answer from cPanel staff, and regarding 10.2.x and not 10.1.x
I am asking this because when I want to upgrade from 10.1.x to 10.2.x, I am getting this critical warning:



Now, I know how to manually disable strict mode after the upgrade, but I am just concerned that it will re-enable itself when I upgrade minor 10.2.x versions or cPanel versions and break all my sites.
Can anyone please confirm disabling strict mode it survives all cPanel/WHM upgrades and MariaDB minor version updates?

 

[rpvw]

I am still interested in answer from cPanel staff, and regarding 10.2.x and not 10.1.x

I thought that was exactly what we had been discussing but if you want an answer from cPanel staff, that's fine ...... sorry to have wasted your (and my) time !

 

[dr_lucas]

Maybe I misunderstand something, sorry for that, my English isn't great.
Can you please confirm disabling strict mode (by setting sql_mode="" in /usr/my.cnf) survives all cPanel/WHM upgrades and MariaDB 10.2.x minor version updates?

Thanks

 

[dr_lucas]

I would like to upgrade MariaDB 10.1.x to 10.2.x and when I tried that I noticed this critical notice (attached image).


Can you please explain how to can disable strict mode in a way that it will survives all cPanel/WHM upgrades and MariaDB 10.2.x minor version updates and won't get re-enabled by itself?

Thank you

 

[dalem]

add
sql_mode= ""

to your /etc/my.cnf and restart MySQL

 

[dr_lucas]

Thanks. Isn't it supposed to be done in /usr/my.cnf?
Also - will it stick the upgrades I mentioned and won't re enable itself at some point in the future and break my sites?

 

[dalem]

Do not have a /usr/my.cnf in any of my installs
cpanel will overwrite /etc/my.cnf

 

[cPanelMichael]

Hello,

Thanks. Isn't it supposed to be done in /usr/my.cnf?

Prior to cPanel & WHM version 58, installing or updating to MySQL version 5.6 lead to the creation of a /usr/my.cnf file with the sql_mode directive set to "strict mode". Internal case CPANEL-6030 was implemented in cPanel & WHM version 58 to prevent this from happening on new installations, however the file may still exist on older systems. Any systems still using a /usr/my.cnf file should make note of any entries in this file, remove it, and then add/merge those entries into the /etc/my.cnf file.

If so, is it going to stick while upgrading minor MariaDB 10.2.x versions?

Upgrading to MariaDB version 10.2 will automatically enable strict mode and remove any existing sql_mode entries in the /etc/my.cnf. This is by-design due how MariaDB implements the change. After the initial upgrade to MariaDB 10.2, you'd need to manually add the following value under the [mysqld] section in the /etc/my.cnf file if you want to revert this change:

sql_mode=NO_ENGINE_SUBSTITUTION

Then, restart MariaDB to apply the changes. Once your system is using MariaDB 10.2, the future minor updates will not require that you manually update the sql_mode value again.

Thank you.

 

[dr_lucas]

Thanks, @cPanelMichael
Could you please confirm that strict mode won't get re-enabled after getting it disabled in the way you described (at the end of the initial 10.2 upgrade), when later upgrading minor 10.2.x versions and/or when upgrading cPanel/WHM versions?

 

[cPanelMichael]

Thanks, @cPanelMichael
Could you please confirm that strict mode won't get re-enabled after getting it disabled in the way you described (at the end of the initial 10.2 upgrade), when later upgrading minor 10.2.x versions and/or when upgrading cPanel/WHM versions?

Hello @dr_lucas,

I can confirm the sql_mode value in the /etc/my.cnf file is preserved when you re-add it after the initial upgrade to MariaDB 10.2. Subsequent cPanel updates and MariaDB 10.2 minor version updates will not disable the entry.

Thank you.

 

[dr_lucas]

Works well. Thank you.

 

[cPanelMichael]

Hello @dr_lucas,

I'm glad to see that helped. Thank you for sharing the outcome.

 

[Bidi]

Hello guys, i got a a big dillema :D is it same to set

sql_mode=NO_ENGINE_SUBSTITUTION

With accounts on the server ? The think is i got around 300 websites and some of them some functions not working since i update to MariaDB 10.3 and i`m a bit afraid not to make sompting with the databases on the server by setting the sql_mode

thank you

 

[Spirogg]

Hello guys, i got a a big dillema :D is it same to set

sql_mode=NO_ENGINE_SUBSTITUTION

With accounts on the server ? The think is i got around 300 websites and some of them some functions not working since i update to MariaDB 10.3 and i`m a bit afraid not to make sompting with the databases on the server by setting the sql_mode

thank you

What do you have now in /etc/my.cnf
Do you have any sql_mode= ?

If some sites not working you need to set sql_mode that is why some sites not working most likely. All you do is add
under the
[mysqld]
sql_mode=NO_ENGINE_SUBSTITUTION

Restart MySQL
And see if problems persist .
You can always remove this line if needed. But most likely your problems will be solved after adding this line.

 

[Bidi]

What do you have now in /etc/my.cnf
Do you have any sql_mode= ?

If some sites not working you need to set sql_mode that is why some sites not working most likely. All you do is add
under the
[mysqld]
sql_mode=NO_ENGINE_SUBSTITUTION

Restart MySQL
And see if problems persist .
You can always remove this line if needed. But most likely your problems will be solved after adding this line.

Hello,
No o dont have any sql_mode= on my.cnf

I will added it in the nigh time and test.
Thank you.