MySQL timestamp '0000-00-00 00:00:00' incorrectly converts to '1970-01-01 00:00:00'

[djuntgen]

MySQL timestamp '0000-00-00 00:00:00' incorrectly converts to '1970-01-01 00:00:00'

create table `test` (
   id int unsigned not null,
   test_timestamp timestamp not null default '0000-00-00 00:00:00'
);

insert into test set id=1;

mysql root@localhost> select * from test;
+----+---------------------+
| id | test_timestamp      |
+----+---------------------+
|  1 | 0000-00-00 00:00:00 |
+----+---------------------+
1 row in set (0.00 sec)

The output from Maxwell, notice the 1970-01-01 00:00:00

{"database":"test","table":"test","type":"insert","ts":1521752956,"commit":true,"position":"mysql-bin.018181:58739437","data":{"id":1,"test_timestamp":"1970-01-01 00:00:00"}}

Maxwell states that MySQL allows for invalid DATETIME as 0000-00-00 00:00:00. The same rule should hold true for a TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00' column.

[osheroff]

yeah, this used to be behavior that we preserved, looks like it broke when we moved from open-replicator to mysql-binlog-connector. I can fix it for timestamp columns, but for datetime columns I've opened an issue with the binlog-connector guys.

[benzintrinker]

MySQL datetime '0000-00-00 00:00:00' converts to 'null'. Is there any chance that we get a fix?

[osheroff]

Yup, fix is in development

[benzintrinker]

Thank you for the fix.
But still not working for dates less than 1000 year.
Dates like 0001-01-01 and 0999-01-01 now become 0000-00-00.

[osheroff]

all fixed in v1.14.0

[benzintrinker]

Perfectly fixed. Thanks a lot!