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

Mysql connector: The primary key cannot reference a non-existant column 'id' in table '***'

    Details

    • Steps to Reproduce:
      Hide

      1. Create a table and the primary key is 'id' column.
      2. Delete the 'id' column, and save changes.
      3. Change one of other columns to primary key.

      Show
      1. Create a table and the primary key is 'id' column. 2. Delete the 'id' column, and save changes. 3. Change one of other columns to primary key.

      Description

      I have an table named 'address'. And the column 'id' is the primary key.
      DDL follows:

      CREATE TABLE `addresses` (
        `customer_id` int(11) NOT NULL,
        `street` varchar(255) NOT NULL,
        `city` varchar(255) NOT NULL,
        `state` varchar(255) NOT NULL,
        `zip` varchar(255) NOT NULL,
        `type` enum('SHIPPING','BILLING','LIVING') NOT NULL,
        `id` int(4) NOT NULL AUTO_INCREMENT,
        PRIMARY KEY (`id`) USING BTREE
      ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;
      

      Then, I delete the 'id' column and save changes. The connector and task is running now.

      Then, I chagne column 'street' to primary key.
      DDL follow:

      ALTER TABLE `inventory`.`addresses` 
      ADD PRIMARY KEY (`street`);
      

      Then, the connector is failed. Error logs follows:

      2019-10-16 08:36:07,787 ERROR  MySQL|dbserver5|binlog  Error during binlog processing. Last offset stored = null, binlog reader near position = mysql-bin.000006/3992602   [io.debezium.connector.mysql.BinlogReader]
      2019-10-16 08:36:07,787 ERROR  MySQL|dbserver5|binlog  Failed due to error: Error processing binlog event   [io.debezium.connector.mysql.BinlogReader]
      org.apache.kafka.connect.errors.ConnectException: The primary key cannot reference a non-existant column'id' in table 'inventory.addresses'
      	at io.debezium.connector.mysql.AbstractReader.wrap(AbstractReader.java:230)
      	at io.debezium.connector.mysql.AbstractReader.failed(AbstractReader.java:208)
      	at io.debezium.connector.mysql.BinlogReader.handleEvent(BinlogReader.java:523)
      	at com.github.shyiko.mysql.binlog.BinaryLogClient.notifyEventListeners(BinaryLogClient.java:1095)
      	at com.github.shyiko.mysql.binlog.BinaryLogClient.listenForEventPackets(BinaryLogClient.java:943)
      	at com.github.shyiko.mysql.binlog.BinaryLogClient.connect(BinaryLogClient.java:580)
      	at com.github.shyiko.mysql.binlog.BinaryLogClient$7.run(BinaryLogClient.java:825)
      	at java.lang.Thread.run(Thread.java:748)
      Caused by: java.lang.IllegalArgumentException: The primary key cannot reference a non-existant column'id' in table 'inventory.addresses'
      	at io.debezium.relational.TableEditorImpl.setPrimaryKeyNames(TableEditorImpl.java:132)
      	at io.debezium.relational.TableImpl.edit(TableImpl.java:116)
      	at io.debezium.relational.Tables.editTable(Tables.java:314)
      	at io.debezium.connector.mysql.antlr.listener.AlterTableParserListener.enterAlterTable(AlterTableParserListener.java:59)
      	at io.debezium.ddl.parser.mysql.generated.MySqlParser$AlterTableContext.enterRule(MySqlParser.java:11779)
      	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 io.debezium.antlr.AntlrDdlParser.parse(AntlrDdlParser.java:85)
      	at io.debezium.connector.mysql.MySqlSchema.applyDdl(MySqlSchema.java:306)
      	at io.debezium.connector.mysql.BinlogReader.handleQueryEvent(BinlogReader.java:716)
      	at io.debezium.connector.mysql.BinlogReader.handleEvent(BinlogReader.java:507)
      	... 5 more
      

      The 'position = mysql-bin.000006/3992602' in binary log follows:

      # at 3992602
      #191016  8:26:51 server id 223344  end_log_pos 3992749 CRC32 0x4952d894 	Query	thread_id=164	exec_time=0	error_code=0
      SET TIMESTAMP=1571214411/*!*/;
      ALTER TABLE `inventory`.`addresses`
      ADD PRIMARY KEY (`street`)
      /*!*/;
      

        Gliffy Diagrams

          Attachments

            Activity

              People

              • Assignee:
                jpechanec Jiri Pechanec
                Reporter:
                kai.hou kai hou
              • Votes:
                0 Vote for this issue
                Watchers:
                4 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: