Debugging an Airbyte 'All the Defined Primary Keys Are Null' Outage

An Outage Where the Error Never Changed

One of our clients runs a setup you have probably seen a hundred times: replicate a production database into a warehouse so the analytics team can work off fresh data without ever touching the live system. Concretely, a managed cloud MySQL 8.0 database feeds BigQuery through Airbyte, self-hosted on a single-node Kubernetes cluster, using change data capture (CDC) so the warehouse trails production by minutes rather than hours. It had run untouched for roughly a year, which is usually a good sign and occasionally a trap.

Over a weekend, the warehouse went stale. Dashboards froze at a fixed timestamp and stayed there. Every Airbyte sync was failing, and every failure carried the same message from the MySQL source connector:

All the defined primary keys are null, the primary keys are: id

That one line sent us down a rabbit hole for the better part of a day. This is the postmortem: what the error actually meant, the mistake we made in the middle of the incident that made things worse, and the two independent failures that were stacked on top of each other the whole time.

Why the Error Message Is a Trap

Airbyte will not write a record whose deduplication primary key is null. When it sees one, it refuses the record and aborts the entire sync. So the message reads exactly like a data problem: somewhere in your tables there is a row with a null id, go find it and fix it.

Except there was no such row. Every id in the database was an ordinary, non-null integer, exactly as it had been for a year. The key the connector was complaining about, id, was present and populated on every record. The message describes something the connector observed inside its own pipeline and invites you to read it as a statement about your data. It is not. That framing is what cost us most of a day, because it points at a very reasonable place to start looking, and it is the wrong place.

The Wrong Rabbit Hole, Ruling Out Suspects One at a Time

When an error points at the data, you check the data. We did that methodically, and each check came back clean, which turns out to be its own kind of signal.

First suspect: a table with a missing or malformed primary key. We audited the full live schema, every table and its key. It was clean. A handful of tables legitimately have no id column (they key on a different column, or they are junction tables), but those were either configured correctly or not selected for sync in the first place. No enabled stream had a primary-key mismatch. If a real row or table were the problem, this is exactly where it would have surfaced. It did not.

Second suspect: stale or corrupt connection state. Airbyte keeps per-connection metadata, including the CDC read position it resumes from, in its own database. A poisoned cursor or a torn checkpoint can produce strange failures. So we cleared that state, which forces Airbyte to abandon the incremental CDC read and take a fresh full snapshot instead. Same error, immediately.

Third suspect: the connection object itself, some accumulated misconfiguration on an entity that had existed for a year. So we built a brand-new connection from scratch. Fresh schema discovery, pointed at just two tiny tables, each with a clean single-column integer primary key, sharing nothing with the original connection except the same source and the same destination. It failed identically, in about three seconds, emitting zero records.

The Turn, Stop Debugging the Data and Diff the Binary

That third test is the one that mattered, and not because it worked. It mattered because it failed in exactly the same way as everything else.

Think about what the third test controlled for. Different tables. A different, freshly created connection. No shared state. A clean schema discovery. Two of the simplest possible streams, with textbook primary keys. Every variable we had been chasing was gone, and the error did not move a single byte. When a failure is invariant across every input you throw at it, when it is identical whether you feed it a year-old connection or a thirty-second-old one, the problem is not in the inputs. It is upstream of them.

So we stopped asking what was wrong with the records and asked a different question: what is different between the last sync that worked and the first sync that failed? Not in the data. In the machinery.

The Evidence, a One-Line Version Diff

Airbyte records, for every sync job it has ever run, the exact connector image version that ran it, and it keeps that history in its metadata database. That history is a changelog of your pipeline that you never had to write. Pulling the recent job history gave an answer that fit on one screen:

  status    |     created_at      |         source connector
------------+---------------------+------------------------------
 running    | (after our fix)     | source-mysql:3.11.21   <- works
 failed     | (during incident)   | source-mysql:3.53.0    <- fails
 succeeded  | (three days prior)  | source-mysql:3.11.21   <- last good sync
 succeeded  | (three days prior)  | source-mysql:3.11.21

Every sync that had ever succeeded ran source connector version 3.11.21. Every failing one ran 3.53.0. Same database, same configuration, same Airbyte platform. The only real variable was the connector version, and it was not a small bump. It was a jump of more than forty minor releases.

Owning the Mistake, That Upgrade Was Ours

Here is the part that is uncomfortable to write and important to say plainly. The jump to 3.53.0 was not something that happened to the client. We did it. It was made during the incident, before we had identified the real original cause, on logic that sounds sensible right up until it does not: the syncs are failing, we are several versions behind, a newer connector might be healthier. So we upgraded the connector in the middle of an outage.

Version 3.53.0 had been released only about a week earlier, and it does not work on this Airbyte platform version. So the upgrade did not help. It quietly added a second, independent failure on top of the first, and because that second failure looked identical no matter what we changed around it, it actively disguised the fact that anything else was wrong. We spent hours investigating a data problem that did not exist, in part because our own change had planted a symptom that mimicked one.

Two Failures, Stacked

The outage was never one problem. It was two, layered on top of each other.

Root cause one, the original outage: the Kubernetes cluster issues its own internal control-plane certificates with a one-year validity, and that year was up. When those certificates expired, the platform could no longer launch the pods it uses to run syncs, and everything stopped. This is a mundane, well-understood failure. Managed Kubernetes services rotate these certificates for you. A self-managed single-node cluster does not, unless someone set that up, and here no one had. The warehouse froze the moment the certificates lapsed.

Root cause two, self-inflicted, layered on top: the mid-incident connector upgrade from 3.11.21 to 3.53.0. On this platform version, Airbyte 1.7.1, the new connector fails every record with the null-primary-key error. This is the one that masked itself so well. Because it failed the same way regardless of table, connection, or state, it read as a data problem, and it hid the fact that the real trigger had been a boring expired certificate all along. Fix the certificate without noticing the connector, and syncs would still fail. Roll back the connector without fixing the certificate, and the pods would still not launch. Each fix on its own looked like it had not worked, which is exactly how a two-bug outage keeps you guessing.

Why the New Connector Fails, and Where We Stopped

We want to be precise about what we proved and what we did not. What we proved is narrow and solid: connector 3.11.21 is stable on this platform, connector 3.53.0 fails every record on this same platform, and nothing else about the environment changed between them. That was enough to restore service and to make the failure impossible to hit again.

What we did not do is fully root-cause the connector bug itself. We can offer an observation, clearly labelled as such and not as a conclusion: the 3.5x connector line introduced a new socket and protobuf based record-transport channel, a faster path for moving records out of the source, and there are similar reports of this exact error on the newer connector line. One public example is Airbyte issue 67711, which reports the same "All the defined primary keys are null" error on the same platform version, 1.7.1, with source-mysql 3.50.2, replicating MySQL CDC into BigQuery. That is the same failure class we hit, one connector patch away. It is suggestive, not proof. We did not confirm that the new transport path is the cause of our specific failure on 3.53.0, and we are not going to claim we did. The honest summary is that we restored replication and closed off the failure mode, not that we found and fixed a deep bug inside Airbyte.

The Fix and the Recovery

The fix followed the two causes, one repair each.

For the original outage, we renewed the cluster's expired control-plane certificates and restarted the control plane, which let the platform launch sync pods again. For the self-inflicted second failure, we rolled the source connector back to the known-good 3.11.21 and pinned it so it could not drift again. The first sync after the rollback completed immediately and emitted records, which is the moment you actually know you were right.

"status": "completed"   (2-table test: 39 records, BigQuery load jobs succeeded)
Records read: 1170000    (full resync, climbing)

From there we ran a full resync so BigQuery could catch up to production, and as a side benefit the fresh full load also cleared out some stale rows that had accumulated over time. The dashboards unfroze and started tracking real time again.

Prevention, Making Both Failures Impossible to Repeat Quietly

An outage you do not learn from is just a preview of the next one. Two changes make sure neither of these failures can recur silently.

• The cluster certificates now renew automatically on a schedule, so the annual expiry cannot creep up and halt the platform again. A predictable, dated failure has no business surprising anyone twice.
• The connector version is pinned, and upgrades are now deliberate rather than reflexive. The rule we adopted is simple: upgrade the platform before the connector, check the upstream issue tracker for the target version before installing it, test the new version on a throwaway connection, and never upgrade a component in the middle of an incident.

Both failure modes are now written up in a runbook, with the symptom, the diagnosis, and the fix, so the next time either one appears it is a ten-minute lookup instead of a multi-day hunt. That is the whole difference between an incident that teaches you something and one you merely survive.

Two Lessons Worth Keeping

Two things here generalise well beyond Airbyte.

Freeze changes during an incident. The single most damaging move we made was a reasonable-sounding upgrade at the worst possible time. Changing a component mid-incident does not just risk not helping, it stacks a second failure on top of the first and corrupts your ability to diagnose either one. When you are already down, the bar for changing anything should be very high, and "we might as well update while we are in here" does not clear it.

When an error is invariant across every input, stop debugging the data and diff the binaries. A byte-for-byte identical failure across different tables, cleared state, and a freshly built connection is not a coincidence, it is a signature. It tells you the problem lives upstream of everything you have been changing. The job history held the answer the entire time: same config, same data, different connector version. We would have arrived in an hour instead of a day if we had trusted the invariance sooner.

If your pipelines, clusters, or warehouses have quietly become load-bearing and nobody owns their failure modes, that gap is exactly what our SRE and monitoring and observability work is built to close, with the runbooks and the change discipline that turn a multi-day outage into a footnote. For the deeper difference between shipping fast and staying up while you do it, see our note on SRE versus DevOps.