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

Row hashing in LogMiner Query not able to differentiate between rows of a statement.

XMLWordPrintable

    • False
    • False
    • Undefined
    • Hide

      Setup oracle + debezium in a standard way

      CREATE TABLE CDCTEST.CUSTOMER2 (
                                   c_id integer NOT NULL,
                                   c_d_id integer NOT NULL,
                                   c_w_id int NOT NULL,
                                   c_first varchar(16)  DEFAULT NULL,
                                   c_middle char(2)  DEFAULT NULL,
                                   c_last varchar(16)  DEFAULT NULL,
                                   c_street_1 varchar(20)  DEFAULT NULL,
                                   c_street_2 varchar(20)  DEFAULT NULL,
                                   c_city varchar(20)  DEFAULT NULL,
                                   c_state char(2)  DEFAULT NULL,
                                   c_zip char(9)  DEFAULT NULL,
                                   c_phone char(16)  DEFAULT NULL,
                                   c_since timestamp(6) DEFAULT NULL,
                                   c_credit char(2)  DEFAULT NULL,
                                   c_credit_lim decimal(12,2) DEFAULT NULL,
                                   c_discount decimal(4,4) DEFAULT NULL,
                                   c_balance decimal(12,2) DEFAULT NULL,
                                   c_ytd_payment decimal(12,2) DEFAULT NULL,
                                   c_payment_cnt int DEFAULT NULL,
                                   c_delivery_cnt int DEFAULT NULL,
                                   c_data varchar(500)  DEFAULT NULL,
                                PRIMARY KEY (c_w_id,c_d_id,c_id)) ;

ALTER TABLE CDCTEST.CUSTOMER2 ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

      Start engine and insert 10 rows in streaming mode.
      Note below statment is altered becuase jira wont let me type U_N_I_O_N ALL SELECT 

      INSERT INTO  CDCTEST.CUSTOMER2
(c_id,
 c_d_id,
 c_w_id,
 c_first,
 c_middle,
 c_last,
 c_street_1,
 c_street_2,
 c_city,
 c_state,
 c_zip,
 c_phone,
 c_since,
 c_credit,
 c_credit_lim,
 c_discount,
 c_balance,
 c_ytd_payment,
 c_payment_cnt,
 c_delivery_cnt,
 c_data)
SELECT '11', '1', '1', 'YPMEqJQ5IEP', 'OE', 'JAMIE 99', 'wa0VoV7bqHSzD', 'KnccHddq9hQOIJ0C9', 'Dmj3vz6HARRt1FfLOm9w', '8J', '599511111', '9649048615610128', TO_TIMESTAMP('2019-07-12 14:16:53.123456','YYYY-MM-DD HH24:MI:SS.FF6'), 'GC', '50000.00', '0.0100', '-10.00', '10.00', '1', '0', '2uugupWLaZRZ-truncated- FROM DUAL
ONION ALL SELECT '12', '1', '1', 'abjLiHVtX7', 'OE', 'JAMIE 32', 'm8GZI5UurI7pbQn', 'Kc4X1KYH2e7r', '24vhA7CNiPVS4eO', 'MW', '470211111', '3097542740098740', TO_TIMESTAMP('2019-07-12 14:16:53','YYYY-MM-DD HH24:MI:SS'), 'GC', '50000.00', '0.4200', '1025.00', '10.00', '1', '0', '3cdfoAy1wdl8AjZhWuM6JsgJ1BLLkCd75VPjgnkO8IzbfUFs9tKcn0lhuUmlZavaQXyvOBReHJriG0kwRKoAJCXz7j3ypEao4Ukpt7vuzyvdHTl1CiMTr5Hp5yTdnbPv19zVowA09NhU8ZRm7LmXBthjm880NHcPSXXTcD6qN84wVLaDkjtI5oHqDaxwjAFDOA15ucC0XDiuH8U6U8whulQraAPMXOziCFC8k9xKyIoRO1wI437qTKdwuFoK9TqSAsHdp0xxRaa9wHnS6xRUkF6o7NtToaojOFT5hP31fsZTnMrGv1fwsMKZ2KfLFTg7fHG9mxhiBI17QqHAdSfUmcMqctJHOEa834dw5LNQkBEnh5s6DzISaJJazbMwzqIXfgjQD6oMopKeIBxccf1S2utAHqmawTGf79jzVcHb6z' FROM DUAL
ONION ALL SELECT '13', '1', '1', 'zUopfB4OpFQVj54t', 'OE', 'JAMIE 32', '8oq3O01Uc5o7HQCjO', 'CMfV4MJvVKastzjDcc', 'Jlfj9uZ2NgN', 'L5', '929911111', '3706237873872500', TO_TIMESTAMP('2019-07-12 14:16:53','YYYY-MM-DD HH24:MI:SS'), 'GC', '50000.00', '0.4400', '-10.00', '10.00', '1', '0', 'V5FFBJvCzs82Ft9Tc8TNUfty195nLEmr2YtxbLer7ZSpUJHpVpIHwpjV4txsJpOtALGVzuC7AhTtVvQuLp0dHvXFz2wSqf3IzAmaS0BI8J6afvZpLXKGzjueUOHRiRdDokoC3OZgmdqdkpYYnbGZfJJnA6RAp4ZQFNFdXWDnCM1yPbIjBQWxo3eg9kJCwFCM20VtuPvGnGP5EtlfNlnCxSkcf2d45xxYnHMUsngz6cAW946FZZR0UINWX0d6SSan1ntpFFIRIm1RKNuBH4lYiB3sCI0SW4Cj8wxiDij4xwZGWvomTnleF8yES4t3IIjhhnoKHziQD9rG6HGuAPjtbtcHDBkyjT8wVshbKt1ATgIWPjgd4pt1KSWodPyyXqLNTN7CRBdIizWaVS24WOqLSf2ik3UkZuK6MrDnB9Y7ALuIHDWaTAuuJUAWjXpQmar' FROM DUAL
ONION ALL SELECT '14', '1', '1', 'CsPuJqvoI1J', 'OE', 'JAMIE 32', 'c27rmEkoVqJYZ71Ggo', '0l3I3FMHcuC1NqB', 'S6CPNjNydqcNednml', 'hc', '611311111', '5505005826338725', TO_TIMESTAMP('2019-07-12 14:16:53','YYYY-MM-DD HH24:MI:SS'), 'GC', '50000.00', '0.3800', '-10.00', '10.00', '1', '0', '1qPxmch1cIN5tXnLD9sNlIB9Fkv7vRJdA7kVvDnCZRylJrNF8T6btvtcp0TFtd4jqmbKbbvLwLVn67oXUftpPb5CwykpkcLY5vpb7ldI1tgVFdSo0QIZ41HnO7ZqKjdC3HdHQKhjISr2qnzRQxo70TkEA7WuowteMMuXTKeGmBtpTiLW0MCEis5SxkdZb3RQ0PoXE0OjseU4x6VVZ05FscxBGYZYWr8MBhvfvX3jqj7WSUK7R4L05rspnDb5SgcNkZOL6ctu7jtZb2PT9KyWpbJtlJ9GrkXmVHBYicBRl1di0AycQDhNtuxXjAxkHgJZpD1QZzFIIO' FROM DUAL
ONION ALL SELECT '15', '1', '1', 'AEW2DG6sX7e', 'OE', 'JAMIE 32', 'AxXqoHD5toGJuKj', 'SiyQapU9kf', 'fse4IfX9UvKnr8fhFyQ', 'qX', '497911111', '2495984918559300', TO_TIMESTAMP('2019-07-12 14:16:53','YYYY-MM-DD HH24:MI:SS'), 'BC', '50000.00', '0.1700', '-10.00', '10.00', '1', '0', 'fRdmoMKxIKQQRdqV50tM2Who9xj9hLKHg7qeywVgFAUAJT7kKaKhGFPGsqvf5r0DZSwg1pEYxzTROUcE0Q6kQKyLlc6Ls3bZh27B0ZYFscr4RbqDrdNTFd9C90QmIpzXpf39AMn47vdG7sUAMk9kzkIu0LeSisDbWyOxJZaMiljaPADLQgIVpGswWJemCHxCHLCjmsJAG8DLEkqxwshCVvFAaBBlNQPLYOTdzetUEBjW9P0Ks8tLFtvbKvhbYbRitRGE0vMA3VxorVvDcXEj5mt7gND7VsVPbl6mQyeEarX74cEqWe33IHfh5TiaEluBGOKGdLnOzzfbAyStNYUEqc70QH8n9DZ78pXLoduwQIHibO0FHyg3Bx1gz6LbasPsK2g2fzPw05CYaiXedj89OFlRwqlcszv6pRUAVctXLw7gF9uQm130N4C09ArN4EbECaCPhEf8w5U' FROM DUAL
ONION ALL SELECT '16', '1', '1', 'ulBbR8TU7iQb99u', 'OE', 'JAMIE 32', 'R1Wflx5UA1oyENPab', 'j6hkkcwXdMqxIdvrJFe3', 'eIAGQlVXzQX8uUF', 'g8', '250111111', '3373213736026669', TO_TIMESTAMP('2019-07-12 14:16:53','YYYY-MM-DD HH24:MI:SS'), 'BC', '50000.00', '0.3800', '-10.00', '10.00', '1', '0', 'lUYWb2E2heTBCTOXbBTWlRjKWRjO3zHtB2eOXj5wUHIkbwiuWU9uPpjzqLBV71PcyNtINZlXHRM44xrHft8moxlTmNksSAPhAMOZ0lUPfC0Gspa5K9yWP8JmRxfvyxUnPKSGyvJeazi4mEkHeyBIXKz38OboCi43xslOz9DSlv1naYtWHBDaGbxprj44bkBIz4uNQBGSrSKU19pjyc7KzI0zijSDagXqTMUVVDqWkJefjC6RbD8WAi5d6kdguKkzWAen3wU9QxPnlJSGBaUk2aPxhffafFdaCM4GtT9KlUFja26R0CTwOzwc8GjSjKhVPUh848R2W9' FROM DUAL
ONION ALL SELECT '17', '1', '1', 'BIafFFyLsy0J', 'OE', 'JAMIE 32', 'BFVRJmhmjuWVy', 'k1OU6fa3dU0YlLpVRPsL', 'RrkryfXyDVZh7Z', 'rN', '699611111', '2450903023267766', TO_TIMESTAMP('2019-07-12 14:16:53','YYYY-MM-DD HH24:MI:SS'), 'GC', '50000.00', '0.3600', '-10.00', '10.00', '1', '0', 'Gs6WGvwr5S0tNQBsTKAIbow5stnevfKlATK4JIUwJfVM2hkVJnms5A6mqU3GOChkrtNbrHj3qWonFh9xpsGmqarb4L58U2uyuaBti8fzjzJNaKO3fCdCPBC5aIJzWTfepMWFIojUW1pIxyHe2TE7yldYdL3ZEuOemBdHr6F766JXF9VVWWwwjx2ZbXkMWS9f1QcJu7AiSQZTe2VJk0UyQerUsQcPgfR1Z0IGfYfyLrdOXOWIpQoCKpAlDxvMydAhSfxXGF7NkX17oQXuOQWzzMur6OOLZcUaj5i5AKPgKwmRt2dkkCXc721oaQd5uJgSoy5QZPhbF2mdJszExgjOPLjs1fiz7vpZvuDkeB01dYGdtettJlO7476oymGJVVG5vCC6jOnNVPxGKnc7KYAoacu6tRhpkDHSYnfHGN1NFgNBkSTXZkOdWa8h461vAU9sd2ppjQaoFijkGQjEi8rCAw1dZlS673YY9RRwm8amu0' FROM DUAL
ONION ALL SELECT '18', '1', '1', 'XwVhWChT4IoVi0v', 'OE', 'JAMIE 32', 'KdFUMTLgwP75Br', 'whvauesLLce', '6tLMB4JX66vE4', 'bX', '688411111', '3066490385028404', TO_TIMESTAMP('2019-07-12 14:16:53','YYYY-MM-DD HH24:MI:SS'), 'GC', '50000.00', '0.2400', '-10.00', '10.00', '1', '0', '6UaoGqTlQy6dghP2puMPwSfAm97jz4UyMxD9FTDetXPGhM0fKAuL4ki1lkVz5oS5Kvj83xCByNy2MGeguTYiQiHifbxSVsfG4A71f12LgFpWBzVEBBylcYZbfpiV1XQyzXqNjJpEfkX1O2Felahx2Pem4ukT0oQ5lgRBoytsOtfy7CZhf3sgkz7QCvuQJkOzViECc4uKVGjwTaoQBNDq2c7yIiDlkTBokczwJ9z30nTTCZtDsmgpvocMTQSG2SccqhYoYeh6vAphf41k0mspoTuBcLsz9HAzzs4U1pZuiMHkbqPMLkqxKyrCid6QzRqSTMBU9YeLnW3vTvE6EwYEq6VZfxUvXJxo178fa6IjlDVY4mDiIlvPd9YPcO2sUkA9UgPtvovmwDmk2S' FROM DUAL
ONION ALL SELECT '19', '1', '1', 'pbVTUJJ17', 'OE', 'JAMIE 32', 'xBtvtdOLUwq', '5hcqnvVCz2GE2Px7tSJw', 'mXEmGh5p60ldqEmAq', 'Hf', '894711111', '4092852185655237', TO_TIMESTAMP('2019-07-12 14:16:53','YYYY-MM-DD HH24:MI:SS'), 'BC', '50000.00', '0.2900', '3720.00', '10.00', '1', '0', '9 1 1 1 1 3,730.002019-11-04hOxeLh I0g70QcgZzjAya8S09GPGCnKojjoZopo6OgVYc9DXoh7hU6MGtXAlUmFP4TyTJFDtIqSRMkctyeRoxN861hBg915r9Avn9oYxOAW7TZqoLB7ZmlN1WUHJ21VfokYlaAfwST6uPrzrQLfKJpvNiKHuYZ1FROi8E5cnX8bFQWCm5GHNxkaePjqOoEHataG0Nb9ZY7UCKFcjxzuuMi2xrnEaaq9Y8pCZFsTtAHor3SrAnqFuduk5q3wGR0qZ0pywCeJoGVk8bULI8dsH0q39EQ7CKR2XTFGoYGUHirZhsH776NsP7QSb7bVnjhzIvKGWNIvQTeVxJUDknW6KKpy05qWLrXVd98jxWImodCW7uDJpBnGVk3R8dorUBBEBSnejubvQKHhTxq6BFi4RgKUeYrORLgn1wCiwG5f' FROM DUAL
ONION ALL SELECT '20', '1', '1', 'waEz5i5uDbmjiZ', 'OE', 'JAMIE 32', 'gTHsYIP7Yel', 'DltOIKCnAIZ70Lct5drO', 'jDFeY5LzC4ylYDObHs8', 'JK', '484711111', '1347174919698951', TO_TIMESTAMP('2019-07-12 14:16:53','YYYY-MM-DD HH24:MI:SS'), 'GC', '50000.00', '0.0100', '-10.00', '10.00', '1', '0', 'W6xny7pn3rlZ3AGYNYEpvwAaW5G5Cpl8No9QsYrDmLzTgD1uMAUV2nmR4q73b8RIDi5YydnJkzmbyUTbkG42QaQqKAcAIcrfJRv0RSjvtv5i6ORSGFlW8FQTKbeGzg0uXqy9lW2eRtNp5ppW7UaEvhcQlZU2rDAPnvDHA9aMBtv1MtrKj6eOJamLpHVGLq1g9wri1HV932bEDcvAqXZvPBBcBw5hnvE4Vr7RTdObVjCD9qgLAuobLQwrtTwrlGnQskRgrnw1TpfHAU8WZpFCpQLY3Y5bDddtY76lrUZ4g74XEqY1lxPciUrUAA6B0q68QM1LGfoI3c942mkEc0z4QUVnOrYwkAqDhk088jQrmOy' FROM DUAL;
)
)

      This results in 10 rows in the DML trace like this

       2021-08-09 11:05:34 TRACE debezium-oracleconnector-debezium-change-event-source-coordinator LogMinerQueryResultProcessor:240 - DML, transactionId=0500140016040000, SCN=9669099, table_name=CUSTOMER2, segOwner=CDCTEST, operationCode=1, offsetSCN=9667176, commitOffsetSCN=null, sql insert into "CDCTEST"."CUSTOMER2"("C_ID","C_D_ID","C_W_ID","C_FIRST","C_MIDDLE","C_LAST","C_STREET_1","C_STREET_2","C_CITY","C_STATE","C_ZIP","C_PHONE","C_SINCE","C_CREDIT","C_CREDIT_LIM","C_DISCOUNT","C_BALANCE","C_YTD_PAYMENT","C_PAYMENT_CNT","C_DELIVERY_CNT","C_DATA") values ('11','1','1','YPMEqJQ5IEP','OE','JAMIE 99','wa0VoV7bqHSzD','KnccHddq9hQOIJ0C9','Dmj3vz6HARRt1FfLOm9w','8J','599511111','9649048615610128',TO_TIMESTAMP('2019-07-12 14:16:53.123456'),'GC','50000','.01','-10','10','1','0','2uugupWLaZRZBlBxWvPiaiR0HaNotpnmysPu02MO9SLNYFsmlU52vEYKOVDtZVSJEgwpyyGv1GXbu1GYBkLpcVYGBBKELe8LDn4JlTELYgxSSSLw6fTlu9O7RY2XP940DdcOPcQRJNXBnnKUl2EK2AZn3dlhW04ki6Q55qkTYbQu35rE183RR4ObRlmB182LcPe0AP8eUECiPaXfprPUTBVcLb1yZK0GEVop3WEEMVG27k2epsRNpHt5iDOqo59yRrh04xt4mk9cKRemSYkKzihcxbZQaWpZiTtb9GZGHBKlNyCrod9HRq7Vsu6X7g1Avq1VKmkZEHbUYV1S9iFr16DgDy8UFPiRqq4lq5rFcDiPUIw0zuYdSnekMBNOWqJA'); 
2021-08-09 11:06:43 TRACE debezium-oracleconnector-debezium-change-event-source-coordinator LogMinerQueryResultProcessor:123 - scn=9669099, operationCode=1, operation=INSERT, table=CUSTOMER2, segOwner=CDCTEST, userName=CDCTEST, rowId=AAASMHAAMAAAAGzAAB, rollbackFlag=0 
2021-08-09 11:06:43 TRACE debezium-oracleconnector-debezium-change-event-source-coordinator LogMinerQueryResultProcessor:240 - DML, transactionId=0500140016040000, SCN=9669099, table_name=CUSTOMER2, segOwner=CDCTEST, operationCode=1, offsetSCN=9667176, commitOffsetSCN=null, sql insert into "CDCTEST"."CUSTOMER2"("C_ID","C_D_ID","C_W_ID","C_FIRST","C_MIDDLE","C_LAST","C_STREET_1","C_STREET_2","C_CITY","C_STATE","C_ZIP","C_PHONE","C_SINCE","C_CREDIT","C_CREDIT_LIM","C_DISCOUNT","C_BALANCE","C_YTD_PAYMENT","C_PAYMENT_CNT","C_DELIVERY_CNT","C_DATA") values ('12','1','1','abjLiHVtX7','OE','JAMIE 32','m8GZI5UurI7pbQn','Kc4X1KYH2e7r','24vhA7CNiPVS4eO','MW','470211111','3097542740098740',TO_TIMESTAMP('2019-07-12 14:16:53.'),'GC','50000','.42','1025','10','1','0','3cdfoAy1wdl8AjZhWuM6JsgJ1BLLkCd75VPjgnkO8IzbfUFs9tKcn0lhuUmlZavaQXyvOBReHJriG0kwRKoAJCXz7j3ypEao4Ukpt7vuzyvdHTl1CiMTr5Hp5yTdnbPv19zVowA09NhU8ZRm7LmXBthjm880NHcPSXXTcD6qN84wVLaDkjtI5oHqDaxwjAFDOA15ucC0XDiuH8U6U8whulQraAPMXOziCFC8k9xKyIoRO1wI437qTKdwuFoK9TqSAsHdp0xxRaa9wHnS6xRUkF6o7NtToaojOFT5hP31fsZTnMrGv1fwsMKZ2KfLFTg7fHG9mxhiBI17QqHAdSfUmcMqctJHOEa834dw5LNQkBEnh5s6DzISaJJazbMwzqIXfgjQD6oMopKeIBxccf1S2utAHqmawTGf79jzVcHb6z'); 
2021-08-09 11:06:43 TRACE debezium-oracleconnector-debezium-change-event-source-coordinator LogMinerQueryResultProcessor:123 - scn=9669099, operationCode=1, operation=INSERT, table=CUSTOMER2, segOwner=CDCTEST, userName=CDCTEST, rowId=AAASMHAAMAAAAGzAAC, rollbackFlag=0 
2021-08-09 11:06:43 TRACE debezium-oracleconnector-debezium-change-event-source-coordinator LogMinerQueryResultProcessor:240 - DML, transactionId=0500140016040000, SCN=9669099, table_name=CUSTOMER2, segOwner=CDCTEST, operationCode=1, offsetSCN=9667176, commitOffsetSCN=null, sql insert into "CDCTEST"."CUSTOMER2"("C_ID","C_D_ID","C_W_ID","C_FIRST","C_MIDDLE","C_LAST","C_STREET_1","C_STREET_2","C_CITY","C_STATE","C_ZIP","C_PHONE","C_SINCE","C_CREDIT","C_CREDIT_LIM","C_DISCOUNT","C_BALANCE","C_YTD_PAYMENT","C_PAYMENT_CNT","C_DELIVERY_CNT","C_DATA") values ('13','1','1','zUopfB4OpFQVj54t','OE','JAMIE 32','8oq3O01Uc5o7HQCjO','CMfV4MJvVKastzjDcc','Jlfj9uZ2NgN','L5','929911111','3706237873872500',TO_TIMESTAMP('2019-07-12 14:16:53.'),'GC','50000','.44','-10','10','1','0','V5FFBJvCzs82Ft9Tc8TNUfty195nLEmr2YtxbLer7ZSpUJHpVpIHwpjV4txsJpOtALGVzuC7AhTtVvQuLp0dHvXFz2wSqf3IzAmaS0BI8J6afvZpLXKGzjueUOHRiRdDokoC3OZgmdqdkpYYnbGZfJJnA6RAp4ZQFNFdXWDnCM1yPbIjBQWxo3eg9kJCwFCM20VtuPvGnGP5EtlfNlnCxSkcf2d45xxYnHMUsngz6cAW946FZZR0UINWX0d6SSan1ntpFFIRIm1RKNuBH4lYiB3sCI0SW4Cj8wxiDij4xwZGWvomTnleF8yES4t3IIjhhnoKHziQD9rG6HGuAPjtbtcHDBkyjT8wVshbKt1ATgIWPjgd4pt1KSWodPyyXqLNTN7CRBdIizWaVS24WOqLSf2ik3UkZuK6MrDnB9Y7ALuIHDWaTAuuJUAWjXpQmar'); 
2021-08-09 11:06:43 TRACE debezium-oracleconnector-debezium-change-event-source-coordinator LogMinerQueryResultProcessor:123 - scn=9669099, operationCode=1, operation=INSERT, table=CUSTOMER2, segOwner=CDCTEST, userName=CDCTEST, rowId=AAASMHAAMAAAAGzAAD, rollbackFlag=0 
2021-08-09 11:06:43 TRACE debezium-oracleconnector-debezium-change-event-source-coordinator LogMinerQueryResultProcessor:240 - DML, transactionId=0500140016040000, SCN=9669099, table_name=CUSTOMER2, segOwner=CDCTEST, operationCode=1, offsetSCN=9667176, commitOffsetSCN=null, sql insert into "CDCTEST"."CUSTOMER2"("C_ID","C_D_ID","C_W_ID","C_FIRST","C_MIDDLE","C_LAST","C_STREET_1","C_STREET_2","C_CITY","C_STATE","C_ZIP","C_PHONE","C_SINCE","C_CREDIT","C_CREDIT_LIM","C_DISCOUNT","C_BALANCE","C_YTD_PAYMENT","C_PAYMENT_CNT","C_DELIVERY_CNT","C_DATA") values ('14','1','1','CsPuJqvoI1J','OE','JAMIE 32','c27rmEkoVqJYZ71Ggo','0l3I3FMHcuC1NqB','S6CPNjNydqcNednml','hc','611311111','5505005826338725',TO_TIMESTAMP('2019-07-12 14:16:53.'),'GC','50000','.38','-10','10','1','0','1qPxmch1cIN5tXnLD9sNlIB9Fkv7vRJdA7kVvDnCZRylJrNF8T6btvtcp0TFtd4jqmbKbbvLwLVn67oXUftpPb5CwykpkcLY5vpb7ldI1tgVFdSo0QIZ41HnO7ZqKjdC3HdHQKhjISr2qnzRQxo70TkEA7WuowteMMuXTKeGmBtpTiLW0MCEis5SxkdZb3RQ0PoXE0OjseU4x6VVZ05FscxBGYZYWr8MBhvfvX3jqj7WSUK7R4L05rspnDb5SgcNkZOL6ctu7jtZb2PT9KyWpbJtlJ9GrkXmVHBYicBRl1di0AycQDhNtuxXjAxkHgJZpD1QZzFIIO'); 
2021-08-09 11:06:43 TRACE debezium-oracleconnector-debezium-change-event-source-coordinator LogMinerQueryResultProcessor:123 - scn=9669099, operationCode=1, operation=INSERT, table=CUSTOMER2, segOwner=CDCTEST, userName=CDCTEST, rowId=AAASMHAAMAAAAGzAAE, rollbackFlag=0 
2021-08-09 11:06:43 TRACE debezium-oracleconnector-debezium-change-event-source-coordinator LogMinerQueryResultProcessor:240 - DML, transactionId=0500140016040000, SCN=9669099, table_name=CUSTOMER2, segOwner=CDCTEST, operationCode=1, offsetSCN=9667176, commitOffsetSCN=null, sql insert into "CDCTEST"."CUSTOMER2"("C_ID","C_D_ID","C_W_ID","C_FIRST","C_MIDDLE","C_LAST","C_STREET_1","C_STREET_2","C_CITY","C_STATE","C_ZIP","C_PHONE","C_SINCE","C_CREDIT","C_CREDIT_LIM","C_DISCOUNT","C_BALANCE","C_YTD_PAYMENT","C_PAYMENT_CNT","C_DELIVERY_CNT","C_DATA") values ('15','1','1','AEW2DG6sX7e','OE','JAMIE 32','AxXqoHD5toGJuKj','SiyQapU9kf','fse4IfX9UvKnr8fhFyQ','qX','497911111','2495984918559300',TO_TIMESTAMP('2019-07-12 14:16:53.'),'BC','50000','.17','-10','10','1','0','fRdmoMKxIKQQRdqV50tM2Who9xj9hLKHg7qeywVgFAUAJT7kKaKhGFPGsqvf5r0DZSwg1pEYxzTROUcE0Q6kQKyLlc6Ls3bZh27B0ZYFscr4RbqDrdNTFd9C90QmIpzXpf39AMn47vdG7sUAMk9kzkIu0LeSisDbWyOxJZaMiljaPADLQgIVpGswWJemCHxCHLCjmsJAG8DLEkqxwshCVvFAaBBlNQPLYOTdzetUEBjW9P0Ks8tLFtvbKvhbYbRitRGE0vMA3VxorVvDcXEj5mt7gND7VsVPbl6mQyeEarX74cEqWe33IHfh5TiaEluBGOKGdLnOzzfbAyStNYUEqc70QH8n9DZ78pXLoduwQIHibO0FHyg3Bx1gz6LbasPsK2g2fzPw05CYaiXedj89OFlRwqlcszv6pRUAVctXLw7gF9uQm130N4C09ArN4EbECaCPhEf8w5U'); 
2021-08-09 11:06:43 TRACE debezium-oracleconnector-debezium-change-event-source-coordinator LogMinerQueryResultProcessor:123 - scn=9669099, operationCode=1, operation=INSERT, table=CUSTOMER2, segOwner=CDCTEST, userName=CDCTEST, rowId=AAASMHAAMAAAAGzAAF, rollbackFlag=0 
2021-08-09 11:06:43 TRACE debezium-oracleconnector-debezium-change-event-source-coordinator LogMinerQueryResultProcessor:240 - DML, transactionId=0500140016040000, SCN=9669099, table_name=CUSTOMER2, segOwner=CDCTEST, operationCode=1, offsetSCN=9667176, commitOffsetSCN=null, sql insert into "CDCTEST"."CUSTOMER2"("C_ID","C_D_ID","C_W_ID","C_FIRST","C_MIDDLE","C_LAST","C_STREET_1","C_STREET_2","C_CITY","C_STATE","C_ZIP","C_PHONE","C_SINCE","C_CREDIT","C_CREDIT_LIM","C_DISCOUNT","C_BALANCE","C_YTD_PAYMENT","C_PAYMENT_CNT","C_DELIVERY_CNT","C_DATA") values ('16','1','1','ulBbR8TU7iQb99u','OE','JAMIE 32','R1Wflx5UA1oyENPab','j6hkkcwXdMqxIdvrJFe3','eIAGQlVXzQX8uUF','g8','250111111','3373213736026669',TO_TIMESTAMP('2019-07-12 14:16:53.'),'BC','50000','.38','-10','10','1','0','lUYWb2E2heTBCTOXbBTWlRjKWRjO3zHtB2eOXj5wUHIkbwiuWU9uPpjzqLBV71PcyNtINZlXHRM44xrHft8moxlTmNksSAPhAMOZ0lUPfC0Gspa5K9yWP8JmRxfvyxUnPKSGyvJeazi4mEkHeyBIXKz38OboCi43xslOz9DSlv1naYtWHBDaGbxprj44bkBIz4uNQBGSrSKU19pjyc7KzI0zijSDagXqTMUVVDqWkJefjC6RbD8WAi5d6kdguKkzWAen3wU9QxPnlJSGBaUk2aPxhffafFdaCM4GtT9KlUFja26R0CTwOzwc8GjSjKhVPUh848R2W9'); 
2021-08-09 11:06:43 TRACE debezium-oracleconnector-debezium-change-event-source-coordinator LogMinerQueryResultProcessor:123 - scn=9669099, operationCode=1, operation=INSERT, table=CUSTOMER2, segOwner=CDCTEST, userName=CDCTEST, rowId=AAASMHAAMAAAAGzAAG, rollbackFlag=0

       Its evident from the trace that 256 chars is not sufficient to disambiguate the rows and it appears all other values are the same.

      String ROW_HASH = "ORA_HASH(SCN||OPERATION||RS_ID||SEQUENCE#||RTRIM(SUBSTR(SQL_REDO,1,256)))";

      I am probably not qualified to suggest a fix here but I wondered if the developer didn't expect the SubString to be relied upon and it's there just in case.

       

      Looks like the change was introduced here:-

      https://github.com/debezium/debezium/pull/2347/files#diff-368116a398e866498f9047ace72eac4d98dc2dcce35780f245e642cf5eda6626

      Show
      Setup oracle + debezium in a standard way CREATE TABLE CDCTEST.CUSTOMER2 ( c_id integer NOT NULL , c_d_id integer NOT NULL , c_w_id int NOT NULL , c_first varchar (16) DEFAULT NULL , c_middle char (2) DEFAULT NULL , c_last varchar (16) DEFAULT NULL , c_street_1 varchar (20) DEFAULT NULL , c_street_2 varchar (20) DEFAULT NULL , c_city varchar (20) DEFAULT NULL , c_state char (2) DEFAULT NULL , c_zip char (9) DEFAULT NULL , c_phone char (16) DEFAULT NULL , c_since timestamp (6) DEFAULT NULL , c_credit char (2) DEFAULT NULL , c_credit_lim decimal (12,2) DEFAULT NULL , c_discount decimal (4,4) DEFAULT NULL , c_balance decimal (12,2) DEFAULT NULL , c_ytd_payment decimal (12,2) DEFAULT NULL , c_payment_cnt int DEFAULT NULL , c_delivery_cnt int DEFAULT NULL , c_data varchar (500) DEFAULT NULL , PRIMARY KEY (c_w_id,c_d_id,c_id)) ; ALTER TABLE CDCTEST.CUSTOMER2 ADD SUPPLEMENTAL LOG DATA ( ALL ) COLUMNS ; Start engine and insert 10 rows in streaming mode. Note below statment is altered becuase jira wont let me type U_N_I_O_N ALL SELECT INSERT INTO CDCTEST.CUSTOMER2 (c_id, c_d_id, c_w_id, c_first, c_middle, c_last, c_street_1, c_street_2, c_city, c_state, c_zip, c_phone, c_since, c_credit, c_credit_lim, c_discount, c_balance, c_ytd_payment, c_payment_cnt, c_delivery_cnt, c_data) SELECT '11' , '1' , '1' , 'YPMEqJQ5IEP' , 'OE' , 'JAMIE 99' , 'wa0VoV7bqHSzD' , 'KnccHddq9hQOIJ0C9' , 'Dmj3vz6HARRt1FfLOm9w' , '8J' , '599511111' , '9649048615610128' , TO_TIMESTAMP( '2019-07-12 14:16:53.123456' , 'YYYY-MM-DD HH24:MI:SS.FF6' ), 'GC' , '50000.00' , '0.0100' , '-10.00' , '10.00' , '1' , '0' , '2uugupWLaZRZ-truncated- FROM DUAL ONION ALL SELECT '12' , '1' , '1' , 'abjLiHVtX7' , 'OE' , 'JAMIE 32' , 'm8GZI5UurI7pbQn' , 'Kc4X1KYH2e7r' , '24vhA7CNiPVS4eO' , 'MW' , '470211111' , '3097542740098740' , TO_TIMESTAMP( '2019-07-12 14:16:53' , 'YYYY-MM-DD HH24:MI:SS' ), 'GC' , '50000.00' , '0.4200' , '1025.00' , '10.00' , '1' , '0' , '3cdfoAy1wdl8AjZhWuM6JsgJ1BLLkCd75VPjgnkO8IzbfUFs9tKcn0lhuUmlZavaQXyvOBReHJriG0kwRKoAJCXz7j3ypEao4Ukpt7vuzyvdHTl1CiMTr5Hp5yTdnbPv19zVowA09NhU8ZRm7LmXBthjm880NHcPSXXTcD6qN84wVLaDkjtI5oHqDaxwjAFDOA15ucC0XDiuH8U6U8whulQraAPMXOziCFC8k9xKyIoRO1wI437qTKdwuFoK9TqSAsHdp0xxRaa9wHnS6xRUkF6o7NtToaojOFT5hP31fsZTnMrGv1fwsMKZ2KfLFTg7fHG9mxhiBI17QqHAdSfUmcMqctJHOEa834dw5LNQkBEnh5s6DzISaJJazbMwzqIXfgjQD6oMopKeIBxccf1S2utAHqmawTGf79jzVcHb6z' FROM DUAL ONION ALL SELECT '13' , '1' , '1' , 'zUopfB4OpFQVj54t' , 'OE' , 'JAMIE 32' , '8oq3O01Uc5o7HQCjO' , 'CMfV4MJvVKastzjDcc' , 'Jlfj9uZ2NgN' , 'L5' , '929911111' , '3706237873872500' , TO_TIMESTAMP( '2019-07-12 14:16:53' , 'YYYY-MM-DD HH24:MI:SS' ), 'GC' , '50000.00' , '0.4400' , '-10.00' , '10.00' , '1' , '0' , 'V5FFBJvCzs82Ft9Tc8TNUfty195nLEmr2YtxbLer7ZSpUJHpVpIHwpjV4txsJpOtALGVzuC7AhTtVvQuLp0dHvXFz2wSqf3IzAmaS0BI8J6afvZpLXKGzjueUOHRiRdDokoC3OZgmdqdkpYYnbGZfJJnA6RAp4ZQFNFdXWDnCM1yPbIjBQWxo3eg9kJCwFCM20VtuPvGnGP5EtlfNlnCxSkcf2d45xxYnHMUsngz6cAW946FZZR0UINWX0d6SSan1ntpFFIRIm1RKNuBH4lYiB3sCI0SW4Cj8wxiDij4xwZGWvomTnleF8yES4t3IIjhhnoKHziQD9rG6HGuAPjtbtcHDBkyjT8wVshbKt1ATgIWPjgd4pt1KSWodPyyXqLNTN7CRBdIizWaVS24WOqLSf2ik3UkZuK6MrDnB9Y7ALuIHDWaTAuuJUAWjXpQmar' FROM DUAL ONION ALL SELECT '14' , '1' , '1' , 'CsPuJqvoI1J' , 'OE' , 'JAMIE 32' , 'c27rmEkoVqJYZ71Ggo' , '0l3I3FMHcuC1NqB' , 'S6CPNjNydqcNednml' , 'hc' , '611311111' , '5505005826338725' , TO_TIMESTAMP( '2019-07-12 14:16:53' , 'YYYY-MM-DD HH24:MI:SS' ), 'GC' , '50000.00' , '0.3800' , '-10.00' , '10.00' , '1' , '0' , '1qPxmch1cIN5tXnLD9sNlIB9Fkv7vRJdA7kVvDnCZRylJrNF8T6btvtcp0TFtd4jqmbKbbvLwLVn67oXUftpPb5CwykpkcLY5vpb7ldI1tgVFdSo0QIZ41HnO7ZqKjdC3HdHQKhjISr2qnzRQxo70TkEA7WuowteMMuXTKeGmBtpTiLW0MCEis5SxkdZb3RQ0PoXE0OjseU4x6VVZ05FscxBGYZYWr8MBhvfvX3jqj7WSUK7R4L05rspnDb5SgcNkZOL6ctu7jtZb2PT9KyWpbJtlJ9GrkXmVHBYicBRl1di0AycQDhNtuxXjAxkHgJZpD1QZzFIIO' FROM DUAL ONION ALL SELECT '15' , '1' , '1' , 'AEW2DG6sX7e' , 'OE' , 'JAMIE 32' , 'AxXqoHD5toGJuKj' , 'SiyQapU9kf' , 'fse4IfX9UvKnr8fhFyQ' , 'qX' , '497911111' , '2495984918559300' , TO_TIMESTAMP( '2019-07-12 14:16:53' , 'YYYY-MM-DD HH24:MI:SS' ), 'BC' , '50000.00' , '0.1700' , '-10.00' , '10.00' , '1' , '0' , 'fRdmoMKxIKQQRdqV50tM2Who9xj9hLKHg7qeywVgFAUAJT7kKaKhGFPGsqvf5r0DZSwg1pEYxzTROUcE0Q6kQKyLlc6Ls3bZh27B0ZYFscr4RbqDrdNTFd9C90QmIpzXpf39AMn47vdG7sUAMk9kzkIu0LeSisDbWyOxJZaMiljaPADLQgIVpGswWJemCHxCHLCjmsJAG8DLEkqxwshCVvFAaBBlNQPLYOTdzetUEBjW9P0Ks8tLFtvbKvhbYbRitRGE0vMA3VxorVvDcXEj5mt7gND7VsVPbl6mQyeEarX74cEqWe33IHfh5TiaEluBGOKGdLnOzzfbAyStNYUEqc70QH8n9DZ78pXLoduwQIHibO0FHyg3Bx1gz6LbasPsK2g2fzPw05CYaiXedj89OFlRwqlcszv6pRUAVctXLw7gF9uQm130N4C09ArN4EbECaCPhEf8w5U' FROM DUAL ONION ALL SELECT '16' , '1' , '1' , 'ulBbR8TU7iQb99u' , 'OE' , 'JAMIE 32' , 'R1Wflx5UA1oyENPab' , 'j6hkkcwXdMqxIdvrJFe3' , 'eIAGQlVXzQX8uUF' , 'g8' , '250111111' , '3373213736026669' , TO_TIMESTAMP( '2019-07-12 14:16:53' , 'YYYY-MM-DD HH24:MI:SS' ), 'BC' , '50000.00' , '0.3800' , '-10.00' , '10.00' , '1' , '0' , 'lUYWb2E2heTBCTOXbBTWlRjKWRjO3zHtB2eOXj5wUHIkbwiuWU9uPpjzqLBV71PcyNtINZlXHRM44xrHft8moxlTmNksSAPhAMOZ0lUPfC0Gspa5K9yWP8JmRxfvyxUnPKSGyvJeazi4mEkHeyBIXKz38OboCi43xslOz9DSlv1naYtWHBDaGbxprj44bkBIz4uNQBGSrSKU19pjyc7KzI0zijSDagXqTMUVVDqWkJefjC6RbD8WAi5d6kdguKkzWAen3wU9QxPnlJSGBaUk2aPxhffafFdaCM4GtT9KlUFja26R0CTwOzwc8GjSjKhVPUh848R2W9' FROM DUAL ONION ALL SELECT '17' , '1' , '1' , 'BIafFFyLsy0J' , 'OE' , 'JAMIE 32' , 'BFVRJmhmjuWVy' , 'k1OU6fa3dU0YlLpVRPsL' , 'RrkryfXyDVZh7Z' , 'rN' , '699611111' , '2450903023267766' , TO_TIMESTAMP( '2019-07-12 14:16:53' , 'YYYY-MM-DD HH24:MI:SS' ), 'GC' , '50000.00' , '0.3600' , '-10.00' , '10.00' , '1' , '0' , 'Gs6WGvwr5S0tNQBsTKAIbow5stnevfKlATK4JIUwJfVM2hkVJnms5A6mqU3GOChkrtNbrHj3qWonFh9xpsGmqarb4L58U2uyuaBti8fzjzJNaKO3fCdCPBC5aIJzWTfepMWFIojUW1pIxyHe2TE7yldYdL3ZEuOemBdHr6F766JXF9VVWWwwjx2ZbXkMWS9f1QcJu7AiSQZTe2VJk0UyQerUsQcPgfR1Z0IGfYfyLrdOXOWIpQoCKpAlDxvMydAhSfxXGF7NkX17oQXuOQWzzMur6OOLZcUaj5i5AKPgKwmRt2dkkCXc721oaQd5uJgSoy5QZPhbF2mdJszExgjOPLjs1fiz7vpZvuDkeB01dYGdtettJlO7476oymGJVVG5vCC6jOnNVPxGKnc7KYAoacu6tRhpkDHSYnfHGN1NFgNBkSTXZkOdWa8h461vAU9sd2ppjQaoFijkGQjEi8rCAw1dZlS673YY9RRwm8amu0' FROM DUAL ONION ALL SELECT '18' , '1' , '1' , 'XwVhWChT4IoVi0v' , 'OE' , 'JAMIE 32' , 'KdFUMTLgwP75Br' , 'whvauesLLce' , '6tLMB4JX66vE4' , 'bX' , '688411111' , '3066490385028404' , TO_TIMESTAMP( '2019-07-12 14:16:53' , 'YYYY-MM-DD HH24:MI:SS' ), 'GC' , '50000.00' , '0.2400' , '-10.00' , '10.00' , '1' , '0' , '6UaoGqTlQy6dghP2puMPwSfAm97jz4UyMxD9FTDetXPGhM0fKAuL4ki1lkVz5oS5Kvj83xCByNy2MGeguTYiQiHifbxSVsfG4A71f12LgFpWBzVEBBylcYZbfpiV1XQyzXqNjJpEfkX1O2Felahx2Pem4ukT0oQ5lgRBoytsOtfy7CZhf3sgkz7QCvuQJkOzViECc4uKVGjwTaoQBNDq2c7yIiDlkTBokczwJ9z30nTTCZtDsmgpvocMTQSG2SccqhYoYeh6vAphf41k0mspoTuBcLsz9HAzzs4U1pZuiMHkbqPMLkqxKyrCid6QzRqSTMBU9YeLnW3vTvE6EwYEq6VZfxUvXJxo178fa6IjlDVY4mDiIlvPd9YPcO2sUkA9UgPtvovmwDmk2S' FROM DUAL ONION ALL SELECT '19' , '1' , '1' , 'pbVTUJJ17' , 'OE' , 'JAMIE 32' , 'xBtvtdOLUwq' , '5hcqnvVCz2GE2Px7tSJw' , 'mXEmGh5p60ldqEmAq' , 'Hf' , '894711111' , '4092852185655237' , TO_TIMESTAMP( '2019-07-12 14:16:53' , 'YYYY-MM-DD HH24:MI:SS' ), 'BC' , '50000.00' , '0.2900' , '3720.00' , '10.00' , '1' , '0' , '9 1 1 1 1 3,730.002019-11-04hOxeLh I0g70QcgZzjAya8S09GPGCnKojjoZopo6OgVYc9DXoh7hU6MGtXAlUmFP4TyTJFDtIqSRMkctyeRoxN861hBg915r9Avn9oYxOAW7TZqoLB7ZmlN1WUHJ21VfokYlaAfwST6uPrzrQLfKJpvNiKHuYZ1FROi8E5cnX8bFQWCm5GHNxkaePjqOoEHataG0Nb9ZY7UCKFcjxzuuMi2xrnEaaq9Y8pCZFsTtAHor3SrAnqFuduk5q3wGR0qZ0pywCeJoGVk8bULI8dsH0q39EQ7CKR2XTFGoYGUHirZhsH776NsP7QSb7bVnjhzIvKGWNIvQTeVxJUDknW6KKpy05qWLrXVd98jxWImodCW7uDJpBnGVk3R8dorUBBEBSnejubvQKHhTxq6BFi4RgKUeYrORLgn1wCiwG5f' FROM DUAL ONION ALL SELECT '20' , '1' , '1' , 'waEz5i5uDbmjiZ' , 'OE' , 'JAMIE 32' , 'gTHsYIP7Yel' , 'DltOIKCnAIZ70Lct5drO' , 'jDFeY5LzC4ylYDObHs8' , 'JK' , '484711111' , '1347174919698951' , TO_TIMESTAMP( '2019-07-12 14:16:53' , 'YYYY-MM-DD HH24:MI:SS' ), 'GC' , '50000.00' , '0.0100' , '-10.00' , '10.00' , '1' , '0' , 'W6xny7pn3rlZ3AGYNYEpvwAaW5G5Cpl8No9QsYrDmLzTgD1uMAUV2nmR4q73b8RIDi5YydnJkzmbyUTbkG42QaQqKAcAIcrfJRv0RSjvtv5i6ORSGFlW8FQTKbeGzg0uXqy9lW2eRtNp5ppW7UaEvhcQlZU2rDAPnvDHA9aMBtv1MtrKj6eOJamLpHVGLq1g9wri1HV932bEDcvAqXZvPBBcBw5hnvE4Vr7RTdObVjCD9qgLAuobLQwrtTwrlGnQskRgrnw1TpfHAU8WZpFCpQLY3Y5bDddtY76lrUZ4g74XEqY1lxPciUrUAA6B0q68QM1LGfoI3c942mkEc0z4QUVnOrYwkAqDhk088jQrmOy' FROM DUAL ; ) ) This results in 10 rows in the DML trace like this  2021-08-09 11:05:34 TRACE debezium-oracleconnector-debezium-change-event-source-coordinator LogMinerQueryResultProcessor:240 - DML, transactionId=0500140016040000, SCN=9669099, table_name=CUSTOMER2, segOwner=CDCTEST, operationCode=1, offsetSCN=9667176, commitOffsetSCN=null, sql insert into "CDCTEST"."CUSTOMER2"("C_ID","C_D_ID","C_W_ID","C_FIRST","C_MIDDLE","C_LAST","C_STREET_1","C_STREET_2","C_CITY","C_STATE","C_ZIP","C_PHONE","C_SINCE","C_CREDIT","C_CREDIT_LIM","C_DISCOUNT","C_BALANCE","C_YTD_PAYMENT","C_PAYMENT_CNT","C_DELIVERY_CNT","C_DATA") values ('11','1','1','YPMEqJQ5IEP','OE','JAMIE 99','wa0VoV7bqHSzD','KnccHddq9hQOIJ0C9','Dmj3vz6HARRt1FfLOm9w','8J','599511111','9649048615610128',TO_TIMESTAMP('2019-07-12 14:16:53.123456'),'GC','50000','.01','-10','10','1','0','2uugupWLaZRZBlBxWvPiaiR0HaNotpnmysPu02MO9SLNYFsmlU52vEYKOVDtZVSJEgwpyyGv1GXbu1GYBkLpcVYGBBKELe8LDn4JlTELYgxSSSLw6fTlu9O7RY2XP940DdcOPcQRJNXBnnKUl2EK2AZn3dlhW04ki6Q55qkTYbQu35rE183RR4ObRlmB182LcPe0AP8eUECiPaXfprPUTBVcLb1yZK0GEVop3WEEMVG27k2epsRNpHt5iDOqo59yRrh04xt4mk9cKRemSYkKzihcxbZQaWpZiTtb9GZGHBKlNyCrod9HRq7Vsu6X7g1Avq1VKmkZEHbUYV1S9iFr16DgDy8UFPiRqq4lq5rFcDiPUIw0zuYdSnekMBNOWqJA'); 2021-08-09 11:06:43 TRACE debezium-oracleconnector-debezium-change-event-source-coordinator LogMinerQueryResultProcessor:123 - scn=9669099, operationCode=1, operation=INSERT, table=CUSTOMER2, segOwner=CDCTEST, userName=CDCTEST, rowId=AAASMHAAMAAAAGzAAB, rollbackFlag=0 2021-08-09 11:06:43 TRACE debezium-oracleconnector-debezium-change-event-source-coordinator LogMinerQueryResultProcessor:240 - DML, transactionId=0500140016040000, SCN=9669099, table_name=CUSTOMER2, segOwner=CDCTEST, operationCode=1, offsetSCN=9667176, commitOffsetSCN=null, sql insert into "CDCTEST"."CUSTOMER2"("C_ID","C_D_ID","C_W_ID","C_FIRST","C_MIDDLE","C_LAST","C_STREET_1","C_STREET_2","C_CITY","C_STATE","C_ZIP","C_PHONE","C_SINCE","C_CREDIT","C_CREDIT_LIM","C_DISCOUNT","C_BALANCE","C_YTD_PAYMENT","C_PAYMENT_CNT","C_DELIVERY_CNT","C_DATA") values ('12','1','1','abjLiHVtX7','OE','JAMIE 32','m8GZI5UurI7pbQn','Kc4X1KYH2e7r','24vhA7CNiPVS4eO','MW','470211111','3097542740098740',TO_TIMESTAMP('2019-07-12 14:16:53.'),'GC','50000','.42','1025','10','1','0','3cdfoAy1wdl8AjZhWuM6JsgJ1BLLkCd75VPjgnkO8IzbfUFs9tKcn0lhuUmlZavaQXyvOBReHJriG0kwRKoAJCXz7j3ypEao4Ukpt7vuzyvdHTl1CiMTr5Hp5yTdnbPv19zVowA09NhU8ZRm7LmXBthjm880NHcPSXXTcD6qN84wVLaDkjtI5oHqDaxwjAFDOA15ucC0XDiuH8U6U8whulQraAPMXOziCFC8k9xKyIoRO1wI437qTKdwuFoK9TqSAsHdp0xxRaa9wHnS6xRUkF6o7NtToaojOFT5hP31fsZTnMrGv1fwsMKZ2KfLFTg7fHG9mxhiBI17QqHAdSfUmcMqctJHOEa834dw5LNQkBEnh5s6DzISaJJazbMwzqIXfgjQD6oMopKeIBxccf1S2utAHqmawTGf79jzVcHb6z'); 2021-08-09 11:06:43 TRACE debezium-oracleconnector-debezium-change-event-source-coordinator LogMinerQueryResultProcessor:123 - scn=9669099, operationCode=1, operation=INSERT, table=CUSTOMER2, segOwner=CDCTEST, userName=CDCTEST, rowId=AAASMHAAMAAAAGzAAC, rollbackFlag=0 2021-08-09 11:06:43 TRACE debezium-oracleconnector-debezium-change-event-source-coordinator LogMinerQueryResultProcessor:240 - DML, transactionId=0500140016040000, SCN=9669099, table_name=CUSTOMER2, segOwner=CDCTEST, operationCode=1, offsetSCN=9667176, commitOffsetSCN=null, sql insert into "CDCTEST"."CUSTOMER2"("C_ID","C_D_ID","C_W_ID","C_FIRST","C_MIDDLE","C_LAST","C_STREET_1","C_STREET_2","C_CITY","C_STATE","C_ZIP","C_PHONE","C_SINCE","C_CREDIT","C_CREDIT_LIM","C_DISCOUNT","C_BALANCE","C_YTD_PAYMENT","C_PAYMENT_CNT","C_DELIVERY_CNT","C_DATA") values ('13','1','1','zUopfB4OpFQVj54t','OE','JAMIE 32','8oq3O01Uc5o7HQCjO','CMfV4MJvVKastzjDcc','Jlfj9uZ2NgN','L5','929911111','3706237873872500',TO_TIMESTAMP('2019-07-12 14:16:53.'),'GC','50000','.44','-10','10','1','0','V5FFBJvCzs82Ft9Tc8TNUfty195nLEmr2YtxbLer7ZSpUJHpVpIHwpjV4txsJpOtALGVzuC7AhTtVvQuLp0dHvXFz2wSqf3IzAmaS0BI8J6afvZpLXKGzjueUOHRiRdDokoC3OZgmdqdkpYYnbGZfJJnA6RAp4ZQFNFdXWDnCM1yPbIjBQWxo3eg9kJCwFCM20VtuPvGnGP5EtlfNlnCxSkcf2d45xxYnHMUsngz6cAW946FZZR0UINWX0d6SSan1ntpFFIRIm1RKNuBH4lYiB3sCI0SW4Cj8wxiDij4xwZGWvomTnleF8yES4t3IIjhhnoKHziQD9rG6HGuAPjtbtcHDBkyjT8wVshbKt1ATgIWPjgd4pt1KSWodPyyXqLNTN7CRBdIizWaVS24WOqLSf2ik3UkZuK6MrDnB9Y7ALuIHDWaTAuuJUAWjXpQmar'); 2021-08-09 11:06:43 TRACE debezium-oracleconnector-debezium-change-event-source-coordinator LogMinerQueryResultProcessor:123 - scn=9669099, operationCode=1, operation=INSERT, table=CUSTOMER2, segOwner=CDCTEST, userName=CDCTEST, rowId=AAASMHAAMAAAAGzAAD, rollbackFlag=0 2021-08-09 11:06:43 TRACE debezium-oracleconnector-debezium-change-event-source-coordinator LogMinerQueryResultProcessor:240 - DML, transactionId=0500140016040000, SCN=9669099, table_name=CUSTOMER2, segOwner=CDCTEST, operationCode=1, offsetSCN=9667176, commitOffsetSCN=null, sql insert into "CDCTEST"."CUSTOMER2"("C_ID","C_D_ID","C_W_ID","C_FIRST","C_MIDDLE","C_LAST","C_STREET_1","C_STREET_2","C_CITY","C_STATE","C_ZIP","C_PHONE","C_SINCE","C_CREDIT","C_CREDIT_LIM","C_DISCOUNT","C_BALANCE","C_YTD_PAYMENT","C_PAYMENT_CNT","C_DELIVERY_CNT","C_DATA") values ('14','1','1','CsPuJqvoI1J','OE','JAMIE 32','c27rmEkoVqJYZ71Ggo','0l3I3FMHcuC1NqB','S6CPNjNydqcNednml','hc','611311111','5505005826338725',TO_TIMESTAMP('2019-07-12 14:16:53.'),'GC','50000','.38','-10','10','1','0','1qPxmch1cIN5tXnLD9sNlIB9Fkv7vRJdA7kVvDnCZRylJrNF8T6btvtcp0TFtd4jqmbKbbvLwLVn67oXUftpPb5CwykpkcLY5vpb7ldI1tgVFdSo0QIZ41HnO7ZqKjdC3HdHQKhjISr2qnzRQxo70TkEA7WuowteMMuXTKeGmBtpTiLW0MCEis5SxkdZb3RQ0PoXE0OjseU4x6VVZ05FscxBGYZYWr8MBhvfvX3jqj7WSUK7R4L05rspnDb5SgcNkZOL6ctu7jtZb2PT9KyWpbJtlJ9GrkXmVHBYicBRl1di0AycQDhNtuxXjAxkHgJZpD1QZzFIIO'); 2021-08-09 11:06:43 TRACE debezium-oracleconnector-debezium-change-event-source-coordinator LogMinerQueryResultProcessor:123 - scn=9669099, operationCode=1, operation=INSERT, table=CUSTOMER2, segOwner=CDCTEST, userName=CDCTEST, rowId=AAASMHAAMAAAAGzAAE, rollbackFlag=0 2021-08-09 11:06:43 TRACE debezium-oracleconnector-debezium-change-event-source-coordinator LogMinerQueryResultProcessor:240 - DML, transactionId=0500140016040000, SCN=9669099, table_name=CUSTOMER2, segOwner=CDCTEST, operationCode=1, offsetSCN=9667176, commitOffsetSCN=null, sql insert into "CDCTEST"."CUSTOMER2"("C_ID","C_D_ID","C_W_ID","C_FIRST","C_MIDDLE","C_LAST","C_STREET_1","C_STREET_2","C_CITY","C_STATE","C_ZIP","C_PHONE","C_SINCE","C_CREDIT","C_CREDIT_LIM","C_DISCOUNT","C_BALANCE","C_YTD_PAYMENT","C_PAYMENT_CNT","C_DELIVERY_CNT","C_DATA") values ('15','1','1','AEW2DG6sX7e','OE','JAMIE 32','AxXqoHD5toGJuKj','SiyQapU9kf','fse4IfX9UvKnr8fhFyQ','qX','497911111','2495984918559300',TO_TIMESTAMP('2019-07-12 14:16:53.'),'BC','50000','.17','-10','10','1','0','fRdmoMKxIKQQRdqV50tM2Who9xj9hLKHg7qeywVgFAUAJT7kKaKhGFPGsqvf5r0DZSwg1pEYxzTROUcE0Q6kQKyLlc6Ls3bZh27B0ZYFscr4RbqDrdNTFd9C90QmIpzXpf39AMn47vdG7sUAMk9kzkIu0LeSisDbWyOxJZaMiljaPADLQgIVpGswWJemCHxCHLCjmsJAG8DLEkqxwshCVvFAaBBlNQPLYOTdzetUEBjW9P0Ks8tLFtvbKvhbYbRitRGE0vMA3VxorVvDcXEj5mt7gND7VsVPbl6mQyeEarX74cEqWe33IHfh5TiaEluBGOKGdLnOzzfbAyStNYUEqc70QH8n9DZ78pXLoduwQIHibO0FHyg3Bx1gz6LbasPsK2g2fzPw05CYaiXedj89OFlRwqlcszv6pRUAVctXLw7gF9uQm130N4C09ArN4EbECaCPhEf8w5U'); 2021-08-09 11:06:43 TRACE debezium-oracleconnector-debezium-change-event-source-coordinator LogMinerQueryResultProcessor:123 - scn=9669099, operationCode=1, operation=INSERT, table=CUSTOMER2, segOwner=CDCTEST, userName=CDCTEST, rowId=AAASMHAAMAAAAGzAAF, rollbackFlag=0 2021-08-09 11:06:43 TRACE debezium-oracleconnector-debezium-change-event-source-coordinator LogMinerQueryResultProcessor:240 - DML, transactionId=0500140016040000, SCN=9669099, table_name=CUSTOMER2, segOwner=CDCTEST, operationCode=1, offsetSCN=9667176, commitOffsetSCN=null, sql insert into "CDCTEST"."CUSTOMER2"("C_ID","C_D_ID","C_W_ID","C_FIRST","C_MIDDLE","C_LAST","C_STREET_1","C_STREET_2","C_CITY","C_STATE","C_ZIP","C_PHONE","C_SINCE","C_CREDIT","C_CREDIT_LIM","C_DISCOUNT","C_BALANCE","C_YTD_PAYMENT","C_PAYMENT_CNT","C_DELIVERY_CNT","C_DATA") values ('16','1','1','ulBbR8TU7iQb99u','OE','JAMIE 32','R1Wflx5UA1oyENPab','j6hkkcwXdMqxIdvrJFe3','eIAGQlVXzQX8uUF','g8','250111111','3373213736026669',TO_TIMESTAMP('2019-07-12 14:16:53.'),'BC','50000','.38','-10','10','1','0','lUYWb2E2heTBCTOXbBTWlRjKWRjO3zHtB2eOXj5wUHIkbwiuWU9uPpjzqLBV71PcyNtINZlXHRM44xrHft8moxlTmNksSAPhAMOZ0lUPfC0Gspa5K9yWP8JmRxfvyxUnPKSGyvJeazi4mEkHeyBIXKz38OboCi43xslOz9DSlv1naYtWHBDaGbxprj44bkBIz4uNQBGSrSKU19pjyc7KzI0zijSDagXqTMUVVDqWkJefjC6RbD8WAi5d6kdguKkzWAen3wU9QxPnlJSGBaUk2aPxhffafFdaCM4GtT9KlUFja26R0CTwOzwc8GjSjKhVPUh848R2W9'); 2021-08-09 11:06:43 TRACE debezium-oracleconnector-debezium-change-event-source-coordinator LogMinerQueryResultProcessor:123 - scn=9669099, operationCode=1, operation=INSERT, table=CUSTOMER2, segOwner=CDCTEST, userName=CDCTEST, rowId=AAASMHAAMAAAAGzAAG, rollbackFlag=0  Its evident from the trace that 256 chars is not sufficient to disambiguate the rows and it appears all other values are the same. String ROW_HASH = "ORA_HASH(SCN||OPERATION||RS_ID||SEQUENCE#||RTRIM(SUBSTR(SQL_REDO,1,256)))" ; I am probably not qualified to suggest a fix here but I wondered if the developer didn't expect the SubString to be relied upon and it's there just in case.   Looks like the change was introduced here:- https://github.com/debezium/debezium/pull/2347/files#diff-368116a398e866498f9047ace72eac4d98dc2dcce35780f245e642cf5eda6626

      The oracle log miner uses a row hash

      1.6.1.Final -> LogMinerQueryBuilder:26

      This fails to differentiate between rows in quite limited and simple DML operations resulting in rows being missed.

            [DBZ-3834] Row hashing in LogMiner Query not able to differentiate between rows of a statement.

            Debezium Builder added a comment -

            Released

            Debezium Builder added a comment - Released

            Chris Cranford added a comment -

            Is there an argument to move the hash into java? (I always get nervous pushing complex operations onto the source becuase often the team running the source will want reassurance that debezium wont materially degrade performance).

            I suppose an argument could be made to move it to Java but my concern then would be what if any GC overhead would be incurred.

            If the hash was done in java then it might be possible to have logic where primary hash was generated with SCN||OPERATION||RS_ID||SEQUENCE# this covers the vast majority of cases and is cheap/predictable. In the case of a cache hit go and perform a secondary hash over the DDL just in case.

            I wish the hash were that simple, but unfortunately, the SQL_REDO must be included. We have seen situations where LogMiner materializes multiple rows all with the same values of those 4 columns where the only variant is the text in SQL_REDO, particularly in the case of where LOB columns are involved. There are a few nuances that have to be guarded with LOB support enabled, but we can certainly look into what we can do to either reduce the impact of the function, only use it where absolutely necessary, and/or move it to the connector runtime code.

            Chris Cranford added a comment - Is there an argument to move the hash into java? (I always get nervous pushing complex operations onto the source becuase often the team running the source will want reassurance that debezium wont materially degrade performance). I suppose an argument could be made to move it to Java but my concern then would be what if any GC overhead would be incurred. If the hash was done in java then it might be possible to have logic where primary hash was generated with SCN||OPERATION||RS_ID||SEQUENCE# this covers the vast majority of cases and is cheap/predictable. In the case of a cache hit go and perform a secondary hash over the DDL just in case. I wish the hash were that simple, but unfortunately, the SQL_REDO must be included. We have seen situations where LogMiner materializes multiple rows all with the same values of those 4 columns where the only variant is the text in SQL_REDO , particularly in the case of where LOB columns are involved. There are a few nuances that have to be guarded with LOB support enabled, but we can certainly look into what we can do to either reduce the impact of the function, only use it where absolutely necessary, and/or move it to the connector runtime code.

            Ed Thompson (Inactive) added a comment - - edited

            Sounds pretty sensible. I can keep an eye on our performance test to check for major adverse effects.

            Is there an argument to move the hash into java? (I always get nervous pushing complex operations onto the source becuase often the team running the source will want reassurance that debezium wont materially degrade performance).

            If the hash was done in java then it might be possible to have logic where primary hash was generated with SCN||OPERATION||RS_ID||SEQUENCE# this covers the vast majority of cases and is cheap/predictable. In the case of a cache hit go and perform a secondary hash over the DDL just in case.

            Ed Thompson (Inactive) added a comment - - edited Sounds pretty sensible. I can keep an eye on our performance test to check for major adverse effects. Is there an argument to move the hash into java? (I always get nervous pushing complex operations onto the source becuase often the team running the source will want reassurance that debezium wont materially degrade performance). If the hash was done in java then it might be possible to have logic where primary hash was generated with SCN||OPERATION||RS_ID||SEQUENCE# this covers the vast majority of cases and is cheap/predictable. In the case of a cache hit go and perform a secondary hash over the DDL just in case.

            Chris Cranford added a comment - - edited

            Unfortunately, I'm not certain a parameter is ideal.

            In 1.7 we've introduced two transaction cache implementations, one that is memory-based and another that is persisted using Infinispan. In the case of the latter, the records are persisted to the cache with this hash value and so if a user inadvertently changed the size from X to Y, this would mean the hash values would be inconsistent and the purpose the hash serves would be fruitless for any in-progress transactions. In short, it would cause any in-progress transaction to emit duplicate events.

            As I mentioned the hash was added for LOB support, so perhaps we can guard this calculation to only be used/take effect when LOB support is enabled. That said, if we did that we could then just use the entire SQL_REDO string rather than relying only on an initial subset of the value. This should improve performance for users who don't enable the LOB support because Oracle won't be calculating these hashes on the mining query and secondly it should remove one extra collection check we do when processing the result-set.

            Chris Cranford added a comment - - edited Unfortunately, I'm not certain a parameter is ideal. In 1.7 we've introduced two transaction cache implementations, one that is memory-based and another that is persisted using Infinispan. In the case of the latter, the records are persisted to the cache with this hash value and so if a user inadvertently changed the size from X to Y, this would mean the hash values would be inconsistent and the purpose the hash serves would be fruitless for any in-progress transactions. In short, it would cause any in-progress transaction to emit duplicate events. As I mentioned the hash was added for LOB support, so perhaps we can guard this calculation to only be used/take effect when LOB support is enabled. That said, if we did that we could then just use the entire SQL_REDO string rather than relying only on an initial subset of the value. This should improve performance for users who don't enable the LOB support because Oracle won't be calculating these hashes on the mining query and secondly it should remove one extra collection check we do when processing the result-set.

            Ed Thompson (Inactive) added a comment -

            Thanks for the response ccranfor@redhat.com you have confirmed my own understanding of the background of the issues. I think it is probably fairly unusual to do inserts in this way outside of testing (we were testing debezium performance and so using these multirow inserts to create a lot of data churn artificially.) It did create a lot of confusion tracking the issue down because we could see "small" tables worked ok when their values fell inside 256 char limit. At this point a parameter for the limit might be the best balance between performance/behavior and allowing us to deal with this bug if it came up in production. I am not familiar enough with logminer to suggest a more elegant fix.

            Ed Thompson (Inactive) added a comment - Thanks for the response ccranfor@redhat.com you have confirmed my own understanding of the background of the issues. I think it is probably fairly unusual to do inserts in this way outside of testing (we were testing debezium performance and so using these multirow inserts to create a lot of data churn artificially.) It did create a lot of confusion tracking the issue down because we could see "small" tables worked ok when their values fell inside 256 char limit. At this point a parameter for the limit might be the best balance between performance/behavior and allowing us to deal with this bug if it came up in production. I am not familiar enough with logminer to suggest a more elegant fix.

            Chris Cranford added a comment -

            Hi ed.thompson, so normally if those inserts were done in separate statements, they would have been assigned different SCN values and so the hashing algorithm would not have failed despite the fact the first 256 characters of the SQL_REDO would have been the same. What ultimately broke the hashing algorithm is how the inserts were done using an insert-select w/union as you described.

            The main concern with using the entire SQL_REDO column value is performance. We introduced the hashing concept for LOB support because the connector needs to re-mine a transaction multiple times if the transaction is split across multiple mining sessions. I'm wondering if an alternative way can be used instead to replace the hashing entirely. The (RS_ID, SSN) tuple identifies a logical change according to the Oracle documentation, but this tuple is not unique across multiple rows and that's ultimately the problem or else we could use that instead.

            We'll look into what we can do to address this ed.thompson but if possible in the meantime I would avoid the use of the syntax described here if possible so that each insert gets assigned differing SCN for now to workaround the hashing failure.

            Chris Cranford added a comment - Hi ed.thompson , so normally if those inserts were done in separate statements, they would have been assigned different SCN values and so the hashing algorithm would not have failed despite the fact the first 256 characters of the SQL_REDO would have been the same. What ultimately broke the hashing algorithm is how the inserts were done using an insert-select w/union as you described. The main concern with using the entire SQL_REDO column value is performance. We introduced the hashing concept for LOB support because the connector needs to re-mine a transaction multiple times if the transaction is split across multiple mining sessions. I'm wondering if an alternative way can be used instead to replace the hashing entirely. The ( RS_ID , SSN ) tuple identifies a logical change according to the Oracle documentation, but this tuple is not unique across multiple rows and that's ultimately the problem or else we could use that instead. We'll look into what we can do to address this ed.thompson but if possible in the meantime I would avoid the use of the syntax described here if possible so that each insert gets assigned differing SCN for now to workaround the hashing failure.

            Gunnar Morling added a comment -

            ccranfor@redhat.com, something for you?

            Gunnar Morling added a comment - ccranfor@redhat.com , something for you?

              ccranfor@redhat.com Chris Cranford
              ed.thompson Ed Thompson (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

                Created:
                Updated:
                Resolved: