How to enable 'allow_drop_detached' in system.settings?

user_5722909 0
Viewed 1229

What should I specify in config.xml to enable (set to 1) the allow_drop_detached parameter in the system.settings table?

It cannot be changed by 'alter table' request.

Error message:

Code: 344. DB::Exception: Received from localhost:9000. DB::Exception: Cannot execute query: DROP DETACHED PART is disabled (see allow_drop_detached setting).
clickhouse
1 Answers

It should be defined in user.xml not config.xml.

Create an arbitrary file with required changes (here it is query.settings.xml) in directory /etc/clickhouse-server/users.d/:

nano /etc/clickhouse-server/users.d/query.settings.xml

with content (here this param is set only for default-profile):

<?xml version="1.0"?>
<yandex>
    <profiles>
        <default>
            <!-- Allow ALTER TABLE ... DROP DETACHED PART[ITION] ... queries. -->
            <allow_drop_detached>1</allow_drop_detached>
        </default>
    </profiles>
</yandex>

Save this file and check that param is set to 1:

SELECT *
FROM system.settings
WHERE name = 'allow_drop_detached'
/*
┌─name────────────────┬─value─┬─changed─┬─description─────────────────────────────────────────────────┬─min──┬─max──┬─readonly─┐
│ allow_drop_detached │ 1     │       1 │ Allow ALTER TABLE ... DROP DETACHED PART[ITION] ... queries │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │        0 │
└─────────────────────┴───────┴─────────┴─────────────────────────────────────────────────────────────┴──────┴──────┴──────────┘
*/

The alternate ways to set this param are:

  1. query level
ALTER TABLE table
    DROP DETACHED PARTITION '2020-04-01'
SETTINGS allow_drop_detached = 1;
  1. session level
SET allow_drop_detached = 1;

ALTER TABLE table
    DROP DETACHED PARTITION '2020-04-01';
user_3032980
Related