Hello! Today, I ran into an interesting challenge while working on schema migrations for a new ClickHouse Cloud service using golang-migrate. Let me walk you through the issue and how I resolved it.

The Problem

While running migrations with the following command:

migrate -database "clickhouse://[redacted].clickhouse.cloud:9440?username=default&password=pass&database=default&x-migrations-table-engine=MergeTree" -path src/clickhouse/migrations up

I encountered this error:

error: driver: bad connection in line 0: SHOW TABLES FROM "default" LIKE 'schema_migrations'

Confident in my connection details, I tested the connection using clickhouse-client:

docker run --rm -it clickhouse/clickhouse-server:24 clickhouse-client --host [redacted].clickhouse.cloud --port 9440 --password

And it worked perfectly, proving the host, port, username, and password were correct.

ClickHouse Database directory appears to contain a database; Skipping initialization
ClickHouse client version 24.1.5.6 (official build).
Password for user (default): 
Connecting to [redacted].clickhouse.cloud:9440 as user default.
Connected to ClickHouse server version 24.0.2.

ClickHouse server version is older than ClickHouse client. It may indicate that the server is out of date and can be upgraded.

Warnings:
 * Obsolete setting ['use_mysql_types_in_show_columns'] is changed. Please check 'SELECT * FROM system.settings WHERE changed AND is_obsolete' and read the changelog at https://github.com/ClickHouse/ClickHouse/blob/master/CHANGELOG.md

clickhouse-cloud :) SELECT 1;

SELECT 1

Query id: 674e1446-61a4-460b-8e3e-7d14da0cac3c

┌─1─┐
│ 1 │
└───┘

1 row in set. Elapsed: 0.001 sec. 

clickhouse-cloud :) Bye.

The Root Cause

After some digging and trial and error, I realized the issue was quite simple: I had initially forgotten to include secure=true in my migration command. This flag is crucial for ensuring the connection is established securely, especially when dealing with cloud services that require encrypted connections.

The Solution

Once I added secure=true to the migration command:

migrate -database "clickhouse://[redacted].clickhouse.cloud:9440?secure=true&username=default&password=pass&database=default&x-migrations-table-engine=MergeTree" -path src/clickhouse/migrations up

The migrations ran smoothly without any errors:

1/u create_ethereum_uniswap_v2_PairCreated_table (500.720042ms)
2/u create_jobs_table (1.122497042s)
...
12/u create_ethereum_uniswap_v2_Sync_table (5.74034575s)

Takeaways

It's fascinating how a small oversight like missing secure=true can lead to hours of troubleshooting. Always double-check your connection strings and flags when working with database migrations, especially in cloud environments where security settings like SSL/TLS encryption are mandatory.

I hope this helps anyone else facing similar issues.