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

delete message "op:d" on tables with unique combination of 2 primary keys = (composite keys) , the d records are not sent

    Details

    • Steps to Reproduce:
      Hide

      Adapted scenario
      Please find enclosed the 3 table definitions (= DDL) and one 1 with inserts. DDL

      • travel_documents.sql is the DDL for table with the composite keys: file_id (pk1) & travel_id (pk2)
      • travel.sql is the DDL of parent table (travel_id)
      • file.sql is the DDL of the other parent table (file_id)
      • travel_file_inserts.sql is DDL for inserts of tables

      after creating the mysql tables config the mysql connector as follows:

      ```
      connector.class=io.debezium.connector.mysql.MySqlConnector
      database.user=username
      database.server.id=4111
      tasks.max=1
      database.history.kafka.bootstrap.servers=ain-kafka-kafka-bootstrap:9092
      database.history.kafka.topic=schema-changes.production
      transforms=route
      database.server.name=ainprod
      database.port=3306
      transforms.route.type=org.apache.kafka.connect.transforms.RegexRouter
      transforms.route.regex=([^.])\.([^.])\.([^.]+)
      database.hostname=x.x.x.x
      database.password=secretpassword
      transforms.route.replacement=$3
      ```
      Adapted scenario
      1. Next step is to add a record in the travel_documents table . Expected result i the creation of `c` message in the topic " travel_documents" OK
      2. Open the "file" table and noticed there is a row created
      3. Remove the new created record in the "file" table you can use the file_id as reference. and notice there is a `d` and null message sent by debezium source connector OK
      4. Open the travel_documents table and noticed there is a row deleted. OK
      5. Go to check the travel_documents topic stream and notice there is no `d` and `null` message sent. NOT OK

      Show
      Adapted scenario Please find enclosed the 3 table definitions (= DDL) and one 1 with inserts. DDL travel_documents.sql is the DDL for table with the composite keys: file_id (pk1) & travel_id (pk2) travel.sql is the DDL of parent table (travel_id) file.sql is the DDL of the other parent table (file_id) travel_file_inserts.sql is DDL for inserts of tables after creating the mysql tables config the mysql connector as follows: ``` connector.class=io.debezium.connector.mysql.MySqlConnector database.user=username database.server.id=4111 tasks.max=1 database.history.kafka.bootstrap.servers=ain-kafka-kafka-bootstrap:9092 database.history.kafka.topic=schema-changes.production transforms=route database.server.name=ainprod database.port=3306 transforms.route.type=org.apache.kafka.connect.transforms.RegexRouter transforms.route.regex=( [^.] )\.( [^.] )\.( [^.] +) database.hostname=x.x.x.x database.password=secretpassword transforms.route.replacement=$3 ``` Adapted scenario 1. Next step is to add a record in the travel_documents table . Expected result i the creation of `c` message in the topic " travel_documents" OK 2. Open the "file" table and noticed there is a row created 3. Remove the new created record in the "file" table you can use the file_id as reference. and notice there is a `d` and null message sent by debezium source connector OK 4. Open the travel_documents table and noticed there is a row deleted. OK 5. Go to check the travel_documents topic stream and notice there is no `d` and `null` message sent. NOT OK

      Description

      We discovered the mysql source connector is not creating a delete message (op: d) for all mysql tables with so called composite keys. Those tables are only having 2 columns and both are primary key. A create message (op:c) is created, but an "op:d" message is not sent when a row is deleted in this table.

        Gliffy Diagrams

          Attachments

          1. file.sql
            1 kB
          2. travel_documents.sql
            1 kB
          3. travel_file_inserts.sql
            3 kB
          4. travel.sql
            1 kB

            Activity

              People

              • Assignee:
                Unassigned
                Reporter:
                bbiking Bert van Houten
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: