Clickhouse: Where should I specify max query size?

[Joozty]

Hi, I would like to ask. Where can I increase max_query_size parameter? I tried to create entry in config.xml (<max_query_size>1000000</max_query_size> but it didn't help. I am still receiving an error that my query is too big.

I am using your Docker image

Thanks in advance.

[dveselov]

I'm using this docker image too and have similar question.
Limits on max_query_size, that I defined in /etc/clickhouse-server/config.xml doesn't work.

[dveselov]

If it help, I'm fetching data from ClickHouse with large number of entries in IN ( ... ) clause.
And ClickHouse throws this exception:

DB::Exception: Syntax error: failed at position 262142 (line 3, col 262083): 29900, 31794, 30743, 27330, 86017, 1237, 86395, 26248, 76827, 8051, 68091, 32989, 15449, 84636, 63755, 27470, 28759, 27766, 41271, 41210, 41253, 41255, 41220, 4. Max query size exceeded. Stack trace:

0. /usr/bin/clickhouse-server(StackTrace::StackTrace()+0x15) [0x84eb125]
1. /usr/bin/clickhouse-server(DB::Exception::Exception(std::__cxx11::basic_string<char, std::char_traits<char>, std::allocator<char> > const&, int)+0x21) [0x2ccbf01]
2. /usr/bin/clickhouse-server(DB::parseQuery(DB::IParser&, char const*, char const*, std::__cxx11::basic_string<char, std::char_traits<char>, std::allocator<char> > const&, unsigned long)+0xab) [0x84b3e5b]
3. /usr/bin/clickhouse-server() [0x7d6dfe6]
4. /usr/bin/clickhouse-server(DB::executeQuery(std::__cxx11::basic_string<char, std::char_traits<char>, std::allocator<char> > const&, DB::Context&, bool, DB::QueryProcessingStage::Enum)+0x89) [0x7d6f189]
5. /usr/bin/clickhouse-server(DB::TCPHandler::runImpl()+0x3d6) [0x2cdc5c6]
6. /usr/bin/clickhouse-server(DB::TCPHandler::run()+0x2a) [0x2cdd4ba]
7. /usr/bin/clickhouse-server(Poco::Net::TCPServerConnection::start()+0xe) [0x86d549e]
8. /usr/bin/clickhouse-server(Poco::Net::TCPServerDispatcher::run()+0x169) [0x86d5879]
9. /usr/bin/clickhouse-server(Poco::PooledThread::run()+0x76) [0x8776a36]
10. /usr/bin/clickhouse-server(Poco::ThreadImpl::runnableEntry(void*)+0x37) [0x8772c47]
11. /usr/bin/clickhouse-server() [0x8dd3a8e]
12. /lib/x86_64-linux-gnu/libpthread.so.0(+0x77fb) [0x7f897d01d7fb]
13. /lib/x86_64-linux-gnu/libc.so.6(clone+0x3e) [0x7f897c7f0b5e]

[filimonov]

I know that it's not clear from documentation where to put some config parameters.

Generally clickhouse have 2 biggest group of settings:
1) "user" settings
2) "server" settings

You can check if some setting is "user"-setting by checking it in one of those places:
1) system.settings table
2) CH sources: https://github.com/yandex/ClickHouse/blob/master/dbms/src/Interpreters/Settings.h
3) CH docs: https://clickhouse.yandex/docs/en/operations/settings/

Generally "user"-settings are relatively dynamic, they can be changed for each user / user group / or for one concrete session without clickhouse-server restarts etc. They can be changed in one of those ways:
1) in your users.xml for some concrete user
2) in your users.xml for some profile which can be used for multiple users,
3) in your users.xml for default profile - in that can it will be used for all user and all profiles
4) if your client support session you can just use SET setting_name = 'new_value'
5) for clickhouse-client you can add setting as command-line parameter (check clickhouse-client --help)
6) for HTTP interface you can add setting as query parameter. like http://localhost:8123/?setting_name=value

In contrast main server setting are listed only here https://clickhouse.yandex/docs/en/operations/server_settings/settings/
and there is not one central place in sources where you can find comprehensive list of them.
Those settings influence the main core parts of server behaviour, and they can't be changed from user to user - like ports to listen, data directory path etc. They are configured inside config.xml. Changing of those settings mostly require restart.

[Joozty]

Thanks, @filimonov I tried to put it into user.xml and config.xml files without any effect.

I also tried to send http request:
http://localhost:8123/?max_query_size=100000000

But this seems it doesn't work at all.
This is the response Code: 62, e.displayText() = DB::Exception: Empty query, e.what() = DB::Exception

@dveselov Have you found any solution?

[filimonov]

@Joozty what client software do you use? JDBC?

[Joozty]

@filimonov Both JDBC and also HTTP.

[filimonov]

@Joozty for JDBC - use setMaxQuerySize method.

For HTTP - send that additional parameter together with your query. Like post query to http://localhost:8123/?max_query_size=100000000 or do http://localhost:8123/?max_query_size=100000000&query=SELECT%201

About users.xml - it SHOULD work. If it don't - probably you're doing something wrong. Can you check if there are some errors in server log, and paste fragment of your users.xml?

[Joozty]

@filimonov Thanks a lot. I finally found a solution. Actually max_query_size did work. The problem was that the number wasn't high enough. The number is in bytes, isn't it? Maybe you could write this to the documentation.