[Clickhouse] Clickhouse FAQ

FAQ for Clickhouse

Q: Clickhouse 报 SQLException : Read timed out

Q: 解决成功连接数据库后立即报错:Code:516. Authentication failed: password is incorrect or there is no user with such name.

  • 环境信息
  • clickhouse version : 21.3
  • 问题描述
Code: 516, e.displayText() = DB::Exception: default: Authentication failed: password is incorrect or there is no user with such name (version 21.3.4.25)

  • 问题分析

有出现同样问题的网友，排查后发现，是集群开始安装时设置了密码，而配置分布式表时，没有添加各服务器的用户名和密码（或用户名/密码填写错误）。
所以，访问不到别的服务器的数据，在我们的/etc/clickhouse-server/config.d/metrika.xml中，加入用户名和密码即可正常查询数据，如下：

<clickhouse_remote_servers>
<!--market_ck_cluster:for market business and Marketing platform-->
<market_ck_cluster>
    <shard>
        <internal_replication>true</internal_replication>
        <replica>
            <host>192.168.38.101</host>
            <port>9000</port>
            <user>default</user>
            <password>123456</password>
 
         <password_sha256_hex>8d969eef6ecad3c29a3a629280e686cf0c3f5d5a86aff3ca12020c923adc6c92</password_sha256_hex>
        </replica>
        <replica>
            <host>192.168.38.102</host>
            <port>9000</port>
            <user>default</user>
<password>123456</password>          <password_sha256_hex>8d969eef6ecad3c29a3a629280e686cf0c3f5d5a86aff3ca12020c923adc6c92</password_sha256_hex> 
       </replica>
    </shard>
    <shard>
        <internal_replication>true</internal_replication>
        <replica>
            <host>192.168.38.103</host>
            <port>9000</port>
            <user>default</user>
<password>123456</password>
 
            <password_sha256_hex>8d969eef6ecad3c29a3a629280e686cf0c3f5d5a86aff3ca12020c923adc6c92</password_sha256_hex>
</replica>
    </shard>
</market_ck_cluster>
</clickhouse_remote_servers>

问题就出在这2行:

<user>default</user>
<password>123456</password>

修改完成后，查询分布式成功：

node102 :) select dt ,count(id) from dbapi_db.dwd_middle_homework_students_correct_detail_1_all  group by dt order by dt;
 
SELECT
    dt,
    count(id)
FROM dbapi_db.dwd_middle_homework_students_correct_detail_1_all
GROUP BY dt
ORDER BY dt ASC
 
Query id: 1409ea4e-0f74-417d-9026-8409a2e4e887
 
┌─dt─────────┬─count(id)─┐
│ 2022-06-09 │    522346 │
│ 2022-06-10 │    460020 │
└────────────┴───────────┘
 
2 rows in set. Elapsed: 0.052 sec. Processed 982.37 thousand rows, 51.08 MB (19.05 million rows/s., 990.39 MB/s.)
  • 参考文献

Q:两个Clickhouse数据库实例之间，迁移指定CK来源表的数据到CK目标表

clickhouse client --host 192.168.0.A  --port 9440 --user userX --password xxxPasswd1 -mn --secure --query "insert into bpd_dwd.dwd_device_status_record_ri_l select * from remote('192.168.0.B:9440', bpd_dwd.dwd_device_status_record_ri_l, 'user1', 'xxxPasswd') WHERE 1=1 and ..." &

X 参考文献

posted @ 2025-05-09 16:16  千千寰宇  阅读(54)  评论(0)    收藏  举报