-
Bug
-
Resolution: Done
-
Critical
-
0.9.3.Final
-
None
I use debezium connector to extract data from oracle 11g/12c.
I found the topic is missing a lot of messages then i wrote a little test code. Below is the code
String insertCustomer = "INSERT INTO CUSTOMER (ID , FULLNAME) VALUES(?, ?)"; try { Class.forName("oracle.jdbc.driver.OracleDriver"); //11g //Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@10.177.83.9:1521:ycdb", "dmdata", "dmdata"); //12c Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@10.7.112.18:1521/dmpdb", "debezium", "dbz"); connection.setAutoCommit(false); for (int i =1 ; i< 5 ; i++) { PreparedStatement ps = connection.prepareStatement(insertCustomer); ps.setInt(1 , i); ps.setString(2 , "helloworld" + i); ps.executeUpdate(); } connection.commit(); connection.close(); } catch (SQLException e) { System.out.println("Connection Failed! Check output console"); e.printStackTrace(); return; } catch (ClassNotFoundException e) { e.printStackTrace(); return; }
Basically the code is turned off the auto commit and use jdbc transaction, so what happened is "insert multiple rows , then do a single commit " i can only see ONE message is produced to the topic
Then i try to debug the code what i found the code below in oracle-connector
"LcrEventHandler.java , line 56 "
// After a restart it may happen we get the event with the last processed SCN again if (scn <= offsetContext.getScn()) { LOGGER.debug("Ignoring change event with already processed SCN {}", scn); return; }
what happens in oracle multiple DML in one transaction share same scn , so when this handler processed a LCR, if next LCR have same scn it will just return.
I don't fully understand the logic , this is all information i know.
Thanks for the help
[DBZ-1223] Oracle connector: JDBC transaction can only capture single DML record
Gunnar Morling
added a comment - Thanks a lot for the report, dreamcatchernick! It seems this is based on a mis-interpretation of the SCN on our side. So this skipping logic you pointed to should be based on the actual position bytes array instead of the SCN. Here's what we'll do:
- alter the skipping logic as suggested above
- add the bytes array to the offset produced by the connector
- base offset committing on that byte array instead of the SCN
- optionally: add the position bytes array to the "source info" block of CDC events
We won't be able to do this for 0.9.4, so this all will be part of Debezium 0.9.5 (planned for next week or latest the one after that).
Gunnar Morling
added a comment - Thanks a lot for the report, dreamcatchernick ! It seems this is based on a mis-interpretation of the SCN on our side. So this skipping logic you pointed to should be based on the actual position bytes array instead of the SCN. Here's what we'll do:
alter the skipping logic as suggested above
add the bytes array to the offset produced by the connector
base offset committing on that byte array instead of the SCN
optionally: add the position bytes array to the "source info" block of CDC events
We won't be able to do this for 0.9.4, so this all will be part of Debezium 0.9.5 (planned for next week or latest the one after that). 
Jiri Pechanec
added a comment - Thanks for the report, we can confirm the finding.
The problem is that LCRs for the smae transaction has associated same SCN. So we need to replace scns in offsets with raw position in byte[] format. Accroding to Oracle docs we can easily execute byte-by-byte comparison betweem two LCR positions to establish order.
Jiri Pechanec
added a comment - Thanks for the report, we can confirm the finding.
The problem is that LCRs for the smae transaction has associated same SCN. So we need to replace scns in offsets with raw position in byte[] format. Accroding to Oracle docs we can easily execute byte-by-byte comparison betweem two LCR positions to establish order. 
Released