airbytehq / airbytehq/airbyte

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

Aberta
#84,934 2 comentários 0 reações 1 responsável Reivindicada por @yardencarmeli Ver no GitHub
area/connectors connectors/source/mysql needs-try-reproduce team/db-dw-sources type/bug
Linguagem predominante
Python
Estrelas
22.1k
Forks
5.4k
Merge médio
5h
PRs com merge (30d)
671

Descrição

## 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

Guia de contribuição

Abrir o guia de contribuição

Direção de pesquisa

Start in source-mysql/src/main/kotlin/io/airbyte/integrations/source/mysql/MySqlSourceJdbcPartitionFactory.kt, focusing on create() and the warm-start paths that read configuredPrimaryKey. Reproduce full refresh, cursor-based incremental, and CDC initial-snapshot behavior after removing or changing the PK, then inspect MySqlSourceCdcInitialSnapshotStateValue and MySqlSourceJdbcStreamStateValue. Done means the stale-PK behavior is characterized across these modes and the intended handling, including whether other bulk-CDK database sources share it, is established.

Escrita pelo modelo de indexação a partir do texto da issue.

Avaliação

Stack de tecnologia
kotlin, mysql
Domínio
data-engineering, databases
Tipo de issue
Bug
Dificuldade
4/5
Tempo estimado
3-5 dias
Status de atividade
Ativa
Clareza
Razoavelmente clara
Facilidade para iniciantes
35/100

Receba novas issues na sua caixa de entrada

Um resumo curto de issues do GitHub para quem está começando.