Skip to main content

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?

Comments

18 comments

Date Votes
  • 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
    0
  • 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.
    0
  • rpvw
    I would agree with @sparek-3 The relevant text seems to be [quote]Since MariaDB 10.2, and since MariaDB 10.1, and
    0
  • 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: 53981 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?
    0
  • 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 !
    0
  • 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
    0
  • 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
    0
  • dalem
    add sql_mode= "" to your /etc/my.cnf and restart MySQL
    0
  • 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?
    0
  • dalem
    Do not have a /usr/my.cnf in any of my installs cpanel will overwrite /etc/my.cnf
    0
  • 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.
    0
  • 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?
    0
  • 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.
    0
  • dr_lucas
    Works well. Thank you. :)
    0
  • cPanelMichael
    Hello @dr_lucas, I'm glad to see that helped. Thank you for sharing the outcome.
    0
  • 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
    0
  • 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.
    0
  • 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.
    0

Please sign in to leave a comment.

Didn't find what you were looking for?

New post