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

Schema change events of excluded databases are discarded

    XMLWordPrintable

Details

    • False
    • False
    • Undefined
    • Hide
      1. Before starting the connector make sure you have a database to be excluded with a table in it (I'll use  db_excludetest.t1 in my examples).
      2. Start the connector. In my case I have the following configuration:
        name=debezium-mysql-connector
        connector.class=io.debezium.connector.mysql.MySqlConnector
        database.hostname=localhost
        database.port=3306
        database.user=mysqluser
        database.password=mysqlpw
        database.server.id=184054
        database.server.name=mysqlsrvr
        database.history.kafka.bootstrap.servers=localhost:9092
        database.history.kafka.topic=debezium-mysql-connector.dbhistory
        include.schema.changes=false
        

        Please note that database.history.store.only.monitored.tables.ddl is not specified, which means the default value is used.

      3. Issue the following DDL to make sure schema change events are stored in dbhistory prior to the database exclusion:
        ALTER TABLE db_excludetest.t1 ADD c1 varchar(100) NULL;
        
      4. Observe a new message in dbhistory:
        {
          "source": {
            "server": "mysqlsrvr"
          },
          "position": {
            "transaction_id": null,
            "ts_sec": 1623412247,
            "file": "mysql-bin.000004",
            "pos": 4547,
            "server_id": 112233
          },
          "databaseName": "db_excludetest",
          "ddl": "ALTER TABLE db_excludetest.t1 ADD c1 varchar(100) NULL",
          "tableChanges": [
            {
              "type": "ALTER",
              "id": "\"db_excludetest\".\"t1\"",
              "table": {
                "defaultCharsetName": "latin1",
                "primaryKeyColumnNames": [
                  "id"
                ],
                "columns": [
                  {
                    "name": "id",
                    "jdbcType": 4,
                    "typeName": "INT UNSIGNED",
                    "typeExpression": "INT UNSIGNED",
                    "charsetName": null,
                    "length": 10,
                    "position": 1,
                    "optional": false,
                    "autoIncremented": true,
                    "generated": true
                  },
                  {
                    "name": "c1",
                    "jdbcType": 12,
                    "typeName": "VARCHAR",
                    "typeExpression": "VARCHAR",
                    "charsetName": "latin1",
                    "length": 100,
                    "position": 2,
                    "optional": true,
                    "autoIncremented": false,
                    "generated": false
                  }
                ]
              }
            }
          ]
        }
        
      5. Add the following line to the connector configuration and restart the connector:
        database.exclude.list=db_excludetest
        
      6. Issue another DDL:
        ALTER TABLE db_excludetest.t1 ADD c2 varchar(100) NULL;
        
      7. Observe that there is no new messages in dbhistory.
      Show
      Before starting the connector make sure you have a database to be excluded with a table in it (I'll use  db_excludetest.t1 in my examples). Start the connector. In my case I have the following configuration: name=debezium-mysql-connector connector.class=io.debezium.connector.mysql.MySqlConnector database.hostname=localhost database.port=3306 database.user=mysqluser database.password=mysqlpw database.server.id=184054 database.server.name=mysqlsrvr database.history.kafka.bootstrap.servers=localhost:9092 database.history.kafka.topic=debezium-mysql-connector.dbhistory include.schema.changes= false Please note that database.history.store.only.monitored.tables.ddl is not specified, which means the default value is used. Issue the following DDL to make sure schema change events are stored in dbhistory prior to the database exclusion: ALTER TABLE db_excludetest.t1 ADD c1 varchar (100) NULL ; Observe a new message in dbhistory: { "source" : { "server" : "mysqlsrvr" }, "position" : { "transaction_id" : null , "ts_sec" : 1623412247, "file" : "mysql-bin.000004" , "pos" : 4547, "server_id" : 112233 }, "databaseName" : "db_excludetest" , "ddl" : "ALTER TABLE db_excludetest.t1 ADD c1 varchar(100) NULL" , "tableChanges" : [ { "type" : "ALTER" , "id" : "\" db_excludetest\ ".\" t1\"", "table" : { "defaultCharsetName" : "latin1" , "primaryKeyColumnNames" : [ "id" ], "columns" : [ { "name" : "id" , "jdbcType" : 4, "typeName" : "INT UNSIGNED" , "typeExpression" : "INT UNSIGNED" , "charsetName" : null , "length" : 10, "position" : 1, "optional" : false , "autoIncremented" : true , "generated" : true }, { "name" : "c1" , "jdbcType" : 12, "typeName" : "VARCHAR" , "typeExpression" : "VARCHAR" , "charsetName" : "latin1" , "length" : 100, "position" : 2, "optional" : true , "autoIncremented" : false , "generated" : false } ] } } ] } Add the following line to the connector configuration and restart the connector: database.exclude.list=db_excludetest Issue another DDL: ALTER TABLE db_excludetest.t1 ADD c2 varchar (100) NULL ; Observe that there is no new messages in dbhistory.

    Description

      If a database name is added to the exclude list it's schema change events are not stored in dbhistory topic and therefore discarded.

       

      According to my analysis the following is happening:

      1. The "add column" statement comes in to parseDdl.
      2. It's successfully parsed in line 210.
      3. It passes the check in line 229 as it was successfully parsed and it's therefore not empty.
      4. Then in line 236 it fails the check and the schema change event gets discarded as the database name is excluded (acceptableDatabase(dbName) returns false).

       

      We've discovered this issue this way: after a restart the connector has failed with a message saying it can't modify a column as it doesn't exist. We've examined the dbhistory topic and haven't found any statements that would add the column to the table although the column does exist in this table. We assume since we've excluded a database we've lost some of the schema change history because of this bug. In particular we've lost the statement that adds the column. Therefore the connector fails upon receiving the "modify column" statement as it doesn't know such column exists.

       

      Attachments

        Activity

          People

            jpechane Jiri Pechanec
            ramanenka Vadzim Ramanenka (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: