1don-ljy opened a new issue, #13280:
URL: https://github.com/apache/seatunnel/issues/13280

   ### Search before asking
   
   - [x] Searched existing issues for SQL Server backfill/stuck snapshots and 
`afterHandleLsn`; no matching report was found.
   
   ### What happened
   
   With SeaTunnel 3.0.0, SQL Server CDC can stall in **bounded snapshot 
backfill** when another CDC-enabled table advances the database-wide HIGH 
watermark, but the selected table has no changes in the LOW-HIGH interval.
   
   The snapshot query finishes, but its buffered records do not reach the sink 
because backfill never emits END. The job remains RUNNING and checkpoints 
continue completing. This was reproduced locally with ordinary integer-key 
tables and the standard Microsoft JDBC sink driver; no custom sink driver is 
required.
   
   Expected: after a valid bounded CDC interval has been queried and all 
results processed, backfill should complete even if the selected table has zero 
changes in that interval.
   
   #### Deterministic reproduction
   
   Use disposable test databases `source_db` and `target_db`, SQL Server Agent 
running, and a login with the required CDC permissions.
   
   1. In `source_db`, create these two tables, enable database CDC and table 
CDC on both, and insert the test rows. In `target_db`, create an empty `dbo.A` 
with the same schema.
   
      ```sql
      CREATE TABLE dbo.A (Id int NOT NULL PRIMARY KEY, Value int NOT NULL);
      CREATE TABLE dbo.Other (Id int NOT NULL PRIMARY KEY, Value int NOT NULL);
      INSERT dbo.A VALUES (1, 10), (2, 20), (3, 30);
      INSERT dbo.Other VALUES (1, 0);
      ```
   
   2. Commit an update to `source_db.dbo.Other` and wait for CDC to capture it, 
establishing a valid initial log position.
   3. In a separate connection to `source_db`, hold a test-only exclusive table 
lock using a read-only query:
   
      ```sql
      BEGIN TRAN;
      SELECT COUNT(*) FROM dbo.A WITH (TABLOCKX, HOLDLOCK);
      -- Leave this transaction open temporarily.
      ```
   
   4. Start the job below, capturing **only A**. Wait until the log records the 
snapshot LOW watermark and starts the snapshot SELECT on A, before `Finished 
exporting`.
   5. Through another connection, commit `UPDATE dbo.Other SET Value = Value + 
1 WHERE Id = 1;`. Wait until CDC has captured it and the database-wide real 
transaction LSN is greater than LOW. The latter can be observed with:
   
      ```sql
      SELECT MAX(start_lsn) AS latest_real_transaction_lsn
      FROM cdc.lsn_time_mapping
      WHERE tran_id <> 0x00;
      ```
   
   6. Execute `ROLLBACK;` in the lock-holding connection. Do not write to A.
   
   The lock is only a test device to make the LOW/HIGH timing deterministic. It 
is released before the stall is observed; an outstanding database lock is not 
the cause of the backfill wait.
   
   Actual result in the local reproduction:
   
   - A's three snapshot rows were exported; bounded backfill started with LOW < 
HIGH but did not finish.
   - The target stayed empty during a 12-second observation window while **four 
checkpoints completed**.
   - As a diagnostic experiment, updating one row of the disposable test table 
A then unblocked backfill and all three rows reached the target. This is not 
proposed as a production workaround.
   
   #### Code investigation (v3.0.0)
   
   `SqlServerStreamingChangeEventSource` calls `afterHandleLsn(partition, 
offsetContext.getOffset())` after querying changes. The bounded reader in 
`SqlServerTransactionLogFetchTask.TransactionLogSplitReadTask.afterHandleLsn` 
compares that row-event offset with `lsnSplit.getStopOffset()` before emitting 
END.
   
   - [Bounded reader completion 
check](https://github.com/apache/seatunnel/blob/v3.0.0/seatunnel-connectors-v2/connector-cdc/connector-cdc-sqlserver/src/main/java/org/apache/seatunnel/connectors/seatunnel/cdc/sqlserver/source/reader/fetch/transactionlog/SqlServerTransactionLogFetchTask.java#L130-L153)
   - [SQL Server change-query 
loop](https://github.com/apache/seatunnel/blob/v3.0.0/seatunnel-connectors-v2/connector-cdc/connector-cdc-sqlserver/src/main/java/io/debezium/connector/sqlserver/SqlServerStreamingChangeEventSource.java)
   
   HIGH is a database-wide position. If only Other advances it, A's last actual 
event offset cannot reach HIGH, even though the CDC query has already covered 
that interval. An empty selected-table result must be distinguishable from a 
query that has not reached HIGH.
   
   #### Local experimental fix and validation
   
   A local experimental patch was tested which:
   
   - Caps each bounded query at the snapshot HIGH watermark.
   - Checks that the capture instances cover the requested interval.
   - Emits END at the original HIGH after the complete query results have been 
processed successfully, including an empty result; it does not fabricate a 
row-event offset.
   - Leaves ordinary unbounded streaming and checkpoint serialization unchanged.
   
   Targeted local validation passed:
   
   - Restore from a checkpoint made by the stalled stock connector, with the 
same job configuration: backfill completed without any write to A and the 
target matched the source.
   - A subsequent checkpoint restore followed by insert/update/delete 
operations: the test target converged.
   - Direct bounded-reader tests against real SQL Server CDC, with 
`max.iteration.transactions` set to both 0 and 1: CRUD events in the HIGH 
transaction were included, and later transactions were excluded.
   
   These are targeted tests, not comprehensive coverage of schema changes, 
capture-instance transitions, or all deployment configurations. The last test 
exercises the real bounded CDC reader, not a full snapshot-to-sink boundary 
audit. The patch is local, not an Apache release. This report does not claim 
that the current development branch or other releases have been reproduced.
   
   ### SeaTunnel Version
   
   3.0.0, stock `connector-cdc-sqlserver-3.0.0.jar` for the failing 
reproduction.
   
   ### SeaTunnel Config
   
   Sanitized local reproduction configuration. Replace the example credentials 
and local SQL Server port as appropriate. Both destination schema and table 
already exist.
   
   ```hocon
   env {
     parallelism = 1
     job.mode = "STREAMING"
     checkpoint.interval = 3000
   }
   source {
     SqlServer-CDC {
       plugin_output = "changes"
       url = 
"jdbc:sqlserver://localhost:1433;databaseName=source_db;encrypt=true;trustServerCertificate=true"
       username = "<test-login>"
       password = "<test-password>"
       database-names = ["source_db"]
       table-names = ["source_db.dbo.A"]
       startup.mode = "initial"
       exactly_once = true
       enable_concurrent_read = false
     }
   }
   sink {
     Jdbc {
       plugin_input = ["changes"]
       url = 
"jdbc:sqlserver://localhost:1433;databaseName=target_db;encrypt=true;trustServerCertificate=true"
       driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver"
       username = "<test-login>"
       password = "<test-password>"
       database = "target_db"
       table = "dbo.A"
       primary_keys = ["Id"]
       generate_sink_sql = true
       enable_upsert = true
       schema_save_mode = "ERROR_WHEN_SCHEMA_NOT_EXIST"
       data_save_mode = "APPEND_DATA"
       batch_size = 100
       batch_interval_ms = 1000
       is_exactly_once = false
     }
   }
   ```
   
   ### Running Command
   
   Windows PowerShell, from the SeaTunnel installation directory, with its 
usual Java/environment settings:
   
   ```powershell
   .\bin\seatunnel.cmd --config .\config\repro.conf -m local
   ```
   
   ### Error Exception
   
   There is no fatal exception. Relevant log sequence, with LSNs and 
identifiers omitted:
   
   ```text
   Finished exporting ... records
   start execute backfillReadTask, start offset : {commit_lsn=<LOW>}, stop 
offset : {commit_lsn=<HIGH>}
   ```
   
   `backfillReadTask execute end` is absent while the selected table remains 
idle. Checkpoints continue completing, but the split is not finished and 
source/sink record counters do not advance. In the failing test, CDC is 
enabled, its retained interval covers LOW/HIGH, and its database-wide capture 
progress has reached HIGH.
   
   ### Zeta or Flink or Spark Version
   
   SeaTunnel Zeta 3.0.0, local mode. Local database reproduction used SQL 
Server 2022 in a container, with the SeaTunnel client running on Windows.
   
   ### Java or Scala Version
   
   OpenJDK 11.
   
   ### Are you willing to submit PR?
   
   - [ ] 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