Weird problem with strict tables

fingerprn

• September 01, 2017 17:47

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

• 

  cPanelMichael

  • September 06, 2017 14:58

  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.

  0
• 

  fingerprn

  • September 06, 2017 17:16

  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

  0
• 

  cPanelMichael

  • September 07, 2017 15:45

  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...

  
  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.

  0
• 

  fingerprn

  • September 07, 2017 17:12

  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.

  0
• 

  cPanelMichael

  • September 07, 2017 17:15

  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.

  0

Please sign in to leave a comment.