Weird problem with strict tables

[fingerprn]

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]

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.

 

[fingerprn]

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

 

[cPanelMichael]

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.

 

[fingerprn]

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.

 

[cPanelMichael]

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.