mysql 5.7 timestamp default insert null error

Question

I have the following create table statement.

CREATE TABLE `test_table` ( 
`id` INT(11) NOT NULL,
`field1` varchar(10),
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
);

when I do the following insert, I'll get the erro "Column 'updated_at' cannot be null"

insert into test_table (id, field1, updated_at) values (1234, 'foo', null);

I expected updated_at would just take on the default value in this case.

mysql version 5.7.12

However when I do this in mysql version 5.6, the insert commands works. Is there a change in the versoin from 5.6 to 5.7? The only difference I thought was 5.7 has NO_ZERO_DATE default to true. But I thought that was only for datetime. Is there a configuration change that I need to make?

It's possible to achieve the result by not passing in updated_at but I don't have over insert statement in this case.

updatwe your 5.7.12 to 5.7.31 that works perfectly see dbfiddle.uk/… — nbk
– nbk, Commented Jul 16, 2020 at 21:33

GMB · Accepted Answer · 2020-07-16 23:38:11Z

1

I suspect that this is related to SQL Strict mode - but I can't find the exact quote in the documentation that matches your use case.

Bottom line, you should not be expecting the server to use the default when you provide it with an explicit null value.

If you can't remove the column from the insert list for some reason, a possible workaround is to use the default keyword, which makes your intent unambiguous:

insert into test_table (id, field1, updated_at) 
values (1234, 'foo', default);

answered Jul 16, 2020 at 23:38

GMB

224k25 gold badges103 silver badges151 bronze badges

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

Comments

Rahul RK · Accepted Answer · 2022-03-16 03:45:13Z

0

This error is thrown because explicit_defaults_for_timestamp is enabled. Disabling this will solve the issue Refer: https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_explicit_defaults_for_timestamp

answered Mar 16, 2022 at 3:45

Rahul RK

152 bronze badges

Collectives™ on Stack Overflow

mysql 5.7 timestamp default insert null error

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related