ClickHouse MySQL protocol does not work with Golang MySQL driver

[olegkv]

I use MySQL Go driver https://github.com/go-sql-driver/mysql to connect to ClickHouse.Cloud using MySQL port and protocol. I get error: "[mysql] connection.go:49: unexpected EOF invalid connection" So, I am not able to connect and run any query.

The code I use is this: cfg := mysql.Config{ User: mysql_user, Passwd: pwd, Net: "tcp", Addr: host, DBName: database, AllowNativePasswords: true, Params: map[string]string{"parseTime": "True"}, } connString := cfg.FormatDSN() conn, err := sql.Open("mysql", connString) conn.QueryRow("select 1")

My understanding is that ClickHouse should look like MySQL for applications connecting using Go driver, that's the point of the feature, so that existing apps can be re-pointed to ClickHouse from MySQL.

[nikitamikhaylov]

Please provide the full reproducer (the whole Go program) and step-by-step guidance how to get this error. This will simplify the work for developers and increase chances for it to be fixed quickly.

[olegkv]

ch-test.zip attached sample Go program Just run it in debugger and see the error when query "select 1" is run

[slvrtrn]

The MySQL interface is mainly for specific tools that don't have native ClickHouse support yet. We also have the official ClickHouse Go driver available (clickhouse-go).

In any case, if you grab the Let's Encrypt root cert, the following will work:

package main

import (
    "context"
    "crypto/tls"
    "crypto/x509"
    "database/sql"
    "fmt"
    "log"
    "os"

    "github.com/go-sql-driver/mysql"
)
func main() {
    ctx := context.Background()

    rootCertPool := x509.NewCertPool()
    pem, err := os.ReadFile("./isrgrootx1.pem")
    if err != nil {
        panic(err)
    }
    if ok := rootCertPool.AppendCertsFromPEM(pem); !ok {
        log.Fatal("Failed to append PEM.")
    }
    err = mysql.RegisterTLSConfig("custom", &tls.Config{
        ServerName: "<service>.clickhouse.cloud",
        RootCAs:    rootCertPool,
    })
    if err != nil {
        panic(err)
    }

    db, err := sql.Open("mysql", "<username>:<password>@tcp(<service>.clickhouse.cloud:3306)/default?tls=custom")
    if err != nil {
        panic(err)
    }

    conn, _ := db.Conn(ctx)
    var result string
    err = conn.QueryRowContext(ctx, `SELECT version() AS result`).Scan(&result)
    if err != nil {
        panic(err)
    }
    fmt.Printf("result: %s\n", result)
}

Which prints the correct result for my test instance:

result: 24.1.2.10900

Don't forget to allow the instance access for your IP address; otherwise, you might encounter the same EOF error again.

[olegkv]

Hi guys I thought that ClickHouse MySQL interface was intended to allow existing apps which use MySQL to use ClickHouse as drop-in replacement - just switch to new address, assuming that table structures are the same and SQL queries are compatible. That is not the case? ClickHouse documentation says: "ClickHouse supports the MySQL wire protocol. This allow tools that are MySQL-compatible to interact with ClickHouse seamlessly" I thought that means all apps which use MySQL? If that is not the case, please clarify. Also, another example, I cannot connect to ClickHouse's MySQL port using DBeaver in same way as I connect to real MySQL database - is that expected behavior? Thanks!

[slvrtrn]

DBeaver has native ClickHouse support via the official JDBC driver — why would you use the MySQL interface for that? The same is true for DataGrip.

Regarding the intended use case, our primary focus was on the (proprietary) BI tools that don't have a native plugin or driver, such as:

• Looker Studio
• Tableau Online
• QuickSight

When possible, using native drivers is always preferred.