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

MySQL Connector keeps stale offset metadata after snapshot.new.tables is changed

XMLWordPrintable

    • Icon: Bug Bug
    • Resolution: Done
    • Icon: Minor Minor
    • 1.4.0.Alpha1
    • 1.2.3.Final
    • mysql-connector
    • None
    • False
    • False
    • Undefined
    • Hide
      1. Create new connector with "snapshot.new.tables": "parallel", "snapshot.mode": "initial", and some set of tables for table.whitelist. Offsets for this connector will contain table_whitelist, table_blacklist, database_whitelist, and database_blacklist fields
      2. Remove the "snapshot.new.tables: parallel" setting. (At this point I would expect the connector's offsets should not contain the additional filter metadata anymore, but they remain)
      3. Convert the connector to "schema_only" mode (Following the instructions here: https://groups.google.com/g/debezium/c/Iw4BgLZ8Mq4/m/SZBLGwEaAQAJ)
      4. Add a table to the connector's table.whitelist.

      After this you should observe zero new writes coming through for this connector, manually removing the filter metadata from the connector's offsets will allow writes to resume.

      Show
      Create new connector with "snapshot.new.tables": "parallel", "snapshot.mode": "initial", and some set of tables for table.whitelist. Offsets for this connector will contain table_whitelist, table_blacklist, database_whitelist, and database_blacklist fields Remove the "snapshot.new.tables: parallel" setting. (At this point I would expect the connector's offsets should not contain the additional filter metadata anymore, but they remain) Convert the connector to "schema_only" mode (Following the instructions here: https://groups.google.com/g/debezium/c/Iw4BgLZ8Mq4/m/SZBLGwEaAQAJ ) Add a table to the connector's table.whitelist. After this you should observe zero new writes coming through for this connector, manually removing the filter metadata from the connector's offsets will allow writes to resume.

      If a MySQL connector is initially created with `snapshot.new.tables: parallel` configured, and then that configuration is removed, the custom offset metadata will remain.

      In certain edge cases, the resulting state will halt message processing.

      The "custom offset metadata" I'm referring to is the following

      public static final String DATABASE_WHITELIST_KEY = "database_whitelist";
public static final String DATABASE_BLACKLIST_KEY = "database_blacklist";
public static final String TABLE_WHITELIST_KEY = "table_whitelist";
public static final String TABLE_BLACKLIST_KEY = "table_blacklist";

      Which was added as a part of: DBZ-387

      The problem with this metadata remaining in the offsets arises when the connector is modified to `schema_only` mode and a new table is added to its whitelist. The connector with the newly updated whitelist will sit consuming no rows at all, with no error messages.

      The fix for a connector in this state is to manually change its most recent offset event. An example of the the change needed to fix a connector in this state is described below:

      {
"table_whitelist":"shopify_shard_126.(some|tables)","ts_sec":1600799733,"file":"binlog.233769","table_blacklist":null,"pos":79078651,"database_whitelist":"shopify_shard_126","database_blacklist":null,"gtids":"2636c975-cca6-11e8-abee-42010aef0017:1-33622507,311af35b-cca6-11e8-a7f4-42010aef0016:1-553659615,730ba419-0019-11ea-a4eb-42010aef00ee:1-901577661,a75c0b5e-001a-11ea-88cc-42010ae700a7:1-200018057,d5f00eed-cca5-11e8-a548-42010ae7000c:1-244272080,e9cceed4-cca5-11e8-a5fa-42010ae70029:1-295996960","row":1,"server_id":2310167,"event":3
}

      to: 

      {
"ts_sec":1600799733,"file":"binlog.233769","pos":79078651,"gtids":"2636c975-cca6-11e8-abee-42010aef0017:1-33622507,311af35b-cca6-11e8-a7f4-42010aef0016:1-553659615,730ba419-0019-11ea-a4eb-42010aef00ee:1-901577661,a75c0b5e-001a-11ea-88cc-42010ae700a7:1-200018057,d5f00eed-cca5-11e8-a548-42010ae7000c:1-244272080,e9cceed4-cca5-11e8-a5fa-42010ae70029:1-295996960","row":1,"server_id":2310167,"event":3
}

      I am not sure why the connector gets into a state of processing no new records when this occurs, but I have verified that the mismatch of `table_whitelist` from the offset metadata and `table.whitelist` from the connector config causes this problem.

              Unassigned Unassigned
              johnjmartin John Martin (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Created:
                Updated:
                Resolved: