Debezium and Oracle DDL Change Capture support or not in version 1.9

[Bordin Suwannatri]

hi 

        Debezium  support for oracle DDL change (alter add , drop .. column) or not ?

if support how can i config that,  i try to poc this after i alter add column it's  Error.

debezium Caused by: io.debezium.connector.oracle.logminer.parser.DmlParserException: Failed to parse insert DML:

more information

I use

oracle 19c

debezium

kafka connect

kafka cluster

Please help.

Bordin S.

[Chris Cranford]

Hi Bordin -

So normally when we've seen DDL events not captured its due to one of two reasons:

    * All changes performed by the SYS or SYSTEM user are not captured
    * DDL event was not captured because connector is configured with "log.mining.strategy=online_catalog"

For the former, at no point should any objects in Oracle be manipulated by the SYS/SYSTEM users if you intend for those objects to be captured by the connector.  This is an optimization we decided upon with the Oracle LogMiner implementation as a way to minimize the noise of all the database-specific events that are often written by SYS and SYSTEM users. 

For the latter, there is unfortunately not much we can do if you hit this corner case.  The LogMiner results are predicated using the table include/exclude filters from the connector's configuration.  When a table is changed, you do have a chance that LogMiner will not report the DDL change when using online_catalog mode.  There are two use cases, one being when the table is renamed or a signficant change change caused the table's object id to change.  In this case, we wouldn't see the DDL change at all in the change stream since the query's predicates exclude the row since the table's name will be "OBJ#xxxxxx" where "xxxxxx" is the table's former object id.  This is usually the rarer of the two cases.  The second case is when you add/remove/rename a column.  In this case, we still get the table name resolved but unfortunately the columns in the DDL will be reported as "COLx" where x is the columns ordinal position in the table after the change.  This is more common and if you have skip unparseable DDL enabled, this error condition will only report a log warning and then any future change event could fail with a DML failure as you've described.

The easiest way to debug this is to look at your database history topic and see if you see the DDL for the table in question, and if so what are the column names. 

If you believe you've hit some other scenario, then please raise a Jira with all the details on how to reproduce the problem.

Hope that helps,
Chris

--
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 on the web visit https://groups.google.com/d/msgid/debezium/340e3540-f818-4157-b2b7-6bb4cb5a1688n%40googlegroups.com.

[Bordin Suwannatri]

thank you  so much Chris

-->   * All changes performed by the SYS or SYSTEM user are not captured

yes i alter add column by sys dba. i will try to use another user alter.

 

--> The second case is when you add/remove/rename a column.  In this case, we still get the table name resolved but unfortunately the columns in the DDL will be reported as "COLx" where x is the columns ordinal position in the table after the change.  This is more common and if you have skip unparseable DDL enabled, this error condition will only report a log warning and then any future change event could fail with a DML failure as you've described.

my configuration follow this

cat /data/debezium/debezium-oracle-connector.properties

name=debezium-ora-005
connector.class=io.debezium.connector.oracle.OracleConnector
db_type=oracle
tasks.max=1
database.server.name=tdpoem
database.tablename.case.insensitive=true
database.oracle.version=12+
database.hostname=172.19.130.59
database.port=1521
database.user=xstrm
database.password=xs
#database.user=dbzuser
#database.password=dbz
database.connection.adapter=logminer
database.dbname=DBZ
database.out.server.name=dbzxout
database.history.kafka.bootstrap.servers=poc-pinot01:9092
database.history.kafka.topic=debezium.oracle
database.history.skip.unparseable.ddl=false
include.schema.changes=true
table.whitelist=debezium.customers
errors.log.enable=true
ddl=ALTER,CREATE,DROP
#type=ALTER
tableChanges=debezium.customers
#snapshot.mode=schema_only_recovery
#snapshot.mode=initial
#snapshot.mode=schema_only

i try to test DDL capture like alter  add column, drop column, rename column.

if some parameter on my config wrong please let me know. i'm beginner of debezium. i'm poc debezium working with oracle , kafka , delta lake.

if i want to cdc ddl do i need to config some thing special or do something on oracle db ?

this parameter i just try to add but still error..

ddl=ALTER,CREATE,DROP
#type=ALTER
tableChanges=debezium.customers

normally in version 1.9 debezium support DDL capture ?

Thank you for your answer,  Chris

[Bordin Suwannatri]

Dear chris

* All changes performed by the SYS or SYSTEM user are not captured --> it happen because this case.  thank you very much.  i change another user alter table.  Capture DDL working now.

thank you somuch it's solved.

BR

Bordin S.

On Wednesday, May 18, 2022 at 8:09:50 PM UTC+7 Chris Cranford wrote:

[Chris Cranford]

Hi Bordin -

Your configuration looks fine to me, it seems the user of "SYS" was the problem.  Let us know if you hit any other issues!

Chris

To view this discussion on the web visit https://groups.google.com/d/msgid/debezium/60230300-21bd-4d37-ac6e-70ee6e289042n%40googlegroups.com.

[Bordin Suwannatri]

Thank you

Now continue POC.

[Bordin Suwannatri]

Hi Chris and Team

     i try to use apicurio for schema registry when i test alter drop column  by user debezium not sys or system ,   apicurio not update schema DDL. 

Please help recommend how to working with apicurio.

this is my configuration.

[ debezium]# cat debezium-oracle-apicurio-connector.properties
name=debezium-ora-007

snapshot.mode=initial
#snapshot.mode=schema_only

#registry
#key.converter=io.apicurio.registry.utils.converter.ExtJsonConverter
key.converter=io.apicurio.registry.utils.converter.AvroConverter
key.converter.apicurio.registry.url=http://poc-pinot01:8081/apis/registry/v2
key.converter.apicurio.registry.auto-register=true
key.converter.apicurio.registry.find-latest=true
#key.converter.apicurio.registry.converter.serializer=io.apicurio.registry.utils.serde.AvroKafkaSerializer
#key.converter.apicurio.registry.converter.deserializer=io.apicurio.registry.utils.serde.AvroKafkaDeserializer
key.converter.apicurio.registry.global-id=io.apicurio.registry.utils.serde.strategy.GetOrCreateIdStrategy
value.converter=io.apicurio.registry.utils.converter.AvroConverter
value.converter.apicurio.registry.url=http://poc-pinot01:8081/apis/registry/v2
value.converter.apicurio.registry.auto-register=true
value.converter.apicurio.registry.find-latest=true
#value.converter.apicurio.registry.converter.serializer=io.apicurio.registry.utils.serde.AvroKafkaSerializer
#value.converter.apicurio.registry.converter.deserializer=io.apicurio.registry.utils.serde.AvroKafkaDeserializer
value.converter.apicurio.registry.global-id=io.apicurio.registry.utils.serde.strategy.GetOrCreateIdStrategy

BR

Bordin S.

[Chris Cranford]

Hi Bordin -

Is the issue that you are not getting the DDL events still or is the issue related specifically to Apicurio and Schema Registry integration?

If its the former, could you please enable TRACE logging for "io.debezium" in your connector environment.  Could you then deploy the connector as a fresh new connector so we get the full logs of the snapshot & transition to streaming.  Could you then perform the DDL change followed by a manual INSERT or UPDATE into the table; and wait until you see the INSERT or UPDATE arrive in the connector logs.  Once you've done this could you share the logs?  Also, please note your precise SQL steps taken, including what user was used, etc. 

Thanks,
Chris

To view this discussion on the web visit https://groups.google.com/d/msgid/debezium/1db4b2cd-0c19-4b7c-88c9-56b06ab4e251n%40googlegroups.com.

[Bordin Suwannatri]

Hi Chris and ALL

      I use debezium , oracle connector ,  schema registry is Apicurio.

when i alter add new column, rename column Apicurio generate new version DDL it's corrent.

when i alter drop column Apicurio not generate new version DDL. it's wrong.  what i missing?

Plase help.

BR

Bordin S.

[Chris Cranford]

Hi Bordin -

How have you configured the integration with Apicurio, what strategies have you elected to use, etc.

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 on the web visit https://groups.google.com/d/msgid/debezium/21d0b44c-9155-4720-813a-6c42c339b0fcn%40googlegroups.com.

[Bordin Suwannatri]

HI chris

          

       My Configuration debezium connector part.

#registry

key.converter=io.apicurio.registry.utils.converter.AvroConverter
key.converter.apicurio.registry.url=http://poc-pinot01:8081/apis/registry/v2
key.converter.apicurio.registry.auto-register=true
key.converter.apicurio.registry.find-latest=true

key.converter.apicurio.registry.global-id=io.apicurio.registry.utils.serde.strategy.GetOrCreateIdStrategy
value.converter=io.apicurio.registry.utils.converter.AvroConverter
value.converter.apicurio.registry.url=http://poc-pinot01:8081/apis/registry/v2
value.converter.apicurio.registry.auto-register=true
value.converter.apicurio.registry.find-latest=true

value.converter.apicurio.registry.global-id=io.apicurio.registry.utils.serde.strategy.GetOrCreateIdStrategy

#######################

     I Need to use apicurio for schema registry (avro format) ,CDC store in kafka  , Provide for  DELTA LAKE consume data.

Normally if table structure change it should make new version on apicurio (i'm i correct ?) .  I test alter table add new column and alter table rename column it's make new version for new structure table.

when i test alter drop column  table,  apicurio not have new version, it's still old version.... : (

i check topic on kafka it's have correct column change.

Thank you for answer.

please suggest.

BR

Bordin S.