Truncate Table Error/Warning In ClickHouse

Error/Warning

If table size bigger than your max_table_size_to_drop parameter’s value, you will get an error like this:

“Code: 359. DB::Exception: Received from localhost:9000. DB::Exception: Table or Partition in StarSchema.lineorder_flat was not dropped.”

In this article you will able to fix this issue.

automation.internal :) truncate table lineorder_flat

TRUNCATE TABLE lineorder_flat

Query id: 5027febe-49de-46a9-af4a-ae87d5852bb3


Elapsed: 0.015 sec. 

Received exception from server (version 24.2.1):
Code: 359. DB::Exception: Received from localhost:9000. DB::Exception: Table or Partition in StarSchema.lineorder_flat was not dropped.
Reason:
1. Size (580.34 GB) is greater than max_[table “” not found /]

_size_to_drop (50.00 GB)
2. File '/var/lib/clickhouse/flags/force_drop_table' intended to force DROP doesn't exist

Solution 1:

Create forcing file /var/lib/clickhouse/flags/force_drop_table and make sure that ClickHouse has write permission for it.

sudo touch '/var/lib/clickhouse/flags/force_drop_table' && sudo chmod 666 '/var/lib/clickhouse/flags/force_drop_table'

Solution 2:

Set a bigger value or set to zero max_table_size_to_drop through query settings.

Zero means unlimited.

<max_table_size_to_drop>0</max_table_size_to_drop>

Or set a bigger value. Example below shows that value has been set for 50 GB.

<max_table_size_to_drop>5000000000</max_table_size_to_drop>

How to calculate max_table_size_to_drop

You should run the following command to check your table size.

SELECT
    formatReadableSize(sum(bytes_on_disk)),
    round(((sum(bytes_on_disk) / 1000) / 1000) / 1000, 2) AS GB
FROM system.parts
WHERE table = 'lineorder_flat';

Result:

Query id: 5646b75c-c786-4c25-ad41-d6441b15e782

┌─formatReadableSize(sum(bytes_on_disk))─┬─────GB─┐
│ 540.48 GiB                             │ 580.34 │
└────────────────────────────────────────┴────────┘

Now you can calculate your parameter value with the example given below;

Please consider “formatReadableSize(sum(bytes_on_disk))” value.

540.48 * 1024 / 1000 = 554.1888 GB GB.

Your “max_table_size_to_drop” parameter value should be at least 554.1888 in this case.

<max_table_size_to_drop>555000000000</max_table_size_to_drop>