source-mysql: `MySqlSourceCdcPosition` completion check silently emits zero records when binlog numbering resets (Blue/Green, restore-to-new-cluster)

### Connector Name

source-mysql

### Connector Version

3.53.3

### What step the error happened?

None

### Relevant information

## Summary

`CdcPartitionsCreator` performs a completion check based on
`MySqlSourceCdcPosition.compareTo`, which is derived from the integer suffix
of the binlog file name. When a DB's binlog numbering is reset — as happens
after an AWS RDS Blue/Green promotion, a restore into a fresh cluster, or a
`RESET MASTER` — the saved state's file suffix from the defunct cluster can
numerically exceed the current server's file suffix. In that case,
`CdcPartitionsCreator` treats the sync as already-complete and returns
`emptyList()` from partition creation. The sync completes with `status:
completed`, `recordsSynced: 0`, and no error surfaced — a silent no-op.

This is dangerous because it masquerades as a healthy sync while dropping
weeks or months of updates on the floor.

## Environment

- `airbyte/source-mysql:3.53.3`
- Reproduced with `airbyte/destination-s3:1.9.8` (destination-agnostic)
- Source DB: AWS RDS for MySQL 8.4.9 (promoted from 8.0.42 via Blue/Green)
- GTID mode: enabled

## Reproduction

1. Run a MySQL CDC connection against an RDS cluster whose current binlog
name is `mysql-bin-changelog.NNNNNN` (numbering grows over time on RDS —
ours had reached the ~250 000s).
2. Promote the green side of a Blue/Green deployment (this creates a fresh
binlog sequence starting at `mysql-bin-changelog.000001`).
3. Repair the saved state's GTIDs so that `validate()` passes (see companion
issue on the Debezium NPE). Leave `file` and `pos` untouched — they still
point at the old cluster's binlog.
4. Trigger a sync. It "succeeds" with zero records read, zero bytes emitted,
and no error in the failure array. Log line:

```
Current position 'MySqlSourceCdcPosition(fileName=mysql-bin-changelog.251761,
position=388584)' equals or exceeds target position
'MySqlSourceCdcPosition(fileName=mysql-bin-changelog.000616, position=18830620)'.
```

5. The next sync will do the same thing indefinitely, silently.

## Root cause

**`MySqlSourceCdcPosition.kt`** (Airbyte source-mysql):

```kotlin
data class MySqlSourceCdcPosition(val fileName: String, val position: Long) :
PartiallyOrdered {

val fileExtension: Int
get() = Path(fileName).extension.toInt()

val cursorValue: Long
get() = (fileExtension.toLong() shl Int.SIZE_BITS) or position

override fun compareTo(other: MySqlSourceCdcPosition): Int =
cursorValue.compareTo(other.cursorValue)
}
```

**`CdcPartitionsCreator.kt`** (Airbyte CDK):

```kotlin
if (lowerBound.isGreaterOrEqual(upperBound)) {
log.info { "Current position '$lowerBound' equals or exceeds target position '$upperBound'." }
return emptyList()
}
```

The composite cursor built from `mysql-bin-changelog.251761` is
`(251761L shl 32) or 388584 = 1_081_161_691_890_888`. The composite cursor for
the current green target `mysql-bin-changelog.000616` is
`(616L shl 32) or 18830620 = 2_665_842_286_236`. Numerically the (stale)
lower bound is far greater than the upper bound, so the completion check
short-circuits.

The implicit assumption baked into `MySqlSourceCdcPosition.compareTo` is that
binlog file numbering monotonically increases within a single connector's
lifetime. That's true for a stable cluster but false whenever cluster
identity changes underneath the connector, which is precisely the scenario
GTID-based CDC exists to survive.

## Impact

- Cluster-identity-change events (Blue/Green promotion, restore-to-new
cluster, `RESET MASTER`, some managed replica-promotion flows) cause CDC
to silently drop all subsequent changes.
- The sync is reported as `status: completed` — no failure trace, nothing to
alert on, nothing to retry against. The only signal is "records emitted
went to 0 and stayed there" which most alerting stacks don't catch until
downstream data starts looking suspicious.
- Even after fixing the GTID validation (see companion issue), this second
check independently blocks recovery.

## Mitigation (what we did without upgrading the connector)

We rewrite `file` and `pos` in the saved state to point at the earliest
possible binlog on the green cluster, forcing the numeric cursor to be small
enough that `lowerBound < upperBound`:

```json
"file": "mysql-bin-changelog.000001",
"pos": 4
```

This is safe because in GTID mode `file`/`pos` in the saved offset are not
used for the actual binlog resume decision — Debezium hands its
`GtidSet` to the server via `COM_BINLOG_DUMP_GTID` and the server picks the
correct binlog to start streaming from. `file`/`pos` are only used by
Airbyte's `CdcPartitionsCreator` completion check. Once the sync writes its
own state, subsequent syncs use the connector's fresh, cluster-native
`file`/`pos` values and the workaround is no longer needed.

After this rewrite (combined with the GTID repair from the companion issue),
we recovered ~66 000 backlog transactions across two GTID UUIDs on the green
cluster with zero data gap.

## Requested fix

The completion check should not be able to override GTIDs when GTIDs are
present in the state. A few options in decreasing order of preference:

1. **Prefer GTID for the completion check when the saved state has GTIDs.**
Compare `savedGtidSet` to `availableGtidSet` (which the connector already
computes elsewhere) instead of the numeric `MySqlSourceCdcPosition` when
both are available. GTIDs are the source of truth in GTID mode.
2. **Detect a binlog reset and refuse to short-circuit.** If the saved
`file`'s numeric extension is greater than the target's, do not treat that
as "already past" — treat it as "state is from a different cluster
generation, proceed with a resume from GTID and let MySQL sort out the
binlog file." Alternately, raise a clear
`ConfigErrorException("saved binlog file name doesn't match current server
binlog sequence; likely cluster identity change (Blue/Green cutover,
restore, RESET MASTER) — reset the connection or patch state to advance")`
so the failure is visible.
3. **At minimum, log a warning when the completion check fires with a saved
file name that has no lineage relationship to the current server's file
name** (e.g., both have integer extensions but the saved one is
significantly higher). Do not silently emit zero records.

## Related

- Companion issue on the Debezium `MySqlGtidSet.UUIDSet.subtract` NPE, which
is the first failure mode users hit in the Blue/Green scenario. Once that's
fixed, this issue becomes the next blocker: https://github.com/airbytehq/airbyte/issues/85782

### Relevant log output

```shell

```

### Contribute

- [ ] Yes, I want to contribute