[BUG-DATA-5] Non-atomic offset update — crash between DELETE and INSERT loses offset permanently

[minguyen9988]

Summary

DebeziumJdbcStorageOperations.updateDebeziumStorageStatus() first DELETEs the offset row, then INSERTs a new one. If the process crashes between the DELETE and the INSERT, the offset is permanently lost.

Affected Code

File: sink-connector-lightweight/src/main/java/com/altinity/clickhouse/debezium/embedded/ddl/parser/DebeziumJdbcStorageOperations.java

Lines 348-350 and 395-397:

// First: DELETE the existing offset
deleteOffsetStorageRow(conn, offsetKey, tableName);
// Then: INSERT the new offset  
insertOffsetStorageRow(conn, offsetKey, offsetValue, recordTimeStamp, tableName);

These two operations are NOT wrapped in a transaction.

Impact

If the JVM crashes, is killed, or loses the ClickHouse connection between the DELETE and INSERT:

1. The offset row is deleted but the new one is never inserted
2. On restart, the connector has no stored offset
3. Depending on snapshot.mode, the connector either:
   • Re-processes the entire binlog from the beginning (massive duplicates)
   • Starts from the current binlog position (data loss for the gap)

Fix

Since the offset table uses ReplacingMergeTree, simply INSERT the new row without deleting first. ClickHouse's ReplacingMergeTree engine will keep only the latest version after merges. This makes the operation atomic (single INSERT) and eliminates the crash window.

Alternatively, wrap the DELETE and INSERT in a transaction if ClickHouse version supports it.

Severity

HIGH — Data loss on crash (CWE-362)