CodeWithPravinMaske opened a new pull request, #12608:
URL: https://github.com/apache/seatunnel/pull/12608
Closes #12599
### Purpose of this pull request
For a MySQL table **without a primary key** that has a **nullable unique
key**, MySQL-CDC emits the type default (`0`) instead of `NULL` for the key
column, in the snapshot and in binlog events. Silent data corruption: the sink
gets `0` and no error is reported.
**Root cause:** `MySqlSchema#parseSnapshotDdl` builds the table with
Debezium 1.9.8. For a table without a primary key, Debezium promotes the first
`UNIQUE KEY` to the primary key and forces all of its columns to
`optional(false)` (`CreateTableParserListener#enterUniqueKeyTableConstraint` ->
`MySqlAntlrDdlParser#parsePrimaryIndexColumnNames`), although MySQL allows NULL
in them. The value converters then substitute the default for NULL.
**Fix** (as suggested in #12599): after parsing,
`MySqlSchema#restoreNullableColumns` re-applies the nullability of the catalog
table, which is read from the database metadata. A column Debezium marked NOT
NULL but the database reports as nullable is made optional again. Real primary
key columns are NOT NULL in the database, so they are unchanged; columns
declared as `primaryKeys` in `table-names-config` are also unchanged (the
catalog table marks them NOT NULL).
**Composite unique key** (question from #12599): affected too. With `UNIQUE
KEY (a, b)`, `a NOT NULL`, `b NULL`, Debezium marks both columns NOT NULL and
`b`'s NULLs were emitted as `0`. The fix restores `b` and keeps `a` required.
### Does this PR introduce _any_ user-facing change?
Yes, a bug fix: NULL values in a nullable unique key column of a MySQL table
without a primary key are now emitted as NULL instead of `0`, in the snapshot
and binlog phases. No option or default changes.
### How was this patch tested?
- **Unit tests** (`MySqlSchemaTest`, extended): a single nullable unique key
column stays optional (Debezium still promotes it to the primary key), and in a
composite unique key only the nullable column is optional. Without the fix both
tests fail.
- **E2E**
(`AbstractMysqlCDCITBase#testMysqlCdcNullInNullableUniqueKeyWithoutPrimaryKey`):
two tables without a primary key, one with a single and one with a composite
nullable unique key; compares complete rows (including NULLs) between source
and sink after the snapshot phase and after binlog inserts of NULL rows.
- **Manual, real MySQL 8.0**, before / after:
| | before | after |
|---|---|---|
| single key, snapshot | `2, 0` | `2, NULL` |
| single key, binlog | `4, 0` | `4, NULL` |
| composite key, snapshot | `2, 1, 0` | `2, 1, NULL` |
| composite key, binlog | `4, 3, 0` | `4, 3, NULL` |
Related: #12597 (fix for #12598), the same Debezium promotion causing the
snapshot to skip NULL rows. The two PRs are independent.
### Check list
* [x] If any new Jar binary package adding in your PR, please add License
Notice according
[New License
Guide](https://github.com/apache/seatunnel/blob/dev/docs/en/developer/new-license.md)
— N/A, no new jar.
* [x] If necessary, please update the documentation to describe the new
feature. — N/A, bug fix.
* [x] If necessary, please update `incompatible-changes.md` to describe the
incompatibility caused by this PR. — N/A, values that were wrongly `0` are now
NULL as stored in the database.
* [x] If you are contributing the connector code, please check that the
following files are updated: — N/A, fix to an existing connector.
--
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]
