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

MySQL connector fails to parse default integer value expressed as decimal

    XMLWordPrintable

Details

    • Hide
      1. Start a connector, wait until the snapshot is done (the following DDL should be sourced from the binlog, not introspected from the information schema).
      2. Execute the following DDL:
        CREATE TABLE foo (
            id INT DEFAULT '0.000000'
        )
        
      3. Observe a connector failure:
        2021-05-19 17:26:43,117 ERROR  MySQL|dbserver1|binlog  Failed due to error: Error processing binlog event   [io.debezium.connector.mysql.BinlogReader]
        org.apache.kafka.connect.errors.ConnectException: For input string: "0.000000"
        	at io.debezium.connector.mysql.AbstractReader.wrap(AbstractReader.java:241)
        	at io.debezium.connector.mysql.AbstractReader.failed(AbstractReader.java:218)
        	at io.debezium.connector.mysql.BinlogReader.handleEvent(BinlogReader.java:607)
        	at com.github.shyiko.mysql.binlog.BinaryLogClient.notifyEventListeners(BinaryLogClient.java:1104)
        	at com.github.shyiko.mysql.binlog.BinaryLogClient.listenForEventPackets(BinaryLogClient.java:955)
        	at com.github.shyiko.mysql.binlog.BinaryLogClient.connect(BinaryLogClient.java:595)
        	at com.github.shyiko.mysql.binlog.BinaryLogClient$7.run(BinaryLogClient.java:839)
        	at java.base/java.lang.Thread.run(Thread.java:834)
        Caused by: java.lang.NumberFormatException: For input string: "0.000000"
        	at java.base/java.lang.NumberFormatException.forInputString(NumberFormatException.java:65)
        	at java.base/java.lang.Integer.parseInt(Integer.java:652)
        	at java.base/java.lang.Integer.valueOf(Integer.java:983)
        	at io.debezium.jdbc.JdbcValueConverters.lambda$convertInteger$41(JdbcValueConverters.java:892)
        	at io.debezium.jdbc.JdbcValueConverters.convertValue(JdbcValueConverters.java:1287)
        	at io.debezium.jdbc.JdbcValueConverters.convertInteger(JdbcValueConverters.java:880)
        	at io.debezium.jdbc.JdbcValueConverters.lambda$converter$5(JdbcValueConverters.java:295)
        	at io.debezium.connector.mysql.MySqlDefaultValueConverter.setColumnDefaultValue(MySqlDefaultValueConverter.java:405)
        	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:357)
        	at io.debezium.connector.mysql.antlr.listener.CreateTableParserListener.exitColumnCreateTable(CreateTableParserListener.java:55)
        	at io.debezium.ddl.parser.mysql.generated.MySqlParser$ColumnCreateTableContext.exitRule(MySqlParser.java:4713)
        	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:810)
        	at io.debezium.connector.mysql.BinlogReader.handleEvent(BinlogReader.java:590)
        	... 5 more
        

      It looks like MySQL rounds the default value internally:

      CREATE TABLE `foo15` (
          id INTEGER DEFAULT '1.5'
      );
      SHOW CREATE TABLE foo15;
      -- CREATE TABLE `foo15` (
      --   `id` int(11) DEFAULT '2'
      -- )
      

      Unlike MySQL, SQL Server uses the provided default value as is to build the table but it cannot be used at runtime:

      CREATE TABLE int_as_decimal (col1 INT DEFAULT '0.0', col2 INT DEFAULT '1.5')
      INSERT INTO int_as_decimal (col1, col2) VALUES (DEFAULT,DEFAULT)
      [2021-05-19 11:33:08] [S0001][245] Conversion failed when converting the varchar value '0.0' to data type int.
      

      At this point, it seems that the connector shouldn't even attempt to convert the default to the column's type:

      create table int_as_decimal
      (
          col1 int default '0.0',
          col2 int default 1.5,
          col3 int default 0.0,
          col4 int default '0'
          col5 int default 'blah'
      )
      
      exec sp_columns int_as_decimal, dbo
      
      +-------------+-----------+------------+
      | COLUMN_NAME | TYPE_NAME | COLUMN_DEF |
      +-------------+-----------+------------+
      | col1        | int       | ('0.0')    |
      | col2        | int       | ((1.5))    |
      | col3        | int       | ((0.0))    |
      | col4        | int       | ('0')      |
      | col5        | int       | ('blah')   |
      +-------------+-----------+------------+
      
      Show
      Start a connector, wait until the snapshot is done (the following DDL should be sourced from the binlog, not introspected from the information schema). Execute the following DDL: CREATE TABLE foo ( id INT DEFAULT '0.000000' ) Observe a connector failure: 2021-05-19 17:26:43,117 ERROR MySQL|dbserver1|binlog Failed due to error: Error processing binlog event [io.debezium.connector.mysql.BinlogReader] org.apache.kafka.connect.errors.ConnectException: For input string: "0.000000" at io.debezium.connector.mysql.AbstractReader.wrap(AbstractReader.java:241) at io.debezium.connector.mysql.AbstractReader.failed(AbstractReader.java:218) at io.debezium.connector.mysql.BinlogReader.handleEvent(BinlogReader.java:607) at com.github.shyiko.mysql.binlog.BinaryLogClient.notifyEventListeners(BinaryLogClient.java:1104) at com.github.shyiko.mysql.binlog.BinaryLogClient.listenForEventPackets(BinaryLogClient.java:955) at com.github.shyiko.mysql.binlog.BinaryLogClient.connect(BinaryLogClient.java:595) at com.github.shyiko.mysql.binlog.BinaryLogClient$7.run(BinaryLogClient.java:839) at java.base/java.lang.Thread.run(Thread.java:834) Caused by: java.lang.NumberFormatException: For input string: "0.000000" at java.base/java.lang.NumberFormatException.forInputString(NumberFormatException.java:65) at java.base/java.lang.Integer.parseInt(Integer.java:652) at java.base/java.lang.Integer.valueOf(Integer.java:983) at io.debezium.jdbc.JdbcValueConverters.lambda$convertInteger$41(JdbcValueConverters.java:892) at io.debezium.jdbc.JdbcValueConverters.convertValue(JdbcValueConverters.java:1287) at io.debezium.jdbc.JdbcValueConverters.convertInteger(JdbcValueConverters.java:880) at io.debezium.jdbc.JdbcValueConverters.lambda$converter$5(JdbcValueConverters.java:295) at io.debezium.connector.mysql.MySqlDefaultValueConverter.setColumnDefaultValue(MySqlDefaultValueConverter.java:405) 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:357) at io.debezium.connector.mysql.antlr.listener.CreateTableParserListener.exitColumnCreateTable(CreateTableParserListener.java:55) at io.debezium.ddl.parser.mysql.generated.MySqlParser$ColumnCreateTableContext.exitRule(MySqlParser.java:4713) 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:810) at io.debezium.connector.mysql.BinlogReader.handleEvent(BinlogReader.java:590) ... 5 more It looks like MySQL rounds the default value internally: CREATE TABLE `foo15` ( id INTEGER DEFAULT '1.5' ); SHOW CREATE TABLE foo15; -- CREATE TABLE `foo15` ( -- `id` int(11) DEFAULT '2' -- ) Unlike MySQL, SQL Server uses the provided default value as is to build the table but it cannot be used at runtime: CREATE TABLE int_as_decimal (col1 INT DEFAULT '0.0', col2 INT DEFAULT '1.5') INSERT INTO int_as_decimal (col1, col2) VALUES (DEFAULT,DEFAULT) [2021-05-19 11:33:08] [S0001][245] Conversion failed when converting the varchar value '0.0' to data type int. At this point, it seems that the connector shouldn't even attempt to convert the default to the column's type: create table int_as_decimal ( col1 int default '0.0', col2 int default 1.5, col3 int default 0.0, col4 int default '0' col5 int default 'blah' ) exec sp_columns int_as_decimal, dbo +-------------+-----------+------------+ | COLUMN_NAME | TYPE_NAME | COLUMN_DEF | +-------------+-----------+------------+ | col1 | int | ('0.0') | | col2 | int | ((1.5)) | | col3 | int | ((0.0)) | | col4 | int | ('0') | | col5 | int | ('blah') | +-------------+-----------+------------+

    Description

      MySQL connector fails to parse a DDL statement if it contains the default value for an INTEGER column expressed as DECIMAL.

      Attachments

        Activity

          People

            Unassigned Unassigned
            sergeimorozov Sergei Morozov
            Votes:
            0 Vote for this issue
            Watchers:
            7 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: