li3zhi4 opened a new issue, #11741: URL: https://github.com/apache/seatunnel/issues/11741
### Search before asking - [X] I had searched in the [issues](https://github.com/apache/seatunnel/issues?q=is%3Aissue) and found no issue/PR covering composite-key **multi-column chunk splitting** for the JDBC source (`connector-jdbc` has its own splitter and is a different module from the CDC connectors): - #9949 "[Bug] [mysql-cdc] ... composite index ... retrieves fields other than the primary key ... as the partitioning column" (closed) — split-column **selection**, fixed by #10236/#10319; not multi-column splitting. - #7794 "[Bug] [MYSQL-CDC] ... 不使用主键(id)进行分割，而是使用唯一键" (closed) — split-column selection, single-column splitting. - #10596 / #10604 "[Feature][CDC/JDBC] Add option to disable sampling-based sharding" (closed/merged) — adds the `split.allow-sampling=false` toggle that this issue's scenario relies on (single-column split degrades when sampling is off and the first key column repeats heavily); related prerequisite, but that work is about the sampling switch itself, not multi-column composite-key splitting. - #11396 "[Fix][Connector-V2][Jdbc] Fix DATE partition column splitting" (open) — single-column DATE type handling. - #8301 "[Bug] [JDBC source] Automatic Split Chunk does not support string type primary key" (closed, stale) — single-column **String** PK splitting is slow; addressed by the single-column string split strategy, not multi-column splitting. - #10861 "[Improve][Connector-V2][JDBC] Add string split strategy" (merged) — single-column String partition strategy (`none`/`hash`/`range`/`auto`); not composite-key splitting. ### What happened The JDBC source (`connector-jdbc`) splits a table into chunks using **only the first column of the primary key**. `ChunkSplitter.findSplitKey()` (verified on `dev` 2026-08-10): ```java PrimaryKey pk = schema.getPrimaryKey(); if (pk != null) { for (String pkField : pk.getColumnNames()) { Column column = columnMap.get(pkField); if (isSupportSplitColumn(column)) { return Optional.of(new SeaTunnelRowType( new String[] {pkField}, // ← single column only new SeaTunnelDataType[] {column.getDataType()})); } } } ``` For tables with a **composite primary key** (`PRIMARY KEY (col1, col2, ...)`), the whole split is driven by the first key column alone: 1. **First column has (almost) one distinct value** — the worst case. The chunk loop hits `min.equals(max)` and returns a **single full-table chunk**, so the whole table is read by one reader: parallelism is lost entirely and a large table's snapshot takes as long as a single-threaded scan. 2. **First column has low cardinality / many duplicates** — `splitUnevenlySizedChunks`' `nextChunkEnd()` must keep calling `queryMin` to skip past duplicated boundary values; chunk count collapses and remaining chunks are severely skewed, so a single chunk can dominate the job duration. 3. `DynamicChunkSplitter` on `dev` has **no** multi-column (`Composite`) methods at all — grep for `Composite`/`queryNextChunkMaxComposite` returns zero hits (verified 2026-08-10). ### SeaTunnel Version Current upstream `dev` (verified 2026-08-10 by code inspection); the same single-column split behavior exists in 2.3.13. ### SeaTunnel Config ```hocon source { Jdbc { url = "jdbc:mysql://<host>:3306/<db>" driver = "com.mysql.cj.jdbc.Driver" user = "user" password = "pass" table_path = "<db>.<composite_key_table>" parallelism = 4 } } ``` A table like: ```sql CREATE TABLE t ( order_id BIGINT, line_no INT, payload TEXT, PRIMARY KEY (order_id, line_no) ); ``` ### Root cause (confirmed by code inspection) - `ChunkSplitter.findSplitKey()` returns a single-column `SeaTunnelRowType` (first PK column supported by `isSupportSplitColumn`). - `DynamicChunkSplitter` on `dev` exposes only single-column `queryMinMax` / `queryMin` / `queryNextChunkMax` (taking `String splitColumnName`); there is no multi-column path. - The bounding-box predicate issue also exists for the data-scan SQL once multi-column keys are introduced (per-column AND comparison is not lexicographic). ### Scope of application (which tables are affected) The multi-column split path would apply only to composite-PK tables on the JDBC source: | Table shape | Split key selection (dev) | Path taken | |---|---|---| | **Composite PK** (≥2 columns) | `findSplitKey()` picks **only the first supported PK column** → single-column split on one key | Degraded (this issue): single-chunk or severely skewed chunks when the first column repeats heavily | | Single PK (1 column) | First supported PK column | Unchanged single-column path | | User-configured `partition_column` | Always wins, single column | Unchanged | | Query-based table (`query` set, no `partition_column`) | No split key → single split | Unchanged | | No PK | First unique-key column, else single split | Unchanged | ### Design rationale (why this is the right approach) 1. **Tuple-ordered splitting is the correct semantics for a composite key** — chunk boundaries must follow the key's lexicographic order, otherwise ranges overlap or leave gaps. With a single-column split key the lexicographic order is trivial, but for a composite key `(col1, col2, ...)` the correct boundary condition is a tuple comparison (`(col1, col2) > (v1, v2)`), not per-column AND predicates. Splitting on the full key tuple is therefore the direct, correctness-first consequence of having a composite PK. 2. **Expanded OR/AND form is the index-friendly way to express tuple comparisons** — a row-constructor comparison `(a, b) >= (?, ?)` often makes MySQL's optimizer fall back to a full scan, while the equivalent expanded form `a > ? OR (a = ? AND b >= ?)` lets the composite index be used. This is a standard SQL technique for multi-column range scans, so the fix is not a workaround but the canonical SQL shape. 3. **Directly complements #10604** — with the sampling switch (`split.allow-sampling=false`) now available, tables that are unevenly distributed go straight to `splitUnevenlySizedChunks`; for composite-PK tables whose first column repeats heavily, only tuple-ordered multi-column splitting keeps chunks balanced. This issue is the natural next step after that switch. ### Proposed fix 1. `ChunkSplitter.findSplitKey()`: when the primary key has multiple columns, return all supported PK columns as the split key (multi-column `SeaTunnelRowType`), with a user override (`partition_column`) still winning. 2. `DynamicChunkSplitter`: add multi-column `queryMinMaxComposite` / `queryNextChunkMaxComposite` / `queryMinComposite` and `createCompositeSplit` — tuple comparisons **expanded into OR/AND form** (`col1 > ? OR (col1 = ? AND col2 >= ?)`) so the composite index can be used, and `ORDER BY ... LIMIT chunkSize` for boundary walking. 3. Keep single-column behavior unchanged when the table has a single-column key (backward compatible). ### Verification Unit tests (composite `findSplitKey`, multi-column `queryMinMax`/`queryNextChunkMax`, expanded OR/AND SQL shape, half-open boundary semantics) plus an official JDBC E2E on `dev` with a composite-PK table asserting PK-level source/target consistency — to be included in the PR. ### Zeta or Flink or Spark Version zeta ### Java or Scala Version Java 8 / 11 (both OK) ### Screenshots _No response_ ### Are you willing to submit PR? - [X] Yes I am willing to submit a PR! (port to `dev` in progress) ### Code of Conduct - [X] I agree to follow this project's [Code of Conduct](https://www.apache.org/foundation/policies/conduct) -- 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]
