airbytehq / airbytehq/airbyte

[source-mysql] Removing the configured primary key from the catalog: connector still uses the old PK

Ouverte
#84,934 2 commentaires 0 réactions 1 personne assignée Réclamée par @yardencarmeli Voir sur GitHub
area/connectors connectors/source/mysql needs-try-reproduce team/db-dw-sources type/bug
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.

Recevez les nouvelles issues par e-mail

Un résumé court des issues GitHub adaptées aux débutants.