ClickHouse error (version 21.6.6): Code: 1000 DB::Exception: Exception: Connections to all replicas failed

Question

I am trying to connect ClickHouse with a local MYSQL database, ie, using the Engine MySQL. I have run this commands to obtain the important data.

mysql> SELECT * FROM amazon_redshift.analytics limit 10;

+-------+------------+----------+------------+------------+-----------------------------+-------------------------------+-------------------------+
| count | date       | store_id | created_at | updated_at | ret_cust_rate               | orders_ret_cust               | recommendation_accuracy |
+-------+------------+----------+------------+------------+-----------------------------+-------------------------------+-------------------------+
|   875 | 2021-03-01 |   199703 | 2021-07-26 | 2021-07-26 | {"new":"1","returning":"0"} | {"new":"100","returning":"0"} | NULL                    |
|   425 | 2021-03-01 |   212743 | 2021-07-26 | 2021-07-26 | {"new":"1","returning":"0"} | {"new":"100","returning":"0"} | NULL                    |
|    81 | 2021-03-01 |   213745 | 2021-07-26 | 2021-07-26 | {"new":"1","returning":"0"} | {"new":"100","returning":"0"} | NULL                    |
|   189 | 2021-03-01 |   204112 | 2021-07-26 | 2021-07-26 | {"new":"1","returning":"0"} | {"new":"100","returning":"0"} | NULL                    |
|   309 | 2021-03-01 |   204125 | 2021-07-26 | 2021-07-26 | {"new":"1","returning":"0"} | {"new":"100","returning":"0"} | NULL                    |
|   761 | 2021-03-01 |   209940 | 2021-07-26 | 2021-07-26 | {"new":"1","returning":"0"} | {"new":"100","returning":"0"} | NULL                    |
|   699 | 2021-03-01 |   214119 | 2021-07-26 | 2021-07-26 | {"new":"1","returning":"0"} | {"new":"100","returning":"0"} | NULL                    |
|   913 | 2021-03-01 |   201062 | 2021-07-26 | 2021-07-26 | {"new":"1","returning":"0"} | {"new":"100","returning":"0"} | NULL                    |
|   792 | 2021-03-01 |   210493 | 2021-07-26 | 2021-07-26 | {"new":"1","returning":"0"} | {"new":"100","returning":"0"} | NULL                    |
|   182 | 2021-03-01 |   203925 | 2021-07-26 | 2021-07-26 | {"new":"1","returning":"0"} | {"new":"100","returning":"0"} | NULL                    |
+-------+------------+----------+------------+------------+-----------------------------+-------------------------------+-------------------------+
10 rows in set (0,00 sec)

mysql> status
--------------
mysql  Ver 8.0.25-0ubuntu0.20.04.1 for Linux on x86_64 ((Ubuntu))

Connection id:      13
Current database:   amazon_redshift
Current user:       root@localhost
SSL:            Not in use
Current pager:      stdout
Using outfile:      ''
Using delimiter:    ;
Server version:     8.0.25-0ubuntu0.20.04.1 (Ubuntu)
Protocol version:   10
Connection:     Localhost via UNIX socket
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8mb4
Conn.  characterset:    utf8mb4
UNIX socket:        /var/run/mysqld/mysqld.sock
Binary data as:     Hexadecimal
Uptime:         2 hours 53 min 46 sec

Threads: 2  Questions: 52  Slow queries: 0  Opens: 157  Flush tables: 3  Open tables: 76  Queries per second avg: 0.004
--------------

mysql> SELECT SUBSTRING_INDEX(USER(), '@', -1) AS ip,  @@hostname as hostname, @@port as port, DATABASE() as current_database;
+-----------+--------------------------------+------+------------------+
| ip        | hostname                       | port | current_database |
+-----------+--------------------------------+------+------------------+
| localhost | helena-ZenBook-UX451FL-UX289FL | 3306 | amazon_redshift  |
+-----------+--------------------------------+------+------------------+

$ sudo lsof -i TCP:3306 
COMMAND  PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld  1134 mysql   27u  IPv4  42528      0t0  TCP localhost:mysql (LISTEN)

Then I run the docker Image of clickHouse:

CREATE DATABASE dummy ENGINE = Memory;

CREATE TABLE dummy.analytics
(
    `count` UInt8,
    `date` date NOT NULL,
    `store_id` bigint NOT NULL,
    `created_at` date NOT NULL,
    `updated_at` date NOT NULL,
    `ret_cust_rate` text,
    `orders_ret_cust` text,
    `recommendation_accuracy` text
)
ENGINE = MySQL('localhost:3306', 'amazon_redshift', 'analytics', 'root', 'Password9876')


SELECT *
FROM dummy.analytics
LIMIT 10;

Received exception from server (version 21.6.6): Code: 1000. DB::Exception: Received from clickhouse-server:9000. DB::Exception: Exception: Connections to all replicas failed: amazon_redshift@localhost:3306 as user root.

Does someone knows why?

Answer 1

The reason is using localhost:3306 where localhost points to the container, not the external host.

For testing purposes consider using network-settings as host to have access to host network:

docker run --network="host" ..

---

As an alternate way can be used docker-compose to host both MySQL and ClickHouse (see for example CH & Kafka).