[operate] Schema migration failing (Code: 47, UNKNOWN_IDENTIFIER) despite clean setup and low concurrency

[goerliu]

Description

I am experiencing consistent failures when running the signoz-schema-migrator tool. The migration fails during an ALTER TABLE statement because it cannot find the timestamp column in the signoz_traces.span_attributes_keys table.

Despite completely rebuilding our ClickHouse and ZooKeeper clusters and limiting ClickHouse DDL concurrency to 1, the issue persists.

Error Logs

Error: code: 47, message: There was an error on [10.10.140.73:10000]: Code: 47. DB::Exception: Missing columns: 'timestamp' while processing: 'timestamp + toIntervalDay(15)', required columns: 'timestamp', available columns: 'isColumn' 'dataType' 'tagType' 'tagKey'. (UNKNOWN_IDENTIFIER) (version 25.5.6.14 (official build))

Environment

• ClickHouse Version: 25.5.6.14 (official build)
• ZooKeeper Version: 3.9
• SigNoz Migrator Version: 0.144.3 and 0.144.0 (both tested)
• Deployment Method: Tested with both Helm chart migrator and binary migrator.

Steps Taken to Resolve

1. Rebuilt Infrastructure: Completely wiped and rebuilt the ClickHouse and ZooKeeper clusters from scratch to ensure no old metadata existed.
2. Limited Concurrency: Set ClickHouse DDL concurrency to 1 to reduce ZK load:

<max_distributed_ddl_queries>1</max_distributed_ddl_queries>

Running Command

bin/signoz-schema-migrator sync --dsn="tcp://<clickhouse_host>:10000?password=xxxx" --replication=true --cluster-name=singoz_cluster --up

Questions

1. Where exactly is this mismatch occurring between the migrator's expected schema and the actual schema being created?
2. How can we resolve this failure?
3. Could you provide the raw SQL statements required to manually create the signoz_traces.span_attributes_keys table (distributed and local) on a clustered setup? If we can create it manually with the timestamp column, we might be able to bypass this step.

[welcome]

Thanks for opening this issue. A team member should give feedback soon. In the meantime, feel free to check out the contributing guidelines.