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

Oracle connector: JDBC transaction can only capture single DML record

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Critical
    • Resolution: Done
    • Affects Version/s: 0.9.3.Final
    • Fix Version/s: 0.9.5.Final
    • Component/s: oracle-connector
    • Labels:
      None
    • Environment:

      database: oracle 11g (11.2.0.4.0) / oracle 12c(12.2.0.1.0)
      debezium : 0.9.3-Final

      Description

      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

        Gliffy Diagrams

          Attachments

            Activity

              People

              • Assignee:
                jpechanec Jiri Pechanec
                Reporter:
                dreamcatchernick Nick Gu
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: