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!

Weird problem with strict tables

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

  1. fingerprn

    fingerprn Active Member

    Joined:
    Feb 19, 2007
    Messages:
    25
    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 Technical Support Community Manager
    Staff Member

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

    fingerprn Active Member

    Joined:
    Feb 19, 2007
    Messages:
    25
    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 Technical Support Community Manager
    Staff Member

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

    fingerprn Active Member

    Joined:
    Feb 19, 2007
    Messages:
    25
    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 Technical Support Community Manager
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    44,803
    Likes Received:
    1,898
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    Twitter:
    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.
     
    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