Oracle table schema generation: PK columns marked optional

[Kiril Piskunof]

Hi,

I believe we have run into a bug in the Oracle connector that affects how it builds a table schema when it receives a DML event for a table that is not in its schema history. The connector can fail to mark a primary key column as non optional.

Environment:
• Debezium 2.7.3 Final
• Kafka Connect in standalone mode
• Schema history stored in a local file
• XStream outbound server as the source
• Connector configured to capture only a specific set of table schemas

The issue appears at the intersection of two conditions.

1. Debezium connects to the database using a service name, for example DB1_SVC.WORLD rather than the actual database name DB1.WORLD. The connector is configured with database.dbname=DB1_SVC.WORLD. The database.pdb.name property is not set at the moment, although we will investigate it because it may allow a workaround.

2. The table has an ID primary key, but the CREATE TABLE statement does not declare ID as not null or include the primary key. A separate ALTER TABLE statement adds the primary key constraint later.

Here is the sequence that reveals the bug. When Debezium starts, it builds the initial table schema through JDBC. This uses the name in database.dbname, so the tableId is built with DB1_SVC.WORLD. The JDBC based path correctly marks ID as non optional.

However, the incoming DML events contain the database name DB1.WORLD. Because this does not match the initial tableId, Debezium fails to find the schema and the LcrEventHandler rebuilds it. This rebuild uses a different code path. It parses the table DDL rather than using JDBC. This path appears to have a bug. It does not infer that ID is non optional when it processes the ALTER TABLE that adds the primary key.

Later on, this mismatch causes Avro schema serialization failures.

[Chris Cranford]

Hi Kiril -

If I understand correctly, the "database.dbname" and the database/catalog name in the LCRs differ. This is not expected, and having this situation will result in undefined behavior. The "database.dbname" should be identical "database/catalog" name in the LCR. 

-cc

--
You received this message because you are subscribed to the Google Groups "debezium" group.
To unsubscribe from this group and stop receiving emails from it, send an email to debezium+u...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/debezium/2c98dc42-b7ff-46ea-bfc0-c0c3917a9daan%40googlegroups.com.

[Big Andy]

There is at least two bugs in the DDL parsing logic.

1. In ColumnDefinitionParserListener resolveColumnDataType(PlSqlParser.Column_definitionContext ctx), it determines if a column is optional only by looking at the field definition:

boolean hasNotNullConstraint = ctx.inline_constraint().stream().anyMatch(c -> c.NOT() != null);
columnEditor.optional(!hasNotNullConstraint);

So if the table definition lists the column as a primary key, its still marked optional.
Hence the following DDL will incorrectly result in an optional primary key column:

   CREATE TABLE FOO  (

"ID" VARCHAR2(255),

        ...
        CONSTRAINT FOO_PK PRIMARY KEY ("ID")    
   );

  Note: `ID` column is not marked as non-nullable, but is due to it being in the primary key.

2. In AlterTableParserListener.enterConstraint_clauses, when it sees a primary key constraint being added, it doesn't mark those columns as non-optional
    Hence the following DDL will incorrectly result in an optional primary key column:

   CREATE TABLE FOO  (

"ID" VARCHAR2(255),

        ...
   );

    ALTER TABLE FOO ADD CONSTRAINT "FOO_PK" PRIMARY KEY ("ID");
 
 Note: `ID` column is not marked as non-nullable, but is due to it being in the primary key.

There may also be other bugs in this code. This could be fixed, or maybe a better solution would be to use the same logic as used to build the table schema during the initial bootstrapping, i.e. by querying the meta tables in Oracle.

Andy

[jiri.p...@gmail.com]

Hi,

given you are on Debezium 2.7 and we are on 3.3 I'd recommend to give it a try to see if the issue is still present or not.

Jiri

[Andrew Coates]

Hi Jiri,

We’re in the process of updating. However, I identified the bugs in the current truck branch.

Andy

--
You received this message because you are subscribed to a topic in the Google Groups "debezium" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/debezium/mluoW9dlhak/unsubscribe.
To unsubscribe from this group and all its topics, send an email to debezium+u...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/debezium/0b6e1ca9-b06c-4694-9f6f-e07678a99eb6n%40googlegroups.com.

[Chris Cranford]

Please raise a Jira issue and a PR if you are able Andy.

You received this message because you are subscribed to the Google Groups "debezium" group.

To unsubscribe from this group and stop receiving emails from it, send an email to debezium+u...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/debezium/CANpG74HQyTV6kajOBnD%3DzK-xOQiBx-_8whh7XxX_sPfsvck-Ug%40mail.gmail.com.