Cannot run query on clickhouse

[yasharne]

Hi I have this configuration:

    - metric_name: not_available_status
      type: gauge
      help: 'Not Available Drivers From Base-API Database'
      values: ['not_available_total']
      query: select count() as not_available_total from products where status=1

But I get this error:

ts=2024-05-29T12:13:55.489Z caller=klog.go:134 level=error func=Errorf msg="Error gathering metrics: [from Gatherer #1] [collector=custom_metrics query=not_available_status] code: 62, message: Syntax error: failed at position 67 ('VALUES'): VALUES. Expected one of: token, DoubleColon, MOD, DIV, NOT, BETWEEN, LIKE, ILIKE, NOT LIKE, NOT ILIKE, IN, NOT IN, GLOBAL IN, GLOBAL NOT IN, IS, AND, OR, QuestionMark, alias, AS, GROUP BY, WITH, HAVING, WINDOW, ORDER BY, LIMIT, OFFSET, SETTINGS, UNION, EXCEPT, INTERSECT, INTO OUTFILE, FORMAT, end of query"

I'm using version 0.14.3 and the query works as expected on clickhouse api

[burningalchemist]

Hi @yasharne from the error it actually looks to be a syntax error.

I'm also confused it mentions VALUES in the error although it's not used in the query provided. Could you also finish the query with a semicolon in the example (whether it's related or not - just noticed)?

There is no special processing on the query in the sql_exporter, so I'd suggest checking the query again (maybe you have some other queries in the list), or you can debug with xo/usql to check the query outside of the sql_exporter in the cli way but still with the go library.

[yasharne]

Hi @burningalchemist
I tried with semicolon, and the error changed:

code: 62, message: Syntax error (Multi-statements are not allowed): failed at position 66 (end of query): ; VALUES."

BTW, this is the only query I have in the file, so no there are no other config that can affect this query
Also xo/usql works as expected:

Also this is the same error as I get: #465 (reply in thread) But then I try no_prepared_statement in the connection string, I end up with this error:

code: 115, message: Unknown setting no_prepared_statements

[burningalchemist]

Hey @yasharne, try to add no_prepared_statement: true to the metric config instead of the connection string, this should work. 🙂👍

[yasharne]

Thanks for your support, this fixed it