Uploaded image for project: 'Debezium'
  1. Debezium
  2. DBZ-1318

Partial zero date datetime/timestamp will fail snapshot

    Details

      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)

        Gliffy Diagrams

          Attachments

            Activity

              People

              • Assignee:
                jpechanec Jiri Pechanec
                Reporter:
                joel.salmeron Joel Salmeron Viver
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: