MySQL connector has problems loading Datetime columns with Zero Date values

[coquej]

Expected Behavior

Load a table from MySQL Database that has datetime columns with NULL values, into BigQuery.

Current Behavior

Job crashes raising "java.sql.SQLException: Zero date value prohibited" exception.

Logs

2021-03-24 08:22:52 �[32mINFO�[m i.a.i.s.j.JdbcStateManager(createCursorInfoForStream):126 - {} - No cursor field set in catalog but not present in state. Stream: defaultdb.people_users, New Cursor Field: modified_at. Resetting cursor value
2021-03-24 08:22:54 INFO (/tmp/workspace/438/0) LineGobbler(voidCall):69 - 2021-03-24 08:22:54 �[32mINFO�[m i.a.i.d.b.BigQueryDestination(createTable):264 - {} - Table created successfully
2021-03-24 08:23:11 INFO (/tmp/workspace/438/0) DefaultAirbyteStreamFactory(lambda$create$0):73 - 2021-03-24 08:23:11 �[32mINFO�[m i.a.i.s.j.AbstractJdbcSource(queryTableFullRefresh):389 - {} - Queueing query for table: people_users
2021-03-24 08:23:12 INFO (/tmp/workspace/438/0) DefaultAirbyteStreamFactory(lambda$create$0):73 - 2021-03-24 08:23:12 �[32mINFO�[m i.a.i.s.j.AbstractJdbcSource(lambda$queryTableFullRefresh$22):394 - {} - Preparing query for table: people_users
2021-03-24 08:23:12 INFO (/tmp/workspace/438/0) DefaultAirbyteStreamFactory(lambda$create$0):73 - 2021-03-24 08:23:12 �[32mINFO�[m i.a.i.s.j.AbstractJdbcSource(lambda$queryTableFullRefresh$22):399 - {} - Executing query for table: people_users
2021-03-24 08:23:15 INFO (/tmp/workspace/438/0) DefaultAirbyteStreamFactory(lambda$create$0):73 - 2021-03-24 08:23:15 �[32mINFO�[m i.a.i.s.j.AbstractJdbcSource(lambda$read$3):181 - {} - Closing database connection pool.
2021-03-24 08:23:15 INFO (/tmp/workspace/438/0) DefaultAirbyteStreamFactory(lambda$create$0):73 - 2021-03-24 08:23:15 �[32mINFO�[m i.a.i.s.j.AbstractJdbcSource(lambda$read$3):183 - {} - Closed database connection pool.
2021-03-24 08:23:15 ERROR (/tmp/workspace/438/0) LineGobbler(voidCall):69 - Exception in thread "main" java.lang.RuntimeException: java.sql.SQLException: Zero date value prohibited

Steps to Reproduce

I don't know how to solve this, but maybe the problem is in the Data Catalog (?)

Severity of the bug for you

High

Airbyte Version

0.17.2-alpha

Connector Version (if applicable)

0.2.1

[ChristopheDuong]

Thanks for the feedback!
We'll look into why NULL values throw errors on date-type columns!

We also have this issue open: #1006

So I am pretty sure this is not coming from the BigQuery destination/normalization and the source is not handling the date type column well in this case.

A temporary solution for you could be to somehow make sure the source column data type is not date but string maybe? can you build a view that changes the datatypes of that column for the moment maybe? and use that in the replication.
When we figure things out with issue 1006, we'd be able to handle the date-type in a more proper way...

[coquej]

Ok. Although we think the temporary solution you propose is a good (provisional) idea, we cannot change anything from the source as we don't have permissions to modify it (we only have read access). So, for now, we will wait until that issue is closed.

Is it planned to work on the #1006 issue in the near future?

[ChristopheDuong]

1. We've been talking about First class support for date types in Airbyte #1006 but haven't committed on it in the very near future yet as it induce significant work to airbyte protocol and some other pieces, I think (cc @jrhizor)

2. Medium term solution we could deploy to unblock you would be to support custom SQL Views in the source connector settings as discussed here then? Excluding columns from MSSQL-source tables using UI #2463 (comment)

3. or the fastest would be to handle MySQL date columns to properly return rows even with null values and emit empty string values

[coquej]

2 and 3 would be great!

[ChristopheDuong]

Actually, your issue here is not about NULL values but that Zero date value are prohibited.

One solution to the option 3 we discussed earlier is described here:
https://topic.alibabacloud.com/a/javasqlsqlexceptionzero-date-value-prohibited-exception-handling_1_27_30012188.html

Which would be possible with the PR #2611

[coquej]

Ok, you're right. Thanks a lot for the quick solution and for helping us understand the real issue!