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.

I created a new RDS instance and it is with the latest version of MySQL, so I think yes. — kemety
– kemety, Commented Jan 5, 2018 at 9:10

kemety · Accepted Answer · 2018-01-06 14:56:27Z

2

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.

answered Jan 6, 2018 at 14:56

kemety

1552 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Hassen Ch. · Accepted Answer · 2020-12-23 19:41:39Z

2

Simply deactivate explicit_defaults_for_timestamp

SET GLOBAL explicit_defaults_for_timestamp = 0;

answered Dec 23, 2020 at 19:41

Hassen Ch.

1,8331 gold badge21 silver badges32 bronze badges

1 Comment

Marcio J Over a year ago

Make sure you include this in your my.cnf file to make it persistent.

Michał Zaborowski · Accepted Answer · 2018-01-04 07:51:57Z

0

I have no idea why it works like that in this particular case, so I would concentrate on fixing a problem.

According to the docs NO_ENGINE_SUBSTITUTION has nothing to do with the error, during application run. I would select rows with column "xyz" of NULL value and update it to something - not null.

Default is applied when row is created. Let's say you have a table with some millions of rows, and want to add column with not null. That would block your table for significant amount of time. So you can create column without not null, but with default. That operation deals only with metadata, so is fast. Default will deal with all new rows. After that you can slowly update all rows. At the end not null constraint can be added. Not sure if DB is checking constraint when adding it at last step. Or maybe prev. version had problem with it? With MySQL things like that happens.

answered Jan 4, 2018 at 7:51

Michał Zaborowski

4,4032 gold badges23 silver badges42 bronze badges

6 Comments

kemety Over a year ago

I think I maybe did not explain the problem very well. The column xyz always existed and always had a NOT NULL constraint and always had a default value CURRENT_TIMESTAMP, there are no rows with NULL value. The application used to insert records to the table and the default value was normally inserted to the field xyz. I did not change anything in the application, all I did was that I created a dump from the existing database, created a new AWS RDS instance with MySql 5.7.19, imported the dump file. After that whenever the application tries to insert a record in this table, I get this error

Michał Zaborowski Over a year ago

Are you providing column xyz in the insert query? What about the data, maybe there is something strange after reimporting? Just trying to figure our what could go wrong...

kemety Over a year ago

I do not think the problem has to do with the application code, the code was working just fine before the migration. It must be some configuration with the MySql.

Michał Zaborowski Over a year ago

That is also clear to me, however it can be that something was allowed in previous version and is prohibited now... That is why I was asking about putting xyz into insert query. It can be that prev. version applied default when null was passed, and new one just takes null.

kemety Over a year ago

Totally understood and probably is the case since I am using an ORM that is probably doing that, still I don't want to correct 100 instances in the code where this takes place but rather find out what the MySql configuration difference needed to fix this and make it work like before.

|

Collectives™ on Stack Overflow

MySQL error column cannot be null although default value set

3 Answers 3

Comments

1 Comment

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related