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

Timestamp default value in 'yyyy-mm-dd' format fails MySQL connector

    XMLWordPrintable

    Details

      Description

      Applying the following DDL to MySQL fails the MySQL connector task: 

      CREATE DATABASE db;
      USE db;
      CREATE TABLE IF NOT EXISTS `db`.`t` (
        `my_timestamp` TIMESTAMP NOT NULL default '2020-01-02'
      );
      

      with the following exception: 

      org.apache.kafka.connect.errors.ConnectException: Timestamp format must be yyyy-mm-dd hh:mm:ss[.fffffffff]org.apache.kafka.connect.errors.ConnectException: Timestamp format must be yyyy-mm-dd hh:mm:ss[.fffffffff] 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.BinlogReader.handleEvent(BinlogReader.java:604) at com.github.shyiko.mysql.binlog.BinaryLogClient.notifyEventListeners(BinaryLogClient.java:1100) at com.github.shyiko.mysql.binlog.BinaryLogClient.listenForEventPackets(BinaryLogClient.java:951) at com.github.shyiko.mysql.binlog.BinaryLogClient.connect(BinaryLogClient.java:594) at com.github.shyiko.mysql.binlog.BinaryLogClient$7.run(BinaryLogClient.java:838) at java.base/java.lang.Thread.run(Thread.java:834)Caused by: java.lang.IllegalArgumentException: Timestamp format must be yyyy-mm-dd hh:mm:ss[.fffffffff] at java.sql/java.sql.Timestamp.valueOf(Timestamp.java:196) at io.debezium.connector.mysql.MySqlDefaultValueConverter.convertToTimestamp(MySqlDefaultValueConverter.java:161) at io.debezium.connector.mysql.MySqlDefaultValueConverter.convert(MySqlDefaultValueConverter.java:81) at io.debezium.connector.mysql.MySqlDefaultValueConverter.setColumnDefaultValue(MySqlDefaultValueConverter.java:279) at io.debezium.connector.mysql.antlr.listener.CreateTableParserListener.convertDefaultValueToSchemaType(CreateTableParserListener.java:146) at java.base/java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:195) at java.base/java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:195) at java.base/java.util.ArrayList$ArrayListSpliterator.forEachRemaining(ArrayList.java:1655) at java.base/java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:484) at java.base/java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:474) at java.base/java.util.stream.ReduceOps$ReduceOp.evaluateSequential(ReduceOps.java:913) at java.base/java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234) at java.base/java.util.stream.ReferencePipeline.collect(ReferencePipeline.java:578) at io.debezium.connector.mysql.antlr.listener.CreateTableParserListener.lambda$exitColumnCreateTable$1(CreateTableParserListener.java:75) at io.debezium.connector.mysql.antlr.MySqlAntlrDdlParser.runIfNotNull(MySqlAntlrDdlParser.java:350) at io.debezium.connector.mysql.antlr.listener.CreateTableParserListener.exitColumnCreateTable(CreateTableParserListener.java:55) at io.debezium.ddl.parser.mysql.generated.MySqlParser$ColumnCreateTableContext.exitRule(MySqlParser.java:4682) at io.debezium.antlr.ProxyParseTreeListenerUtil.delegateExitRule(ProxyParseTreeListenerUtil.java:64) at io.debezium.connector.mysql.antlr.listener.MySqlAntlrDdlParserListener.exitEveryRule(MySqlAntlrDdlParserListener.java:106) at org.antlr.v4.runtime.tree.ParseTreeWalker.exitRule(ParseTreeWalker.java:48) at org.antlr.v4.runtime.tree.ParseTreeWalker.walk(ParseTreeWalker.java:30) at org.antlr.v4.runtime.tree.ParseTreeWalker.walk(ParseTreeWalker.java:28) at org.antlr.v4.runtime.tree.ParseTreeWalker.walk(ParseTreeWalker.java:28) at org.antlr.v4.runtime.tree.ParseTreeWalker.walk(ParseTreeWalker.java:28) at org.antlr.v4.runtime.tree.ParseTreeWalker.walk(ParseTreeWalker.java:28) at io.debezium.antlr.AntlrDdlParser.parse(AntlrDdlParser.java:85) at io.debezium.connector.mysql.MySqlSchema.applyDdl(MySqlSchema.java:326) at io.debezium.connector.mysql.BinlogReader.handleQueryEvent(BinlogReader.java:807) at io.debezium.connector.mysql.BinlogReader.handleEvent(BinlogReader.java:587) ... 5 more
      

       

      In addition, using any of the following values as the default value of a TIMESTAMP column would fail the connector task:

      • 2020-01-02:03:04:05
      • 2020-01-02-03:04:05
      • 2020-01-02:03:
      • 2020-01-02:03:04

      The reason is that MySQL can auto-correct the value when applying the DDL, but the raw DDL is sent to Debezium and when Debezium tries to update the internal schema of the table, it fails to do so.

        Attachments

          Activity

            People

            Assignee:
            kewei.shang kewei shang
            Reporter:
            kewei.shang kewei shang
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: