Connecting to clickhouse db with mysql protocol

[mxfwang]

Hi, I'm trying to read from a clickhouse database and got this error

RuntimeError: MySqlError { ERROR 48 (00000): Code: 48. DB::Exception: Command [ERRFMT] is not implemented. (NOT_IMPLEMENTED) (version 21.11.3.6 (official build)) }

I'm running very simple queries like SELECT 1

I'm not sure if this error is from clickhouse, or connectorx?

My setup is also very simple. I spinned up a local clickhouse on my machine and enabled mysql port 9004 like described in this page, exposed the port, then did this

import connectorx as cx
url = "mysql://default:@<hostname>:9004/"
query = "SELECT 1"
move = cx.read_sql(url, query)

[wangxiaoying]

Hi @mxfwang , can you add protocol= text in read_sql function and try again? Clickhouse only support text protocol and we use binary protocol by default in ConnectorX. More info can be found here.

[mxfwang]

That was it. Thank you!

[jeeinn]

if with PDO， how to config?

[wangxiaoying]

We haven't test on it. If they share the same protocol, can you try the method above? Or setting the schema to clickhouse like clickhouse://username:password@host:port/db? Internally we will transfer it to url = mysql://username:password@host:port/db and protocol=text.

[am000k]

Hi, I am also trying to test the connector with clickhouse

import connectorx as cx
url = "mysql://default:@hostname:9004/"
query = "SELECT 1"
move = cx.read_sql(url, query, protocol="text")

and I get the error:
RuntimeError: MySqlError { ERROR 48 (00000): Code: 48. DB::Exception: Command [ERRFMT] is not implemented. (NOT_IMPLEMENTED) (version 22.1.3.7 (official build)) }

[wangxiaoying]

Hi @am000k , previously we updated the schema fetching using the prepared statement for mysql protocol and it turns out clickhouse's mysql interface does not support prepared queries. We are now adding the original limit1 method to get the schema when prepared statement is not supported and will release a new alpha version. Will let you know when it's ready. In the meanwhile, versions <=0.2.1 should works for clickhouse.

[wangxiaoying]

@am000k the new alpha version 0.2.4a7 is ready. Can you try whether it works?

[am000k]

Hi wangxiaoying, the alpha version works fine.
Thanks.