amacneil / amacneil/dbmate

Default ZooKeeper path for schema_migration table in a distributed (on_cluster) ClickHouse setup clashes with multiple databases

Aperta
#783 17 commenti 1 reazione 0 assegnatari Vedi su GitHub
bug
Lingua principale
Go
Stelle
7.4k
Fork
379
Merge medio
6m
PR unite (30g)
1

Descrizione

**Description**

The ClickHouse scheme for dbmate supports using the `on_cluster` parameter to indicate that cluster statements and replicated tables should be used for migrations.

However, the default ZooKeeper path for the `schema_migrations` table is `/clickhouse/tables/{cluster}/{table}` which does not include the database name which is being used.

This means that if a ClickHouse cluster has two databases (`db_a` and `db_b`), running a dbmate schema migration on `db_a` with default settings and only specifying the `on_cluster` parameter will create a `schema_migrations` table in that database and will associate that table with the ZK path `/clickhouse/tables/{cluster}/{table}` but then running a dbmate schema migration for `db_b` will use the exact same path and cause an error.

It's possible to work around this issue by specifying the `zoo_path` parameter in the connection URL but I would argue that this is probably a common enough thing to do that the default settings should cover it.

There are two possible solutions to this:

1. Change the default path to `/clickhouse/tables/{cluster}/{database}/{table}`
2. Change the default path to `/clickhouse/{cluster}/tables/{shard}/{uuid}`

The second solution seems to be something that modern documentation and practices seem to lean towards. The `{uuid}` macro will be universally unique for every table, so even though the table has the same name in two different databases, the UUID will be distinct. It has the downside of it being more difficult to decrypt what table a given path is referring to but as I said, it seems to be a quite standardized way of doing this in recent documentation.

I can probably whip up a PR for this if there is consensus on which format to use.

- Version: 2.32.0
- Database: ClickHouse
- Operating System: agnostic

Guida per i contributori

Nessuna guida per i contributori indicizzata per questo repository

Valutazione

Questa issue non è ancora stata valutata.

Ricevi le nuove issue nella tua casella

Un breve riepilogo di issue GitHub adatte ai principianti.