SEPURI-SAI-KRISHNA opened a new issue, #13221:
URL: https://github.com/apache/seatunnel/issues/13221

   ### Search before asking
   
   - [X] I had searched in the 
[issues](https://github.com/apache/seatunnel/issues?q=is%3Aissue+label%3A%22Bug%22)
 and found no similar issues. I also checked the open PRs that touch the 
`multitablesink` package. #12366 routes Paimon fixed buckets on Flink and 
requires `multi_table_sink_replica = 1`. #11077, and the issue #13108, are 
about many source tables that write into one sink table. #13201 changes how 
timer flushes run, and #13216 changes the source section of `multi-table.md`. 
None of them changes how rows of one table are routed to the replicas.
   
   ### What happened
   
   With `multi_table_sink_replica` above 1, a sink can apply the changes of one 
key in the wrong order. The job succeeds, and the sink table ends with rows 
that were deleted at the source, old values, and more than one row for a key. 
Every sink that applies updates and deletes by key is affected, except the Jdbc 
sink. The Jdbc sink with `is_exactly_once = true` is affected too: 
`JdbcExactlyOnceSinkWriter` keeps the default `primaryKey()` (checked with 
reflection on the built jar), so its rows take the random route. I have not run 
that writer against a database.
   
   The cause is in `MultiTableSinkWriter.write`. Each replica is a queue with 
its own sub-writer for every table. A row is routed by the key that the 
sub-writer declares in `SupportMultiTableSinkWriter.primaryKey()`:
   
   ```java
   Optional<Integer> primaryKey = tableId == null ? null : 
sinkPrimaryKeys.get(tableId);
   if ((primaryKey == null && sinkPrimaryKeys.size() == 1)
           || (primaryKey != null && !primaryKey.isPresent())) {
       int index = random.nextInt(blockingQueues.size());
       offerRowElement(index, element);
   }
   ```
   
   `primaryKey()` returns `Optional.empty()` by default, and only 
`JdbcSinkWriter` overrides it. For every other sink the queue is random, so the 
`INSERT`, `UPDATE_AFTER` and `DELETE` of one key go to different sub-writers. 
The sub-writers flush on their own, and `prepareCommit` runs them in parallel, 
so nothing keeps the order between them.
   
   The docs say the order is kept. 
`docs/en/architecture/features/multi-table.md` (5.3, "Hash-based (primary key 
available)"): "the same key always reaches the same queue, which is what 
preserves ordering for that key". The Iceberg sink page lists CDC writes as 
supported and documents `multi_table_sink_replica` for tables that need more 
than one writer. The project's own E2Es run MySQL-CDC with 
`multi_table_sink_replica = 2` (for example 
`mysql_cdc_to_iceberg_for_schema_change.conf` and 
`mysqlcdc_to_mysql_with_schema_change.conf`), so this is a supported 
combination. No sink page warns about the order.
   
   ### How I checked it
   
   All on dev `535d4237dd`, JDK 17. The input is 1000 keys. Each key gets 
`INSERT (v1)`, `UPDATE_BEFORE (v1)`, `UPDATE_AFTER (v2)`, and every second key 
also gets `DELETE`. That is 3500 rows, and the correct result is 500 rows with 
the value `v2`.
   
   - **`MultiTableSinkWriter` on its own.** Sub-writers that record what they 
receive, in the style of `MultiTableSinkWriterTest`:
   
     | sub-writer | replicas | keys whose changes were split across sub-writers 
|
     | --- | --- | --- |
     | declares no key (every sink except the default Jdbc writer) | 1 | 0 of 
1000 |
     | declares no key | 2 | 821 of 1000 |
     | declares no key | 4 | 962 of 1000 |
     | declares a key (as Jdbc does) | 2 and 4 | 0 of 1000 |
     | declares a key, and the key column is `BYTES` | 2 | 821 of 1000 |
   
     The last row is a second problem, in the Jdbc path: the route uses 
`object.hashCode()`, and for a `byte[]` that is the identity hash, so equal 
keys do not reach the same queue.
   
   - **A real job.** I built the starter and the connectors from dev and ran 
`bin/seatunnel.sh --config job.conf -m local` (Zeta). FakeSource with a primary 
key writes the 3500 rows to the Iceberg sink (Hadoop catalog on local disk, 
`iceberg.table.primary-keys = "id"`). A second job reads the table back.
   
     | `multi_table_sink_replica` | rows | distinct ids | deleted ids still in 
the table | rows with the old value | ids with more than one row |
     | --- | --- | --- | --- | --- | --- |
     | 1 | 500 | 500 | 0 | 0 | 0 |
     | 2 | 911 | 796 | 296 | 183 | 115 |
     | 4 | 1372 | 943 | 443 | 493 | 429 |
   
     All three jobs exit 0 with `Total Failed Count 0`. The wrong counts change 
from run to run, because the route is random. With 
`iceberg.table.upsert-mode-enabled = true`, the setting of the E2E above, 2 
replicas give 1123 rows.
   
   - **With a prototype of the fix below**, the same jobs each give 500 rows, 
500 ids, and 0 in the other three columns, also in upsert mode.
   
   - **As an E2E.** I turned the job into an Iceberg E2E with 100 keys and 4 
replicas. Run as a local job on dev, its table has 139 rows instead of 50. With 
the prototype, the E2E passes on Zeta and on Flink.
   
   - **The Jdbc sink, on an embedded DuckDB.** With 2 replicas, an `int` 
primary key gives the correct 500 rows, because `JdbcSinkWriter` declares the 
key. A `BYTES` primary key fails the job with `Duplicate key "id: k0003" 
violates primary key constraint` and `Conflict on tuple deletion!`. A table 
that has only a unique key fails the same way. Both give the correct 500 rows 
with 1 replica.
   
   - **A key that exists only as a sink option.** The upstream table has no 
primary key and the sink sets `iceberg.table.primary-keys = "id"`: 931 rows 
with 2 replicas, and the prototype does not fix this one (958 and 970 rows in 
two runs), because the table schema has no key to route by.
   
   On dev I have not run a Docker sink (Doris, StarRocks, Elasticsearch), Flink 
or Spark. The routing code is in `seatunnel-api` and is the same for all of 
them. The same code is in `v3.0.0` and `2.3.12` (read, not run).
   
   ### Proposed fix
   
   When the sub-writer declares no key, route by the primary key of the table 
schema instead of at random. `MultiTableSink` already has each sink's 
`getWriteCatalogTable()`, and almost every sink implements it. The route hashes 
all key columns, and hashes a `byte[]` by content. A key that the sub-writer 
declares still wins, and a table with no primary key stays random, as today. 
The Jdbc exactly-once writer declares no key, so the fallback covers it too.
   
   This changes the routing of keyed tables from random to hash, so I would add 
it to `incompatible-changes.md`. No option and no state format changes.
   
   The key position has to follow schema changes. Today the declared keys are 
read once, when the writer is built, and `JdbcSinkWriter.primaryKeyIndex` is 
final, so after a change that moves the key column (a column added `FIRST`, or 
a column dropped before the key) the route uses the old position. I read this 
in the code and have not run it. In the prototype, the fallback applies each 
schema change to the schema it keeps, as `AbstractJdbcSinkWriter` does, and a 
unit test covers a column added `FIRST`, a rename of the key column and a 
dropped column.
   
   Two things would follow in separate PRs, and I can do them:
   
   1. The Jdbc gaps: the `BYTES` key and the unique-key table above, and the 
key position after a schema change.
   2. Sinks whose key is a sink option and can differ from the upstream key 
(for example `primary_keys` in Elasticsearch, `iceberg.table.primary-keys`, 
`primary-key` in MongoDB). Those sinks should declare their own key through 
`primaryKey()`.
   
   Is the schema fallback in `MultiTableSinkWriter` the direction you want, or 
would you prefer that each sink declares its key? And for a table with no key 
at all, should the route stay random, or should all its rows go to one queue?
   
   ### SeaTunnel Version
   
   dev (535d4237dd)
   
   ### SeaTunnel Config
   
   ```conf
   env {
     parallelism = 1
     job.mode = "BATCH"
   }
   
   source {
     FakeSource {
       schema = {
         fields {
           id = int
           val = string
         }
         primaryKey {
           name = "pk"
           columnNames = [id]
         }
       }
       # 1000 keys in this pattern: even ids are deleted, odd ids stay with v2
       rows = [
         { kind = INSERT, fields = [0, "v1"] },
         { kind = UPDATE_BEFORE, fields = [0, "v1"] },
         { kind = UPDATE_AFTER, fields = [0, "v2"] },
         { kind = DELETE, fields = [0, "v2"] },
         { kind = INSERT, fields = [1, "v1"] },
         { kind = UPDATE_BEFORE, fields = [1, "v1"] },
         { kind = UPDATE_AFTER, fields = [1, "v2"] }
       ]
     }
   }
   
   sink {
     Iceberg {
       catalog_name = "probe"
       iceberg.catalog.config = {
         type = "hadoop"
         warehouse = "file:///tmp/seatunnel/iceberg-replica/"
       }
       namespace = "ns"
       table = "replica_2"
       iceberg.table.primary-keys = "id"
       multi_table_sink_replica = 2
     }
   }
   ```
   
   ### Running Command
   
   ```shell
   bin/seatunnel.sh --config job.conf -m local
   ```
   
   ### Error Exception
   
   ```log
   No error. The job ends with:
   Total Read Count          :                3500
   Total Write Count         :                3500
   Total Failed Count        :                   0
   ```
   
   ### Zeta or Flink or Spark Version
   
   Zeta (dev). The routing code is engine independent.
   
   ### Java or Scala Version
   
   JDK 17
   
   ### Screenshots
   
   _No response_
   
   ### Are you willing to submit PR?
   
   - [X] Yes I am willing to submit a PR!
   
   ### 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]

Reply via email to