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

Oracle Logminer: LOB truncated in streaming mode

    XMLWordPrintable

Details

    • False
    • False
    • Hide

      In Oracle 19c, set up the following:

      CREATE TABLE foo (id INT PRIMARY KEY, lobcol CLOB);

CREATE OR REPLACE PROCEDURE grow_proc(rid IN PLS_INTEGER, str IN varchar2, n IN PLS_INTEGER) IS
        loc CLOB;
        i PLS_INTEGER;
BEGIN
        SELECT lobcol INTO loc FROM foo WHERE id = rid FOR UPDATE;
        DBMS_LOB.OPEN(loc, DBMS_LOB.LOB_READWRITE);
        FOR i IN 1..n LOOP
            DBMS_LOB.WRITEAPPEND(loc, LENGTH(str), str);
        END LOOP;
        DBMS_LOB.CLOSE(loc);
END;

      Then set up a debezium connector with logminer+lob support to read this table. Wait until the snapshot phase is over.
      Then insert some rows into the table as follows:

      INSERT INTO foo VALUES (1, 'x');
CALL grow_proc(1, '<some 1024-byte string>', 5000);
INSERT INTO foo VALUES (2, 'x');
CALL grow_proc(2, '<some 1024-byte string>', 5000);
      Show
      In Oracle 19c, set up the following: CREATE TABLE foo (id INT PRIMARY KEY , lobcol CLOB ); CREATE OR REPLACE PROCEDURE grow_proc(rid IN PLS_INTEGER, str IN varchar2 , n IN PLS_INTEGER) IS loc CLOB ; i PLS_INTEGER; BEGIN SELECT lobcol INTO loc FROM foo WHERE id = rid FOR UPDATE ; DBMS_LOB. OPEN (loc, DBMS_LOB.LOB_READWRITE); FOR i IN 1..n LOOP DBMS_LOB.WRITEAPPEND(loc, LENGTH (str), str); END LOOP ; DBMS_LOB. CLOSE (loc); END ; Then set up a debezium connector with logminer+lob support to read this table. Wait until the snapshot phase is over. Then insert some rows into the table as follows: INSERT INTO foo VALUES (1, 'x' ); CALL grow_proc(1, '< some 1024-byte string >' , 5000); INSERT INTO foo VALUES (2, 'x' ); CALL grow_proc(2, '< some 1024-byte string >' , 5000);

    Description

      When I set up a connector to read the contents of a table with a LOB column (I verified this with both BLOB and CLOB), with LOB support enabled in the configuration, I observe the following behavior for medium-sized (5-10MiB) LOBs:

      • initial snapshot replication works perfectly; the LOBs are emitted onto the Kafka topic in their entirety
      • during streaming replication, LOBs are truncated. Only the last ~300KiB of the LOB appears on the Kafka topic.

      I figured out what is going on: the code that processes LOBs (debezium-connector-oracle/src/main/java/io/debezium/connector/oracle/logminer/processor/TransactionCommitConsumer.java) assumes that a LOB value is represented in LogMiner as

      • a single SELECT <lob-column> INTO <variable> FOR UPDATE (SEL_LOB_LOCATOR event), followed by
      • an unbroken sequence of LOB_WRITE events

      When the LOB_WRITE event sequence is interrupted by any other event, the accumulated LOB contents are written to lastEvent's newValues array (mergeLobWriteData).

      However, in my reproduction scenario, I see the following behavior from Oracle (19c EE):

      • SEL_LOB_LOCATOR for row X, column Y
      • a (short) series of LOB_WRITE events
      • SEL_LOB_LOCATOR for row X, column Y (so same LOB as the first SEL_LOB_LOCATOR)
      • another shortish series of LOB_WRITE events
      • ... and so on

      Every time the LOB_WRITE series is interrupted with a (spurious) SEL_LOB_LOCATOR, mergeLobWriteData is called, the accumulated bytes are concatenated, and the previous accumulation is  overwritten in lastEvent.newValues.

      Ultimately, what ends up in the Kafka topics are the bytes from the last unbroken string of LOB_WRITE events, everything prior to that is discarded.

      Attachments

        Issue Links

          is related to

          Feature Request - Feature requests from customers and/or users DBZ-4321 Explore BLOB support via re-selection

          • Major - To be worked after higher priority work items are addressed. When the severity is high and the effort to change it is low to moderate, this term is chosen. Issues in this category likely have an existing workaround but implementation or execution may be non-trivial.
          • Closed

          Bug - A problem that impairs or prevents the functions of the product. DBZ-4650 Mark incompatible Xstream tests as LogMiner only

          • Major - To be worked after higher priority work items are addressed. When the severity is high and the effort to change it is low to moderate, this term is chosen. Issues in this category likely have an existing workaround but implementation or execution may be non-trivial.
          • Closed

          Bug - A problem that impairs or prevents the functions of the product. DBZ-4716 Document that Oracle Xstream emits DBMS_LOB method calls as separate events

          • Major - To be worked after higher priority work items are addressed. When the severity is high and the effort to change it is low to moderate, this term is chosen. Issues in this category likely have an existing workaround but implementation or execution may be non-trivial.
          • Closed

          Task - Represents a small unit of work that is not end-user facing. DBZ-4741 Unify Xstream behavior with LogMiner when using DBMS_LOB methods

          • Minor - To be worked after urgent, high, and medium priorities are resolved. This term is chosen when the severity of the issue is low, or the complexity or effort to correct it may be higher, relatively speaking. For low priority issues, known workarounds exist or are not needed due to the trivial effort needed to address the issue.
          • Open

          Activity

            People

              Unassigned Unassigned
              dominique.chanet@klarrio.com Dominique Chanet (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: