Clickhouse: DROP PARTITION does not work
SELECT
partition,
name
FROM system.parts
WHERE active
ββpartitionββ¬βnameβββββββββββββββββββββββββββββ
β 201606 β 20160630_20160630_6306_6382_4 β
β 201606 β 20160630_20160630_6384_6416_3 β
β 201606 β 20160630_20160630_6418_6450_3 β
β 201606 β 20160630_20160630_6452_6452_0 β
β 201606 β 20160630_20160630_6456_6456_0 β
β 201607 β 20160709_20160717_2_4578_7 β
β 201607 β 20160701_20160722_4580_8870_7 β
β 201607 β 20160722_20160729_8872_13109_7 β
β 201607 β 20160701_20160731_13110_16819_7 β
β 201607 β 20160704_20160708_16820_19220_6 β
β 201607 β 20160708_20160709_19222_19462_5 β
β 201607 β 20160709_20160709_19464_19642_4 β
β 201607 β 20160709_20160731_19644_20426_4 β
β 201607 β 20160731_20160731_20428_20428_0 β
β 201608 β 20160801_20160825_19664_23970_7 β
β 201608 β 20160801_20160831_23972_28450_7 β
β 201608 β 20160802_20160831_28452_32470_7 β
β 201608 β 20160809_20160809_32472_32682_5 β
β 201608 β 20160809_20160809_32684_32898_5 β
β 201608 β 20160809_20160831_32900_39532_3 β
β 201608 β 20160831_20160831_39534_39598_3 β
β 201608 β 20160831_20160831_39600_39626_2 β
β 201608 β 20160831_20160831_39628_39628_0 β
β 201608 β 20160831_20160831_39632_39632_0 β
β 201609 β 20160909_20160916_32922_37476_7 β
β 201609 β 20160901_20160921_37478_41690_7 β
β 201609 β 20160921_20160927_41693_45386_7 β
β 201609 β 20160901_20160930_45388_48568_7 β
β 201609 β 20160903_20160907_48570_51646_7 β
β 201609 β 20160907_20160908_51648_52248_6 β
β 201609 β 20160908_20160909_52250_52850_5 β
β 201609 β 20160909_20160909_52852_53028_4 β
β 201609 β 20160909_20160930_53030_65093_5 β
β 201609 β 20160930_20160930_65094_65100_1 β
β 201610 β 20161001_20161031_57672_77638_8 β
β 201610 β 20161007_20161008_77640_78298_6 β
β 201610 β 20161008_20161009_78300_79216_6 β
β 201610 β 20161009_20161009_79218_79294_4 β
β 201610 β 20161009_20161009_79297_79306_1 β
β 201610 β 20161009_20161009_79308_79314_1 β
βββββββββββββ΄ββββββββββββββββββββββββββββββββββ
ALTER TABLE actions DROP PARTITION '201610';
SELECT
partition,
name
FROM system.parts
WHERE active
ββpartitionββ¬βnameβββββββββββββββββββββββββββββ
β 201606 β 20160630_20160630_6306_6382_4 β
β 201606 β 20160630_20160630_6384_6416_3 β
β 201606 β 20160630_20160630_6418_6450_3 β
β 201606 β 20160630_20160630_6452_6452_0 β
β 201606 β 20160630_20160630_6456_6456_0 β
β 201607 β 20160709_20160717_2_4578_7 β
β 201607 β 20160701_20160722_4580_8870_7 β
β 201607 β 20160722_20160729_8872_13109_7 β
β 201607 β 20160701_20160731_13110_16819_7 β
β 201607 β 20160704_20160708_16820_19220_6 β
β 201607 β 20160708_20160709_19222_19462_5 β
β 201607 β 20160709_20160709_19464_19642_4 β
β 201607 β 20160709_20160731_19644_20426_4 β
β 201607 β 20160731_20160731_20428_20428_0 β
β 201608 β 20160801_20160825_19664_23970_7 β
β 201608 β 20160801_20160831_23972_28450_7 β
β 201608 β 20160802_20160831_28452_32470_7 β
β 201608 β 20160809_20160809_32472_32682_5 β
β 201608 β 20160809_20160809_32684_32898_5 β
β 201608 β 20160809_20160831_32900_39532_3 β
β 201608 β 20160831_20160831_39534_39598_3 β
β 201608 β 20160831_20160831_39600_39626_2 β
β 201608 β 20160831_20160831_39628_39628_0 β
β 201608 β 20160831_20160831_39632_39632_0 β
β 201609 β 20160909_20160916_32922_37476_7 β
β 201609 β 20160901_20160921_37478_41690_7 β
β 201609 β 20160921_20160927_41693_45386_7 β
β 201609 β 20160901_20160930_45388_48568_7 β
β 201609 β 20160903_20160907_48570_51646_7 β
β 201609 β 20160907_20160908_51648_52248_6 β
β 201609 β 20160908_20160909_52250_52850_5 β
β 201609 β 20160909_20160909_52852_53028_4 β
β 201609 β 20160909_20160930_53030_65093_5 β
β 201609 β 20160930_20160930_65094_65100_1 β
βββββββββββββ΄ββββββββββββββββββββββββββββββββββ
Looks like it works fine, but after restarting the server the partition exist again.
sudo service clickhouse-server stop
sudo service clickhouse-server start
SELECT
partition,
name
FROM system.parts
WHERE active
ββpartitionββ¬βnameβββββββββββββββββββββββββββββ
β 201606 β 20160630_20160630_6306_6382_4 β
β 201606 β 20160630_20160630_6384_6416_3 β
β 201606 β 20160630_20160630_6418_6450_3 β
β 201606 β 20160630_20160630_6452_6452_0 β
β 201606 β 20160630_20160630_6456_6456_0 β
β 201607 β 20160709_20160717_2_4578_7 β
β 201607 β 20160701_20160722_4580_8870_7 β
β 201607 β 20160722_20160729_8872_13109_7 β
β 201607 β 20160701_20160731_13110_16819_7 β
β 201607 β 20160704_20160708_16820_19220_6 β
β 201607 β 20160708_20160709_19222_19462_5 β
β 201607 β 20160709_20160709_19464_19642_4 β
β 201607 β 20160709_20160731_19644_20426_4 β
β 201607 β 20160731_20160731_20428_20428_0 β
β 201608 β 20160801_20160825_19664_23970_7 β
β 201608 β 20160801_20160831_23972_28450_7 β
β 201608 β 20160802_20160831_28452_32470_7 β
β 201608 β 20160809_20160809_32472_32682_5 β
β 201608 β 20160809_20160809_32684_32898_5 β
β 201608 β 20160809_20160831_32900_39532_3 β
β 201608 β 20160831_20160831_39534_39598_3 β
β 201608 β 20160831_20160831_39600_39626_2 β
β 201608 β 20160831_20160831_39628_39628_0 β
β 201608 β 20160831_20160831_39632_39632_0 β
β 201609 β 20160909_20160916_32922_37476_7 β
β 201609 β 20160901_20160921_37478_41690_7 β
β 201609 β 20160921_20160927_41693_45386_7 β
β 201609 β 20160901_20160930_45388_48568_7 β
β 201609 β 20160903_20160907_48570_51646_7 β
β 201609 β 20160907_20160908_51648_52248_6 β
β 201609 β 20160908_20160909_52250_52850_5 β
β 201609 β 20160909_20160909_52852_53028_4 β
β 201609 β 20160909_20160930_53030_65093_5 β
β 201609 β 20160930_20160930_65094_65100_1 β
β 201610 β 20161001_20161031_57672_77638_8 β
β 201610 β 20161007_20161008_77640_78298_6 β
β 201610 β 20161008_20161009_78300_79216_6 β
β 201610 β 20161009_20161009_79218_79294_4 β
β 201610 β 20161009_20161009_79297_79306_1 β
β 201610 β 20161009_20161009_79308_79314_1 β
βββββββββββββ΄ββββββββββββββββββββββββββββββββββ
My version is 1.1.54197.
bug
khrisanfov
All 4 comments
Hi, what engine do you use for table actions? Replicated*?
Is actions solely table with MergeTree* engine in ClickHouse?
Use
select * from system.tables WHERE engine LIKE '%MergeTree%' and
select * from system.parts where table='actions'
(We need to check that appeared parts belong to the same table and there are no MaterializedViews viewing on it).
ludv1x
on 2 May 2017
We have known issue, that dropped partition is physically dropped only after few minutes, and if you restart ClickHouse before this moment, it will be alive again.
If you use DETACH instead of DROP, the partition will be detached instantly (but you will need to remove it from detached directory by yourself).
alexey-milovidov
on 4 May 2017
👍4
I just have a similar problem, it only happens to some tables / partitions it seems. It looks like dropping or detaching succeeds, but it doesn't seem to be doing anything. These partitions were converted from MergeTree to ReplicatedMergeTree if that helps.
:) SELECT formatReadableSize(sum(bytes)) as sz FROM system.parts WHERE database = 'r0' AND table = 'dnslogs_replica' AND partition = '201705'
ββszββββββββ
β 4.57 TiB β
ββββββββββββ
:) ALTER TABLE r0.dnslogs_replica DROP PARTITION '201705'
Ok. 0 rows in set. Elapsed: 0.652 sec. Processed: 0.0 rows, 0.0B (0.0 rows/s, 0.0B/s)
:) SELECT formatReadableSize(sum(bytes)) as sz FROM system.parts WHERE database = 'r0' AND table = 'dnslogs_replica' AND partition = '201705'
ββszββββββββ
β 4.57 TiB β
ββββββββββββ
:) SELECT version()
ββversion()ββ
β 1.1.54265 β
βββββββββββββ
:) SELECT DISTINCT engine FROM system.parts WHERE database = 'r0' AND table = 'dnslogs_replica'
ββengineβββββββββββββββ
β ReplicatedMergeTree β
ββββββββββββββββββββββ
Detach does the same thing, it doesn't seem to apply any changes even after several minutes.
CC @bobrik
vavrusa
on 10 Aug 2017
I talked with @alexey-milovidov, the issue is that partitions were converted from MergeTree to ReplicatedMergeTree in an older version of ClickHouse that attached the parts in a private block number space [0, 200), without increasing the actual block number. Sine the part hasn't been written into, the block number is 0. The new version of ClickHouse attaches unreplicated parts properly now, but also treats tables with block number 0 as empty, so neither DROP nor DETACH do anything. I resolved it by manually appending the drop request to the table queue in ZK that looks like:
SELECT name,
value
FROM system.zookeeper
WHERE path = '/clickhouse/pdx-main/tables/15/logs/log'
AND value LIKE '%201705%'
ORDER BY name ASC
LIMIT 50
ββnameββββββββββββ¬βvalueβββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ
β log-0000323043 β format version: 4\ncreate_time: 2017-08-10 21:12:30\nsource replica: 36s140\nblock_id: \ndrop\n20170501_20170531_0_200_999999999\n β
ββββββββββββββββββ΄βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ
Ok. 1 row in set. Elapsed: 3.397 sec. Processed: 0.0 rows, 0.0B (0.0 rows/s, 0.0B/s)
The format is a bit tricky, obviously the part number covers all days in May, and block numbers 0 - 200.
This successfully makes replicas to drop the partition. CC @bobrik
vavrusa
on 11 Aug 2017
Was this page helpful?
0 / 5 - 0 ratings
Related issues
vixa2012
Β·
3Comments
bseng
Β·
3Comments
greenx
Β·
3Comments
innerr
Β·
3Comments
opavader
Β·
3Comments
Most helpful comment
We have known issue, that dropped partition is physically dropped only after few minutes, and if you restart ClickHouse before this moment, it will be alive again.
If you use DETACH instead of DROP, the partition will be detached instantly (but you will need to remove it from
detacheddirectory by yourself).