CodeWithPravinMaske opened a new issue, #12598:
URL: https://github.com/apache/seatunnel/issues/12598
### Search before asking
- [x] I had searched in the issues and found no similar issues.
### What happened
For a table **without a primary key**, the JDBC-based CDC sources choose the
snapshot split column from a **unique key**
(`AbstractJdbcSourceChunkSplitter#getSplitColumn`). A unique key column can be
**nullable**, and MySQL/PostgreSQL allow any number of NULLs in it.
The snapshot chunks are range queries on the split column (`col <= ?`, `col
>= ? AND col <= ?`, `col >= ?`). A NULL never matches a range predicate, and
there is no `col IS NULL` chunk, so **every row with NULL in the split column
is silently skipped by the snapshot** as soon as the table is split into more
than one chunk. The job reports no error.
`getSplitColumn` lives in `connector-cdc-base`, so all JDBC CDC sources that
use it are affected (MySQL, PostgreSQL, Oracle, SQL Server, DB2).
For MySQL the nullability check on the parsed schema does not help: Debezium
1.9.8 promotes the first `UNIQUE KEY` of a table without a primary key to a
primary key and marks its columns NOT NULL
(`CreateTableParserListener#enterUniqueKeyTableConstraint` ->
`MySqlAntlrDdlParser#parsePrimaryIndexColumnNames`), even though the database
allows NULL in them.
### How to reproduce (MySQL 8.0, verified on `dev`)
```sql
CREATE TABLE shop.no_pk_orders (
id INT NOT NULL,
code INT NULL,
name VARCHAR(32),
UNIQUE KEY uk_code (code)
);
-- 1000 rows with code 1..1000 and 100 rows with code NULL (1100 rows in
total)
```
```hocon
source {
MySQL-CDC {
url = "jdbc:mysql://localhost:3306/shop"
table-names = ["shop.no_pk_orders"]
startup.mode = "initial"
exactly_once = false
snapshot.split.size = 100
# username, password, server-id ...
}
}
```
Result: **1000 of 1100 rows** reach the sink; **all 100 rows with `code IS
NULL` are missing**. The log shows the nullable column being chosen:
```
No primary key found for table shop.no_pk_orders
Chosen split column code for table shop.no_pk_orders
Splitting table shop.no_pk_orders into chunks, split column: code, min: 1,
max: 1000, chunk size: 100
```
### Expected behavior
All rows are read. A nullable column must not be used as the snapshot split
column; if no non-nullable key column is available, the table should be read as
a single split (the existing fallback when no key exists).
### SeaTunnel Version
dev (3.0.0-SNAPSHOT)
### Are you willing to submit a PR?
- [x] Yes I am willing to submit a PR!
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
