Clickhouse: Failed to load table with Dictionary engine when initializing ClickHouse server

[maison2710]

1 month ago, I found this issue when trying to use version 20.4.1 even it was not released. Now there is an official release of version 20.4.2 but the issue is still there.
I created an issue on GitHub but there is no update then I want to re-post here

https://github.com/ClickHouse/ClickHouse/issues/10397

Recently, I upgraded ClickHouse from 19.5.3 to 20.4.2 and I got some issue when trying to load table with Dictionary engine during server's start up in version 20.4.2. (It worked fine with 19.5.3).

I defined a Dictionary xml file with name topics_article and put this xml file under /etc/clickhouse-server/config.d/

My table create statement as:
CREATE TABLE intermediate.topics_article (topic_idString,entryString,type String) ENGINE = Dictionary(topics_article)

I also have setting in config.xml as *_dictionary.xml
true

However, in version 20.4.2, server can not start successfully during this error:

``2020.05.18 07:25:16.032714 [ 191 ] {} <Information> DatabaseOrdinary (intermediate): Total 30 tables and 0 dictionaries. 2020.05.18 07:25:16.087128 [ 218 ] {}<Error> ThreadPool: Exception in ThreadPool(max_threads: 4, max_free_threads: 4, queue_size: 4, shutdown_on_exception: 1).: Code: 36, e.displayText() = DB::Exception: external dictionary 'topics_article' not found: Cannot attach tableintermediate.topics_articlefrom metadata file /var/lib/clickhouse/metadata/intermediate/topics_article.sql from query ATTACH TABLE topics_article (topic_idString,entryString,type` String) ENGINE = Dictionary('topics_article'), Stack trace (when copying this message, always include the lines below):

1. Poco::Exception::Exception(std::__1::basic_string, std::__1::allocator > const&, int) @ 0x10406ef0 in /usr/bin/clickhouse
2. DB::Exception::Exception(std::__1::basic_string, std::__1::allocator > const&, int) @ 0x8ff88ad in /usr/bin/clickhouse
3. ? @ 0xd03de31 in /usr/bin/clickhouse
4. std::__1::shared_ptr DB::ExternalLoader::load, void>(std::__1::basic_string, std::__1::allocator > const&) const @ 0xd049ab3 in /usr/bin/clickhouse
5. ? @ 0xd64ebcc in /usr/bin/clickhouse
6. DB::StorageFactory::get(DB::ASTCreateQuery const&, std::__1::basic_string, std::__1::allocator > const&, DB::Context&, DB::Context&, DB::ColumnsDescription const&, DB::ConstraintsDescription const&, bool) const @ 0xd67073d in /usr/bin/clickhouse
7. DB::createTableFromAST(DB::ASTCreateQuery, std::__1::basic_string, std::__1::allocator > const&, std::__1::basic_string, std::__1::allocator > const&, DB::Context&, bool) @ 0xd02c9b7 in /usr/bin/clickhouse
8. ? @ 0xd01fc58 in /usr/bin/clickhouse
9. ? @ 0xd020612 in /usr/bin/clickhouse
10. ThreadPoolImpl::worker(std::__1::__list_iterator) @ 0x901f3ab in /usr/bin/clickhouse
11. ThreadFromGlobalPool::ThreadFromGlobalPool::scheduleImpl(std::__1::function)::'lambda1'()>(void&&, void ThreadPoolImpl::scheduleImpl(std::__1::function)::'lambda1'()&&...)::'lambda'()::operator()() const @ 0x902017a in /usr/bin/clickhouse
12. ThreadPoolImpl::worker(std::__1::__list_iterator) @ 0x901e26b in /usr/bin/clickhouse
13. ? @ 0x901c753 in /usr/bin/clickhouse
14. start_thread @ 0x76db in /lib/x86_64-linux-gnu/libpthread-2.27.so
15. __clone @ 0x12188f in /lib/x86_64-linux-gnu/libc-2.27.so
    (version 20.4.2.9 (official build))
    ```

[nikitamikhaylov]

What will be if you disable dictionaries_lazy_load ?

[den-crane]

https://github.com/ClickHouse/ClickHouse/issues/10397#issuecomment-631027204

[maison2710]

https://github.com/ClickHouse/ClickHouse/issues/10397#issuecomment-631200765

[maison2710]

@nikitamikhaylov I don't think disable _dictionaries_lazy_load_ should resolve the root cause.

[Tri0L]

Have the same issue after upgrade 20.1.2.4 => 20.4.2.9, dictionaries_lazy_load=false didn't help.

[den-crane]

reproduce

<dictionaries_lazy_load>false</dictionaries_lazy_load>

cat /etc/clickhouse-server/node.tsv
1   xxxx


cat /etc/clickhouse-server/dict/node.xml
<dictionaries>
    <dictionary>
        <name>node</name>
        <source>
            <file>
                <path>/etc/clickhouse-server/node.tsv</path>
                <format>TabSeparated</format>
            </file>
        </source>
        <lifetime>0</lifetime>
        <layout><flat /></layout>
        <structure>
            <id><name>key</name></id>
            <attribute>
                <name>name</name>
                <type>String</type>
                <null_value></null_value>
            </attribute>
        </structure>
    </dictionary>
</dictionaries>

cat /var/lib/clickhouse/metadata/default/node.sql
ATTACH TABLE node
(
    `key` UInt64,
    `name` String
)
ENGINE = Dictionary('node')

Application: Caught exception while loading metadata: Code: 36, e.displayText() =
DB::Exception: external dictionary 'node' not found: Cannot attach table default.node from metadata file /var/lib/clickhouse/metadata/default/node.sql from query ATTACH TABLE node (key UInt64, name String) ENGINE = Dictionary('node'), Stack trace (when copying this message, always include the lines below):

1. Poco::Exception::Exception(std::__1::basic_string, std::__1::allocator > const&, int) @ 0x112387c0 in /usr/bin/clickhouse
2. DB::Exception::Exception(std::__1::basic_string, std::__1::allocator > const&, int) @ 0x972236d in /usr/bin/clickhouse
3. ? @ 0xdf0d851 in /usr/bin/clickhouse
4. std::__1::shared_ptr DB::ExternalLoader::load, void>(std::__1::basic_string, std::__1::allocator > const&) const @ 0xdf188e3 in /usr/bin/clickhouse
5. ? @ 0xe5148fc in /usr/bin/clickhouse
6. DB::StorageFactory::get(DB::ASTCreateQuery const&, std::__1::basic_string, std::__1::allocator > const&, DB::Context&, DB::Context&, DB::ColumnsDescription const&, DB::ConstraintsDescription const&, bool) const @ 0xe5383ad in /usr/bin/clickhouse
7. DB::createTableFromAST(DB::ASTCreateQuery, std::__1::basic_string, std::__1::allocator > const&, std::__1::basic_string, std::__1::allocator > const&, DB::Context&, bool) @ 0xdefcb69 in /usr/bin/clickhouse
8. ? @ 0xdeef558 in /usr/bin/clickhouse
9. ? @ 0xdeeff12 in /usr/bin/clickhouse
10. ThreadPoolImpl::worker(std::__1::__list_iterator) @ 0x9750b17 in /usr/bin/clickhouse
11. ThreadFromGlobalPool::ThreadFromGlobalPool::scheduleImpl(std::__1::function)::'lambda1'()>(void&&, void ThreadPoolImpl::scheduleImpl(std::__1::function)::'lambda1'()&&...)::'lambda'()::operator()() const @ 0x975128a in /usr/bin/clickhouse
12. ThreadPoolImpl::worker(std::__1::__list_iterator) @ 0x9750027 in /usr/bin/clickhouse
13. ? @ 0x974e563 in /usr/bin/clickhouse
14. start_thread @ 0x7fa3 in /lib/x86_64-linux-gnu/libpthread-2.28.so
15. clone @ 0xf94cf in /lib/x86_64-linux-gnu/libc-2.28.so
    (version 20.6.1.3853 (official build))
    2020.06.30 22:10:01.483280 [ 18610 ] {} Application: Shutting down storages.
    2020.06.30 22:10:01.483302 [ 18645 ] {} SystemLog (system.query_log): Terminating
    2020.06.30 22:10:01.483348 [ 18674 ] {} SystemLog (system.query_thread_log): Terminating
    2020.06.30 22:10:01.483373 [ 18655 ] {} SystemLog (system.trace_log): Terminating

[den-crane]

@vitlibar @alexey-milovidov it's impossible to start CH 20.4+ if one have a table with Engine=Dictionary (old xml dictionary).

to start CH, it needs to remove tables metadata ( rm /var/lib/clickhouse/metadata/default/node.sql ) start CH, create tables.

[vitlibar]

I'll try to fix it.

[MrSsunlight]

@vitlibar @alexey-milovidov it's impossible to start CH 20.4+ if one have a table with Engine=Dictionary (old xml dictionary).

to start CH, it needs to remove tables metadata ( rm /var/lib/clickhouse/metadata/default/node.sql ) start CH, create tables.

Is this feature newly added in version 20.x? When I restart the clickhouse in version 19.16, there is no such problem when loading the dictionary. I found that the latest version could not start

[den-crane]

@MrSsunlight fixed in 20.6.1.4050+