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!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Weird problem with strict tables

Discussion in 'Database Discussions' started by fingerprn, Sep 1, 2017.

  1. fingerprn

    fingerprn Member

    Joined:
    Feb 19, 2007
    Messages:
    23
    Likes Received:
    0
    Trophy Points:
    151
    I recently started getting errors like this...

    "Field 'acctID' doesn't have a default value"

    I discover that I need to add a default value or null. I tried both and neither worked.

    Next suggestion is removing STRICT_TRANS_TABLES from my.cnf

    This suggestion worked, but it left me with two questions.

    1. I read that this problem doesn't exist with InnoDB. Is this correct? My tables use InnoDB!

    2. Why didn't adding a default value or null just fix the problem?

    Thanks in advance for any help or advice on this.


    On a VPS using...
    CENTOS v7.3
    WHM v66.0.17
    MySQL v5.6.37
    PHP is v5.6.30
     
    #1 fingerprn, Sep 1, 2017
    Last edited: Sep 1, 2017
  2. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    38,165
    Likes Received:
    1,372
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    Hello,

    Could you let us know the full contents of the /etc/my.cnf file on the affected system (before you made changes to it)?

    Thank you.
     
  3. fingerprn

    fingerprn Member

    Joined:
    Feb 19, 2007
    Messages:
    23
    Likes Received:
    0
    Trophy Points:
    151
    So here's /etc/my.cnf...

    -------------------------------------------
    [mysqld]
    performance-schema=0
    skip-networking
    slow-query-log
    local-infile = 0

    max_connections=200
    table_open_cache=256
    max_delayed_threads=20
    max_tmp_tables=32
    query_cache_type=1
    query_cache_size=4M
    thread_cache_size=4
    innodb_file_per_table=1
    innodb_use_native_aio=0
    default-storage-engine=MyISAM
    max_allowed_packet=268435456
    open_files_limit=11558

    bind-address=127.0.0.1
    ----------------------------------------------


    As you can see, there is no reference to STRICT_TRANS_TABLES. I was going to add the sql_mode line, but I noticed it was already added in /usr/my.cnf, so I made the change there. Here's what /usr/my.cnf said before changes...

    ------------------------------------------------------------------------
    # For advice on how to change settings please see
    # MySQL :: MySQL 5.6 Reference Manual :: 5.1.2 Server Configuration Defaults

    [mysqld]

    # A bunch of commented-out lines were here and then...

    sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
    -------------------------------------------------------------------------


    From there, I changed it to sql_mode=NO_ENGINE_SUBSTITUTION and restarted
     
  4. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    38,165
    Likes Received:
    1,372
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    I recommend removing the /usr/my.cnf file as it can take precedence over your /etc/my.cnf values. There's a thread on this topic at:

    Turn off Mysql Strict Mode

    Thank you.
     
  5. fingerprn

    fingerprn Member

    Joined:
    Feb 19, 2007
    Messages:
    23
    Likes Received:
    0
    Trophy Points:
    151
    Thanks! I made the update.

    Any ideas about why I was having the other problems? Does it have anything to do with the NO_ENGINE_SUBSTITUTION setting?

    I know I should probably be happy that everything is working now, but it's been my experience that unless the real problem is identified, I'm likely to have more weird problems down the road.
     
  6. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    38,165
    Likes Received:
    1,372
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    I recommend reaching out to the developer or support team of the script associated with the MySQL database you noticed the error message with to see if they can offer any advice on what might cause that error or if changing the SQL mode could lead to any issues.

    Thank you.
     
Loading...

Share This Page