Source MySQL: Connector fails if source contains "tricky" date values #3931

DoNotPanicUA · 2021-06-07T20:14:31Z

Found by comprehensive tests #3562
Requires #5704

Expected Behavior

A connector should finish its work no matter what data source contains.
In addition, such cases should be covered by comprehensive tests after the fix.

Current Behavior

If a source contains a zero date value like 2021-00-00 or even 0000-00-00, the connector will fail with an error "Zero date value prohibited". Relevant for types date and DATETIME
MySQL source can contain a time value larger than can handle the corresponding Java type. The connector fails with the error "The value '-794:59:59' is an invalid TIME value. JDBC Time objects represent a wall-clock time and not a duration as MySQL treats them. If you are treating this type as a duration, consider retrieving this value as a string and dealing with it according to your requirements."

Steps to Reproduce

Short way:

Checkout master branch
Go to MySqlSourceComprehensiveTest.java and uncomment test values at lines: 208, 218, 236
Run the test.
💣

Long way:

Create a table with columns date, DATETIME, and time types.
Insert zero date values(2000-00-00) into DATE and DATETIME columns and -838:59:59.000000 into TIME column.
Setup and run connector
💣

Severity of the bug for you

High

Airbyte Version

0.24.7-alpha

Connector Version

0.3.4

The text was updated successfully, but these errors were encountered:

vsayer · 2021-07-15T21:44:22Z

Confirmed in:

airbyte 0.27.3-alpha
source mysql 0.4.0

2021-07-15 21:35:29 ERROR () LineGobbler(voidCall):85 - Exception in thread "main" java.lang.RuntimeException: java.sql.SQLException: Zero date value prohibited
2021-07-15 21:35:29 ERROR () LineGobbler(voidCall):85 - 	at io.airbyte.db.jdbc.JdbcUtils$1.tryAdvance(JdbcUtils.java:76)
2021-07-15 21:35:29 ERROR () LineGobbler(voidCall):85 - 	at java.base/java.util.Spliterators$1Adapter.hasNext(Spliterators.java:681)
2021-07-15 21:35:29 ERROR () LineGobbler(voidCall):85 - 	at io.airbyte.commons.util.DefaultAutoCloseableIterator.computeNext(DefaultAutoCloseableIterator.java:58)
2021-07-15 21:35:29 ERROR () LineGobbler(voidCall):85 - 	at com.google.common.collect.AbstractIterator.tryToComputeNext(AbstractIterator.java:141)
2021-07-15 21:35:29 ERROR () LineGobbler(voidCall):85 - 	at com.google.common.collect.AbstractIterator.hasNext(AbstractIterator.java:136)
2021-07-15 21:35:29 ERROR () LineGobbler(voidCall):85 - 	at io.airbyte.commons.util.LazyAutoCloseableIterator.computeNext(LazyAutoCloseableIterator.java:62)
2021-07-15 21:35:29 ERROR () LineGobbler(voidCall):85 - 	at com.google.common.collect.AbstractIterator.tryToComputeNext(AbstractIterator.java:141)
2021-07-15 21:35:29 ERROR () LineGobbler(voidCall):85 - 	at com.google.common.collect.AbstractIterator.hasNext(AbstractIterator.java:136)
2021-07-15 21:35:29 ERROR () LineGobbler(voidCall):85 - 	at com.google.common.collect.TransformedIterator.hasNext(TransformedIterator.java:42)
2021-07-15 21:35:29 ERROR () LineGobbler(voidCall):85 - 	at io.airbyte.commons.util.DefaultAutoCloseableIterator.computeNext(DefaultAutoCloseableIterator.java:58)
2021-07-15 21:35:29 ERROR () LineGobbler(voidCall):85 - 	at com.google.common.collect.AbstractIterator.tryToComputeNext(AbstractIterator.java:141)
2021-07-15 21:35:29 ERROR () LineGobbler(voidCall):85 - 	at com.google.common.collect.AbstractIterator.hasNext(AbstractIterator.java:136)
2021-07-15 21:35:29 ERROR () LineGobbler(voidCall):85 - 	at com.google.common.collect.TransformedIterator.hasNext(TransformedIterator.java:42)
2021-07-15 21:35:29 ERROR () LineGobbler(voidCall):85 - 	at io.airbyte.commons.util.DefaultAutoCloseableIterator.computeNext(DefaultAutoCloseableIterator.java:58)
2021-07-15 21:35:29 ERROR () LineGobbler(voidCall):85 - 	at com.google.common.collect.AbstractIterator.tryToComputeNext(AbstractIterator.java:141)
2021-07-15 21:35:29 ERROR () LineGobbler(voidCall):85 - 	at com.google.common.collect.AbstractIterator.hasNext(AbstractIterator.java:136)
2021-07-15 21:35:29 ERROR () LineGobbler(voidCall):85 - 	at io.airbyte.commons.util.CompositeIterator.computeNext(CompositeIterator.java:83)
2021-07-15 21:35:29 ERROR () LineGobbler(voidCall):85 - 	at com.google.common.collect.AbstractIterator.tryToComputeNext(AbstractIterator.java:141)
2021-07-15 21:35:29 ERROR () LineGobbler(voidCall):85 - 	at com.google.common.collect.AbstractIterator.hasNext(AbstractIterator.java:136)
2021-07-15 21:35:29 ERROR () LineGobbler(voidCall):85 - 	at io.airbyte.commons.util.DefaultAutoCloseableIterator.computeNext(DefaultAutoCloseableIterator.java:58)
2021-07-15 21:35:29 ERROR () LineGobbler(voidCall):85 - 	at com.google.common.collect.AbstractIterator.tryToComputeNext(AbstractIterator.java:141)
2021-07-15 21:35:29 ERROR () LineGobbler(voidCall):85 - 	at com.google.common.collect.AbstractIterator.hasNext(AbstractIterator.java:136)
2021-07-15 21:35:29 ERROR () LineGobbler(voidCall):85 - 	at java.base/java.util.Iterator.forEachRemaining(Iterator.java:132)
2021-07-15 21:35:29 ERROR () LineGobbler(voidCall):85 - 	at io.airbyte.integrations.base.IntegrationRunner.run(IntegrationRunner.java:109)
2021-07-15 21:35:29 ERROR () LineGobbler(voidCall):85 - 	at io.airbyte.integrations.source.mysql.MySqlSource.main(MySqlSource.java:250)
2021-07-15 21:35:29 ERROR () LineGobbler(voidCall):85 - Caused by: java.sql.SQLException: Zero date value prohibited
2021-07-15 21:35:29 ERROR () LineGobbler(voidCall):85 - 	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
2021-07-15 21:35:29 ERROR () LineGobbler(voidCall):85 - 	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
2021-07-15 21:35:29 ERROR () LineGobbler(voidCall):85 - 	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:89)
2021-07-15 21:35:29 ERROR () LineGobbler(voidCall):85 - 	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:63)
2021-07-15 21:35:29 ERROR () LineGobbler(voidCall):85 - 	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:73)
2021-07-15 21:35:29 ERROR () LineGobbler(voidCall):85 - 	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:99)
2021-07-15 21:35:29 ERROR () LineGobbler(voidCall):85 - 	at com.mysql.cj.jdbc.result.ResultSetImpl.getTimestamp(ResultSetImpl.java:914)
2021-07-15 21:35:29 ERROR () LineGobbler(voidCall):85 - 	at com.mysql.cj.jdbc.result.ResultSetImpl.getObject(ResultSetImpl.java:1254)
2021-07-15 21:35:29 ERROR () LineGobbler(voidCall):85 - 	at org.apache.commons.dbcp2.DelegatingResultSet.getObject(DelegatingResultSet.java:733)
2021-07-15 21:35:29 ERROR () LineGobbler(voidCall):85 - 	at org.apache.commons.dbcp2.DelegatingResultSet.getObject(DelegatingResultSet.java:733)
2021-07-15 21:35:29 ERROR () LineGobbler(voidCall):85 - 	at io.airbyte.db.jdbc.JdbcUtils.rowToJson(JdbcUtils.java:103)
2021-07-15 21:35:29 ERROR () LineGobbler(voidCall):85 - 	at io.airbyte.db.jdbc.JdbcUtils$1.tryAdvance(JdbcUtils.java:73)
2021-07-15 21:35:29 ERROR () LineGobbler(voidCall):85 - 	... 24 more
2021-07-15 21:35:29 ERROR () LineGobbler(voidCall):85 - Caused by: com.mysql.cj.exceptions.DataReadException: Zero date value prohibited
2021-07-15 21:35:29 ERROR () LineGobbler(voidCall):85 - 	at com.mysql.cj.result.SqlTimestampValueFactory.localCreateFromTimestamp(SqlTimestampValueFactory.java:101)
2021-07-15 21:35:29 ERROR () LineGobbler(voidCall):85 - 	at com.mysql.cj.result.SqlTimestampValueFactory.localCreateFromTimestamp(SqlTimestampValueFactory.java:50)
2021-07-15 21:35:29 ERROR () LineGobbler(voidCall):85 - 	at com.mysql.cj.result.AbstractDateTimeValueFactory.createFromTimestamp(AbstractDateTimeValueFactory.java:87)
2021-07-15 21:35:29 ERROR () LineGobbler(voidCall):85 - 	at com.mysql.cj.protocol.a.MysqlBinaryValueDecoder.decodeTimestamp(MysqlBinaryValueDecoder.java:52)
2021-07-15 21:35:29 ERROR () LineGobbler(voidCall):85 - 	at com.mysql.cj.protocol.result.AbstractResultsetRow.decodeAndCreateReturnValue(AbstractResultsetRow.java:87)
2021-07-15 21:35:29 ERROR () LineGobbler(voidCall):85 - 	at com.mysql.cj.protocol.result.AbstractResultsetRow.getValueFromBytes(AbstractResultsetRow.java:241)
2021-07-15 21:35:29 ERROR () LineGobbler(voidCall):85 - 	at com.mysql.cj.protocol.a.result.BinaryBufferRow.getValue(BinaryBufferRow.java:244)

ChristopheDuong · 2021-07-22T08:44:32Z

Is this solvable by adding jdbc_params?
driver-url=jdbc:mysql://127.0.0.1/test?zerodatetimebehavior=Converttonull

see this comment and linked question:
#2601 (comment)

…avior=convertToNull"

* move logic from static JdbcUtils to JdbcSourceOperations * format * Add methods for all types in order to have possibility rewrite them and use common type mapping. * Make BigQuery in line impl with JDBC changes * fix binary type * add database creations methods with sourceOperations input * add MySqlSourceOperations * fix processing * Fix CDC processing * format * add some tests for double and float * incr version * #3931 #3932 added zero-date converting to null param "zeroDateTimeBehavior=convertToNull" * remove old tests covered by data type tests + incr ver * Update airbyte-integrations/connectors/destination-mysql/src/main/java/io/airbyte/integrations/destination/mysql/MySQLDestination.java Co-authored-by: Sherif A. Nada <[email protected]> * Update docs/integrations/sources/mysql.md Co-authored-by: Sherif A. Nada <[email protected]> * add back comments to the data type mapping * incr config version Co-authored-by: Oleksandr Sheheda <[email protected]> Co-authored-by: Sherif A. Nada <[email protected]>

* move logic from static JdbcUtils to JdbcSourceOperations * format * Add methods for all types in order to have possibility rewrite them and use common type mapping. * Make BigQuery in line impl with JDBC changes * fix binary type * add database creations methods with sourceOperations input * add MySqlSourceOperations * fix processing * Fix CDC processing * format * add some tests for double and float * incr version * airbytehq#3931 airbytehq#3932 added zero-date converting to null param "zeroDateTimeBehavior=convertToNull" * remove old tests covered by data type tests + incr ver * Update airbyte-integrations/connectors/destination-mysql/src/main/java/io/airbyte/integrations/destination/mysql/MySQLDestination.java Co-authored-by: Sherif A. Nada <[email protected]> * Update docs/integrations/sources/mysql.md Co-authored-by: Sherif A. Nada <[email protected]> * add back comments to the data type mapping * incr config version Co-authored-by: Oleksandr Sheheda <[email protected]> Co-authored-by: Sherif A. Nada <[email protected]>

yinondn · 2023-04-10T12:16:53Z

I'm still getting this error for invalid TIME values:
Message: The value '25:00:00' is an invalid TIME value. JDBC Time objects represent a wall-clock time and not a duration as MySQL treats them. If you are treating this type as a duration, consider retrieving this value as a string and dealing with it according to your requirements.

Connector: MySQL 2.0.13
Airbyte v0.41.0

Can someone please check or explain a workaround?

DoNotPanicUA added the type/bug Something isn't working label Jun 7, 2021

sherifnada added area/connectors Connector related issues lang/java labels Jun 8, 2021

alexanderbuhler mentioned this issue Jul 19, 2021

MySQL source: java.sql.SQLException: Zero date value prohibited #4815

Closed

cgardens mentioned this issue Jul 30, 2021

Support for more Granular Data Types #5129

Closed

sherifnada added the priority/high High priority label Aug 13, 2021

etsybaev mentioned this issue Aug 15, 2021

ERROR () LineGobbler(voidCall):85 - Exception in thread "main" java.lang.RuntimeException: java.sql.SQLException: YEAR #5070

Closed

DoNotPanicUA self-assigned this Aug 16, 2021

DoNotPanicUA mentioned this issue Aug 27, 2021

Make JdbcUtils overwritable. #5704

Closed

alexandr-shegeda linked a pull request Oct 4, 2021 that will close this issue

MySql Source: Fix data processing #6093

Merged

38 tasks

alexandr-shegeda added a commit that referenced this issue Oct 6, 2021

#3931 #3932 added zero-date converting to null param "zeroDateTimeBeh…

0f0883b

…avior=convertToNull"

DoNotPanicUA closed this as completed in #6093 Oct 12, 2021

igrankova added connectors/sources-database connectors/source/mysql labels Jan 12, 2022

sherifnada moved this to Done in GL Roadmap Jan 12, 2022

sherifnada added this to GL Roadmap Jan 12, 2022

marcosmarxm mentioned this issue Aug 1, 2022

🐛 Source MySQL: java.sql.SQLException: DAY_OF_MONTH #15161

Closed

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Source MySQL: Connector fails if source contains "tricky" date values #3931

Source MySQL: Connector fails if source contains "tricky" date values #3931

DoNotPanicUA commented Jun 7, 2021 •

edited

Loading

vsayer commented Jul 15, 2021 •

edited

Loading

ChristopheDuong commented Jul 22, 2021 •

edited

Loading

yinondn commented Apr 10, 2023

Source MySQL: Connector fails if source contains "tricky" date values #3931

Source MySQL: Connector fails if source contains "tricky" date values #3931

Comments

DoNotPanicUA commented Jun 7, 2021 • edited Loading

Expected Behavior

Current Behavior

Steps to Reproduce

Severity of the bug for you

Airbyte Version

Connector Version

vsayer commented Jul 15, 2021 • edited Loading

ChristopheDuong commented Jul 22, 2021 • edited Loading

yinondn commented Apr 10, 2023

DoNotPanicUA commented Jun 7, 2021 •

edited

Loading

vsayer commented Jul 15, 2021 •

edited

Loading

ChristopheDuong commented Jul 22, 2021 •

edited

Loading