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

Debezium fails to undo change event due to transaction id ending in ffffffff with LogMiner

XMLWordPrintable

    • False
    • None
    • False
    • Hide

      Bug report

      step 1: Open two oracle session.

      step 2: Insert a data  in the first session without commit.

      SQL>  INSERT INTO CHEN.TEST_LOOP values(1,'1','xxxXXXXXX','cccCCC',sysdate,systimestamp,66.66);

      1 row created.

      step 3: Insert a data which has same primary key in the second session without commit.

      SQL> INSERT INTO CHEN.TEST_LOOP values(1,'2','xxxXXXXXX','cccCCC',sysdate,systimestamp,22.22);

      step 4: Commit in the first session.

      SQL>Commit complete.

      step 5: Commit in the second session.

      SQL>ERROR at line 1:
      ORA-00001: unique constraint (CHEN.TEST_LOOP_PK) violated

       

      Then oracle-connector-logminer will got two insert sql  and one sql with rollback mark because ORA-00001: unique constraint (CHEN.TEST_LOOP_PK) violated

       

      insert into "CHEN"."TEST_LOOP"("NUMTEST","VARTWOTEST","VARTEST","CHARTEST","DATETEST","TIMETEST","DOUBLETEST") values ('1','1','xxxXXXXXX','cccCCC                                            ',TO_DATE('2022-05-05 22:53:24', 'YYYY-MM-DD HH24:MI:SS'),TO_TIMESTAMP('2022-05-05 22:53:24.220999'),'66.66'); ROWID=AAAR33AAHAAAAsSAAL,tx_id=0a000d0045190000
      
      insert into "CHEN"."TEST_LOOP"("NUMTEST","VARTWOTEST","VARTEST","CHARTEST","DATETEST","TIMETEST","DOUBLETEST") values ('1','2','xxxXXXXXX','cccCCC                                            ',TO_DATE('2022-05-05 22:53:29', 'YYYY-MM-DD HH24:MI:SS'),TO_TIMESTAMP('2022-05-05 22:53:29.557254'),'22.22'); ROWID=AAAR33AAHAAAAs8AAB,tx_id=020014003d190000
      
      delete from "CHEN"."TEST_LOOP" where ROWID = 'AAAR33AAHAAAAs8AAB'; ,txid is:02001400ffffffff 

       

      I can't find any transactions like '02001400ffffffff'. Therefore, the last insert SQL cannot be deleted from the queue, the useless insert will push to kafka topic.

      If my sink database use upsert mode ,it will result in a different between source and sink.

      Show
      Bug report step 1: Open two oracle session. step 2: Insert a data  in the first session without commit. SQL>  INSERT INTO CHEN.TEST_LOOP values(1,'1','xxxXXXXXX','cccCCC',sysdate,systimestamp,66.66); 1 row created. step 3: Insert a data which has same primary key in the second session without commit. SQL> INSERT INTO CHEN.TEST_LOOP values(1,'2','xxxXXXXXX','cccCCC',sysdate,systimestamp,22.22); step 4: Commit in the first session. SQL>Commit complete. step 5: Commit in the second session. SQL>ERROR at line 1: ORA-00001: unique constraint (CHEN.TEST_LOOP_PK) violated   Then oracle-connector-logminer will got two insert sql  and one sql with rollback mark because ORA-00001: unique constraint (CHEN.TEST_LOOP_PK) violated   insert into "CHEN" . "TEST_LOOP" ( "NUMTEST" , "VARTWOTEST" , "VARTEST" , "CHARTEST" , "DATETEST" , "TIMETEST" , "DOUBLETEST" ) values ( '1' , '1' , 'xxxXXXXXX' , 'cccCCC                                            ' ,TO_DATE( '2022-05-05 22:53:24' , 'YYYY-MM-DD HH24:MI:SS' ),TO_TIMESTAMP( '2022-05-05 22:53:24.220999' ), '66.66' ); ROWID=AAAR33AAHAAAAsSAAL,tx_id=0a000d0045190000 insert into "CHEN" . "TEST_LOOP" ( "NUMTEST" , "VARTWOTEST" , "VARTEST" , "CHARTEST" , "DATETEST" , "TIMETEST" , "DOUBLETEST" ) values ( '1' , '2' , 'xxxXXXXXX' , 'cccCCC                                            ' ,TO_DATE( '2022-05-05 22:53:29' , 'YYYY-MM-DD HH24:MI:SS' ),TO_TIMESTAMP( '2022-05-05 22:53:29.557254' ), '22.22' ); ROWID=AAAR33AAHAAAAs8AAB,tx_id=020014003d190000 delete from "CHEN" . "TEST_LOOP" where ROWID = 'AAAR33AAHAAAAs8AAB' ; ,txid is:02001400ffffffff   I can't find any transactions like '02001400ffffffff'. Therefore, the last insert SQL cannot be deleted from the queue, the useless insert will push to kafka topic. If my sink database use upsert mode ,it will result in a different between source and sink.

       

      Bug report

      step 1: Open two oracle session.

      step 2: Insert a data  in the first session without commit.

      SQL>  INSERT INTO CHEN.TEST_LOOP values(1,'1','xxxXXXXXX','cccCCC',sysdate,systimestamp,66.66);

      1 row created.

      step 3: Insert a data which has same primary key in the second session without commit.

      SQL> INSERT INTO CHEN.TEST_LOOP values(1,'2','xxxXXXXXX','cccCCC',sysdate,systimestamp,22.22);

      step 4: Commit in the first session.

      SQL>Commit complete.

      step 5: Commit in the second session.

      SQL>ERROR at line 1:
      ORA-00001: unique constraint (CHEN.TEST_LOOP_PK) violated

       

      Then oracle-connector-logminer will got two insert sql  and one sql with rollback mark because ORA-00001: unique constraint (CHEN.TEST_LOOP_PK) violated

      insert into "CHEN"."TEST_LOOP"("NUMTEST","VARTWOTEST","VARTEST","CHARTEST","DATETEST","TIMETEST","DOUBLETEST") values ('1','1','xxxXXXXXX','cccCCC                                            ',TO_DATE('2022-05-05 22:53:24', 'YYYY-MM-DD HH24:MI:SS'),TO_TIMESTAMP('2022-05-05 22:53:24.220999'),'66.66'); ROWID=AAAR33AAHAAAAsSAAL,tx_id=0a000d0045190000
      
      insert into "CHEN"."TEST_LOOP"("NUMTEST","VARTWOTEST","VARTEST","CHARTEST","DATETEST","TIMETEST","DOUBLETEST") values ('1','2','xxxXXXXXX','cccCCC                                            ',TO_DATE('2022-05-05 22:53:29', 'YYYY-MM-DD HH24:MI:SS'),TO_TIMESTAMP('2022-05-05 22:53:29.557254'),'22.22'); ROWID=AAAR33AAHAAAAs8AAB,tx_id=020014003d190000
      
      delete from "CHEN"."TEST_LOOP" where ROWID = 'AAAR33AAHAAAAs8AAB'; ,txid is:02001400ffffffff 

       

      I can't find any transactions like '02001400ffffffff'. Therefore, the last insert SQL cannot be deleted from the queue, the useless insert will push to kafka topic.

      If my sink database use upsert mode ,it will result in a different between source and sink.

      What Debezium connector do you use and what version?

      1.7.2.Final  1.8.1.Final 1.9.0.Final

      What is the connector configuration?

      oracle-connector

       

      What behaviour do you expect?

      Delete the insert SQL that has been put into the transaction queue

       

      Implementation ideas (optional)

      protected void removeEventWithRowId(LogMinerEventRow row) {
          MemoryTransaction transaction = getTransactionCache().get(row.getTransactionId());
          if (transaction == null) {
              String txid = row.getTransactionId();
              if (txid.toLowerCase().indexOf("ffffff") > 0) {
                  String frontWords = txid.substring(0, txid.toLowerCase().indexOf("ffffff"));
                  Optional<String> newTxid = Optional.empty();
                  Iterator<Map.Entry<String, MemoryTransaction>> iterator = transactionCache.iterator();
                  while (iterator.hasNext()) {
                      Map.Entry<String, MemoryTransaction> entry = iterator.next();
                      if (entry.getKey().startsWith(frontWords)) {
                          newTxid = Optional.of(entry.getKey());
                          break;
                      }
                  }
                  if (newTxid.isPresent()) {
                      transaction = getTransactionCache().get(newTxid.get());
                  }
              }
              if (transaction == null) {
                  LOGGER.warn("Cannot undo change '{}' since transaction was not found.", row);
                  return;
              }
          }
          transaction.removeEventWithRowId(row.getRowId());
      }

       

            ccranfor@redhat.com Chris Cranford
            udontknow C J (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

              Created:
              Updated:
              Resolved: