[source-mysql] Removing the configured primary key from the catalog: connector still uses the old PK
- Langage dominant
- Python
- Étoiles
- 22.1k
- Forks
- 5.3k
- Métriques de merge des PR
- Métriques de PR en attente
Description
## What
Reported by the DB Sources team: on **source-mysql**, when a previously configured primary key is **removed from the configured catalog** for a stream, the connector appears to keep using the old primary key on subsequent syncs instead of honoring the new (PK-less) catalog.
Needs investigation: we should confirm the exact behavior (silent use of the stale PK vs. a crash) and decide what the connector should do when a saved state references a PK that is no longer configured.
## Expected
After the PK is removed from the catalog, the stream should either:
- read without PK-based resumable partitioning (non-resumable snapshot / cursor-only reads), or
- fail with a clear, actionable config error asking for a state reset,
rather than silently continuing to partition/checkpoint on the removed PK.
## Actual (reported)
The sync keeps reading/checkpointing using the previously configured PK.
## Where to look
`MySqlSourceJdbcPartitionFactory.create()` reads the PK from the catalog and then, on the warm-start paths, dereferences it without re-checking that it is still present:
- https://github.com/airbytehq/airbyte/blob/master/airbyte-integrations/connectors/source-mysql/src/main/kotlin/io/airbyte/integrations/source/mysql/MySqlSourceJdbcPartitionFactory.kt
```kotlin
val pkChosenFromCatalog: List = stream.configuredPrimaryKey ?: listOf()
// only FULL_REFRESH is guarded against an empty PK here
if (pkChosenFromCatalog.isEmpty() && stream.configuredSyncMode == ConfiguredSyncMode.FULL_REFRESH) { ... }
// CDC initial-snapshot resume:
val pkField = pkChosenFromCatalog.first() // no emptiness check
// cursor-based, still in PK phase:
val pkField = pkChosenFromCatalog.first() // no emptiness check
val pkLowerBound = stateValueToJsonNode(pkChosenFromCatalog[0], sv.pkValue)
```
The saved state itself carries the PK (`pkName` / `pkValue` in `MySqlSourceCdcInitialSnapshotStateValue` / `MySqlSourceJdbcStreamStateValue`), so a state written before the catalog change still describes a PK read phase. There is an existing in-code acknowledgement of this class of problem: *"Loading value from catalog. Note there could be unexpected behaviors if user updates their schema but did not reset their state."*
## Questions to answer
1. Repro: full refresh, incremental (cursor-based) mid-PK-phase, and CDC initial snapshot — which of these keep using the stale PK, and which throw?
2. Is the stale PK coming from the source (partitioning/checkpointing) or from the destination's dedup config, or both?
3. Should removing a PK force a state reset, be silently tolerated, or raise a config error? Same question for changing a PK to a different column.
4. Does the same behavior exist in the other bulk-CDK DB sources (postgres, mssql, oracle)? If so, the fix likely belongs in the shared CDK layer.
## Impact
Silent use of a removed PK can produce wrong partitioning/checkpointing and, in dedup setups, records keyed on a field the user explicitly deconfigured — with no error surfaced to the user.
---
[Written by Devin](https://app.devin.ai/sessions/15b98233cf89467eb133ee97c73edb9e) at Yarden's request.
---
**Internal Tracking:** https://github.com/airbytehq/oncall/issues/13364
Guide de contribution
Ouvrir le guide de contribution
Évaluation
Cette issue n'a pas encore été évaluée.