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]