Clickhouse - [handshake] unexpected packet [72] from server

[khuefgorg]

Hi, I am new to benthos and I have been stuck with this error for several days, please help me.

I have tried to connect benthos to clickhouse database and insert some string to the database but kept receiving this error "Failed to send message to sql_insert: [handshake] unexpected packet [72] from server"

I am using docker-compose to setup the clickhouse server and benthos. This is my docker-compose.yml file:

version: "3.8"

x-logging: &default-logging
  options:
    max-size: "100m"
    max-file: "5"
  driver: json-file

services:
  benthos:
    image: jeffail/benthos:4.24
    logging: *default-logging
    restart: unless-stopped
    command: ["-c", "/etc/benthos/config.yaml"]
    user: 1000:1000
    ports:
      - 4195:4195
    volumes:
      - ./var:/data
      - ./etc:/etc/benthos:ro
      - /etc/passwd:/etc/passwd:ro
      - /etc/group:/etc/group:ro
    networks:
      - https-proxy

  clickhouse:
    image: clickhouse/clickhouse-server:23.9.5.29-alpine
    ports:
      - 8123:8123
      - 9004:9004
      - 9005:9005
    volumes:
      - ./var/db:/var/lib/clickhouse
      - ./etc/clickhouse-server/config.d/my_config.xml:/etc/clickhouse-server/config.d/my_config.xml:ro
      - ./etc/clickhouse-server/users.d/my_users.xml:/etc/clickhouse-server/users.d/my_user.xml:ro

    environment:
      - ALLOW_EMPTY_PASSWORD=yes
    cap_add:
      - SYS_NICE
      - NET_ADMIN
      - IPC_LOCK

    ulimits:
      nofile:
        soft: 65536
        hard: 65536

    networks:
      - https-proxy
      - ch_ntw

networks:
  https-proxy:
    external: true
    name: https-proxy

  ch_ntw:
    driver:
      bridge

My benthos file:

# docker run --rm -v /path/to/your/config.yaml:/benthos.yaml jeffail/benthos
http:
  enabled: true
  address: 0.0.0.0:4195
  root_path: /

logger:
  level: TRACE
  format: json
  add_timestamp: true
  static_fields:
    "@service": benthos

input:
  label: in_sapo_order
  http_server:
    address: ""
    path: /sapo
    allowed_verbs:
      - POST
      - GET

output:
 sql_insert:
    driver: clickhouse
    dsn: clickhouse://default:rOdENuCTIoly@clickhouse:8123
    table: sapokey
    columns: [key]
    args_mapping: |
      root = ["123"]

Finally, I use postman to send POST request to the http_server some raw JSON like {"key": "123"}

This is my log file:

khue@khue-Cyborg-15-A12VE:/ext4data1/john-sapo/khue sapo/clickhouse-benthos$ docker compose up
[+] Running 2/0
 ✔ Container clickhouse-benthos-benthos-1     Recreated                                                                 0.1s 
 ✔ Container clickhouse-benthos-clickhouse-1  Recreated                                                                 0.1s 
Attaching to clickhouse-benthos-benthos-1, clickhouse-benthos-clickhouse-1
clickhouse-benthos-benthos-1     | {"@service":"benthos","benthos_version":"v4.24.0","level":"info","msg":"Running main config from specified file","path":"/etc/benthos/config.yaml","time":"2024-01-12T06:35:44Z"}
clickhouse-benthos-benthos-1     | {"@service":"benthos","level":"info","msg":"Listening for HTTP requests at: http://0.0.0.0:4195","time":"2024-01-12T06:35:44Z"}
clickhouse-benthos-benthos-1     | {"@service":"benthos","level":"info","msg":"Launching a benthos instance, use CTRL+C to close","time":"2024-01-12T06:35:44Z"}
clickhouse-benthos-clickhouse-1  | Processing configuration file '/etc/clickhouse-server/config.xml'.
clickhouse-benthos-clickhouse-1  | Merging configuration file '/etc/clickhouse-server/config.d/docker_related_config.xml'.
clickhouse-benthos-clickhouse-1  | Merging configuration file '/etc/clickhouse-server/config.d/my_config.xml'.
clickhouse-benthos-clickhouse-1  | Logging trace to /var/log/clickhouse-server/clickhouse-server.log
clickhouse-benthos-clickhouse-1  | Logging errors to /var/log/clickhouse-server/clickhouse-server.err.log
clickhouse-benthos-clickhouse-1  | Processing configuration file '/etc/clickhouse-server/config.xml'.
clickhouse-benthos-clickhouse-1  | Merging configuration file '/etc/clickhouse-server/config.d/docker_related_config.xml'.
clickhouse-benthos-clickhouse-1  | Merging configuration file '/etc/clickhouse-server/config.d/my_config.xml'.
clickhouse-benthos-clickhouse-1  | Saved preprocessed configuration to '/var/lib/clickhouse/preprocessed_configs/config.xml'.
clickhouse-benthos-clickhouse-1  | Processing configuration file '/etc/clickhouse-server/users.xml'.
clickhouse-benthos-clickhouse-1  | Merging configuration file '/etc/clickhouse-server/users.d/my_user.xml'.
clickhouse-benthos-clickhouse-1  | Saved preprocessed configuration to '/var/lib/clickhouse/preprocessed_configs/users.xml'.
clickhouse-benthos-benthos-1     | {"@service":"benthos","label":"in_sapo_order","level":"trace","msg":"Consumed 1 messages from POST to '/sapo'.","path":"root.input","time":"2024-01-12T06:36:34Z"}
clickhouse-benthos-benthos-1     | {"@service":"benthos","label":"","level":"trace","msg":"Attempting to write 1 messages to 'sql_insert'.","path":"root.output","time":"2024-01-12T06:36:34Z"}
clickhouse-benthos-benthos-1     | {"@service":"benthos","label":"","level":"error","msg":"Failed to send message to sql_insert: [handshake] unexpected packet [72] from server","path":"root.output","time":"2024-01-12T06:36:34Z"}

Thank you!

[mihaitodor]

Hey @khuefgorg, sorry for the late reply! My best guess is that the issue is related to your Clickhouse security settings. Since you haven't shared the config you're volume mounting into your Clickhouse container, I can't tell for sure what the issue is and not sure how to reproduce it. However, looking at the DSN you have in there (clickhouse://default:rOdENuCTIoly@clickhouse:8123), I think you'll need to play around with some of the supported parameters, namely, if you have SSL/TLS enabled, you'll need to set secure=true and maybe skip_verify=true if you wish to bypass invalid certificate errors for some quick testing (but not in production!). I guess there's no support for specifying custom certs, but hopefully you don't need that. See some extra details here.

[Jeffail]

Also I found this: ClickHouse/clickhouse-go#298, you need to be using the tcp endpoints rather than http.