ClickHouse: MaterializedPostgres stuck if one of the table doesn't contain primary key, crash when attach/detach table
You have to provide the following information whenever possible.
Describe what’s wrong
CREATE DATABASE IF NOT EXISTS db1
ENGINE = MaterializedPostgreSQL('x-x-db-production.x.us-west-2.rds.amazonaws.com', 'x', 'rw_user', 'xxx');
SHOW TABLES FROM db1;
the database source is RDS, after executing those command the clickhouse-client suddenly closed.
Does it reproduce on recent release?
yes, this is the most recent release
ClickHouse client version 22.1.3.7 (official build).
Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 22.1.3 revision 54455.
Enable crash reporting
If possible, change “enabled” to true in “send_crash_reports” section in
config.xml:
ok done
How to reproduce
- create materializepostgres database from rds
- after few seconds it shows:
Connecting to localhost:9000 as user default.
Exception on client:
Code: 210. DB::NetException: Connection refused (localhost:9000). (NETWORK_ERROR)
- the server logs shows:
2022.01.26 18:31:54.861183 [ 19100 ] {} <Fatal> Application: Child process was terminated by signal 9 (KILL). If it is not done by 'forcestop' command or manually, the possible cause is OOM Killer (see 'dmesg' and look at the '/var/log/kern.log' for the details).
2022.01.26 18:32:26.943725 [ 23856 ] {} <Warning> Application: Calculated checksum of the binary: 38FD0E3944230CBD1E0C1028A9D68C83. There is no information about the reference checksum.
2022.01.26 18:32:29.034545 [ 24010 ] {} <Warning> PostgreSQLReplicationHandler: Publication solo_prod_ch_publication already exists and tables list is empty. Assuming publication is correct.
2022.01.26 18:32:29.066844 [ 24010 ] {} <Error> void DB::PostgreSQLReplicationHandler::startSynchronization(bool): Code: 60. DB::Exception: Table solo_prod.user_push_tokens doesn't exist: while loading table solofunds.user_push_tokens. (UNKNOWN_TABLE), Stack trace (when copying this message, always include the lines below):
0. DB::Exception::Exception(std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, int, bool) @ 0xa82d07a in /usr/bin/clickhouse
1. DB::Exception::Exception<std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > >(int, std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> >&&) @ 0xa89b823 in /usr/bin/clickhouse
2. void std::__1::__optional_storage_base<DB::Exception, false>::__construct<int const&, char const (&) [23], std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > >(int const&, char const (&) [23], std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> >&&) @ 0x1352ac0f in /usr/bin/clickhouse
3. DB::DatabaseCatalog::getTableImpl(DB::StorageID const&, std::__1::shared_ptr<DB::Context const>, std::__1::optional<DB::Exception>*) const @ 0x13519082 in /usr/bin/clickhouse
4. DB::DatabaseCatalog::getTable(DB::StorageID const&, std::__1::shared_ptr<DB::Context const>) const @ 0x1352126b in /usr/bin/clickhouse
5. DB::StorageMaterializedPostgreSQL::getNested() const @ 0x132f2c25 in /usr/bin/clickhouse
6. DB::PostgreSQLReplicationHandler::startSynchronization(bool) @ 0x132d640e in /usr/bin/clickhouse
7. DB::DatabaseMaterializedPostgreSQL::startSynchronization() @ 0x132c435f in /usr/bin/clickhouse
8. DB::BackgroundSchedulePoolTaskInfo::execute() @ 0x12f8470e in /usr/bin/clickhouse
9. DB::BackgroundSchedulePool::threadFunction() @ 0x12f870a7 in /usr/bin/clickhouse
10. ? @ 0x12f88170 in /usr/bin/clickhouse
11. ThreadPoolImpl<std::__1::thread>::worker(std::__1::__list_iterator<std::__1::thread, void*>) @ 0xa86f4b7 in /usr/bin/clickhouse
12. ? @ 0xa872ebd in /usr/bin/clickhouse
13. ? @ 0x7f10e173c609 in ?
14. __clone @ 0x7f10e1663293 in ?
(version 22.1.3.7 (official build))
- open new clickchouse-client, it would stuck before the prompt
- open another clickhouse-client, then both now shows the query prompt
- Which ClickHouse server version to use:
22.1.3.7 - Non-default settings, if any
<allow_experimental_database_materialized_postgresql>1</allow_experimental_database_materialized_postgresql>
CREATE TABLEstatements for all tables involved
CREATE DATABASE IF NOT EXISTS db1
ENGINE = MaterializedPostgreSQL('x-x-db-production.x.us-west-2.rds.amazonaws.com', 'x', 'rw_user', 'xxx');
- Queries to run that lead to unexpected result that create table, after crash it would only shows 5-10 tables (first crash only show 10, i drop table and create again, it only show 5 tables):
5 rows in set. Elapsed: 0.002 sec.
Expected behavior
- not crashing
- if crash it would resume
- if table has no replica identity it should keep going with other tables
A clear and concise description of what you expected to happen.
Error message and/or stacktrace
If applicable, add screenshots to help explain your problem.
About this issue
- Original URL
- State: closed
- Created 2 years ago
- Comments: 16 (5 by maintainers)
ok, I ll close this issue for now, the crash is fixed and there was a warning in documentation that if tables do not have primary key, then replica identity index needs to be added, otherwise it will not work. And for better usability I’ll make a change soon to allow to silently skip tables without primary key and no replica identity index.
another proof, after dropping database and recreate database with 1 table that crashed above it works (
materialized_postgresql_tables_list = 'collection_fees'), but crash if attaching table that previously doesn’t crash (initial table on previous step):no error after attach
this one after drop database and only attach 1 on create database materializepostgres (
materialized_postgresql_tables_list = 'collections'):