debezium / debezium/dbz

log.mining.username.exclude.list option does not filtering out "START", "COMMIT" and "ROLLBACK" entries made by specified user

Open
#24 8 comments 0 reactions 1 assignee Claimed by @Naros View on GitHub
type/bug
Dominant language
HTML
Stars
6
Forks
8
Avg merge
2d 19h
Merged PRs (30d)
1

Description

## Bug report

**What Debezium connector do you use and what version?**

` Oracle, 3.3.2.Final `

---

**What is the connector configuration?**

`{
"archive.destination.name": "LOG_ARCHIVE_DEST_1",
"connector.class": "io.debezium.connector.oracle.OracleConnector",
"database.dbname": "KIASPROD",
"database.password": "${file:/etc/kafka/connect/.credentials:oracle_debezium_password}",
"database.schema": "debezium",
"database.url": "jdbc:oracle:thin:@(DESCRIPTION=(LOAD_BALANCE=OFF)(FAILOVER=ON)(ADDRESS_LIST=(ADDRESS = (PROTOCOL = TCP)(HOST = mosrepora01.***)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = mosrepora03.***)(PORT = 1521)))(CONNECT_DATA = (SERVICE_NAME = kiasprodha)))",
"database.user": "debezium",
"database.query.timeout.ms": "0",
"decimal.handling.mode": "string",
"errors.max.retries": "10",
"heartbeat.action.query": "update kafka_heartbeat_table set value = 1",
"heartbeat.interval.ms": "60000",
"log.mining.archive.log.only.mode": "false",
"log.mining.batch.size.default": "50000",
"log.mining.batch.size.max": "200000",
"log.mining.batch.size.min": "50000",
"log.mining.batch.size.increment": "50000",
"internal.log.mining.log.query.max.retries": "50",
"log.mining.username.exclude.list": "SYS",
"log.mining.query.filter.mode": "in",
"log.mining.transaction.retention.ms": "7200000",
"log.mining.sleep.time.increment.ms": "5000",
"log.mining.sleep.time.max.ms": "20000",
"log.mining.sleep.time.default.ms": "5000",
"log.mining.strategy": "hybrid",
"max.batch.size": "32768",
"max.queue.size": "65536",
"openlineage.integration.enabled": "false",
"producer.override.batch.size": "1048576",
"producer.override.compression.type": "lz4",
"producer.override.linger.ms": "200",
"producer.override.max.request.size": "10485760",
"query.fetch.size": "500000",
"schema.history.internal.consumer.sasl.jaas.config": "${file:/etc/kafka/connect/.credentials:jaas_config}",
"schema.history.internal.consumer.sasl.mechanism": "PLAIN",
"schema.history.internal.consumer.security.protocol": "SASL_SSL",
"schema.history.internal.consumer.ssl.truststore.location": "/etc/kafka/ssl/kafka.truststore.jks",
"schema.history.internal.consumer.ssl.truststore.password": "${file:/etc/kafka/connect/.credentials:ssl_password}",
"schema.history.internal.kafka.bootstrap.servers": "kafka01.***:9092,kafka02.***:9092,kafka03.***:9092",
"schema.history.internal.kafka.topic": "__history-kias-debezium",
"schema.history.internal.skip.unparseable.ddl": "true",
"schema.history.internal.store.only.captured.tables.ddl": "true",
"schema.history.internal.store.only.captured.databases.ddl": "true",
"schema.history.internal.producer.sasl.jaas.config": "${file:/etc/kafka/connect/.credentials:jaas_config}",
"schema.history.internal.producer.sasl.mechanism": "PLAIN",
"schema.history.internal.producer.security.protocol": "SASL_SSL",
"schema.history.internal.producer.ssl.truststore.location": "/etc/kafka/ssl/kafka.truststore.jks",
"schema.history.internal.producer.ssl.truststore.password": "${file:/etc/kafka/connect/.credentials:ssl_password}",
"schema.include.list": "INSLAB,DEBEZIUM,KASKO_B2B,SCORING",
"skip.messages.without.change": "true",
"snapshot.mode": "no_data",
"table.include.list": "***sometables***",
"tasks.max": "1",
"tombstones.on.delete": "false",
"topic.heartbeat.prefix": "__debezium-heartbeat",
"topic.prefix": "KIAS",
"transforms": "unwrap,ReplaceField",
"transforms.ReplaceField.exclude": "__deleted",
"transforms.ReplaceField.type": "org.apache.kafka.connect.transforms.ReplaceField$Value",
"transforms.unwrap.add.fields": "op:OPER_TYPE,ts_ms:CAPTURE_TS_MS,source.ts_ms:SOURCE_TS_MS",
"transforms.unwrap.add.fields.prefix": "",
"transforms.unwrap.delete.tombstone.handling.mode": "rewrite",
"transforms.unwrap.type": "io.debezium.transforms.ExtractNewRecordState"
}`

---

**What is the captured database version and mode of deployment?**

_E.g. on-premises, with a specific cloud provider, etc._

`on-premises, Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production`

---

**What behavior do you expect?**

` "log.mining.query.filter.mode": "in" with "log.mining.username.exclude.list": "SYS" should filter out any log entries made by "SYS" user from the V$LOGMNR_CONTENTS query results`

---

**What behavior do you see?**

`The START COMMIT and ROLLBACK operations are still selected. When DBAs moving partitions, these 3 can occupy more than 90% of all log entries. For example, in the SCN range from 156896439209 to 156896521053 there are 11725376 entries overall, and 11624161 are START COMMIT and ROLLBACK with USERNAME = 'SYS'. Seems like the issue is in the case operator:
and (case when OPERATION_CODE in (6
, 7
, 36) then 1 else case when UPPER(USERNAME) not in ('SYS') then 1 else 0 end end = 1)
which is always true for operation codes 6,7,36`

---

**Do you see the same behaviour using the latest released Debezium version?**

_Ideally, also verify with the latest Alpha/Beta/CR version._

`Haven't tried newer than 3.3.2.Final`

---

**Do you have the connector logs, ideally from start till finish?**

_You might be asked later to provide [DEBUG/TRACE](https://debezium.io/documentation/reference/stable/operations/logging.html) level log._

`No, i don't have logs right now`

---

**How to reproduce the issue using our [tutorial](https://github.com/debezium/debezium-examples/tree/main/tutorial) deployment?**

`Don't know, may be move some partitions with SYS`

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.