1. Home
  2. Knowledge Base
  3. ClickHouse DBA
  4. Truncate Table Error/Warning In ClickHouse
  1. Home
  2. Knowledge Base
  3. ClickHouse Troubleshooting
  4. Truncate Table Error/Warning In ClickHouse

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>

 

 

Was this article helpful?

Related Articles

CHISTADATA IS COMMITTED TO OPEN SOURCE SOFTWARE AND BUILDING HIGH PERFORMANCE COLUMNSTORES

In the spirit of freedom, independence and innovation. ChistaDATA Corporation is not affiliated with ClickHouse Corporation 

Need Support?

Can't find the answer you're looking for?
Contact Support

ChistaDATA Inc. Knowledge base is licensed under the Apache License, Version 2.0 (the “License”)

Copyright 2022 ChistaDATA Inc

Licensed under the Apache License, Version 2.0 (the “License”); you may not use this file except in compliance with the License. You may obtain a copy of the License at

http://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an “AS IS” BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License.