Please whitelist cPanel in your adblocker so that you’re able to see our version release promotions, thanks!

The Community Forums

Interact with an entire community of cPanel & WHM users!

SOLVED Mariadb 10.2 - Disabling Strict Mode

Discussion in 'Database Discussion' started by dr_lucas, Aug 9, 2018.

Tags:
  1. dr_lucas

    dr_lucas Well-Known Member

    Joined:
    Jul 6, 2007
    Messages:
    47
    Likes Received:
    1
    Trophy Points:
    58
    Location:
    Thailand
    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?
     
    #1 dr_lucas, Aug 9, 2018
    Last edited: Aug 9, 2018
  2. rpvw

    rpvw Well-Known Member

    Joined:
    Jul 18, 2013
    Messages:
    841
    Likes Received:
    304
    Trophy Points:
    113
    Location:
    Spain
    cPanel Access Level:
    Root Administrator
    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
    Code:
    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
    Code:
    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
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    #2 rpvw, Aug 9, 2018
    Last edited: Aug 9, 2018
  3. sparek-3

    sparek-3 Well-Known Member

    Joined:
    Aug 10, 2002
    Messages:
    1,765
    Likes Received:
    117
    Trophy Points:
    343
    cPanel Access Level:
    Root Administrator
    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.
     
    #3 sparek-3, Aug 9, 2018
    Last edited: Aug 9, 2018
    rpvw likes this.
  4. rpvw

    rpvw Well-Known Member

    Joined:
    Jul 18, 2013
    Messages:
    841
    Likes Received:
    304
    Trophy Points:
    113
    Location:
    Spain
    cPanel Access Level:
    Root Administrator
    I would agree with @sparek-3

    The relevant text seems to be
    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
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  5. dr_lucas

    dr_lucas Well-Known Member

    Joined:
    Jul 6, 2007
    Messages:
    47
    Likes Received:
    1
    Trophy Points:
    58
    Location:
    Thailand
    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:

    chrome_2018-08-10_10-36-51.png

    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?
     
    #5 dr_lucas, Aug 9, 2018
    Last edited by a moderator: Aug 13, 2018
  6. rpvw

    rpvw Well-Known Member

    Joined:
    Jul 18, 2013
    Messages:
    841
    Likes Received:
    304
    Trophy Points:
    113
    Location:
    Spain
    cPanel Access Level:
    Root Administrator
    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 !
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  7. dr_lucas

    dr_lucas Well-Known Member

    Joined:
    Jul 6, 2007
    Messages:
    47
    Likes Received:
    1
    Trophy Points:
    58
    Location:
    Thailand
    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
     
  8. dr_lucas

    dr_lucas Well-Known Member

    Joined:
    Jul 6, 2007
    Messages:
    47
    Likes Received:
    1
    Trophy Points:
    58
    Location:
    Thailand
    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
     

    Attached Files:

    #8 dr_lucas, Aug 10, 2018
    Last edited: Aug 10, 2018
  9. dalem

    dalem Well-Known Member
    PartnerNOC

    Joined:
    Oct 24, 2003
    Messages:
    2,742
    Likes Received:
    78
    Trophy Points:
    353
    Location:
    SLC
    cPanel Access Level:
    DataCenter Provider
    add
    sql_mode= ""

    to your /etc/my.cnf and restart MySQL
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  10. dr_lucas

    dr_lucas Well-Known Member

    Joined:
    Jul 6, 2007
    Messages:
    47
    Likes Received:
    1
    Trophy Points:
    58
    Location:
    Thailand
    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?
     
  11. dalem

    dalem Well-Known Member
    PartnerNOC

    Joined:
    Oct 24, 2003
    Messages:
    2,742
    Likes Received:
    78
    Trophy Points:
    353
    Location:
    SLC
    cPanel Access Level:
    DataCenter Provider
    Do not have a /usr/my.cnf in any of my installs
    cpanel will overwrite /etc/my.cnf
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  12. cPanelMichael

    cPanelMichael Technical Support Community Manager
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    44,827
    Likes Received:
    1,898
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    Twitter:
    Hello,

    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.

    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:

    Code:
    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.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  13. dr_lucas

    dr_lucas Well-Known Member

    Joined:
    Jul 6, 2007
    Messages:
    47
    Likes Received:
    1
    Trophy Points:
    58
    Location:
    Thailand
    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?
     
    #13 dr_lucas, Aug 13, 2018
    Last edited: Aug 13, 2018
  14. cPanelMichael

    cPanelMichael Technical Support Community Manager
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    44,827
    Likes Received:
    1,898
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    Twitter:
    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.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  15. dr_lucas

    dr_lucas Well-Known Member

    Joined:
    Jul 6, 2007
    Messages:
    47
    Likes Received:
    1
    Trophy Points:
    58
    Location:
    Thailand
    Works well. Thank you. :)
     
  16. cPanelMichael

    cPanelMichael Technical Support Community Manager
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    44,827
    Likes Received:
    1,898
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    Twitter:
    Hello @dr_lucas,

    I'm glad to see that helped. Thank you for sharing the outcome.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
Loading...

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice