Migrate: 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;

1. I ran migrate with the following options:

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

1. 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!