MySQL error column cannot be null although default value set

Question

I recently moved my SQL Database to another Amazon RDS server with version 5.7.

Before that, the application was working fine but now I started logging errors:

"ER_BAD_NULL_ERROR: Column xyz cannot be null" - The column already has a default value CURRENT_TIMESTAMP

I checked online and people suggested to have the sql_mode equal to NO_ENGINE_SUBSTITUTION

I checked the existing settings and it is already like that.

Any other reason I am getting this error? Any tricks?

Thanks.

Answer 1

After searching more, the problem was only in timestamp fields with current_timestamp default value. I searched in the parameters and found explicit_defaults_for_timestamp that was enabled (value 1) and with a bit more research, I had to disable this parameter as per the documentation here

https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_explicit_defaults_for_timestamp

in order to get the required result and fix the problem.

Answer 2

Simply deactivate explicit_defaults_for_timestamp

SET GLOBAL explicit_defaults_for_timestamp = 0;