Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

ch complains "Password required for user default., e.what() = DB::Exception." when drop a partition .But actually it has the user. And when try many times it will succeed #4762

Closed
inolddays opened this issue Mar 22, 2019 · 2 comments
Closed

ch complains "Password required for user default., e.what() = DB::Exception." when drop a partition .But actually it has the user. And when try many times it will succeed #4762

inolddays opened this issue Mar 22, 2019 · 2 comments
Labels
comp-dddl Distributed DDL feature question Question?

Comments

@inolddays
Copy link

inolddays commented Mar 22, 2019 •
edited

Version of ch:

ckadsmodel-d724d9d7-9dc5-4694-b6ad-a7ad7883ca65 :) select version()

SELECT version()

┌─version()─┐
│ 18.14.19 │
└───────────┘

Cluster Config:

SELECT * FROM system.clusters

┌─cluster──────────────┬─shard_num─┬─shard_weight─┬─replica_num─┬─host_name─────┬─host_address──┬─port─┬─is_local─┬─user────┬─default_database─┐
│ ads_model_ck_cluster │ 1 │ 1 │ 1 │ xx.xx.30.65 │ xx.xx.30.65 │ 9000 │ 1 │ default │ │
│ ads_model_ck_cluster │ 1 │ 1 │ 2 │ xx.xx.40.123 │ xx.xx.40.123 │ 9000 │ 1 │ default │ │
│ ads_model_ck_cluster │ 2 │ 1 │ 1 │ xx.xx.30.64 │ xx.xx.30.64 │ 9000 │ 0 │ default │ │
│ ads_model_ck_cluster │ 2 │ 1 │ 2 │ xx.xx.30.69 │ xx.xx.30.69 │ 9000 │ 0 │ default │ │
....
│ ads_model_ck_cluster │ 15 │ 1 │ 1 │ xx.xx.30.86 │ xx.xx.30.86 │ 9000 │ 0 │ default │ │
│ ads_model_ck_cluster │ 15 │ 1 │ 2 │ xx.xx.30.83 │ xx.xx.30.83 │ 9000 │ 0 │ default │ │
└──────────────────────┴───────────┴──────────────┴─────────────┴───────────────┴───────────────┴──────┴──────────┴─────────┴──────────────────┘
Then Create test ReplicatedMergeTree loal table and Distribute table

CREATE TABLE default.test1 on cluster ads_model_ck_cluster ( id UInt64, name String, d Date) ENGINE =ReplicatedMergeTree('/clickhouse/tables/{layer}-{shard}/default/test1', '{replica}') PARTITION BY toMonday(d) ORDER BY (id, d) SETTINGS index_granularity = 8192;

CREATE TABLE default.test1_all on cluster ads_model_ck_cluster (id UInt64, name String, d Date) ENGINE = Distributed('ads_model_ck_cluster', 'test', 'test1', rand());

INSERT some volume data into the table

ckadsmodel-d724d9d7-9dc5-4694-b6ad-a7ad7883ca65 :) select count() from test1_all;

SELECT count()
FROM test1_all

┌─count()─┐
│ 4390912 │
└─────────┘
Get Detailed info of the partitions:

ckadsmodel-d724d9d7-9dc5-4694-b6ad-a7ad7883ca65 :) select database,table,partition_id,partition,name,active,rows,path,modification_time from system.parts where table='test1';

SELECT
database,
table,
partition_id,
partition,
name,
active,
rows,
path,
modification_time
FROM system.parts
WHERE table = 'test1'

┌─database─┬─table─┬─partition_id─┬─partition────┬─name─────────────┬─active─┬───rows─┬─path─────────────────────────────────────────────────────────┬───modification_time─┐
│ default │ test1 │ 20190318 │ '2019-03-18' │ 20190318_25_25_0 │ 0 │ 1 │ /export/data/clickhouse/data/default/test1/20190318_25_25_0/ │ 2019-03-22 16:05:07 │
│ default │ test1 │ 20190318 │ '2019-03-18' │ 20190318_25_30_1 │ 0 │ 123 │ /export/data/clickhouse/data/default/test1/20190318_25_30_1/ │ 2019-03-22 16:05:14 │
│ default │ test1 │ 20190318 │ '2019-03-18' │ 20190318_25_35_2 │ 0 │ 4642 │ /export/data/clickhouse/data/default/test1/20190318_25_35_2/ │ 2019-03-22 16:05:17 │
│ default │ test1 │ 20190318 │ '2019-03-18' │ 20190318_25_40_3 │ 0 │ 146165 │ /export/data/clickhouse/data/default/test1/20190318_25_40_3/ │ 2019-03-22 16:06:00 │
│ default │ test1 │ 20190318 │ '2019-03-18' │ 20190318_25_43_4 │ 1 │ 290307 │ /export/data/clickhouse/data/default/test1/20190318_25_43_4/ │ 2019-03-22 16:07:17 │
│ default │ test1 │ 20190318 │ '2019-03-18' │ 20190318_26_26_0 │ 0 │ 8 │ /export/data/clickhouse/data/default/test1/20190318_26_26_0/ │ 2019-03-22 16:05:11 │
│ default │ test1 │ 20190318 │ '2019-03-18' │ 20190318_27_27_0 │ 0 │ 11 │ /export/data/clickhouse/data/default/test1/20190318_27_27_0/ │ 2019-03-22 16:05:11 │
│ default │ test1 │ 20190318 │ '2019-03-18' │ 20190318_28_28_0 │ 0 │ 17 │ /export/data/clickhouse/data/default/test1/20190318_28_28_0/ │ 2019-03-22 16:05:12 │
│ default │ test1 │ 20190318 │ '2019-03-18' │ 20190318_29_29_0 │ 0 │ 26 │ /export/data/clickhouse/data/default/test1/20190318_29_29_0/ │ 2019-03-22 16:05:13 │
│ default │ test1 │ 20190318 │ '2019-03-18' │ 20190318_30_30_0 │ 0 │ 60 │ /export/data/clickhouse/data/default/test1/20190318_30_30_0/ │ 2019-03-22 16:05:13 │
│ default │ test1 │ 20190318 │ '2019-03-18' │ 20190318_31_31_0 │ 0 │ 142 │ /export/data/clickhouse/data/default/test1/20190318_31_31_0/ │ 2019-03-22 16:05:14 │
│ default │ test1 │ 20190318 │ '2019-03-18' │ 20190318_32_32_0 │ 0 │ 305 │ /export/data/clickhouse/data/default/test1/20190318_32_32_0/ │ 2019-03-22 16:05:15 │
│ default │ test1 │ 20190318 │ '2019-03-18' │ 20190318_33_33_0 │ 0 │ 553 │ /export/data/clickhouse/data/default/test1/20190318_33_33_0/ │ 2019-03-22 16:05:15 │
│ default │ test1 │ 20190318 │ '2019-03-18' │ 20190318_34_34_0 │ 0 │ 1182 │ /export/data/clickhouse/data/default/test1/20190318_34_34_0/ │ 2019-03-22 16:05:16 │
│ default │ test1 │ 20190318 │ '2019-03-18' │ 20190318_35_35_0 │ 0 │ 2337 │ /export/data/clickhouse/data/default/test1/20190318_35_35_0/ │ 2019-03-22 16:05:17 │
│ default │ test1 │ 20190318 │ '2019-03-18' │ 20190318_36_36_0 │ 0 │ 4585 │ /export/data/clickhouse/data/default/test1/20190318_36_36_0/ │ 2019-03-22 16:05:17 │
│ default │ test1 │ 20190318 │ '2019-03-18' │ 20190318_37_37_0 │ 0 │ 9213 │ /export/data/clickhouse/data/default/test1/20190318_37_37_0/ │ 2019-03-22 16:05:34 │
│ default │ test1 │ 20190318 │ '2019-03-18' │ 20190318_38_38_0 │ 0 │ 18316 │ /export/data/clickhouse/data/default/test1/20190318_38_38_0/ │ 2019-03-22 16:05:55 │
│ default │ test1 │ 20190318 │ '2019-03-18' │ 20190318_39_39_0 │ 0 │ 36600 │ /export/data/clickhouse/data/default/test1/20190318_39_39_0/ │ 2019-03-22 16:05:58 │
│ default │ test1 │ 20190318 │ '2019-03-18' │ 20190318_40_40_0 │ 0 │ 72809 │ /export/data/clickhouse/data/default/test1/20190318_40_40_0/ │ 2019-03-22 16:06:00 │
│ default │ test1 │ 20190318 │ '2019-03-18' │ 20190318_41_41_0 │ 0 │ 501 │ /export/data/clickhouse/data/default/test1/20190318_41_41_0/ │ 2019-03-22 16:06:00 │
│ default │ test1 │ 20190318 │ '2019-03-18' │ 20190318_42_42_0 │ 0 │ 72817 │ /export/data/clickhouse/data/default/test1/20190318_42_42_0/ │ 2019-03-22 16:06:02 │
│ default │ test1 │ 20190318 │ '2019-03-18' │ 20190318_43_43_0 │ 0 │ 70824 │ /export/data/clickhouse/data/default/test1/20190318_43_43_0/ │ 2019-03-22 16:06:02 │
│ default │ test1 │ 20190318 │ '2019-03-18' │ 20190318_44_44_0 │ 1 │ 3065 │ /export/data/clickhouse/data/default/test1/20190318_44_44_0/ │ 2019-03-22 16:06:02 │
└──────────┴───────┴──────────────┴──────────────┴──────────────────┴────────┴────────┴──────────────────────────────────────────────────────────────┴─────────────────────┘

Try to delete one partition using alter table

ckadsmodel-d724d9d7-9dc5-4694-b6ad-a7ad7883ca65 :) alter table default.test1 on cluster ads_model_ck_cluster drop partition '2019-03-18';

ALTER TABLE default.test1 ON CLUSTER ads_model_ck_cluster
DROP PARTITION '2019-03-18'

┌─host──────────┬─port─┬─status─┬─error─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┬─num_hosts_remaining─┬─num_hosts_active─┐
│ xx.xx.30.66 │ 9000 │ 0 │ │ 29 │ 18 │
│ xx.xx.30.69 │ 9000 │ 0 │ │ 28 │ 18 │
│ xx.xx.30.70 │ 9000 │ 0 │ │ 27 │ 18 │
│ xx.xx.30.76 │ 9000 │ 0 │ │ 26 │ 18 │
│ xx.xx.30.71 │ 9000 │ 0 │ │ 25 │ 18 │
│ xx.xx.30.79 │ 9000 │ 194 │ Code: 194, e.displayText() = DB::Exception: Received from xx.xx.30.82:9000. DB::Exception: Password required for user default., e.what() = DB::Exception │ 24 │ 18 │
│ xx.xx.217.46 │ 9000 │ 0 │ │ 23 │ 18 │
│ xx.xx.30.86 │ 9000 │ 0 │ │ 22 │ 18 │
│ xx.xx.30.65 │ 9000 │ 0 │ │ 21 │ 18 │
│ xx.xx.40.123 │ 9000 │ 0 │ │ 20 │ 18 │
└───────────────┴──────┴────────┴───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┴─────────────────────┴──────────────────┘
┌─host──────────┬─port─┬─status─┬─error──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┬─num_hosts_remaining─┬─num_hosts_active─┐
│ xx.xx.30.72 │ 9000 │ 0 │ │ 19 │ 12 │
│ xx.xx.30.67 │ 9000 │ 194 │ Code: 194, e.displayText() = DB::Exception: Received from xx.xx.30.68:9000. DB::Exception: Password required for user default., e.what() = DB::Exception │ 18 │ 12 │
│ xx.xx.217.49 │ 9000 │ 0 │ │ 17 │ 12 │
│ xx.xx.30.81 │ 9000 │ 194 │ Code: 194, e.displayText() = DB::Exception: Received from xx.xx.217.52:9000. DB::Exception: Password required for user default., e.what() = DB::Exception │ 16 │ 12 │
│ xx.xx.217.47 │ 9000 │ 0 │ │ 15 │ 12 │
│ xx.xx.30.74 │ 9000 │ 194 │ Code: 194, e.displayText() = DB::Exception: Received from xx.xx.30.78:9000. DB::Exception: Password required for user default., e.what() = DB::Exception │ 14 │ 12 │
│ xx.xx.30.85 │ 9000 │ 0 │ │ 13 │ 12 │
│ xx.xx.30.77 │ 9000 │ 0 │ │ 12 │ 12 │
└───────────────┴──────┴────────┴────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┴─────────────────────┴──────────────────┘
┌─host──────────┬─port─┬─status─┬─error─┬─num_hosts_remaining─┬─num_hosts_active─┐
│ xx.xx.30.78 │ 9000 │ 0 │ │ 11 │ 9 │
│ xx.xx.30.73 │ 9000 │ 0 │ │ 10 │ 9 │
│ xx.xx.217.45 │ 9000 │ 0 │ │ 9 │ 9 │
└───────────────┴──────┴────────┴───────┴─────────────────────┴──────────────────┘
┌─host──────────┬─port─┬─status─┬─error─┬─num_hosts_remaining─┬─num_hosts_active─┐
│ xx.xx.217.50 │ 9000 │ 0 │ │ 8 │ 8 │
└───────────────┴──────┴────────┴───────┴─────────────────────┴──────────────────┘
┌─host──────────┬─port─┬─status─┬─error─┬─num_hosts_remaining─┬─num_hosts_active─┐
│ xx.xx.217.52 │ 9000 │ 0 │ │ 7 │ 7 │
└───────────────┴──────┴────────┴───────┴─────────────────────┴──────────────────┘
┌─host─────────┬─port─┬─status─┬─error─┬─num_hosts_remaining─┬─num_hosts_active─┐
│ xx.xx.30.82 │ 9000 │ 0 │ │ 6 │ 6 │
└──────────────┴──────┴────────┴───────┴─────────────────────┴──────────────────┘
┌─host──────────┬─port─┬─status─┬─error─┬─num_hosts_remaining─┬─num_hosts_active─┐
│ xx.xx.30.68 │ 9000 │ 0 │ │ 5 │ 3 │
│ xx.xx.40.126 │ 9000 │ 0 │ │ 4 │ 3 │
│ xx.xx.30.64 │ 9000 │ 0 │ │ 3 │ 3 │
└───────────────┴──────┴────────┴───────┴─────────────────────┴──────────────────┘
┌─host──────────┬─port─┬─status─┬─error─┬─num_hosts_remaining─┬─num_hosts_active─┐
│ xx.xx.217.48 │ 9000 │ 0 │ │ 2 │ 0 │
│ xx.xx.30.80 │ 9000 │ 0 │ │ 1 │ 0 │
│ xx.xx.30.83 │ 9000 │ 0 │ │ 0 │ 0 │
└───────────────┴──────┴────────┴───────┴─────────────────────┴──────────────────┘
Received exception from server (version 18.14.19):
Code: 194. DB::Exception: Received from 127.0.0.1:9000. DB::Exception: There was an error on [xx.xx.30.79:9000]: Code: 194, e.displayText() = DB::Exception: Received from xx.xx.30.82:9000. DB::Exception: Password required for user default., e.what() = DB::Exception.

30 rows in set. Elapsed: 1.072 sec.

Still some node has data
xx.xx.30.68
291660
xx.xx.30.67
291660
xx.xx.30.78
291577
xx.xx.30.74
291577
xx.xx.30.79
293352
xx.xx.30.82
293352
xx.xx.30.81
293279
xx.xx.217.52
293279

Then I try it for the third Time
ckadsmodel-d724d9d7-9dc5-4694-b6ad-a7ad7883ca65 :) alter table default.test1 on cluster ads_model_ck_cluster drop partition '2019-03-18';

ALTER TABLE default.test1 ON CLUSTER ads_model_ck_cluster
DROP PARTITION '2019-03-18'

┌─host──────────┬─port─┬─status─┬─error──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┬─num_hosts_remaining─┬─num_hosts_active─┐
│ xx.xx.30.78 │ 9000 │ 0 │ │ 29 │ 15 │
│ xx.xx.30.67 │ 9000 │ 194 │ Code: 194, e.displayText() = DB::Exception: Received from xx.xx.30.68:9000. DB::Exception: Password required for user default., e.what() = DB::Exception │ 28 │ 15 │
│ xx.xx.30.72 │ 9000 │ 0 │ │ 27 │ 15 │
│ xx.xx.30.81 │ 9000 │ 194 │ Code: 194, e.displayText() = DB::Exception: Received from xx.xx.217.52:9000. DB::Exception: Password required for user default., e.what() = DB::Exception │ 26 │ 15 │
│ xx.xx.30.65 │ 9000 │ 0 │ │ 25 │ 15 │
│ xx.xx.30.86 │ 9000 │ 0 │ │ 24 │ 15 │
│ xx.xx.217.47 │ 9000 │ 0 │ │ 23 │ 15 │
│ xx.xx.30.66 │ 9000 │ 0 │ │ 22 │ 15 │
│ xx.xx.30.69 │ 9000 │ 0 │ │ 21 │ 15 │
│ xx.xx.30.70 │ 9000 │ 0 │ │ 20 │ 15 │
│ xx.xx.30.76 │ 9000 │ 0 │ │ 19 │ 15 │
│ xx.xx.30.71 │ 9000 │ 0 │ │ 18 │ 15 │
│ xx.xx.30.85 │ 9000 │ 0 │ │ 17 │ 15 │
│ xx.xx.30.82 │ 9000 │ 0 │ │ 16 │ 15 │
│ xx.xx.30.77 │ 9000 │ 0 │ │ 15 │ 15 │
└───────────────┴──────┴────────┴────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┴─────────────────────┴──────────────────┘
┌─host──────────┬─port─┬─status─┬─error─┬─num_hosts_remaining─┬─num_hosts_active─┐
│ xx.xx.217.45 │ 9000 │ 0 │ │ 14 │ 12 │
│ xx.xx.217.46 │ 9000 │ 0 │ │ 13 │ 12 │
│ xx.xx.40.123 │ 9000 │ 0 │ │ 12 │ 12 │
└───────────────┴──────┴────────┴───────┴─────────────────────┴──────────────────┘
┌─host──────────┬─port─┬─status─┬─error─┬─num_hosts_remaining─┬─num_hosts_active─┐
│ xx.xx.217.52 │ 9000 │ 0 │ │ 11 │ 11 │
└───────────────┴──────┴────────┴───────┴─────────────────────┴──────────────────┘
┌─host──────────┬─port─┬─status─┬─error─┬─num_hosts_remaining─┬─num_hosts_active─┐
│ xx.xx.217.50 │ 9000 │ 0 │ │ 10 │ 6 │
│ xx.xx.30.73 │ 9000 │ 0 │ │ 9 │ 6 │
│ xx.xx.40.126 │ 9000 │ 0 │ │ 8 │ 6 │
│ xx.xx.217.48 │ 9000 │ 0 │ │ 7 │ 6 │
│ xx.xx.30.79 │ 9000 │ 0 │ │ 6 │ 6 │
└───────────────┴──────┴────────┴───────┴─────────────────────┴──────────────────┘
┌─host──────────┬─port─┬─status─┬─error─┬─num_hosts_remaining─┬─num_hosts_active─┐
│ xx.xx.30.68 │ 9000 │ 0 │ │ 5 │ 4 │
│ xx.xx.217.49 │ 9000 │ 0 │ │ 4 │ 4 │
└───────────────┴──────┴────────┴───────┴─────────────────────┴──────────────────┘
┌─host─────────┬─port─┬─status─┬─error─┬─num_hosts_remaining─┬─num_hosts_active─┐
│ xx.xx.30.64 │ 9000 │ 0 │ │ 3 │ 2 │
│ xx.xx.30.74 │ 9000 │ 0 │ │ 2 │ 2 │
└──────────────┴──────┴────────┴───────┴─────────────────────┴──────────────────┘
┌─host─────────┬─port─┬─status─┬─error─┬─num_hosts_remaining─┬─num_hosts_active─┐
│ xx.xx.30.80 │ 9000 │ 0 │ │ 1 │ 0 │
│ xx.xx.30.83 │ 9000 │ 0 │ │ 0 │ 0 │
└──────────────┴──────┴────────┴───────┴─────────────────────┴──────────────────┘
Received exception from server (version 18.14.19):
Code: 194. DB::Exception: Received from 127.0.0.1:9000. DB::Exception: There was an error on [xx.xx.30.67:9000]: Code: 194, e.displayText() = DB::Exception: Received from xx.xx.30.68:9000. DB::Exception: Password required for user default., e.what() = DB::Exception.

30 rows in set. Elapsed: 1.071 sec.

Although there are errors warning, the data is deleted successfully.
This is very strange. Can anyone give a hint on this?
@inolddays inolddays added the question Question? label Mar 22, 2019
@filimonov
Copy link
Contributor

filimonov commented Mar 22, 2019 •
edited

Duplicate of #1861

PR #3598 fixes that (should work properly in versions > 19.1)

Generally, when you do some DDL on non-leader replica it forwards the request to a leader, and during that forwarding "default" passwordless user was used.

@inolddays
Copy link
Author

Many Thanks! Alexey

@filimonov filimonov added the comp-dddl Distributed DDL feature label Jun 11, 2019
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
comp-dddl Distributed DDL feature question Question?
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@filimonov @inolddays