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

Snapshot fails when reading TIME, DATE, DATETIME fields in mysql from ResultSet

XMLWordPrintable

    • False
    • False
    • Undefined

      From the MySQL Connector/J 8.0 Developer Guide we know the following way can tell the driver to stream the results:

      conn = DriverManager.getConnection("jdbc:mysql://localhost/?useCursorFetch=true", "user", "s3cr3t");
      stmt = conn.createStatement();
      stmt.setFetchSize(100);
      

      From our side, we must specify `'snapshot.fetch.size' = '100'` and `'database.useCursorFetch' = 'true'`. At this time, there will be some problems when reading *TIME, DATE, DATETIME* fields in mysql from ResultSet.

       

      For example the following schema:

      create table only_time
      (
          id       int auto_increment primary key,
          now_time time
      ) engine = innodb
        auto_increment = 1
        default charset = utf8;
      
      create table only_date
      (
          id       int auto_increment primary key,
          now_time date
      ) engine = innodb
        auto_increment = 1
        default charset = utf8;
      
      create table only_datetime
      (
          id       int auto_increment primary key,
          now_time datetime
      ) engine = innodb
        auto_increment = 1
        default charset = utf8;
      
      insert into only_time values (default, now());
      insert into only_date values (default, now());
      insert into only_datetime values (default, now());
      

      The connector produces the following errors during the Snapshot phase:

      // for TIME field
      org.apache.kafka.connect.errors.ConnectException: Unexpected format for TIME column: ���org.apache.kafka.connect.errors.ConnectException: Unexpected format for TIME column: ��� at io.debezium.connector.mysql.AbstractReader.wrap(AbstractReader.java:230) at io.debezium.connector.mysql.AbstractReader.failed(AbstractReader.java:207) at io.debezium.connector.mysql.SnapshotReader.execute(SnapshotReader.java:831) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) at java.lang.Thread.run(Thread.java:748)Caused by: java.lang.RuntimeException: Unexpected format for TIME column: ��� at io.debezium.connector.mysql.MySqlValueConverters.stringToDuration(MySqlValueConverters.java:798) at io.debezium.connector.mysql.SnapshotReader.readTimeField(SnapshotReader.java:188) at io.debezium.connector.mysql.SnapshotReader.readField(SnapshotReader.java:152) at io.debezium.connector.mysql.SnapshotReader.lambda$execute$14(SnapshotReader.java:651) at io.debezium.jdbc.JdbcConnection.query(JdbcConnection.java:473) at io.debezium.connector.mysql.SnapshotReader.execute(SnapshotReader.java:641) ... 3 more
      
      // for DATE field
      org.apache.kafka.connect.errors.ConnectException: Unexpected format for DATE column: ����org.apache.kafka.connect.errors.ConnectException: Unexpected format for DATE column: ���� at io.debezium.connector.mysql.AbstractReader.wrap(AbstractReader.java:230) at io.debezium.connector.mysql.AbstractReader.failed(AbstractReader.java:207) at io.debezium.connector.mysql.SnapshotReader.execute(SnapshotReader.java:831) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) at java.lang.Thread.run(Thread.java:748)Caused by: java.lang.RuntimeException: Unexpected format for DATE column: ���� at io.debezium.connector.mysql.MySqlValueConverters.stringToLocalDate(MySqlValueConverters.java:827) at io.debezium.connector.mysql.SnapshotReader.readDateField(SnapshotReader.java:207) at io.debezium.connector.mysql.SnapshotReader.readField(SnapshotReader.java:155) at io.debezium.connector.mysql.SnapshotReader.lambda$execute$14(SnapshotReader.java:651) at io.debezium.jdbc.JdbcConnection.query(JdbcConnection.java:473) at io.debezium.connector.mysql.SnapshotReader.execute(SnapshotReader.java:641) ... 3 more
      
      // for DATETIME field
      org.apache.kafka.connect.errors.ConnectException: Unexpected format for DATE column: ������)org.apache.kafka.connect.errors.ConnectException: Unexpected format for DATE column: ������) at io.debezium.connector.mysql.AbstractReader.wrap(AbstractReader.java:230) at io.debezium.connector.mysql.AbstractReader.failed(AbstractReader.java:207) at io.debezium.connector.mysql.SnapshotReader.execute(SnapshotReader.java:831) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) at java.lang.Thread.run(Thread.java:748)Caused by: java.lang.RuntimeException: Unexpected format for DATE column: ������) at io.debezium.connector.mysql.MySqlValueConverters.containsZeroValuesInDatePart(MySqlValueConverters.java:844) at io.debezium.connector.mysql.SnapshotReader.readTimestampField(SnapshotReader.java:226) at io.debezium.connector.mysql.SnapshotReader.readField(SnapshotReader.java:161) at io.debezium.connector.mysql.SnapshotReader.lambda$execute$14(SnapshotReader.java:651) at io.debezium.jdbc.JdbcConnection.query(JdbcConnection.java:473) at io.debezium.connector.mysql.SnapshotReader.execute(SnapshotReader.java:641) ... 3 more
      

              Unassigned Unassigned
              yangsanity jie yang (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

                Created:
                Updated:
                Resolved: