处理 Code:516. Authentication failed: password is incorrect or there is no user with such name.

处理clickhouse 22.2.2 分布式表查询报错的问题Code: 516. DB::Exception: Received from 192.168.38.101:9000. DB::Exception: default: Authentication failed: password is incorrect or there is no user with such name.

秋风暮霞挽红曲

7515人浏览 · 2022-07-20 11:08:57

秋风暮霞挽红曲 · 2022-07-20 11:08:57 发布

在测试 ClickHouse 分布式表时，创建分布式表成功，但是查询数据时报错，如下：

Received exception from server (version 22.2.2):
Code: 516. DB::Exception: Received from 192.168.38.101:9000. DB::Exception: Received from 192.168.38.103:9000. DB::Exception: default: Authentication failed: pass
word is incorrect or there is no user with such name. (AUTHENTICATION_FAILED)

排查后发现，是集群开始安装时设置了密码，而配置分布式表时，没有添加各服务器的用户名和密码，所以访问不到别的服务器的数据，在我们的/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>

就是这两行：

 <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.)

问题解决，开始开心的学习.....