Cant run sql query on clickhouse DB connection

Queries and Resources
,
1

  • Goal: Connect to clickhouse and do a select query on one of the tables

  • Steps:

  1. I have added a resource and connected to clickhouse successfully. The user is a readonly user.
  2. The I use the resource to run sql (I have tried both these queries):

select * from MainGroup
select * from dictionary_main_groups

  • Details

But i get an error:

  • error:"Error running instance method java.sql.BatchUpdateException: Code: 164. DB::Exception: Cannot modify 'max_execu"

I dont get the full error message since the message is cut.
But i think its: max_execution_time.

Do you know if retool do an silent alter of the max_execution_time before the query is run?

Here is something that might bring som insight to the issue:

2

Hi @Christian_Ostrem I don't believe we do :thinking: Have you tried adding a limit to the query? Are you able to successfully run a hardcoded query like select 1?

Do you have any table names you can test that don't have special characters _ or capital letters? Usually table names with capital letters need to be wrapped in double quotes - select * from "MainGroup"

Are you using the JDBC resource type? Is the "Test connection" on the resource successful?

4

Hi, I will test the things you mentioned, I have been away for 4 weeks so I haven't tested this yet.

5

Hi @Tess , I have now tested the things mentioned. The test connection is successful.
Its not possible to run select 1, select * from "MainGroup" or show tables.

The error that is returned is:

Error running instance method java.sql.BatchUpdateException: Code: 164. DB::Exception: Cannot modify 'max_execution_time' setting in readonly mode. (READONLY)...

I dont know i you are you have written the connector code your self or used a library but it seems like retool is trying to set max_execution_time before the query is run. This is only allowed if you have set readonly flag as 2 in the clickhouse configuration.

Its possible to change readonly flag in clickhouse but that also means that we need to restart the service. This is a bit of pain for us since there is millions of transactions happening everyday. There responsible person dosent like to "experiment" with live databases. If you can confirm that this is the issue I can probably convince them to do the change since the i no way around. Depending on what you can do on your side.

There is a library (grafana) on github who has addressed this issue.

Refrence:

hi @abh @mastertheknife @pnax

max_execution_time is set by clickhouse-go client and it's expected as you have query timeout set. For some reason, it didn't work in previous versions. I will investigate it.

What is important, your user has to have readonly flag set as 2. Permissions for Queries | ClickHouse Docs
Otherwise, ClickHouse will reject your queries as expected.

Probably, it should be included in sort of documentation.

Possible values:

  • 0 — Read, Write, and Change settings queries are allowed.
  • 1 — Only Read data queries are allowed.
  • 2 — Read data and Change settings queries are allowed.
6

Hi @Christian_Ostrem,

I have flagged this to our team internally, but given the constraints described in that past, I don't think we'll necessarily have a better solution :disappointed: I'd recommend setting aside some time to make the permissions change if you haven't already! :crossed_fingers:

7

Could you leave readonly=1 and set the constraint type of max_execution_time to changeable_in_readonly to allow modification of this setting. Not sure if that would help with the downtime? :crossed_fingers:

8

They opened for a mysql connection instead of the JDBC connection, bypassing the problem entirely. This "fixed" the problem for now.