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

Exception when PK definition precedes column definition

    XMLWordPrintable

Details

    Description

      I got error 

      The primary key cannot reference a non-existant column

      Full message:

       org.apache.kafka.connect.errors.ConnectException: The primary key cannot reference a non-existant column'staff_num' in table 'my_database.staff'
       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:600)
       at com.github.shyiko.mysql.binlog.BinaryLogClient.notifyEventListeners(BinaryLogClient.java:1130)
       at com.github.shyiko.mysql.binlog.BinaryLogClient.listenForEventPackets(BinaryLogClient.java:978)
       at com.github.shyiko.mysql.binlog.BinaryLogClient.connect(BinaryLogClient.java:581)
       at com.github.shyiko.mysql.binlog.BinaryLogClient$7.run(BinaryLogClient.java:860)
       at java.lang.Thread.run(Thread.java:748)
       Caused by: java.lang.IllegalArgumentException: The primary key cannot reference a non-existant column'staff_num' in table 'my_database.staff'
       at io.debezium.relational.TableEditorImpl.setPrimaryKeyNames(TableEditorImpl.java:133)
       at io.debezium.connector.mysql.antlr.MySqlAntlrDdlParser.parsePrimaryIndexColumnNames(MySqlAntlrDdlParser.java:290)
       at io.debezium.connector.mysql.antlr.listener.CreateTableParserListener.lambda$enterPrimaryKeyTableConstraint$4(CreateTableParserListener.java:121)
       at io.debezium.connector.mysql.antlr.MySqlAntlrDdlParser.runIfNotNull(MySqlAntlrDdlParser.java:318)
       at io.debezium.connector.mysql.antlr.listener.CreateTableParserListener.enterPrimaryKeyTableConstraint(CreateTableParserListener.java:120)
       at io.debezium.ddl.parser.mysql.generated.MySqlParser$PrimaryKeyTableConstraintContext.enterRule(MySqlParser.java:8523)
       at io.debezium.antlr.ProxyParseTreeListenerUtil.delegateEnterRule(ProxyParseTreeListenerUtil.java:46)
       at io.debezium.connector.mysql.antlr.listener.MySqlAntlrDdlParserListener.enterEveryRule(MySqlAntlrDdlParserListener.java:89)
       at org.antlr.v4.runtime.tree.ParseTreeWalker.enterRule(ParseTreeWalker.java:41)
       at org.antlr.v4.runtime.tree.ParseTreeWalker.walk(ParseTreeWalker.java:25)
       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 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:314)
       at io.debezium.connector.mysql.BinlogReader.handleQueryEvent(BinlogReader.java:793)
       at io.debezium.connector.mysql.BinlogReader.handleEvent(BinlogReader.java:583)
       ... 5 more
      

      I got this error when add new table with declaration:

      CREATE TABLE staff (
         PRIMARY KEY (staff_num),
         staff_num      INT(5)       NOT NULL,
         first_name     VARCHAR(100) NOT NULL,
         pens_in_drawer INT(2)       NOT NULL,
         CONSTRAINT pens_in_drawer_range
             CHECK(pens_in_drawer BETWEEN 1 AND 99)
      );
      

      I found this trick on sqlguide https://www.sqlstyle.guide/#constraints-and-keys

      But if I change sql to:

      CREATE TABLE staff (
         staff_num      INT(5)       NOT NULL PRIMARY KEY,
         first_name     VARCHAR(100) NOT NULL,
         pens_in_drawer INT(2)       NOT NULL,
         CONSTRAINT pens_in_drawer_range
             CHECK(pens_in_drawer BETWEEN 1 AND 99)
      );
      

      When Debezium MySql connector work without any errors.

      I think Debezim use too strict definition. More strict then Mysql Server.

       

      Attachments

        Activity

          People

            rk3rn3r René Kerner
            oke11o Sergey Bevzenko (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: