Can't execute migrations for Clickhouse

[obsh]

Describe the Bug I'm receiving "driver: bad connection" each time trying to execute migrations for Clickhouse. The error appears after 3 minutes.

Steps to Reproduce

1. My migrations look like:

example/001_init.up.sql

CREATE TABLE test_1 (
    Date Date
) Engine=Memory;

example/001_init.down.sql

DROP TABLE IF EXISTS test_1;

2. I ran migrate with the following options:

migrate \
  -path=./example/ \
  -database "clickhouse://${CL_HOST}:8123?username=${CL_USER}&password=${CL_PASS}&database=default" \
  up

3. See error

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

Expected Behavior Migration executed.

Migrate Version v4.10.0 Obtained by running: migrate -version. Installed with brew.

Loaded Source Drivers godoc-vfs, gcs, file, s3, github, github-ee, gitlab, go-bindata Obtained by running: migrate -help

Loaded Database Drivers clickhouse, cockroach, firebirdsql, mongodb, crdb-postgres, postgresql, firebird, stub, spanner, sqlserver, cassandra, cockroachdb, mysql, postgres, redshift Obtained by running: migrate -help

Go Version go version go1.13.4 darwin/amd64 Obtained by running: go version

Stacktrace Please provide if available

Additional context Clickhouse version: ClickHouse server version 20.1.4 revision 54431

[mnvx]

Have same problem using v3 and v4.

m, err := migrate.New(
	"file:///path_to/db/migrations",
	"clickhouse://localhost:8123?x-multi-statement=true&database=fura")

After 180 seconds:

driver: bad connection in line 0: SHOW TABLES FROM fura LIKE 'schema_migrations'

[Vicheus]

Same thing when I'm trying to execute migrate \ -path=./my_migrations/ \ -database "clickhouse://${MY_DATABASE_HOST}:${MY_DATABASE_PORT}?username=${MY_DATABASE_USER}&password=${MY_DATABASE_PASSWORD}&database=store" \ up Error is shown error: driver: bad connection in line 0: SHOW TABLES FROM store LIKE 'schema_migrations'

[dbaida]

Hi there, I got the same error. When I execute:

migrate -path=./migrations/ -database "clickhouse://${DB_HOST}:${DB_PORT}?username=${DB_USER}&password=${DB_PASSWORD}&database=main" up

the following error occurs:

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

[qllb]

Got the same error message

error: driver: bad connection in line 0:

[nejtr0n]

I've got same issue

2020-05-27T09:03:34.536134803Z error: [hello] unexpected packet [72] from server in line 0: SHOW TABLES FROM dev_igwm LIKE 'statisticsMigrations'

migrate command

/migrate -source gitlab://u:[email protected]/1/database/clickhouse#v0.0.1 -database clickhouse://dev-clickhouse.dev-clickhouse-operator:8123?username=clickhouse&password=pass&database=dev_db&x-migrations-table=statisticsMigrations up

[dhui]

I'm able to reproduce the issue but don't know how to fix it.

[finnan444]

Guys, you are facing this problem, because of port 8123. It is used only for HTTP connections. Port 9000 is for clickhouse-client program. Try connect using 9000!

This issue should be closed

[dhui]

@finnan444 The tests are already using port 9000 but are failing

[nfisher]

Not sure about the tests but I ran into the same issue and applied what @finnan444 mentioned prior to seeing this issue. It resolves the issue with bad connections. Came here looking for a way to migrate every shard in the cluster with one execution of migrate.

[fzyzcjy]

@finnan444 This fixes my problem!

[andyrichardson]

I'm also seeing this issue on an Altinity hosted cluster.

Using the clickhouse client (below) works fine

> docker run clickhouse/clickhouse-client -h myhost --port 9440 -s --user admin --password password

However using the same options with migrate doesn't work

> migrate -verbose -path ../metrics/staging/migrations -database "clickhouse://myhost:9440?username=admin&password=password&database=default" up 1
error: dial tcp: i/o timeout in line 0: SHOW TABLES FROM default LIKE 'schema_migrations'

(tried with + without URL encoding on the password)

[AntonFriberg]

@andyrichardson you need secure=true if using the 9440 TLS encrypted port. This is why you need -s in the docker run command. It is short for --secure.

[artur-shafikov]

2022/11/14 17:42:07 error: [hello] unexpected packet [21] from server in line 0: SHOW TABLES FROM database LIKE 'schema_migrations'
make: *** [Makefile:47: mig] Error 1

Same error, I think that's because that the production db is secured with SSL, but I don't see any SSL settings in CLI of migrate tool...

[AntonFriberg]

@artur-shafikov you can add secure=true to URL params like this clickhouse://localhost:9440?username=admin&password=pass&secure=true

[rutaka-n]

Hello, everyone! 👋 I have the same issue with the connection to ClickHouse. I am trying to run migration from my service and it fails with the same error: {"error": "panic: code: 115, message: Unknown setting database in line 0: SHOW TABLES FROM talon LIKE 'schema_migrations'\

The issue is that clickhouse changed the format of the connection string URL, see this issue: https://github.com/ClickHouse/clickhouse-go/issues/757

So to fix this issue just use a new format.

I would be happy to fix the issue and provide the PR. I think the best approach is to bump up the clickhouse-go and use a new format of the connection string. Please let me know what you think 🙏