How to recreate a table in case of total corruption of the replication queue

How to recreate a table in case of total corruption of the replication queue.

How to fix a replication using hard-reset way

Find the best replica (replica with the most fresh/consistent) data.
Backup the table alter table mydatabase.mybadtable freeze;
Stop all applications!!! Stop ingestion. Stop queries - table will be empty for some time.
Check that detached folder is empty or clean it.

SELECT concat('alter table ', database, '.', table, ' drop detached part \'', name, '\' settings allow_drop_detached=1;')
FROM system.detached_parts
WHERE (database = 'mydatabase') AND (table = 'mybadtable')
FORMAT TSVRaw;

Make sure that detached folder is empty select count() from system.detached_parts where database='mydatabase' and table ='mybadtable';
Detach all parts (table will became empty)

SELECT concat('alter table ', database, '.', table, ' detach partition id \'', partition_id, '\';') AS detach
FROM system.parts
WHERE (active = 1) AND (database = 'mydatabase') AND (table = 'mybadtable')
GROUP BY detach
ORDER BY detach ASC
FORMAT TSVRaw;

Make sure that table is empty select count() from mydatabase.mybadtable;
Attach all parts back

SELECT concat('alter table ', database, '.', table, ' attach part \'', a.name, '\';')
FROM system.detached_parts AS a
WHERE (database = 'mydatabase') AND (table = 'mybadtable')
FORMAT TSVRaw;

Make sure that data is consistent at all replicas

SELECT
    formatReadableSize(sum(bytes)) AS size,
    sum(rows),
    count() AS part_count,
    uniqExact(partition) AS partition_count
FROM system.parts
WHERE (active = 1) AND (database = 'mydatabase') AND (table = 'mybadtable');

Last modified 2023.08.22: Create how_to_recreate_table.md (d1bdf49)