使用DBeaver通过MySQL驱动连接ClickHouse失败求解决方案

问题详情 #

我尝试使用DBeaver（MySQL驱动版本8.0.29）连接ClickHouse，但连接失败，报错日志如下：

2023.03.23 16:27:55.522982 [ 4356 ] {} <Error> MySQLHandler: MySQLHandler: Cannot read packet: : Code: 60. DB::Exception: Table INFORMATION_SCHEMA.KEYWORDS doesn't exist. (UNKNOWN_TABLE), Stack trace (when copying this message, always include the lines below):

0. ./build_docker/../src/Common/Exception.cpp:91: DB::Exception::Exception(DB::Exception::MessageMasked&&, int, bool) @ 0xe12b715 in /usr/bin/clickhouse
1. DB::Exception::Exception<std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char>>>(int, FormatStringHelperImpl<std::__1::type_identity<std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char>>>::type>, std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char>>&&) @ 0x8999504 in /usr/bin/clickhouse
2. ./build_docker/../src/Interpreters/DatabaseCatalog.cpp:0: DB::DatabaseCatalog::getTableImpl(DB::StorageID const&, std::__1::shared_ptr<DB::Context const>, std::__1::optional<DB::Exception>*) const @ 0x12d2af9a in /usr/bin/clickhouse
3. ./build_docker/../contrib/llvm-project/libcxx/include/__memory/shared_ptr.h:801: DB::DatabaseCatalog::getTable(DB::StorageID const&, std::__1::shared_ptr<DB::Context const>) const @ 0x12d31c6a in /usr/bin/clickhouse
4. ./build_docker/../src/Interpreters/JoinedTables.cpp:0: DB::JoinedTables::getLeftTableStorage() @ 0x13807e34 in /usr/bin/clickhouse
5. ./build_docker/../src/Interpreters/InterpreterSelectQuery.cpp:0: DB::InterpreterSelectQuery::InterpreterSelectQuery(std::__1::shared_ptr<DB::IAST> const&, std::__1::shared_ptr<DB::Context> const&, std::__1::optional<DB::Pipe>, std::__1::shared_ptr<DB::IStorage> const&, DB::SelectQueryOptions const&, std::__1::vector<std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char>>, std::__1::allocator<std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char>>>> const&, std::__1::shared_ptr<DB::StorageInMemoryMetadata const> const&, std::__1::shared_ptr<DB::PreparedSets>) @ 0x1372dcb2 in /usr/bin/clickhouse
6. ./build_docker/../contrib/llvm-project/libcxx/include/optional:260: DB::InterpreterSelectWithUnionQuery::buildCurrentChildInterpreter(std::__1::shared_ptr<DB::IAST> const&, std::__1::vector<std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char>>, std::__1::allocator<std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char>>>> const&) @ 0x137c4822 in /usr/bin/clickhouse
7. ./build_docker/../src/Interpreters/InterpreterSelectWithUnionQuery.cpp:0: DB::InterpreterSelectWithUnionQuery::InterpreterSelectWithUnionQuery(std::__1::shared_ptr<DB::IAST> const&, std::__1::shared_ptr<DB::Context>, DB::SelectQueryOptions const&, std::__1::vector<std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char>>, std::__1::allocator<std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char>>>> const&) @ 0x137c27ca in /usr/bin/clickhouse
8. ./build_docker/../contrib/llvm-project/libcxx/include/vector:434: DB::InterpreterFactory::get(std::__1::shared_ptr<DB::IAST>&, std::__1::shared_ptr<DB::Context>, DB::SelectQueryOptions const&) @ 0x136e9750 in /usr/bin/clickhouse
9. ./build_docker/../src/Interpreters/executeQuery.cpp:0: DB::executeQueryImpl(char const*, char const*, std::__1::shared_ptr<DB::Context>, bool, DB::QueryProcessingStage::Enum, DB::ReadBuffer*) @ 0x13ae5460 in /usr/bin/clickhouse
10. ./build_docker/../contrib/llvm-project/libcxx/include/__memory/shared_ptr.h:612: DB::executeQuery(DB::ReadBuffer&, DB::WriteBuffer&, bool, std::__1::shared_ptr<DB::Context>, std::__1::function<void (DB::QueryResultDetails const&)>, std::__1::optional<DB::FormatSettings> const&) @ 0x13aeb6e9 in /usr/bin/clickhouse
11. ./build_docker/../contrib/llvm-project/libcxx/include/optional:260: DB::MySQLHandler::comQuery(DB::ReadBuffer&) @ 0x14867573 in /usr/bin/clickhouse
12. ./build_docker/../src/Server/MySQLHandler.cpp:175: DB::MySQLHandler::run() @ 0x14863fee in /usr/bin/clickhouse
13. ./build_docker/../base/poco/Net/src/TCPServerConnection.cpp:57: Poco::Net::TCPServerConnection::start() @ 0x177a6634 in /usr/bin/clickhouse
14. ./build_docker/../contrib/llvm-project/libcxx/include/__memory/unique_ptr.h:48: Poco::Net::TCPServerDispatcher::run() @ 0x177a785b in /usr/bin/clickhouse
15. ./build_docker/../base/poco/Foundation/src/ThreadPool.cpp:202: Poco::PooledThread::run() @ 0x1792f0a7 in /usr/bin/clickhouse
16. ./build_docker/../base/poco/Foundation/include/Poco/SharedPtr.h:231: Poco::ThreadImpl::runnableEntry(void*) @ 0x1792cadd in /usr/bin/clickhouse
17. ? @ 0x7f8bb062c802 in ?
18. ? @ 0x7f8bb05cc450 in ?
 (version 23.3.1.755 (official build))

但通过Powershell命令行使用MySQL客户端可成功连接：

mysql -h **.***.**.*** -P 9004 -u admin --password=******

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 74
Server version: 23.3.1.755-ClickHouse

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

报错原因 #

ClickHouse的MySQL兼容协议层（默认端口9004）仅实现了MySQL核心交互逻辑，并未完全兼容所有INFORMATION_SCHEMA系统表。DBeaver在建立连接时会自动查询INFORMATION_SCHEMA.KEYWORDS表以获取SQL关键字用于语法提示等功能，而该表在ClickHouse中不存在，因此触发报错；而命令行MySQL客户端不会自动执行该查询，所以能正常连接。

解决方案 #

方案1：修改MySQL驱动连接属性，禁用关键字查询 #

1. 在DBeaver中打开该ClickHouse连接的「编辑连接」窗口
2. 切换到「驱动属性」标签页
3. 找到并修改以下两个属性：
   • 将useInformationSchema设置为false
   • 将nullNamePatternMatchesAll设置为true
4. 保存设置后，重新尝试连接即可

方案2：使用ClickHouse官方驱动连接（推荐） #

1. 在DBeaver中新建连接时，直接选择「ClickHouse」驱动（若未安装，可通过DBeaver的驱动市场搜索安装）
2. 配置连接信息：主机地址、端口（默认8123）、用户名、密码
3. 测试连接并完成创建

内容的提问来源于stack exchange，提问作者weipengHU