Go programming language: Connecting to clickhouse is a major pain

[gagangoku]

Hi, today I spent more than 2 hours just trying to connect to clickhouse cloud, but it kept throwing all sorts of errors. Can someone please make which ports to connect to a little clear.

Cloud connection issues

On clickhouse cloud, the basic code in Connect section itself doesn't work:

package main

import (
  "crypto/tls"
  "fmt"
  "github.com/ClickHouse/clickhouse-go/v2"
)

func main() {
  conn := clickhouse.OpenDB(&clickhouse.Options{
    Addr: []string{"xxx.us-east-1.aws.clickhouse.cloud:8443"},
    Auth: clickhouse.Auth{
      Username: "default",
      Password: "<password>",
    },
    TLS: &tls.Config{InsecureSkipVerify: true},
  })
  row := conn.QueryRow("SELECT 1")
  var col uint8
  if err := row.Scan(&col); err != nil {
    fmt.Printf("An error while reading the data: %s", err)
  } else {
    fmt.Printf("Result: %d", col)
  }
}

This throws the error:

An error while reading the data: [handshake] unexpected packet [72] from server

After a lot of trial and error, changed the port to 9440 and it worked !

Local issues

But when I run clickhouse locally using

docker run -d -p19000:9000 -p18123:8123 -p19440:9440 --ulimit nofile=262144:262144 clickhouse/clickhouse-server

and try to connect using:

    username := ""
    password := ""
    conn, err := ConnectToClickhouse("localhost", "19440", username, password, "")
    if err != nil {
        t.Fatalf("failed to connect to clickhouse: %s", err)
    }

func ConnectToClickhouse(host, port, username, password, database string) (*sql.DB, error) {
    conn := clickhouse.OpenDB(&clickhouse.Options{
        Addr: []string{fmt.Sprintf("%s:%s", host, port)},
        Auth: clickhouse.Auth{
            Username: username,
            Password: password,
            Database: database,
        },
        Debug: false,
        TLS:   &tls.Config{InsecureSkipVerify: true},
    })
    err := conn.Ping()
    if err != nil {
        return nil, fmt.Errorf("failed to connect to clickhouse: %s", err)
    }
    return conn, nil
}

it throws the following error:

failed to connect to clickhouse: failed to connect to clickhouse: read tcp [::1]:52012->[::1]:19440: read: connection reset by peer

But the following works:

    address := "tcp://localhost:19000/"
    db, err := sql.Open("clickhouse", address)
    if err != nil {
        t.Fatalf("failed to connect to clickhouse: %s", err)
    }
    err = db.Ping()
    if err != nil {
        t.Fatalf("failed to ping clickhouse: %s", err)
    }

Why is connecting to clickhouse so confusing ?

Btw, I'm using Macbook pro.

[jkaflik]

Hi @gagangoku

Cloud connection issues

The code snippet provided in Cloud UI needs to be corrected. It assumes you use the default TCP protocol, but the port provided (8443) is an HTTPS port. 9443 is a port for secure TCP. I will follow up on this in an internal issue.

Local issues

This is specifically related to whether clickhouse-server correctly exposes a secure TCP port. By default it does not expose 9440 port. You can refer to the docs page: https://clickhouse.com/docs/en/guides/sre/configuring-ssl

---

The snippet provided in Cloud UI is helping to onboard to Cloud specifically and it will not cover all ClickHouse use cases.

[gagangoku]

Setting TLS config as nil and talking to 9000 port instead also works.

[ytigiev]

I also can't connect to clickhouse through library Port: '8123'

Error connecting to database: tls: first record does not look like a TLS handshake

In the same time dbeaver connects to the server and port 8123 and works correctly

func ConnectDb(dbHost string, dbPort int, dbName string, dbUser string, dbPass string) (*clickhouse.Conn, error) {
    var (
        ctx       = context.Background()
        conn, err = clickhouse.Open(&clickhouse.Options{
            Addr: []string{dbHost + ":" + strconv.Itoa(dbPort)},
            Auth: clickhouse.Auth{
                Database: dbName,
                Username: dbUser,
                Password: dbPass,
            },
            ClientInfo: clickhouse.ClientInfo{
                Products: []struct {
                    Name    string
                    Version string
                }{
                    {Name: "ClickHouse-Importer", Version: "0.1"},
                },
            },

            Debugf: func(format string, v ...interface{}) {
                fmt.Printf(format, v)
            },
            TLS: nil,
        })
    )

    if err != nil {
        return nil, err
    }

    if err := conn.Ping(ctx); err != nil {
        if exception, ok := err.(*clickhouse.Exception); ok {
            fmt.Printf("Exception [%d] %s \n%s\n", exception.Code, exception.Message, exception.StackTrace)
        }
        return nil, err
    }
    return &conn, nil
}

[jkaflik]

@ytigiev 8123 (by default) is HTTP protocol. You configured your client to use ClickHouse native TCP protocol. Either yo use 9000 (native) or set Protocol: clickhouse.HTTP.

[ytigiev]

Apologies, it was my mistake. I resolved the issue by opening port 9000 on the server's firewall.