23.3 close_connection and share_connection do not work with named collections

[SaltTan]

I use the close_connection and share_connection settings with MySQL dictionaries.
After an upgrade to 23.3 ClickHouse cannot load such dictionaries.

Code: 36. DB::Exception: Unexpected key close_connection in named collection. Required keys: database, db, password, table, user, username, optional keys: connect_timeout, connection_auto_close, connection_max_tries, connection_pool_size, connection_wait_timeout, addresses_expr, host, hostname, mysql_datatypes_support_level, on_duplicate_clause, port, read_write_timeout, replace_query. (BAD_ARGUMENTS), Stack trace
 (when copying this message, always include the lines below):

0. DB::Exception::Exception(DB::Exception::MessageMasked&&, int, bool) @ 0xe18d895 in /usr/bin/clickhouse
1. ? @ 0x120ddc86 in /usr/bin/clickhouse
2. ? @ 0x120db04a in /usr/bin/clickhouse
3. DB::StorageMySQL::processNamedCollectionResult(DB::NamedCollection const&, DB::MySQLSettings&, bool) @ 0x13f4c3ed in /usr/bin/clickhouse
4. ? @ 0x10a1753c in /usr/bin/clickhouse
5. DB::DictionarySourceFactory::create(String const&, Poco::Util::AbstractConfiguration const&, String const&, DB::DictionaryStructure const&, std::shared_ptr<DB::Context const>, String const&, bool) const @ 0x12445f90 in /usr/bin/clickhouse
6. DB::DictionaryFactory::create(String const&, Poco::Util::AbstractConfiguration const&, String const&, std::shared_ptr<DB::Context const>, bool) const @ 0x12442669 in /usr/bin/clickhouse
7. DB::ExternalDictionariesLoader::create(String const&, Poco::Util::AbstractConfiguration const&, String const&, String const&) const @ 0x1311fa5b in /usr/bin/clickhouse
8. DB::ExternalLoader::LoadingDispatcher::doLoading(String const&, unsigned long, bool, unsigned long, bool, std::shared_ptr<DB::ThreadGroupStatus>) @ 0x1312c653 in /usr/bin/clickhouse
9. void std::__function::__policy_invoker<void ()>::__call_impl<std::__function::__default_alloc_func<ThreadFromGlobalPoolImpl<true>::ThreadFromGlobalPoolImpl<void (DB::ExternalLoader::LoadingDispatcher::*)(String const&, unsigned long, bool, unsigned long, boo
l, std::shared_ptr<DB::ThreadGroupStatus>), DB::ExternalLoader::LoadingDispatcher*, String&, unsigned long&, bool&, unsigned long&, bool, std::shared_ptr<DB::ThreadGroupStatus>>(void (DB::ExternalLoader::LoadingDispatcher::*&&)(String const&, unsigned long, boo
l, unsigned long, bool, std::shared_ptr<DB::ThreadGroupStatus>), DB::ExternalLoader::LoadingDispatcher*&&, String&, unsigned long&, bool&, unsigned long&, bool&&, std::shared_ptr<DB::ThreadGroupStatus>&&)::'lambda'(), void ()>>(std::__function::__policy_storage
 const*) @ 0x131324d3 in /usr/bin/clickhouse
10. ThreadPoolImpl<std::thread>::worker(std::__list_iterator<std::thread, void*>) @ 0xe25cc73 in /usr/bin/clickhouse
11. ? @ 0xe2628e1 in /usr/bin/clickhouse
12. start_thread @ 0x74a4 in /lib/x86_64-linux-gnu/libpthread-2.24.so
13. clone @ 0xe8d0f in /lib/x86_64-linux-gnu/libc-2.24.so
 (version 23.3.1.2823 (official build))

Code: 36. DB::Exception: Unexpected key share_connection in named collection. Required keys: database, db, password, table, user, username, optional keys: connect_timeout, connection_auto_close, connection_max_tries, connection_pool_size, connection_wait_timeout, addresses_expr, host, hostname, mysql_datatypes_support_level, on_duplicate_clause, port, read_write_timeout, replace_query. (BAD_ARGUMENTS),
 Stack trace (when copying this message, always include the lines below):

0. DB::Exception::Exception(DB::Exception::MessageMasked&&, int, bool) @ 0xe18d895 in /usr/bin/clickhouse
1. ? @ 0x120ddc86 in /usr/bin/clickhouse
2. ? @ 0x120db04a in /usr/bin/clickhouse
3. DB::StorageMySQL::processNamedCollectionResult(DB::NamedCollection const&, DB::MySQLSettings&, bool) @ 0x13f4c3ed in /usr/bin/clickhouse
4. ? @ 0x10a1753c in /usr/bin/clickhouse
5. DB::DictionarySourceFactory::create(String const&, Poco::Util::AbstractConfiguration const&, String const&, DB::DictionaryStructure const&, std::shared_ptr<DB::Context const>, String const&, bool) const @ 0x12445f90 in /usr/bin/clickhouse
6. DB::DictionaryFactory::create(String const&, Poco::Util::AbstractConfiguration const&, String const&, std::shared_ptr<DB::Context const>, bool) const @ 0x12442669 in /usr/bin/clickhouse
7. DB::ExternalDictionariesLoader::create(String const&, Poco::Util::AbstractConfiguration const&, String const&, String const&) const @ 0x1311fa5b in /usr/bin/clickhouse
8. DB::ExternalLoader::LoadingDispatcher::doLoading(String const&, unsigned long, bool, unsigned long, bool, std::shared_ptr<DB::ThreadGroupStatus>) @ 0x1312c653 in /usr/bin/clickhouse
9. void std::__function::__policy_invoker<void ()>::__call_impl<std::__function::__default_alloc_func<ThreadFromGlobalPoolImpl<true>::ThreadFromGlobalPoolImpl<void (DB::ExternalLoader::LoadingDispatcher::*)(String const&, unsigned long, bool, unsigned long, bool, std::shared_ptr<DB::ThreadGroupStatus>), DB::ExternalLoader::LoadingDispatcher*, String&, unsigned long&, bool&, unsigned long&, bool, std::shared_ptr<DB::ThreadGroupStatus>>(void (DB::ExternalLoader::LoadingDispatcher::*&&)(String const&, unsigned long, bool, unsigned long, bool, std::shared_ptr<DB::ThreadGroupStatus>), DB::ExternalLoader::LoadingDispatcher*&&, String&, unsigned long&, bool&, unsigned long&, bool&&, std::shared_ptr<DB::ThreadGroupStatus>&&)::'lambda'(), void ()>>(std::__function::__policy_storage const*) @ 0x131324d3 in /usr/bin/clickhouse
10. ThreadPoolImpl<std::thread>::worker(std::__list_iterator<std::thread, void*>) @ 0xe25cc73 in /usr/bin/clickhouse
11. ? @ 0xe2628e1 in /usr/bin/clickhouse
12. start_thread @ 0x74a4 in /lib/x86_64-linux-gnu/libpthread-2.24.so
13. clone @ 0xe8d0f in /lib/x86_64-linux-gnu/libc-2.24.so
 (version 23.3.1.2823 (official build))

The dictionaries work well if I don't use named collections in their definition.
An example:

create dictionary mysql_collection
(
  key    String,
  value  String
)
PRIMARY KEY key
SOURCE(MYSQL(
    name mysql_config
    table 'table_name'
    close_connection 'true'
))
LAYOUT(complex_key_hashed)
LIFETIME(0);

[SaltTan]

I cannot upgrade because of this issue.
Is there a workaround maybe?
Or is there another way to share connections between dictionaries? I have hundreds of them and by default each dictionary creates a connection with MySQL and keeps it open forever.

[kssenii]

Fix #48759. Added backport label to 23.3.

[UnamedRus]

IT seems, that PostgreSQL table also have the same issue 23.3:

23.09.11 19:38:39.541328 [ 7 ] {} <Error> Application: Code: 36. DB::Exception: Unexpected key connection_pool_size in named collection. Required keys: database, db, p
assword, table, user, username, optional keys: addresses_expr, host, hostname, on_conflict, port, schema: Cannot attach table `db`.`tbl
est` from metadata file /var/lib/clickhouse/store/333/33333-1111-3333-1111-33333333/tb.sql from query ATTACH TABLE db.tbl UUID '33333-1111-3333-1111-33333333' (`aaaa` String, `bbbbb` String, `cccc` DateTime, `dddd` String,
) ENGINE = PostgreSQL(db, schema = 'schema', table = 'tbl'). (BAD_ARGUMENTS), Stack trace (when copying thi
s message, always include the lines below):
0. DB::Exception::Exception(DB::Exception::MessageMasked&&, int, bool) @ 0xe2322d5 in /usr/bin/clickhouse
1. ? @ 0x10bcacc6 in /usr/bin/clickhouse
2. ? @ 0x10bca2ea in /usr/bin/clickhouse
3. DB::StoragePostgreSQL::processNamedCollectionResult(DB::NamedCollection const&, bool) @ 0x14105f8a in /usr/bin/clickhouse
4. DB::StoragePostgreSQL::getConfiguration(absl::lts_20211102::InlinedVector<std::shared_ptr<DB::IAST>, 7ul, std::allocator<std::shared_ptr<DB::IAST>>>, std::shared_ptr<
DB::Context const>) @ 0x141069fa in /usr/bin/clickhouse
5. ? @ 0x1410c651 in /usr/bin/clickhouse
6. DB::StorageFactory::get(DB::ASTCreateQuery const&, String const&, std::shared_ptr<DB::Context>, std::shared_ptr<DB::Context>, DB::ColumnsDescription const&, DB::Const
raintsDescription const&, bool) const @ 0x1405f73b in /usr/bin/clickhouse
7. DB::createTableFromAST(DB::ASTCreateQuery, String const&, String const&, std::shared_ptr<DB::Context>, bool) @ 0x12c2691c in /usr/bin/clickhouse
8. DB::DatabaseOrdinary::loadTableFromMetadata(std::shared_ptr<DB::Context>, String const&, DB::QualifiedTableName const&, std::shared_ptr<DB::IAST> const&, DB::LoadingStrictnessLevel) @ 0x12c45e57 in /usr/bin/clickhouse
9. ? @ 0x12c9a05f in /usr/bin/clickhouse
10. ThreadPoolImpl<ThreadFromGlobalPoolImpl<false>>::worker(std::__list_iterator<ThreadFromGlobalPoolImpl<false>, void*>) @ 0xe305845 in /usr/bin/clickhouse
11. void std::__function::__policy_invoker<void ()>::__call_impl<std::__function::__default_alloc_func<ThreadFromGlobalPoolImpl<false>::ThreadFromGlobalPoolImpl<void ThreadPoolImpl<ThreadFromGlobalPoolImpl<false>>::scheduleImpl<void>(std::function<void ()>, long, std::optional<unsigned long>, bool)::'lambda0'()>(void&&)::'lambda'(), void ()>>(std::__function::__policy_storage const*) @ 0xe3083b5 in /usr/bin/clickhouse
12. ThreadPoolImpl<std::thread>::worker(std::__list_iterator<std::thread, void*>) @ 0xe301613 in /usr/bin/clickhouse
13. ? @ 0xe307281 in /usr/bin/clickhouse
14. ? @ 0x7fa7cb638609 in ?
15. __clone @ 0x7fa7cb55d133 in ?