clickhouse context deadline exceeded

[YangNianYi]

log:
ts=2024-03-12T09:53:43.525Z caller=klog.go:94 level=debug func=Infof msg="[target=clickhouse] Database handle successfully opened with 'clickhouse' driver"
ts=2024-03-12T09:53:53.525Z caller=klog.go:134 level=error func=Errorf msg="Error gathering metrics: [from Gatherer #1] [target=clickhouse] context deadline exceeded"

I try the sql use clickhouse-client, execution fast and success.

any idea?

[burningalchemist]

Hey @YangNianYi, It's the connection issue. Could you please share your connection string (with sensitive data redacted)?

[YangNianYi]

clickhouse://user:password@ip:port

@burningalchemist

[burningalchemist]

Try to include the database you're referring to like below:
clickhouse://user:pass@localhost:port/dbname

[YangNianYi]

look like should use port 9000 (I use 8123)

[burningalchemist]

Yeah, true that. clickhouse-go driver uses TCP protocol by default, while HTTP/S protocol is marked as experimental. To use HTTP you need to modify the DSN scheme name, and it might still be unsupported atm in xo/dburl dependency, but it might worth a try, I haven't checked it.

References:

• https://clickhouse.com/docs/en/guides/sre/network-ports
• https://github.com/ClickHouse/clickhouse-go?tab=readme-ov-file#http-support-experimental

[YangNianYi]

another error:

ts=2024-03-12T10:37:16.257Z caller=klog.go:94 level=debug func=Infof msg="[target=clickhouse] Database handle successfully opened with 'clickhouse' driver"
ts=2024-03-12T10:37:16.260Z caller=klog.go:134 level=error func=Errorf msg="Error gathering metrics: [from Gatherer #1] [target=clickhouse] code: 516, message: default: Authentication failed: password is incorrect, or there is no user with such name.\n\nIf you have installed ClickHouse and forgot password you can reset it in the configuration file.\nThe password for default user is typically located at /etc/clickhouse-server/users.d/default-password.xml\nand deleting this file will reset the password.\nSee also /etc/clickhouse-server/users.xml on the server where ClickHouse is installed."

[burningalchemist]

It is the authentication error, and it comes from the driver not the sql_exporter. I guess you need to check the username/password. If your password has special characters - you need to url-encode them. It's written in the README.

[YangNianYi]

user password no special just English characters

[YangNianYi]

I forked the project, and use github.com/ClickHouse/clickhouse-go/v2 instead of github.com/ClickHouse/clickhouse-go.

run in local
got some different error

ts=2024-03-12T10:46:22.116Z caller=klog.go:134 level=error func=Errorf msg="Error gathering metrics: [from Gatherer #1] [target=clickhouse,collector=clickhouse_collector,query=query_name] code: 62, message: Syntax error: failed at position 113 ('VALUES') (line 2, col 2): VALUES. Expected one of: token, Dot, OR, AND, IS NOT DISTINCT FROM, IS NULL, IS NOT NULL, BETWEEN, NOT BETWEEN, LIKE, ILIKE, NOT LIKE, NOT ILIKE, REGEXP, IN, NOT IN, GLOBAL IN, GLOBAL NOT IN, MOD, DIV, alias, AS, Comma, WITH, HAVING, WINDOW, ORDER BY, LIMIT, OFFSET, FETCH, SETTINGS, UNION, EXCEPT, INTERSECT, INTO OUTFILE, FORMAT, end of query"
ts=2024-03-12T10:46:22.117Z caller=klog.go:134 level=error func=Errorf msg="Error gathering metrics: [from Gatherer #1] [target=clickhouse,collector=clickhouse_collector,query=query_name] only Exec method supported in batch mode"
ts=2024-03-12T10:46:22.117Z caller=klog.go:134 level=error func=Errorf msg="Error gathering metrics: [from Gatherer #1] [target=clickhouse,collector=clickhouse_collector,query=query_name] code: 62, message: Syntax error: failed at position 225 ('VALUES') (line 3, col 2): VALUES. Expected one of: token, Dot, OR, AND, IS NOT DISTINCT FROM, IS NULL, IS NOT NULL, BETWEEN, NOT BETWEEN, LIKE, ILIKE, NOT LIKE, NOT ILIKE, REGEXP, IN, NOT IN, GLOBAL IN, GLOBAL NOT IN, MOD, DIV, alias, AS, Comma, WITH, HAVING, WINDOW, ORDER BY, LIMIT, OFFSET, FETCH, SETTINGS, UNION, EXCEPT, INTERSECT, INTO OUTFILE, FORMAT, end of query"

[burningalchemist]

Clickhouse-wise, I can't recommend anything, as I don't use Clickhouse in my available environments. General assumption, is that not every query passed might be supported - because if the native client supports multiple statements, sql/database interface usually doesn't, and there might be additional constraints on the driver end.

The best recommendation so far I can give is - use xo/usql cli to debug the connection and queries before passing it to sql_exporter.

[burningalchemist]

@YangNianYi did it work out for you eventually?

[YangNianYi]

with github.com/ClickHouse/clickhouse-go/v2
tcp port 9000
query: no_prepared_statement: true

success finally. @burningalchemist

[YangNianYi]

but with github.com/ClickHouse/clickhouse-go
I still got error

ts=2024-03-13T02:05:33.143Z caller=klog.go:94 level=debug func=Infof msg="[target=clickhouse] Database handle successfully opened with 'clickhouse' driver"
ts=2024-03-13T02:05:41.150Z caller=klog.go:134 level=error func=Errorf msg="Error gathering metrics: [from Gatherer #1] [target=clickhouse] code: 516, message: default: Authentication failed: password is incorrect, or there is no user with such name.\n\nIf you have installed ClickHouse and forgot password you can reset it in the configuration file.\nThe password for default user is typically located at /etc/clickhouse-server/users.d/default-password.xml\nand deleting this file will reset the password.\nSee also /etc/clickhouse-server/users.xml on the server where ClickHouse is installed."

[burningalchemist]

The driver must be old and unsupported for the new version of Clickhouse it seems.

It's also marked as deprecated in favour of v2.

I updated the version in the upstream. Thanks for checking it! 👏👍

[burningalchemist]

The solution is to use go-clickhouse v2 with unprepared statement connection parameter (due to the issue in the driver) as stated here:

#465 (reply in thread)

[YangNianYi]

@burningalchemist new release?

[burningalchemist]

Yeah, will release shortly today/tomorrow. 🙂

[YangNianYi]

thx for your job.

[burningalchemist]

The new version is available (0.14.1).