ClickHouse DB::Exception: Cannot drop function

The "DB::Exception: Cannot drop function" error in ClickHouse appears when you attempt to drop a user-defined function (UDF) and the operation cannot be completed. This might happen because the function does not exist, it is a built-in function, or there are dependency or permission issues. The error code is CANNOT_DROP_FUNCTION.

Impact

The DROP FUNCTION statement fails, and the function remains registered. This is a non-destructive failure. However, if you need to drop the function as part of a migration or cleanup process, your workflow will be blocked until the issue is resolved.

Common Causes

1. Function does not exist -- attempting to drop a UDF that was never created or was already dropped.
2. Trying to drop a built-in function -- built-in ClickHouse functions cannot be dropped.
3. Insufficient permissions -- the user does not have the privilege to drop functions.
4. Function is in use by a materialized view or other object -- while ClickHouse does not always enforce this, dependency conflicts can arise.
5. Cluster synchronization issues -- on a cluster, the function may have been dropped on some nodes but not others.

Troubleshooting and Resolution Steps

1. Check if the function exists and is user-defined:

   SELECT name, origin FROM system.functions WHERE name = 'your_function_name';
   

   If origin is not SQLUserDefined, it is a built-in function and cannot be dropped.

2. Use IF EXISTS to avoid errors when the function may not exist:

   DROP FUNCTION IF EXISTS your_function_name;
   

3. Check your permissions. Verify you have the necessary privileges:

   SHOW GRANTS FOR CURRENT_USER;
   

   You need the DROP FUNCTION privilege or the broader CREATE FUNCTION privilege (which typically covers DROP as well).

4. On a cluster, ensure consistency. Drop the function on all nodes:

   DROP FUNCTION IF EXISTS your_function_name ON CLUSTER 'your_cluster';
   

5. Check for dependencies. If the function is referenced by a materialized view or another UDF, you may need to drop or alter the dependent object first:

   -- Search for references to the function
   SELECT name, create_table_query FROM system.tables
   WHERE create_table_query LIKE '%your_function_name%';
   

6. Verify the function file on disk. UDFs are stored in the user_defined directory within the ClickHouse data path. If the metadata is corrupted, you may need to remove the file manually and restart the server (as a last resort).

Best Practices

• Always use DROP FUNCTION IF EXISTS in scripts to make them idempotent and avoid failures on repeated runs.
• Use ON CLUSTER for DROP FUNCTION on distributed setups to maintain consistency across nodes.
• Before dropping a UDF, search for dependencies in materialized views, other UDFs, and application queries.
• Maintain version-controlled UDF definitions so dropped functions can be easily recreated if needed.
• Restrict DROP FUNCTION privileges to administrators to prevent accidental removal of shared functions.

Frequently Asked Questions

Q: Can I drop a built-in ClickHouse function?
A: No. Built-in functions are part of the ClickHouse binary and cannot be dropped. Only user-defined functions (created with CREATE FUNCTION) can be dropped.

Q: What happens if I drop a UDF that is used in a materialized view?
A: ClickHouse may allow the drop, but subsequent INSERTs into the materialized view's source table will fail because the function no longer exists. Always check for dependencies before dropping a UDF.

Q: How do I drop and recreate a function atomically?
A: Use CREATE OR REPLACE FUNCTION instead of a DROP followed by CREATE. This avoids a window where the function is missing.

Q: The function file exists on disk but DROP FUNCTION still fails. What should I do?
A: This may indicate metadata corruption. As a last resort, stop ClickHouse, remove the function's XML or SQL file from the user_defined directory in the ClickHouse data path, and restart the server. Always back up before manual file operations.