Technical Tip: Clickhouse: 'DUPLICATE DATA PART' error

Description

This article describes how to handle, under ClickHouse, the duplicate data part error.

Scope

FortiSIEM.

Solution

If a 'DUPLICATE_DATA_PART' error occurs in ClickHouse, the service may be stopped.

To review its status:

systemctl status clickhouse-server

Check the logs for details.

Review the ClickHouse server logs to identify the specific parts causing the error. The logs are typically located at:

grep -i duplicate /opt/clickhouse/log/clickhouse-server.log /opt/clickhouse/log/clickhouse-server.err.log

grep -i duplicate /opt/phoenix/log/phoenix.log  /opt/glas*/dom*/dom*/logs/phoenix.log

Detach or remove duplicate parts (if the service is running).

If the logs indicate specific duplicate parts, it may be necessary to take action:

Detach duplicate parts: This can be done using the following command:

clickhouse-client -q "ALTER TABLE <table_name> DETACH PART '<part_name>';"

Remove duplicate parts: If detaching does not resolve the issue, it may be necessary to drop the duplicate parts:

clickhouse-client -q "ALTER TABLE <table_name> DROP DETACHED PART '<part_name>';"

Manual deletion from the filesystem (if the service is not running).

If it is desired to delete detached parts manually, follow these steps:

• Stop the ClickHouse server to ensure data integrity.

• Navigate to the detached parts directory:
  
  

cd /data-clickhouse-*/

find . -iname 18250-

Verify that the parts intended to be deleted are safe to remove.

Use the following command to delete the unwanted parts:

rm -rf part_name

Important considerations:

Always ensure that the detached parts are not needed before deletion.

Regularly monitor the system.detached_parts to prevent accumulation of unnecessary data.

Restart the ClickHouse service.

After addressing the duplicate parts, attempt to restart the ClickHouse service:

systemctl restart clickhouse-server 

Example:

Compared HOT-1 and HOT-2 on the Worker and found exactly three same-name data parts with different contents:

18250-20260923_16026_16026_0 — HOT-1: 0 rows / 36K, HOT-2: 70 rows / 192K

18250-20260923_16037_16042_1 — HOT-1: 0 rows / 36K, HOT-2: 39,500 rows / 3.9M

18250-20260923_16040_16040_0 — HOT-1: 0 rows / 36K, HOT-2: 13,422 rows / 1.8M

All three HOT-1 copies contain zero rows and have the same checksum, while the HOT-2 copies contain event data.

ClickHouse is stopped on the Worker.

Identify three same-name parts existing on both HOT-1 and HOT-2:

- 18250-20260923_16026_16026_0

- 18250-20260923_16037_16042_1

- 18250-20260923_16040_16040_0

For all three parts, the HOT-1 copies contained 0 rows (36 KB), while the HOT-2 copies contained actual event data (70, 39,500 and 13,422 rows, respectively).

With ClickHouse stopped on the Worker, first create a backup of the three zero-row HOT-1 copies. Then remove only those three HOT-1 copies and preserve the data-bearing HOT-2 copies.

After the cleanup, ClickHouse started successfully on the Worker. The previous DUPLICATE_DATA_PART/Different checksum startup error is no longer occurring.