MySQL replication: every other transaction stays pending in SHOW READYSET STATUS

[altmannmarcelo]

Problem

Every other MySQL transaction remains in a [pending] state in SHOW READYSET STATUS, even though the data is fully applied. This is a cosmetic/reporting issue — there is no delay in applying events, rows are committed to noria immediately. The problem is only that the persisted replication offset still shows a [pending] marker.

Observed behavior

-- Insert 1: finalized correctly
readyset> insert into t1 values (3);
readyset> SHOW READYSET STATUS;
Maximum Replication Offset: ...a2fc8caa:1-17     -- no pending, good

-- Insert 2: stuck as pending (but data IS applied)
readyset> insert into t1 values (4);
readyset> SHOW READYSET STATUS;
Maximum Replication Offset: ...a2fc8caa:1-17 [pending: a2fc8caa:18@1]  -- cosmetic

The pattern repeats: odd transactions finalize, even ones stay pending until the next transaction arrives.

Root Cause

The issue is in connector.rs in the XID_EVENT handler:

EventType::XID_EVENT => {
    self.finalize_current_gtid();          // finalizes in connector memory
    if self.report_position_elapsed() || is_last {
        return Ok((vec![ReplicationAction::LogPosition], self.current_offset()));
    }
    continue;  // <-- finalized offset is NEVER persisted to noria
}

The flow for each transaction is:

1. WRITE_ROWS_EVENT → returns TableAction + offset with pending GTID. The rows are applied to noria immediately, and this offset (with pending) is stored per-table via SetReplicationOffset.
2. XID_EVENT → calls finalize_current_gtid() which promotes the pending GTID to committed in the connector's in-memory state only. But it only returns LogPosition (which would persist the finalized offset to noria) if report_position_elapsed() returns true (>10 seconds since last report).

When report_position_elapsed() returns false, the finalized offset is never persisted. The table in noria keeps the old offset with the pending GTID, even though the data itself was fully applied at step 1.

Why "every other"

• last_reported_pos_ts is initialized to Instant::now() - MAX_POSITION_TIME (line 430), so the first call to report_position_elapsed() always returns true and resets the timer.
• The second transaction's XID arrives within <10s, so report_position_elapsed() returns false → finalized offset is not persisted.
• After >10s passes (user does another insert), the timer elapses again → first XID reports, second doesn't, etc.

The same issue exists in the COMMIT query handler (process_event_query):

Ok(SqlQuery::Commit(_)) => {
    self.finalize_current_gtid();
    Err(ReadySetError::SkipEvent)  // <-- also never persists
}

Constraints on the fix

The naive fix of "always return LogPosition from XID" is problematic because handle_log_position updates the replication offset for all tables, not just the one modified in this transaction.

Possible approaches

1. Buffer row actions until XID: Don't return from next_action_inner on WRITE_ROWS_EVENT. Instead buffer the TableAction and only return it when XID_EVENT arrives, using the finalized offset. Pro: offset is always clean. Con: changes streaming semantics, rows are applied later.
2. Targeted offset update from XID: Track which table(s) were modified in the current transaction and return a targeted offset update that only updates the affected tables.
3. Return LogPosition from XID only when a pending GTID was actually finalized: Less frequent than "always return", but still updates all tables.
4. Don't include pending GTID in the offset stored per-table: Only store the committed ranges in SetReplicationOffset. Keep the pending GTID only in the connector's state for crash recovery.

Reproduction

1. Start Readyset with MySQL upstream (GTID mode)
2. Create a table and insert two rows in quick succession (<10s apart)
3. Check SHOW READYSET STATUS after each insert
4. First insert shows finalized offset, second shows [pending: ...]