Inconsistent behavior for '0000-00-00 00:00:00' garbage dates

[osheroff]

Hey guys,
I'm poking around on zendesk/maxwell#928 -- one of those issues where yeah, it sucks that mysql ever allowed '0000-00-00' dates, but with maxwell I tend towards "reproduce it faithfully, even if it's garbage".

binlog-connector has some differing behavior for timestamp/datetime columns when encountering these columns (note that I'm using deserializeDateAndTimeAsLong):

• timestamps come back "I guess it's right" - it comes back as OL.
• datetimes come back NULL, which seems wrong. See the checks in asUnixTime.

I guess I'd like it if they all came back as OL

Anyway, hope y'all are doing well.

[shyiko]

Hi Ben.

Makes sense. Fixed in 0.14.0 🐦.

To activate:

EventDeserializer eventDeserializer = new EventDeserializer();
eventDeserializer.setCompatibilityMode(CompatibilityMode.INVALID_DATE_AND_TIME_AS_ZERO, 
    ...rest/* order doesn't matter */);
binaryLogClient.setEventDeserializer(eventDeserializer);

🙇‍♂️

[osheroff]

thanks Stanley! you are, as they say, the man.

[osheroff]

bah, sorry @shyiko, but after poking into some failing tests I realize that with this new behavior I can't distinguish between 0000-00-00 00:00:00 and 1970-01-01 00:00:00. guh. Maybe we represent 0000-00-00 as -1L?

[shyiko]

I don't think there is a way to distinguish between these two:

create table `test` (
   id int unsigned not null,
   test_timestamp timestamp
);

insert into test values(1, '0000-00-00 00:00:00');
insert into test values(2, '1970-01-01 00:00:00');
insert into test values(3, null);

select * from test;

+----+---------------------+
| id | test_timestamp |
+----+---------------------+
| 1 | 0000-00-00 00:00:00 |
| 2 | 0000-00-00 00:00:00 |
| 3 | 2018-04-05 06:15:35 |
+----+---------------------+
3 rows in set (0.00 sec)

(binlog contains zeros in both cases)

[osheroff]

ah, yes. that's true in the case of timestamps, but not in the case of datetime

[shyiko]

Alrighty :)

EventDeserializer eventDeserializer = new EventDeserializer();
eventDeserializer.setCompatibilityMode(
    CompatibilityMode.INVALID_DATE_AND_TIME_AS_NEGATIVE_ONE, 
    ...rest/* order doesn't matter */
);
binaryLogClient.setEventDeserializer(eventDeserializer);

(available in 0.15.0)
(Maven Central sync might take up to 25 minutes)

🎉

[osheroff]

ok @shyiko now you're really gonna kill me. I realize too late that 1969-12-31 23:59:59.999999 is -1. Do I care? argh. no. but do I want to get it right? argh. yes.

How about Long.MIN_VALUE instead? that is definitely not a valid datetime.

A millllllion apologies.

[shyiko]

:D No worries, Ben. I can't believe I haven't thought of this myself. I'll publish another release at 12pm, PDT (today).

[shyiko]

EventDeserializer eventDeserializer = new EventDeserializer();
eventDeserializer.setCompatibilityMode(
    CompatibilityMode.INVALID_DATE_AND_TIME_AS_MIN_VALUE, 
    ...rest/* order doesn't matter */
);
binaryLogClient.setEventDeserializer(eventDeserializer);

(0.16.0 published)

[shyiko]

@osheroff 0.16.1