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');