Details
-
Bug
-
Resolution: Done
-
Major
-
0.9.5.Final
-
None
Description
Due to lax permissions set up by lazy dba's in the past, invalid datetimes & timestamps were allowed, giving an error like this:
{
"name": "sacarino-br-pro-20190531",
"connector": {
"state": "RUNNING",
"worker_id": "172.16.230.69:8083"
},
"tasks": [
{
"state": "FAILED",
"trace": "org.apache.kafka.connect.errors.ConnectException: Zero date value prohibited Error code: 0; SQLSTATE: S1009.\n\tat io.debezium.connector.mysql.AbstractReader.wrap(AbstractReader.java:230)\n\tat io.debezium.connector.mysql.AbstractReader.failed(AbstractReader.java:208)\n\tat io.debezium.connector.mysql.SnapshotReader.execute(SnapshotReader.java:721)\n\tat java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)\n\tat java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)\n\tat java.lang.Thread.run(Thread.java:748)\nCaused by: java.sql.SQLException: Zero date value prohibited\n\tat com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)\n\tat com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)\n\tat com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:89)\n\tat com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:63)\n\tat com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:73)\n\tat com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:99)\n\tat com.mysql.cj.jdbc.result.ResultSetImpl.getTimestamp(ResultSetImpl.java:1059)\n\tat io.debezium.connector.mysql.SnapshotReader.readField(SnapshotReader.java:139)\n\tat io.debezium.connector.mysql.SnapshotReader.lambda$execute$15(SnapshotReader.java:543)\n\tat io.debezium.jdbc.JdbcConnection.query(JdbcConnection.java:444)\n\tat io.debezium.connector.mysql.SnapshotReader.execute(SnapshotReader.java:533)\n\t... 3 more\nCaused by: com.mysql.cj.exceptions.DataReadException: Zero date value prohibited\n\tat com.mysql.cj.result.SqlTimestampValueFactory.createFromTimestamp(SqlTimestampValueFactory.java:93)\n\tat com.mysql.cj.result.SqlTimestampValueFactory.createFromTimestamp(SqlTimestampValueFactory.java:46)\n\tat com.mysql.cj.result.ZeroDateTimeToNullValueFactory.createFromTimestamp(ZeroDateTimeToNullValueFactory.java:64)\n\tat com.mysql.cj.result.BaseDecoratingValueFactory.createFromTimestamp(BaseDecoratingValueFactory.java:61)\n\tat com.mysql.cj.result.BaseDecoratingValueFactory.createFromTimestamp(BaseDecoratingValueFactory.java:61)\n\tat com.mysql.cj.protocol.a.MysqlTextValueDecoder.decodeTimestamp(MysqlTextValueDecoder.java:183)\n\tat com.mysql.cj.protocol.result.AbstractResultsetRow.decodeAndCreateReturnValue(AbstractResultsetRow.java:87)\n\tat com.mysql.cj.protocol.result.AbstractResultsetRow.getValueFromBytes(AbstractResultsetRow.java:250)\n\tat com.mysql.cj.protocol.a.result.TextBufferRow.getValue(TextBufferRow.java:132)\n\tat com.mysql.cj.jdbc.result.ResultSetImpl.getNonStringValueFromRow(ResultSetImpl.java:656)\n\tat com.mysql.cj.jdbc.result.ResultSetImpl.getDateOrTimestampValueFromRow(ResultSetImpl.java:679)\n\t... 8 more\n",
"id": 0,
"worker_id": "172.16.230.69:8083"
}
],
"type": "source"
}
Yet even though debezium contemplates for this (https://debezium.io/docs/connectors/mysql/#temporal-values), still the connector failed
The culprit is a field, set with a wacko datetime like this:
ordel_warehouse_shipdate: 0000-00-00 14:02:10
Manually updating our prod db took us through.
Note: Most jdbc client mysql apps, like dbeaver, will auto null this correctly, but debezium it seems. Hence to debug this use a non-java client app.
it happens during the snapshot phase. I actually NULLed the data at source via an update, so no need to subset the source with, in our case and impossible query contemplating all datetime/timestamp fields that match 0000-00-00%, as the date part seemed to bomb debezium/jdbc, not the time part. The fact is that
MySQL allows zero-values for DATE, DATETIME, and TIMESTAMP columns, which are sometimes preferred over null values. These values cannot be represented using any of the Java types with either of the time.precision.mode options, and therefore the MySQL connector will represent them as null values when the column definition allows nulls, or as the epoch day when the column does not allow nulls.
does not work correctly when data is so mangled. (I understand it is a weird case)
