← All posts

A migration job that was a coin toss: ClickHouse kept the schema version per replica

SELECT from alembic_version returned a different revision depending on which node answered. The version table had never been replicated, and its creation date predicted which environments were affected.

The bug report was short: SELECT * FROM alembic_version on a staging environment returned different revisions depending on which ClickHouse node answered. The reporter was worried that the migration job would one day land on a lagging node and replay migrations that had already run.

The migration job itself behaved like a coin. Some runs were green. Others failed with Can't locate revision identified by '<rev>'.

Why a version table can live per node

In ClickHouse, a table is replicated only if it uses a Replicated* engine and was created with ON CLUSTER. Alembic does not create its version table through a migration. In this stack, clickhouse-sqlalchemy patches it, and the patch picks the engine:

if 'cluster' in kwargs:
    version.engine = engines.ReplicatedReplacingMergeTree(...)   # ON CLUSTER, replicated
else:
    version.engine = engines.ReplacingMergeTree(...)             # local, one per node

The cluster argument was added to our migration environment at some point. Alembic never migrates its own version table, so the patch only affects CREATE. Every database created before that change still had a separate local version table on each replica, and it would never fix itself.

The migrator connects through a regular Kubernetes Service that load-balances across replicas. Each run lands on a random node, reads that node’s version, applies migrations from there and writes the result back to the same node. The nodes drift apart, and every run makes it worse.

The creation date predicted the engine

One detail made the diagnosis quick. Alembic updates the version with ALTER TABLE ... UPDATE version_num. The dt column is the version column of ReplacingMergeTree, and that mutation does not touch it. So dt in the version row is the date of the first INSERT, which means the date the table was created.

Across four environments, the rule held exactly: one database created before the patch had a local engine and different versions; three created after it had a replicated engine and one version. Only the oldest environment was sick.

Read every replica, never the Service

A query through the Service shows you one random node, and the whole bug hid behind that. The diagnosis has to use clusterAllReplicas:

SELECT hostName() AS host, engine
FROM clusterAllReplicas('default', system.tables)
WHERE database = '<db>' AND name = 'alembic_version';

SELECT hostName() AS host, version_num, dt
FROM clusterAllReplicas('default', <db>.alembic_version);

Before touching the version, I checked that the actual schema matched on all replicas, with a fingerprint over system.columns:

SELECT hostName() AS host, count() AS cols,
       sum(cityHash64(table, name, type)) AS fingerprint
FROM clusterAllReplicas('default', system.columns)
WHERE database = '<db>' GROUP BY host;

1,147 columns and the same hash on every node. The schema was identical. Only the version row was lying.

There was one more surprise. The lagging replica held a revision that did not exist anywhere in the repository history. Alembic cannot build a path from an unknown revision, so it failed before running any DDL. That was the lucky case. A valid old revision would have made alembic silently replay migrations, and some of them recreate materialized views non-atomically.

The repair

  1. Create a new ReplicatedReplacingMergeTree version table ON CLUSTER, with the same columns.
  2. Insert the head revision once, without ON CLUSTER. Replication copies it.
  3. Wait until the row is visible on every replica. Right after the insert it showed up on one node only. That is asynchronous replication lag, measured in seconds, not a broken Keeper path.
  4. EXCHANGE TABLES the old and new tables ON CLUSTER. The database must use the Atomic engine.

The old local table stays under the swapped name. It is the only rollback: run the same EXCHANGE again. I would not drop it before two green deployments. The migration job then ran green twice in a row.

Takeaways

  • Check migration state on every replica. A load-balanced connection shows a random node and hides the drift.
  • A green migration job is not proof that you are at head while the version table is local.
  • Libraries that create tables for you are not migrated with your migrations. Fixing the code fixes new environments only. Old ones need a one-off repair.
  • Add a preflight that fails the job before upgrade head if the version table engine is not Replicated* or replicas disagree on the version.