clickhouse 报错 Not enough privileges. To execute this query, it‘s necessary to have the grant

问题描述

clickhouse执行命令
CREATE DATABASE hzh_test
ENGINE = MaterializeMySQL(‘192.168.1.1:3306’, ‘temp’, ‘root’, ‘123456’);
时报错

SQL 错误 [497] [07000]: Code: 497. DB::Exception: root: Not enough privileges.
 To execute this query, it's necessary to have the grant CREATE DATABASE ON 
 hzh_test.*. (ACCESS_DENIED) (version 24.12.2.29 (official build))
, server ClickHouseNode [uri=http://192.168.148.116:8123/default, options=
{socket_timeout=300000,session_id=gb,use_server_time_zone=false,use_time_zone=false}]
@-563396697

问题原因

该命令尝试使用 ClickHouse 的 MaterializedMySQL 数据库引擎，但该功能是实验性的，默认情况下未启用。错误信息提示需要启用 allow_experimental_database_materialized_mysql 设置。

问题解决

1、临时启用

SET allow_experimental_database_materialized_mysql = 1; 仅对当前对话生效

2、永久启用

1)、进入配置文件users.xml

vim /etc/clickhouse-server/users.xml

2)、增加以下配置

    <profiles>
        <default>
            <allow_experimental_database_materialized_mysql>1</allow_experimental_database_materialized_mysql>
        </default>
    </profiles>

3)、重启服务，并且检查服务状态，确保服务正常运行：

sudo systemctl restart clickhouse-server
sudo systemctl status clickhouse-server

4)、重新执行命令即可