Connection to ClickHouse via port 9004 (MySQL protocol) from PowerBI is not working

[areshayevcaci]

You have to provide the following information whenever possible. Describe what's wrong cannot connect to ClickHouse from PowerBI via MySQL port 9004 different syntax errors

A clear and concise description of what works not as it is supposed to. ClickHouse version 24.3.2.23 (official build) PowerBI Desktop: 2.127.1327.0 64-bit (March 2024) cannot setup connection from PowerBI to ClickHouse via port 9004 (MySQL protocol)

How to reproduce Scenario 1:

• PowerBI - New Report, Get Data - MySQL server
• Connection Details: Server: mytestch.uk.cloudapp.azure.com:9004 Database: dev Include Relationship Columns: FALSE/TRUE Navigate Using full Hierarchy: FALSE Command timeout: empty SQL statement: empty Connection type: Database Username: test; Password: %%%%%
• ERROR: _"MySQL: Code: 47. DB::Exception: Unknown expression identifier 'CREATE_TIME' in scope SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, CREATE_TIME AS CREATED_DATE, UPDATE_TIME AS MODIFIED_DATE, TABLE_COMMENT AS DESCRIPTION FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'dev'. Maybe you meant: ['CREATED_DATE']. (UNKNOWNIDENTIFIER) (version 24.3.2.23 (official build))"

Scenario 2:

• PowerBI - New Report, Get Data - MySQL server
• Connection Details: Server: mytestch.uk.cloudapp.azure.com:9004 Database: dev Include Relationship Columns: FALSE/TRUE Navigate Using full Hierarchy: TRUE Command timeout: empty SQL statement: empty Connection type: Database Username: test; Password: %%%%%
• ERROR: "Details: "An error happened while reading data from the provider: 'Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.'"

Scenario 3:

• PowerBI - New Report, Get Data - MySQL server

• Connection Details: Server: mytestch.uk.cloudapp.azure.com:9004 Database: dev Include Relationship Columns: FALSE Navigate Using full Hierarchy: FALSE Command timeout: empty SQL statement: select * from mysql_test Connection type: Database Username: test; Password: %%%%%

• ERROR: Details: "MySQL: Code: 62. DB::Exception: Syntax error: failed at position 6 ('COLLATION'): COLLATION. Expected one of: CREATE, FULL, DATABASES, CLUSTERS, MERGES, FILESYSTEM CACHES, CLUSTER, CHANGED, SETTINGS, TABLES, DICTIONARIES, EXTENDED, COLUMNS, FIELDS, ENGINES, FUNCTIONS, INDEX, INDEXES, INDICES, KEYS, SETTING, CREATE, DATABASE, DICTIONARY, VIEW, TABLE, PROCESSLIST, ACCESS, USERS, ROLES, SETTINGS PROFILES, PROFILES, ROW POLICIES, POLICIES, QUOTAS, CURRENT ROLES, ENABLED ROLES, CURRENT QUOTA, QUOTA, GRANTS, PRIVILEGES."

• Which ClickHouse server version to use: 24.3.2.23 (official build)

• Which interface to use, if it matters: MySQL, Port 9004

• CREATE TABLE statements for all tables involved

create table mysql_test (id UInt32 not null, code Nullable(String)) Engine = MergeTree() order by (id);

• Sample data for all these tables, use [clickhouse-obfuscator]

  
  insert into mysql_test values (1,'1');
  insert into mysql_test values (2,'2');
  insert into mysql_test values (3,'3');

* Error Stack:
0. DB::Exception::Exception(DB::Exception::MessageMasked&&, int, bool) @ 0x000000000cbcedbb
1. DB::Exception::Exception<String>(int, FormatStringHelperImpl<std::type_identity<String>::type>, String&&) @ 0x000000000765c563
2. DB::DataTypeNullable::DataTypeNullable(std::shared_ptr<DB::IDataType const> const&) @ 0x00000000100a1528
3. std::shared_ptr<DB::DataTypeNullable> std::allocate_shared[abi:v15000]<DB::DataTypeNullable, std::allocator<DB::DataTypeNullable>, std::shared_ptr<DB::IDataType const> const&, void>(std::allocator<DB::DataTypeNullable> const&, std::sh
ared_ptr<DB::IDataType const> const&) @ 0x000000000e5a6872
4. DB::InterpreterCreateQuery::getColumnsDescription(DB::ASTExpressionList const&, std::shared_ptr<DB::Context const>, bool, bool) @ 0x0000000010b6573f
5. DB::InterpreterCreateQuery::getTablePropertiesAndNormalizeCreateQuery(DB::ASTCreateQuery&) const @ 0x0000000010b69302
6. DB::InterpreterCreateQuery::createTable(DB::ASTCreateQuery&) @ 0x0000000010b71834
7. DB::InterpreterCreateQuery::execute() @ 0x0000000010b842cf
8. DB::SystemLog<DB::MetricLogElement>::prepareTable() @ 0x0000000011041b2b
9. DB::SystemLog<DB::MetricLogElement>::savingThreadFunction() @ 0x00000000110437cb
10. void std::__function::__policy_invoker<void ()>::__call_impl<std::__function::__default_alloc_func<ThreadFromGlobalPoolImpl<true>::ThreadFromGlobalPoolImpl<DB::SystemLogBase<DB::MetricLogElement>::startup()::'lambda'()>(DB::SystemLog
Base<DB::MetricLogElement>::startup()::'lambda'()&&)::'lambda'(), void ()>>(std::__function::__policy_storage const*) @ 0x000000000ccc37b4
11. void* std::__thread_proxy[abi:v15000]<std::tuple<std::unique_ptr<std::__thread_struct, std::default_delete<std::__thread_struct>>, void ThreadPoolImpl<std::thread>::scheduleImpl<void>(std::function<void ()>, Priority, std::optional<u
nsigned long>, bool)::'lambda0'()>>(void*) @ 0x000000000cc7d9ed
12. ? @ 0x00007b9e76694ac3
13. ? @ 0x00007b9e76726850
 (version 24.3.2.23 (official build))
2024.04.05 12:57:53.346650 [ 6687 ] {} <Error> MySQLHandler: DB::Exception: Cannot read all data. Bytes read: 0. Bytes expected: 3
2024.04.05 12:57:53.419285 [ 6687 ] {} <Error> MySQLHandler: DB::Exception: Cannot read all data. Bytes read: 0. Bytes expected: 3
2024.04.05 12:57:57.105449 [ 4758 ] {} <Error> void DB::SystemLog<DB::MetricLogElement>::flushImpl(const std::vector<LogElement> &, uint64_t) [LogElement = DB::MetricLogElement]: Code: 43. DB::Exception: Nested type LowCardinality(String
) cannot be inside Nullable type. (ILLEGAL_TYPE_OF_ARGUMENT), Stack trace (when copying this message, always include the lines below):

2024.04.05 12:59:52.980016 [ 6766 ] {} <Error> MySQLHandler: MySQLHandler: Cannot read packet: : Code: 62. DB::Exception: Syntax error: failed at position 6 ('COLLATION'): COLLATION. Expected one of: CREATE, FULL, DATABASES, CLUSTERS, ME
RGES, FILESYSTEM CACHES, CLUSTER, CHANGED, SETTINGS, TABLES, DICTIONARIES, EXTENDED, COLUMNS, FIELDS, ENGINES, FUNCTIONS, INDEX, INDEXES, INDICES, KEYS, SETTING, CREATE, DATABASE, DICTIONARY, VIEW, TABLE, PROCESSLIST, ACCESS, USERS, ROLE
S, SETTINGS PROFILES, PROFILES, ROW POLICIES, POLICIES, QUOTAS, CURRENT ROLES, ENABLED ROLES, CURRENT QUOTA, QUOTA, GRANTS, PRIVILEGES. (SYNTAX_ERROR), Stack trace (when copying this message, always include the lines below):

0. DB::Exception::Exception(DB::Exception::MessageMasked&&, int, bool) @ 0x000000000cbcedbb
1. DB::Exception::createDeprecated(String const&, int, bool) @ 0x000000000cc2b04d
2. DB::parseQueryAndMovePosition(DB::IParser&, char const*&, char const*, String const&, bool, unsigned long, unsigned long, unsigned long) @ 0x0000000012c0d90a
3. DB::executeQueryImpl(char const*, char const*, std::shared_ptr<DB::Context>, DB::QueryFlags, DB::QueryProcessingStage::Enum, DB::ReadBuffer*) @ 0x000000001126cdce
4. DB::executeQuery(DB::ReadBuffer&, DB::WriteBuffer&, bool, std::shared_ptr<DB::Context>, std::function<void (DB::QueryResultDetails const&)>, DB::QueryFlags, std::optional<DB::FormatSettings> const&, std::function<void (DB::IOutputForm
at&)>) @ 0x000000001127587e
5. DB::MySQLHandler::comQuery(DB::ReadBuffer&, bool) @ 0x000000001232a4c2
6. DB::MySQLHandler::run() @ 0x0000000012324188
7. Poco::Net::TCPServerConnection::start() @ 0x0000000014c9bef2
8. Poco::Net::TCPServerDispatcher::run() @ 0x0000000014c9cd39
9. Poco::PooledThread::run() @ 0x0000000014d954a1
10. Poco::ThreadImpl::runnableEntry(void*) @ 0x0000000014d93a3d
11. ? @ 0x00007b9e76694ac3
12. ? @ 0x00007b9e76726850
 (version 24.3.2.23 (official build))
2024.04.05 12:59:57.180302 [ 4758 ] {} <Error> void DB::SystemLog<DB::MetricLogElement>::flushImpl(const std::vector<LogElement> &, uint64_t) [LogElement = DB::MetricLogElement]: Code: 43. DB::Exception: Nested type LowCardinality(String
) cannot be inside Nullable type. (ILLEGAL_TYPE_OF_ARGUMENT), Stack trace (when copying this message, always include the lines below):

[slvrtrn]

Have you tried the native Power BI connector?

[areshayevcaci]

yes, we tried Power BI Connector unfortunately there're bugs in Power BI Connector Direct Mode that don't let us use it on product environment for example this one: https://github.com/ClickHouse/power-bi-clickhouse/issues/19 is show stopper

[slvrtrn]

@areshayevcaci, I think the right course of action here is to resolve the existing issues with the Power BI connector cause we don't guarantee that the MySQL interface will work with Power BI - the primary use case for it is the other BI tools that don't support native ClickHouse connectors at all: QuickSight, Looker (not Studio), and Tableau Online.

It also reduces the time to deliver the fixes if we do it in the connector instead - for changes in the MySQL interface, we are tied to the ClickHouse release cadence and cannot react faster if needed.

Regarding the bugs with the connector, is it only that one that you linked or others? If there were more, it would be beneficial if you could create the related issues in the connector there. I will pass it on to the team, and we will investigate.

[areshayevcaci]

on slack help thread, when similar connection issue was discussed, Ryadh Dahimene (ClickHouse Inc) mentioned MySQL interface as a way for BI tools

_Ryadh Dahimene (ClickHouse Inc) 4 months ago We recently improved the MySQL interface support specifically for BI scenarios, so MyQSL is the way to go_

we are trying to use PowerBI and Clickhouse together and I'm happy to raise separate issues about PowerBI Native connector and ODBC driver