orphan_parts_issue_repro

orphan_parts_issue_repro.md

create table test_bug(A Int64, D Date, S String) 
Engine = ReplicatedMergeTree('/clickhouse/tables/test_bug_orphans', '{replica}')
partition by D order by A;

insert into test_bug select number, today(), '' from numbers(1000);
insert into test_bug select number, today()-1, '' from numbers(1000);
insert into test_bug select number, today()-1, '' from numbers(1001);
insert into test_bug select number, today()-1, '' from numbers(1002);
select sleep(.5);
optimize table test_bug partition tuple(today()-1) final settings optimize_throw_if_noop=1;

detach table test_bug;
attach table test_bug;


select name, active from system.parts where table = 'test_bug';
┌─name───────────┬─active─┐
│ 20230130_0_2_1 │      1 │   -- only one part
│ 20230131_0_0_0 │      1 │  
└────────────────┴────────┘

select concat(path,'/',name) as p_path
from system.zookeeper where path = '/clickhouse/tables/test_bug_orphans/replicas/localhost/parts'
┌─p_path──────────────────────────────────────────────────────────────────────┐
│ /clickhouse/tables/test_bug_orphans/replicas/localhost/parts/20230131_0_0_0 │ 
│ /clickhouse/tables/test_bug_orphans/replicas/localhost/parts/20230130_0_2_1 │ 
│ /clickhouse/tables/test_bug_orphans/replicas/localhost/parts/20230130_0_0_0 │ x orphans
│ /clickhouse/tables/test_bug_orphans/replicas/localhost/parts/20230130_1_1_0 │ x orphans
│ /clickhouse/tables/test_bug_orphans/replicas/localhost/parts/20230130_2_2_0 │ x orphans
└─────────────────────────────────────────────────────────────────────────────┘


select 'delete '||part_zoo
from (
select zoo.p_path as part_zoo, zoo.ctime, zoo.mtime, disk.p_path as part_disk
from
(
  select concat(path,'/',name) as p_path, ctime, mtime
  from system.zookeeper where path = '/clickhouse/tables/test_bug_orphans/replicas/localhost/parts'
) zoo
left join 
(
  select concat(replica_path,'/parts/',name) as p_path
  from system.parts inner join system.replicas using (database, table)
) disk on zoo.p_path = disk.p_path
WHERE part_disk = '' and zoo.mtime <= now() - interval 30 second /* for production !!! zoo.mtime <= now() - interval 1 day  */
order by part_zoo) format TSVRaw;

delete /clickhouse/tables/test_bug_orphans/replicas/localhost/parts/20230130_0_0_0
delete /clickhouse/tables/test_bug_orphans/replicas/localhost/parts/20230130_1_1_0
delete /clickhouse/tables/test_bug_orphans/replicas/localhost/parts/20230130_2_2_0

drop table test_bug sync;