ClickHouse DB::Exception: Settings profile does not exist

The THERE_IS_NO_PROFILE error occurs when ClickHouse encounters a reference to a settings profile that does not exist. The error message typically looks like DB::Exception: Settings profile 'analytics' doesn't exist. This can happen when a user is assigned a profile that was never created, was deleted, or is misspelled in the configuration.

Impact

If the missing profile is referenced in a user definition, that user may be unable to connect or may fall back to the default profile (depending on the ClickHouse version and configuration). When the error occurs during server startup -- because the missing profile is referenced in users.xml -- the server may log errors but usually still starts. However, the affected users will not have the expected settings applied, which can lead to unexpected query behavior or resource consumption.

Common Causes

1. Typo in the profile name -- The profile name in the user definition does not match any defined profile (e.g., anaytics instead of analytics).
2. Profile was deleted but still referenced -- A profile was removed from users.xml or dropped via SQL, but users or roles still reference it.
3. Mixed configuration sources -- The profile is defined in SQL-based access control but the user references it from users.xml, or vice versa.
4. Configuration file not loaded -- The file defining the profile (e.g., a fragment in users.d/) failed to load or was accidentally removed.
5. Case sensitivity -- Profile names are case-sensitive. Default and default are treated as different profiles.

Troubleshooting and Resolution Steps

1. List all available profiles:

   -- SQL-based profiles
   SHOW SETTINGS PROFILES;
   
   -- Or query the system table
   SELECT name FROM system.settings_profiles;
   

   For XML-defined profiles, check users.xml:

   grep -A1 '<profiles>' /etc/clickhouse-server/users.xml
   

2. Find where the missing profile is referenced:

   grep -r 'analytics' /etc/clickhouse-server/users.xml /etc/clickhouse-server/users.d/
   

   Also check SQL-based user definitions:

   SHOW CREATE USER my_user;
   

3. Create the missing profile: If the profile should exist but was never created:

   CREATE SETTINGS PROFILE 'analytics'
   SETTINGS max_memory_usage = 10000000000,
            max_execution_time = 300;
   

   Or add it to users.xml:

   <profiles>
       <analytics>
           <max_memory_usage>10000000000</max_memory_usage>
           <max_execution_time>300</max_execution_time>
       </analytics>
   </profiles>
   

4. Fix the reference if the name is wrong: Update the user definition to point to the correct profile name:

   ALTER USER my_user SETTINGS PROFILE 'correct_profile_name';
   

5. Reload configuration after XML changes:

   SYSTEM RELOAD CONFIG;
   

6. Check for case sensitivity issues:

   SELECT name FROM system.settings_profiles WHERE name ILIKE '%analytics%';
   

Best Practices

• Establish a naming convention for profiles (e.g., always lowercase with underscores) to avoid case-sensitivity issues.
• Before deleting a profile, search for all references to it in user definitions, role grants, and configuration files.
• Use SQL-based access control consistently rather than mixing XML and SQL profile definitions, to reduce confusion.
• Document your profiles and their intended use cases so that administrators know which ones are active.
• Test profile changes in a staging environment before applying them in production.

Frequently Asked Questions

Q: Will the server crash if a referenced profile does not exist?
A: No, the server typically continues to start. However, the affected user will either be unable to connect or will use default settings, depending on the version and how the profile is referenced.

Q: Can I inherit one profile from another?
A: Yes. In SQL-based access control, use CREATE SETTINGS PROFILE 'child' SETTINGS INHERIT 'parent'. In XML, use the <profile> tag inside another profile definition to reference a parent. Make sure the parent profile exists.

Q: How do XML-defined profiles interact with SQL-defined profiles?
A: They coexist but live in separate namespaces internally. A user defined in users.xml can reference an XML-defined profile. A user created with CREATE USER can reference a SQL-defined profile. Mixing the two (e.g., an XML user referencing a SQL profile) may not work as expected and is generally discouraged.

Q: I see the profile in users.xml but ClickHouse says it does not exist. Why?
A: Check that the XML file is syntactically valid and was actually loaded. Look at the server log for config loading errors. Also verify that the profile is inside the correct <profiles> section and that the XML structure is properly nested.