Skip to content

Help

ClickHouse errors and how to fix them

Tested with:ClickHouse 26.8 on Databasezy · zb CLI 0.1

ClickHouse is available on paid plans. It is not on the Free plan, and it cannot be paused. You reach it two ways, both over TLS:

  • HTTPS on port 8443. Used by clickhouse-connect, @clickhouse/client, JDBC, Grafana and BI tools.
  • The native protocol on port 9440. Used by clickhouse-client --secure and native-mode drivers.

The user is app. Most errors below are ClickHouse’s own, shown with their code.

Connection refused or timeout on port 443, 8123 or 9000

Section titled “Connection refused or timeout on port 443, 8123 or 9000”

Cause: ClickHouse does not use the usual HTTP engine port. The HTTPS interface is on 8443 and the native protocol is on 9440, also in single-IP regions. ClickHouse’s in-cluster ports (8123, 9000) and plaintext ports are never exposed.

Fix: use https://<host>:8443 for HTTP clients. For the native protocol, use clickhouse-client --host <host> --port 9440 --secure --user app --ask-password. The Connect tab shows both.

The connection closes during the TLS handshake

Section titled “The connection closes during the TLS handshake”

Cause: the gateway closes the connection without a reply in these cases:

  • your address is not on the allow-list
  • the client connected by IP address, so no TLS server name was sent
  • the client did not use TLS (--secure is missing)
  • your address is temporarily blocked: by default, 10 failed logins from one address within a minute block it for 10 minutes

Fix: use the host name from Connect, keep TLS on, and add your egress CIDR under Network → IP allow-list. See IP allow-lists.

Code: 516 ... Authentication failed (AUTHENTICATION_FAILED)

Section titled “Code: 516 ... Authentication failed (AUTHENTICATION_FAILED)”

Cause: wrong password, or the credential was rotated. A rotation restarts the server hosts one at a time, and the old password stops working when each host restarts. There is no overlap. The rotation is confirmed only once every host has restarted with the new password; a password revealed before the confirmation can be refused by hosts that have not restarted yet.

Fix: reveal the current password with zb instances credentials reveal <instance_id> and update your clients. See Rotating credentials.

A JSON error on port 8443, or a garbled error on port 9440

Section titled “A JSON error on port 8443, or a garbled error on port 9440”

On HTTPS, the gateway answers problems itself with a JSON body such as {"error": "...", "message": "...", "retryable": ...}. On the native port, the same problem arrives as a plain text line that clickhouse-client cannot parse, so it may report an unexpected packet. Run curl against port 8443 to see the readable reason.

errorHTTP statusWhat it means
too_many_connections429The size’s connection limit is reached. Retry after the Retry-After header
rate_limited429More than 50 new connections in one second
upstream_unavailable502The server is not reachable right now, for example during a restart. Retry
suspended, quota_exceeded503Billing or a quota stopped the instance. See Spending caps and quota errors

Cause: each query may use up to half the size’s memory. Large GROUP BY and JOIN queries on small sizes hit this limit.

Fix: filter earlier, or set max_bytes_before_external_group_by so the aggregation spills to disk. You can also resize.

Code: 202 ... (TOO_MANY_SIMULTANEOUS_QUERIES)

Section titled “Code: 202 ... (TOO_MANY_SIMULTANEOUS_QUERIES)”

Cause: max_concurrent_queries follows the size (at least 20), and that many queries are already running.

Fix: queue queries in your application, cache dashboard results, or resize.

Cause: many small inserts create many small parts. The server merges them in the background and cannot keep up.

Fix: insert in batches of thousands of rows, or set async_insert=1 for many small writers.

A table is missing, or has fewer rows, on some connections (HA)

Section titled “A table is missing, or has fewer rows, on some connections (HA)”

Cause: with --ha, connections are spread over every replica. A table created without ON CLUSTER main, or with plain MergeTree, exists on one replica only.

Fix: recreate the table with ON CLUSTER main and a ReplicatedMergeTree engine. See ClickHouse high availability.

default: Authentication failed when a dictionary loads

Section titled “default: Authentication failed when a dictionary loads”

Cause: the dictionary’s CLICKHOUSE source has no USER, so ClickHouse signs in as its built-in default user, which has a password nobody is given (on Databasezy nothing can sign in as default).

Fix: name the user in the source, for example SOURCE(CLICKHOUSE(TABLE 'rates_src' USER 'app' PASSWORD '...')), or use a named collection that holds them. See ClickHouse external data.

A query over the cluster fails on one replica with an unknown-user or authentication error (HA)

Section titled “A query over the cluster fails on one replica with an unknown-user or authentication error (HA)”

Cause: Distributed tables and cluster() run on every replica as the user who sent the query, and a user created without ON CLUSTER main exists on one replica only.

Fix: create users and roles with ON CLUSTER main, or use app. See ClickHouse high availability.

s3, url, remote or Kafka table functions fail

Section titled “s3, url, remote or Kafka table functions fail”

Cause: the host is not on the instance’s external-hosts list, which is empty by default (URL "…" is not allowed in configuration file, see <remote_url_allow_hosts>), or the port is not open (a timeout: Kafka on 9092, remoteSecure on 9440). Only public addresses are reachable.

Fix: add the host (and open the port if it is not 443; on secure placement open 443 too, since nothing public is open there by default) with PUT …/instances/{id}/egress, then write the host in lower case exactly as listed. See ClickHouse external data. You can also load data from your side: clickhouse-client ... --query "INSERT INTO t FORMAT Parquet" < file.parquet.

Users or grants are missing after a restore

Section titled “Users or grants are missing after a restore”

Cause: backups contain the data parts only. Users, roles and grants that you created with SQL are not backed up. Backups are full copies each time, and ClickHouse has no point-in-time recovery.

Fix: keep the SQL that creates your users and grants in version control, and run it again after a restore. See Restore a backup.

ClickHouse cannot be paused, so it bills compute until you resize or delete it. See Usage and billing.

Search the help center or ask the assistant in the portal Help panel. If that does not solve it, open a support ticket from the portal (Help → Open a ticket) and include:

  • the instance id and region
  • the port you used (8443 or 9440)
  • the exact error code and text, or the JSON body
  • your client or driver and its version
  • the query id if you have one
  • the time of the failure in UTC

Never paste passwords, connection strings with passwords, API keys or patient data (PHI) into a ticket. See How to get help.