MySQL Bugs: #96211: insert valid datetime to timestamp column stop working, zero datetime error

Bug #96211	insert valid datetime to timestamp column stop working, zero datetime error
Submitted:	16 Jul 2019 0:38	Modified:	17 Jul 2019 14:35
Reporter:	saeed parsaee	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S2 (Serious)
Version:	Server version: 8.0.16 MySQL Community S	OS:	CentOS (CentOS release 6.10 (Final))
Assigned to:	MySQL Verification Team	CPU Architecture:	Any (Intel(R) Core(TM) i7-4790 CPU @ 3.60GHz)
Tags:	0000-00-00 00:00:00, ERROR 1525, timestamp, zero date

Description:
I have a table with 2M records and everything works fine until few hours ago. suddenly it throw error on a query that previously works fine for more than 1 year. 
The problem is that inserting a correct dateTime like '2019-07-15 22:22:47' into a timestamp column return error:

Incorrect datetime value: '0000-00-00 00:00:00' for column 'created_at' at row 1

1- I did duplicated table structure into new table and same query works fine on duplicated table

When error start?
I did run yum update today. and after that this table start giving this error.

More query Details?

1- insert query on table dont work:

insert into `feeds` (`entry_id`, `title`, `link`, `content`, `alert_id`, `state`, `description`, `date`, `created_at`, `date2`, `newsType_id`, `fullText`, `isResign`, `rss_id`, `site_name`, `updated_at`) 
values 
(
'blah blah', 'abcd' , 'xyz', 'qwerty', 726, 0, 203, 0, '2019-07-15 23:59:10', '2019-07-15', 0, '.....sample text...', 0, 4249, 'text'
, '2019-07-15 23:59:10'
)
in query created_at field is set to '2019-07-15 23:59:10' but mysql return error:
Error: Incorrect datetime value: '0000-00-00 00:00:00' for column 'created_at' at row 1
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
2- update query works fine
UPDATE `feeds` SET `created_at` = '2019-07-15 15:33:12' WHERE `id` = 1906117
Time: 0.056s
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
3- in a new table with same structure of base table, both insert and update queries works fine.
insert into `feedsDuplicate` (`entry_id`, `title`, `link`, `content`, `alert_id`, `state`, `description`, `date`, `created_at`, `date2`, `newsType_id`, `fullText`, `isResign`, `rss_id`, `site_name`, `updated_at`) 
values 
(
'blah blah', 'abcd' , 'xyz', 'qwerty', 726, 0, 203, 0, '2019-07-15 23:59:10', '2019-07-15', 0, '.....sample text...', 0, 4249, 'text'
, '2019-07-15 23:59:10'
)

How to repeat:
No idea

yum update log that i think cause the problem

Attachment: yumlog.txt (text/plain), 10.32 KiB.

table structure

Attachment: table structure.txt (text/plain), 3.28 KiB.

Dear Bogdan,
I found the cause,
It seems that yum update had updated mysql and i think there is some new roles added foe comparing datetime values, however i think the error thrown is completely irrelevant,
i had a trigger on my table which check some parameter and also check if created_at column is equal to '0000-00-00 00:00:00' then change it to current_timestamp. part of trigger is

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-==-=
IF(NEW.created_at = '0000-00-00 00:00:00') THEN
 	SET NEW.created_at = current_timestamp();
END IF	
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-==-=
its just a compare and the result should be true or false,
and should not throw Incorrect datetime value: '0000-00-00 00:00:00' for column 'created_at' at row 1
....................................................

In short i think comparing a datetime like '2019-07-15 22:22:47' to '0000-00-00 00:00:00' should not throw exception and it just should return true/false.

Hi,

This is not a bug. If you configure mysql to not accept invalid dates you cannot compare a date to an invalid one and error will be thrown. If the mysql is configured to not accept invalid dates that comparison is not needed (it will always fail) and should check if field is null to setup value and not if field is '000...' ...

So, you either configure mysql to allow invalid dates or configure mysql to not allow invalid dates and change that trigger.

Thanks
Bogdan