All posts
ClickHouse DROP TABLE Fails: Table Is Too Large to Drop

ClickHouse DROP TABLE Fails: Table Is Too Large to Drop

November 18, 20268 min readMohamed Hussain S
Share:

You run DROP TABLE, TRUNCATE or ALTER TABLE ... DROP PARTITION. ClickHouse refuses. It returns error code 359.

Code: 359. DB::Exception: Table or Partition in a8_test.t_a was not dropped.
Reason:
1. Size (10.45 MB) is greater than max_[table/partition]_size_to_drop (5.00 MB)
2. File '/var/lib/clickhouse/flags/force_drop_table' intended to force DROP doesn't exist
How to fix this:
1. Either increase (or set to zero) max_[table/partition]_size_to_drop in server config
2. Either pass a bigger (or set to zero) max_[table/partition]_size_to_drop through query settings
3. Either create forcing file /var/lib/clickhouse/flags/force_drop_table and make sure that ClickHouse has write permission for it.
Example:
sudo touch '/var/lib/clickhouse/flags/force_drop_table' && sudo chmod 666 '/var/lib/clickhouse/flags/force_drop_table'. (TABLE_SIZE_EXCEEDS_MAX_DROP_SIZE_LIMIT)

I used a 5 MB limit to keep the test data small. On your server, the default limit is 50 GB.

I see this error most often on system log tables and old event tables. Nothing is broken. ClickHouse did what it was told to do.

The error text lists three fixes. This article explains each fix and tells you which one to use.

Why it happens

ClickHouse has two server-wide limits. They block a drop when the object is bigger than the limit.

  • max_table_size_to_drop limits the size of a table.
  • max_partition_size_to_drop limits the size of a partition.

The limits protect you from accidental data loss. A wrong table name in a console is easy to type. A drop of a large table can be hard to undo.

The limits are server settings. They apply to every user on the server.

These statements returned the error in my tests:

  • DROP TABLE
  • TRUNCATE TABLE
  • ALTER TABLE ... DROP PARTITION
  • ALTER TABLE ... DETACH PARTITION
  • DROP DATABASE, when the database holds a table above the limit

DETACH PARTITION surprises many people. It keeps the data, but the limit still blocks it.

The size in the error is the sum of bytes_on_disk for the active parts in system.parts.

How to confirm

First, find the largest tables. Use system.parts.

SELECT
    database,
    table,
    formatReadableSize(sum(bytes_on_disk)) AS size,
    sum(bytes_on_disk) AS bytes
FROM system.parts
WHERE active
GROUP BY database, table
ORDER BY bytes DESC
LIMIT 10;

For DROP PARTITION, check the size of each partition. Replace the database and table names.

SELECT
    partition,
    formatReadableSize(sum(bytes_on_disk)) AS size,
    sum(bytes_on_disk) AS bytes
FROM system.parts
WHERE active
  AND database = 'default'
  AND table = 'events'
GROUP BY partition
ORDER BY bytes DESC;

Next, read the limits that your server uses now.

SELECT name, value, `default`, changeable_without_restart
FROM system.server_settings
WHERE name IN ('max_table_size_to_drop', 'max_partition_size_to_drop');

Use system.server_settings for the server limit. The system.settings table also lists these two names, but it shows the session value. In my test, it showed 50 GB while the server limit was 5 MB.

Compare the bytes value with the limit. If the size is larger than the limit, you found the cause.

The settings

SettingDefaultWhat it controlsRestart needed
max_table_size_to_drop50000000000 (50 GB)Largest table, in bytes, that you can drop or truncate. Value 0 means no limit.No
max_partition_size_to_drop50000000000 (50 GB)Largest partition, in bytes, that you can drop or detach. Value 0 means no limit.No

I confirmed these defaults on 26.8.39.19. Defaults can change between releases. Check your own server with system.server_settings.

The changeable_without_restart column shows Yes for both settings. The two limits work on their own. You can change one and keep the other.

The same two names also exist as query settings. A query setting changes the limit for one session. See Fix 4.

How to fix it

You have four options. For a one-time drop, use the flag file (Fix 2).

Fix 1: Increase the limit

Use this fix when you drop large objects often. An example is a daily partition of 80 GB.

Create the file /etc/clickhouse-server/config.d/drop_limits.xml.

<clickhouse>
    <max_table_size_to_drop>200000000000</max_table_size_to_drop>
    <max_partition_size_to_drop>100000000000</max_partition_size_to_drop>
</clickhouse>

The values are examples. Pick a table limit above your largest planned drop. Pick a partition limit above your largest partition. A limit close to your real data keeps some protection.

No restart is necessary. Run this command to load the file now.

SYSTEM RELOAD CONFIG;

Fix 2: Create the flag file (best for a one-time drop)

Use this fix for one planned drop. The server keeps the limits for all other drops.

Create the flag file in the flags folder of the data directory. The default path is /var/lib/clickhouse/flags/force_drop_table. Use the path from your own error message.

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

ClickHouse must have write permission for the file. It needs this permission to delete the file.

Run your DROP statement now. No restart and no config change is necessary. One flag file works for DROP TABLE, TRUNCATE and DROP PARTITION.

ClickHouse deletes the flag file after it uses the flag. For the next large drop, you must create the file again.

There is one exception. ClickHouse does not delete the flag after a drop that is below the limit. See the risks section.

Fix 3: Set the limit to 0

Use this fix only on a test or staging server. The value 0 disables the check.

<clickhouse>
    <max_table_size_to_drop>0</max_table_size_to_drop>
    <max_partition_size_to_drop>0</max_partition_size_to_drop>
</clickhouse>

Save the file as /etc/clickhouse-server/config.d/drop_limits.xml. No restart is necessary. Run SYSTEM RELOAD CONFIG; to load it.

You can set only one of the two limits to 0. The other limit stays active.

Do not leave this file on a production server. See the risks section.

Fix 4: Set the limit for one session

Use this fix when you cannot edit the server config. Your change affects only your own session.

Run the setting and the drop in the same session.

SET max_table_size_to_drop = 0;
DROP TABLE default.old_events SYNC;

In my test on 26.8, this removed a table that was above the server limit. I tested this with DROP TABLE and the value 0 only.

The setting ends when your session ends. Use a value that is just above the table size if you know it.

How to verify the fix

First, confirm that the server loaded the new limits.

SELECT name, value
FROM system.server_settings
WHERE name IN ('max_table_size_to_drop', 'max_partition_size_to_drop');

Then run your statement again.

ALTER TABLE default.events DROP PARTITION 202609;

This example assumes a monthly partition key, such as toYYYYMM(date).

If you used the flag file, check that ClickHouse removed it.

ls -l /var/lib/clickhouse/flags/force_drop_table

The command must report that the file does not exist. If the file is still there, remove it yourself.

Last, check the free space on disk. ClickHouse can delete the data a little later, so the disk can free up with a short delay.

SELECT name, formatReadableSize(free_space) AS free
FROM system.disks;

Risks and trade-offs

A limit of 0 removes the protection for all users. Any user with the DROP privilege can then remove any table, with no size check. One typing error can delete a full table.

A session setting can bypass the server limit. A user who can change query settings can set the limit to 0 for their own session. The server limit protects against mistakes. It does not stop a user who wants to bypass it.

A high limit protects less. A limit of 10 TB blocks almost nothing. Set the limit close to your real needs.

The flag file can stay on disk. In my test, a drop of a small table left the flag in place. The next large drop then passes without a check. Always check that the file is gone.

The flag file must be writable. The chmod 666 command gives write access to every local user. Remove the file yourself if you did not run the drop.

Limits apply to one server. These are server settings. Change the config on every node that needs the change.

Take a backup first. A drop of a large table can be hard to undo. Use BACKUP before you drop data that you may need later.

Big tables often come from other problems. System logs can grow until they fill the disk. A runaway query can also write or scan far too much data. Fix the cause, or the table will grow again.

Summary

  • max_table_size_to_drop and max_partition_size_to_drop block drops of objects larger than 50 GB by default.
  • The error code is 359, TABLE_SIZE_EXCEEDS_MAX_DROP_SIZE_LIMIT. Find the real size in system.parts.
  • For a one-time drop, create force_drop_table in the flags folder. ClickHouse deletes it after use, but not after a small drop.
  • For regular drops, increase the limit in config.d/. No restart is necessary. For one session, use SET max_table_size_to_drop.
  • Do not set the limit to 0 on production. It removes the protection for all users.

Reference

Share: