The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Trying to disable MySQL strict mode

Discussion in 'Database Discussions' started by KLAMP, May 25, 2016.

  1. KLAMP

    KLAMP Member

    Joined:
    Oct 22, 2013
    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    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]
     
    #1 KLAMP, May 25, 2016
    Last edited by a moderator: May 25, 2016
  2. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,678
    Likes Received:
    654
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
  3. musioc

    musioc Well-Known Member

    Joined:
    Aug 4, 2011
    Messages:
    105
    Likes Received:
    0
    Trophy Points:
    16
    Hi
    It seams there is a problem in new cpanel or mysql version

    We can not disable strict mode

    Code:
    [root@server /]# 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:
    [root@server /]# service mysql restart
    Shutting down MySQL.... SUCCESS!
    Starting MySQL. SUCCESS!
    [root@server /]# mysql -e 'select @@GLOBAL.sql_mode;'
    +--------------------------------------------+
    | @@GLOBAL.sql_mode                          |
    +--------------------------------------------+
    | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
    +--------------------------------------------+
     
  4. musioc

    musioc Well-Known Member

    Joined:
    Aug 4, 2011
    Messages:
    105
    Likes Received:
    0
    Trophy Points:
    16
    This problem exists in original cpanel mysql, original cpanel mariadb and cloudlinux mariadb (installed by mysqlgovernor)
     
  5. rpvw

    rpvw Well-Known Member

    Joined:
    Jul 18, 2013
    Messages:
    120
    Likes Received:
    34
    Trophy Points:
    28
    Location:
    Spain
    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.
     
    maverick23 and musioc like this.
  6. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,678
    Likes Received:
    654
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    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.
     
  7. musioc

    musioc Well-Known Member

    Joined:
    Aug 4, 2011
    Messages:
    105
    Likes Received:
    0
    Trophy Points:
    16
    Thank you very much :)
    This was my solution.
     
  8. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,678
    Likes Received:
    654
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    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.
     
  9. sahostking

    sahostking Well-Known Member

    Joined:
    May 15, 2012
    Messages:
    299
    Likes Received:
    1
    Trophy Points:
    18
    Location:
    Cape Town, South Africa
    cPanel Access Level:
    Root Administrator
    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.
     
  10. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,678
    Likes Received:
    654
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    Yes, this affects new installations of cPanel with MySQL 5.6, as that's the version that enables strict mode by default.

    Thank you.
     
Loading...

Share This Page