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 DougK94, Dec 5, 2010.

Thread Status:
Not open for further replies.
  1. DougK94

    DougK94 Registered

    Joined:
    Aug 28, 2004
    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    1
    I need to turn off MySQL strict mode for a couple of applications to work properly and have been unsuccessful. System is as follows:

    WHM 11.28.52
    Centos 5.5
    MySQL 5.1.51

    I have tried editing etc/my.cnf

    The original is:
    Code:
    [mysqld]
    set-variable = max_connections=500
    safe-show-database
    local-infile=0
    I have tried adding each of the below individually and restarting MySQL after each

    Code:
    sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
    sql-mode=""
    sql-mode="TRADITIONAL"
    Any ideas on what I am doing wrong?
     
  2. cPanelDavidN

    cPanelDavidN Integration Developer
    Staff Member

    Joined:
    Dec 17, 2009
    Messages:
    571
    Likes Received:
    1
    Trophy Points:
    18
    Location:
    Houston, TX
    cPanel Access Level:
    Root Administrator
    Hi Doug,

    I was able to modify the /etc/my.cnf with the desired results.

    My Scenario:
    1)added to /etc/my.cnf

    Code:
    [mysqld]
    sql_mode="TRADITIONAL,NO_AUTO_CREATE_USER"
    
    2)verified that the mode was previously set
    Code:
    root@054 [~]# mysql -e 'select @@GLOBAL.sql_mode;'
    +-------------------+
    | @@GLOBAL.sql_mode |
    +-------------------+
    |                   | 
    +-------------------+
    
    3) restart mysql
    Code:
    root@054 [~]# /scripts/restartsrv mysql
    Waiting for mysql to restart...............finished.
    
    mysqld_safe (/bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/054.cpanel.test.pid) running as root with PID 26370
    mysqld (/usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/054.cpanel.test.pid --skip-external-locking) running as mysql with PID 26394
    
    mysql started ok
    
    4) check mysql
    Code:
    root@054 [~]# mysql -e 'select @@GLOBAL.sql_mode;'
    +-------------------------------------------------------------------------------------------------------------------------------+
    | @@GLOBAL.sql_mode                                                                                                             |
    +-------------------------------------------------------------------------------------------------------------------------------+
    | STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER | 
    +-------------------------------------------------------------------------------------------------------------------------------+
    root@054 [~]# 
    
    The only thing that stands out as a possible oversight is to make sure that you added the variable within the [mysqld] section of the my.cnf

    If your problem persists, you may want to open a ticket with cPanel Technical Support.

    Regards,
    -DavidN
     
  3. cPanelDavidN

    cPanelDavidN Integration Developer
    Staff Member

    Joined:
    Dec 17, 2009
    Messages:
    571
    Likes Received:
    1
    Trophy Points:
    18
    Location:
    Houston, TX
    cPanel Access Level:
    Root Administrator
    Oh also, make sure you use "sql_mode" and not "sql-mode" for the variable key name in /etc/my.cnf.
     
  4. DougK94

    DougK94 Registered

    Joined:
    Aug 28, 2004
    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    1
    The "_" was the major problem I think.
     
  5. juniorich

    juniorich Registered

    Joined:
    Feb 7, 2012
    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    This can be done in two ways...

    1- Open your "my.ini" file within the MySQL installation directory, and look for the text "sql-mode".

    Find:

    Code:
    # Set the SQL mode to strict sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
    
    Replace with:

    Code:
    # Set the SQL mode to strict sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
    
    Or

    2- You can run an SQL query within your database management tool, such as phpMyAdmin:

    Code:
    SET @@global.sql_mode= '';
    
     
Loading...
Thread Status:
Not open for further replies.

Share This Page