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

Internal schema may get corrupted due to duplicates in database history topic

XMLWordPrintable

    • False
    • None
    • False
    • Hide

      From the mailing list thread:

      Hi Community,
       
      My team stumbled upon an issue where the internal Debezium schema is corrupted due to the database history topic containing duplicates.
       
      Here's an example of the messages in the topic:

      {"f":"mysql-bin-changelog.276589","p":88563795,"d":"CREATE TABLE tbl_cleaned LIKE tbl"}
{"f":"mysql-bin-changelog.276599","p":7066,"d":"RENAME TABLE tbl TO tbl_backup"}
{"f":"mysql-bin-changelog.276599","p":7066,"d":"RENAME TABLE tbl_cleaned TO tbl"}
{"f":"mysql-bin-changelog.276599","p":14390,"d":"DROP TABLE `db_redacted`.`tbl_backup`"}
{"f":"mysql-bin-changelog.276599","p":7066,"d":"RENAME TABLE tbl TO tbl_backup"} // duplicate
{"f":"mysql-bin-changelog.276599","p":7066,"d":"RENAME TABLE tbl_cleaned TO tbl"} // duplicate
{"f":"mysql-bin-changelog.276599","p":14390,"d":"DROP TABLE `db_redacted`.`tbl_backup`"} // duplicate
{"f":"mysql-bin-changelog.281865","p":40305,"d":"DROP TABLE IF EXISTS `db_redacted`.`tbl_cleaned`"} 

      The keys in the above documents translate to the following in the actual message schema:

      {
  "position": {
    "file": "...", // f
    "pos": ...     // p
  },
  "ddl": "...",    // d
}

      If there were no duplicates, the statement on line 3 would create table "tbl", so all the CDC events would be interpreted properly. But what happens instead is:

      1. The duplicate statement on line 5 renames "tbl" to "tbl_backup", so after it "tbl" doesn't exist in the schema.
      2. The duplicate statement on line 6 attempts to rename "tbl_cleaned" to "tbl" but "tbl_cleaned" was dropped by the statement on line 3, so nothing happens.

      As a result, table "tbl" doesn't exist in the internal schema.

      Show
      From the mailing list thread : Hi Community,   My team stumbled upon an issue where the internal Debezium schema is corrupted due to the database history topic containing duplicates.   Here's an example of the messages in the topic: {"f":"mysql-bin-changelog.276589","p":88563795,"d":"CREATE TABLE tbl_cleaned LIKE tbl"} {"f":"mysql-bin-changelog.276599","p":7066,"d":"RENAME TABLE tbl TO tbl_backup"} {"f":"mysql-bin-changelog.276599","p":7066,"d":"RENAME TABLE tbl_cleaned TO tbl"} {"f":"mysql-bin-changelog.276599","p":14390,"d":"DROP TABLE `db_redacted`.`tbl_backup`"} {"f":"mysql-bin-changelog.276599","p":7066,"d":"RENAME TABLE tbl TO tbl_backup"} // duplicate {"f":"mysql-bin-changelog.276599","p":7066,"d":"RENAME TABLE tbl_cleaned TO tbl"} // duplicate {"f":"mysql-bin-changelog.276599","p":14390,"d":"DROP TABLE `db_redacted`.`tbl_backup`"} // duplicate {"f":"mysql-bin-changelog.281865","p":40305,"d":"DROP TABLE IF EXISTS `db_redacted`.`tbl_cleaned`"}  The keys in the above documents translate to the following in the actual message schema: { "position": { "file": "...", // f "pos": ... // p }, "ddl": "...", // d } If there were no duplicates, the statement on line 3 would create table "tbl", so all the CDC events would be interpreted properly. But what happens instead is: The duplicate statement on line 5 renames "tbl" to "tbl_backup", so after it "tbl" doesn't exist in the schema. The duplicate statement on line 6 attempts to rename "tbl_cleaned" to "tbl" but "tbl_cleaned" was dropped by the statement on line 3, so nothing happens. As a result, table "tbl" doesn't exist in the internal schema.

      In order to make your issue reports as actionable as possible, please provide the following information, depending on the issue type.

      Bug report

      For bug reports, provide this information, please:

      What Debezium connector do you use and what version?

      v1.9.2.Final

      What is the connector configuration?

      This is a design issue unrelated to the configuration.

      What is the captured database version and mode of depoyment?

      Irrelevant

      What behaviour do you expect?

      The connector ignores duplicates in the database schema history.

      What behaviour do you see?

      The connector consumes duplicates from the database schema history which causes schema corruption.

      Do you see the same behaviour using the latest relesead Debezium version?

      I haven't tested it but the likely exists in all versions since it's by design.

      Do you have the connector logs, ideally from start till finish?

      No.

      How to reproduce the issue using our tutorial deployment?

      Reproducing the issue requires an emergency shutdown of the Kafka Connect worker (e.g. due to an out-of-memory issue). It's hard to reproduce it in a reliable way. See details in the steps to reproduce.

      Feature request or enhancement

      Which use case/requirement will be addressed by the proposed feature?

      Connector stability.

      Implementation ideas (optional)

      1. Make the keys of schema change history globally unique by adding the sequence number within the binlog event to the schema change metadata.
      2. When recovering schema from history, keep track of the binlog filename, position, and sequence number of the last processed schema change. Ignore all changes with the binlog filename, position, and sequence number less or equal to the last processed thereby deduplicating history.

      TODO:

      Is the source metadata of schema change events of other connectors sufficient for deduplication? If not, what do we do to address the issue in the MySQL connector without breaking others?

              Unassigned Unassigned
              sergeimorozov Sergei Morozov (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Created:
                Updated: