ClickHouse DB::Exception: External server is not responding

The "DB::Exception: External server is not responding" error in ClickHouse surfaces when a query relies on an external data source — such as MySQL, PostgreSQL, MongoDB, or an HTTP endpoint — and that source fails to respond within the configured timeout. The error code is EXTERNAL_SERVER_IS_NOT_RESPONDING.

Impact

Queries that read from external table engines, dictionaries backed by external sources, or table functions like mysql(), postgresql(), and url() will fail. If materialized views or scheduled refreshes depend on these sources, data pipelines may stall until connectivity is restored.

Common Causes

1. The external database or service is down or unreachable due to a network outage.
2. A firewall rule or security group is blocking traffic between ClickHouse and the external host.
3. DNS resolution failure for the external server hostname.
4. The external server is overloaded and not accepting new connections within the timeout window.
5. Connection timeout settings in ClickHouse are too aggressive for the network latency involved.
6. TLS/SSL handshake failures causing the connection to hang before being established.

Troubleshooting and Resolution Steps

1. Test network connectivity from the ClickHouse host:

   telnet external-host 3306   # MySQL
   telnet external-host 5432   # PostgreSQL
   curl -v http://external-host:8080/health
   

2. Check DNS resolution:

   nslookup external-host
   dig external-host
   

3. Verify the external server is running: Connect directly to the external server using its native client to confirm it accepts connections.

4. Review firewall and security group rules: Ensure that outbound connections from the ClickHouse server and inbound connections on the external server are permitted on the required ports.

5. Increase connection timeouts: If the external server is distant or slow, adjust timeout settings:

   SET external_storage_connect_timeout_sec = 30;
   SET external_storage_rw_timeout_sec = 300;
   

6. Check ClickHouse server logs: Look for more detailed connection error messages that may indicate TLS issues, authentication failures, or protocol mismatches.

7. Test with a table function: Isolate the issue by running a simple query using a table function:

   SELECT count() FROM mysql('host:3306', 'database', 'table', 'user', 'password');
   

8. Review external server logs: The external database may be rejecting the connection due to max-connections limits, authentication rules, or IP allow-lists.

Best Practices

• Set up monitoring and alerting for external data source availability.
• Use connection pooling where supported to reduce connection establishment overhead.
• Configure reasonable timeouts that account for network latency and external server load.
• Implement retry logic in application code for queries that depend on external sources.
• Use ClickHouse dictionaries with lifetime settings to cache external data and reduce dependency on real-time availability.

Frequently Asked Questions

Q: Can I set a retry policy for external source connections in ClickHouse?
A: ClickHouse does not have built-in retry logic for external table queries. You should implement retries in your application layer or use dictionaries with automatic background refresh, which do retry on failure.

Q: How do I check if the issue is network-related or on the external server side?
A: Try connecting from the ClickHouse host using the external server's native client. If that also fails, the issue is network-level or on the external server. If the native client works but ClickHouse does not, the issue may be in ClickHouse's connection settings or protocol handling.

Q: Does this error affect dictionary updates?
A: Yes. If a dictionary source is unreachable, the dictionary will serve stale data until the source becomes available again. ClickHouse logs a warning and retries according to the dictionary's lifetime configuration.