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

            kewei.shang kewei shang (Inactive)
            kewei.shang kewei shang (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: