Clickhouse failure?

[iSCJT]

Hi,

I'm trying to install Plausible on a Digital Ocean droplet alongside Ghost (already installed). Install seems to run ok but Plausible isn't available on :8000. On checking the logs I think I might have a problem with either Clickhouse or possibly my reverse-proxy (nginx). If anyone can give me any pointers that would be much appreciated. Clickhouse logs:

`Processing configuration file '/etc/clickhouse-server/config.xml'.
Merging configuration file '/etc/clickhouse-server/config.d/docker_related_config.xml'.
Merging configuration file '/etc/clickhouse-server/config.d/logging.xml'.
Include not found: clickhouse_remote_servers
Include not found: clickhouse_compression
Logging warning to /var/log/clickhouse-server/clickhouse-server.log
Logging errors to /var/log/clickhouse-server/clickhouse-server.err.log
Logging warning to console
Include not found: networks
2021.03.15 22:43:02.109257 [ 1 ] {} Application: Listen [::]:8123 failed: Poco::Exception. Code: 1000, e.code() = 0, e.displayText() = DNS error: EAI: -9 (version 20.3.8.53 (official build)). If it is an IPv6 or IPv4 address and your host has disabled IPv6 or IPv4, then consider to specify not disabled IPv4 or IPv6 address to listen in <listen_host> element of configuration file. Example for disabled IPv6: <listen_host>0.0.0.0</listen_host> . Example for disabled IPv4: <listen_host>::</listen_host>
2021.03.15 22:43:02.111388 [ 1 ] {} Application: Listen [::]:9000 failed: Poco::Exception. Code: 1000, e.code() = 0, e.displayText() = DNS error: EAI: -9 (version 20.3.8.53 (official build)). If it is an IPv6 or IPv4 address and your host has disabled IPv6 or IPv4, then consider to specify not disabled IPv4 or IPv6 address to listen in <listen_host> element of configuration file. Example for disabled IPv6: <listen_host>0.0.0.0</listen_host> . Example for disabled IPv4: <listen_host>::</listen_host>
2021.03.15 22:43:02.111829 [ 1 ] {} Application: Listen [::]:9009 failed: Poco::Exception. Code: 1000, e.code() = 0, e.displayText() = DNS error: EAI: -9 (version 20.3.8.53 (official build)). If it is an IPv6 or IPv4 address and your host has disabled IPv6 or IPv4, then consider to specify not disabled IPv4 or IPv6 address to listen in <listen_host> element of configuration file. Example for disabled IPv6: <listen_host>0.0.0.0</listen_host> . Example for disabled IPv4: <listen_host>::</listen_host>
2021.03.15 22:43:02.112300 [ 1 ] {} Application: Listen [::]:9004 failed: Poco::Exception. Code: 1000, e.code() = 0, e.displayText() = DNS error: EAI: -9 (version 20.3.8.53 (official build)). If it is an IPv6 or IPv4 address and your host has disabled IPv6 or IPv4, then consider to specify not disabled IPv4 or IPv6 address to listen in <listen_host> element of configuration file. Example for disabled IPv6: <listen_host>0.0.0.0</listen_host> . Example for disabled IPv4: <listen_host>::</listen_host>
Include not found: clickhouse_remote_servers
Include not found: clickhouse_compression
2021.03.15 22:43:23.881000 [ 81 ] {a24ab394-f243-4ece-ba35-908620d90a84} executeQuery: Code: 62, e.displayText() = DB::Exception: Syntax error: failed at position 658 (line 1, col 658): timestamp ORDER BY s0."timestamp"
FORMAT JSONCompact. Expected one of: LIMIT, WHERE, INNER, LIKE, GLOBAL NOT IN, AS, HAVING, IS, INTO OUTFILE, GROUP BY, OR, QuestionMark, BETWEEN, NOT LIKE, LEFT ARRAY JOIN, PREWHERE, AND, JOIN, Comma, alias, ORDER BY, array join, SETTINGS, UNION ALL, IN, ARRAY JOIN, FORMAT, Dot, WITH, NOT, Arrow, token, NOT IN, GLOBAL IN, table, table function, subquery or list of joined tables (version 20.3.8.53 (official build)) (from 172.18.0.5:40487) (in query: SELECT s0."hostname", s0."domain", s0."user_id", s0."session_id", s0."start", s0."duration", s0."is_bounce", s0."entry_page", s0."exit_page", s0."pageviews", s0."events", s0."sign", s0."utm_medium", s0."utm_source", s0."utm_campaign", s0."referrer", s0."referrer_source", s0."country_code", s0."screen_size", s0."operating_system", s0."operating_system_version", s0."browser", s0."browser_version", s0."timestamp" FROM "sessions" AS s0 INNER JOIN (SELECT s0."session_id" AS "session_id", max(s0."timestamp") AS "timestamp" FROM "sessions" AS s0 WHERE (s0."timestamp" >= now() - INTERVAL 3600 SECOND) GROUP BY s0."session_id") AS s1 USING session_id, USING timestamp ORDER BY s0."timestamp" FORMAT JSONCompact), Stack trace (when copying this message, always include the lines below):

0. Poco::Exception::Exception(std::__1::basic_string<char, std::__1::char_traits, std::__1::allocator > const&, int) @ 0x10542450 in /usr/bin/clickhouse
1. DB::Exception::Exception(std::__1::basic_string<char, std::__1::char_traits, std::__1::allocator > const&, int) @ 0x8f4272d in /usr/bin/clickhouse
2. ? @ 0xe0850a7 in /usr/bin/clickhouse
3. ? @ 0xd55e87d in /usr/bin/clickhouse
4. DB::executeQuery(DB::ReadBuffer&, DB::WriteBuffer&, bool, DB::Context&, std::__1::function<void (std::__1::basic_string<char, std::__1::char_traits, std::__1::allocator > const&, std::__1::basic_string<char, std::__1::char_traits, std::__1::allocator > const&, std::__1::basic_string<char, std::__1::char_traits, std::__1::allocator > const&, std::__1::basic_string<char, std::__1::char_traits, std::__1::allocator > const&)>) @ 0xd561fb9 in /usr/bin/clickhouse
5. DB::HTTPHandler::processQuery(Poco::Net::HTTPServerRequest&, HTMLForm&, Poco::Net::HTTPServerResponse&, DB::HTTPHandler::Output&) @ 0x8fee706 in /usr/bin/clickhouse
6. DB::HTTPHandler::handleRequest(Poco::Net::HTTPServerRequest&, Poco::Net::HTTPServerResponse&) @ 0x8ff1bc3 in /usr/bin/clickhouse
7. Poco::Net::HTTPServerConnection::run() @ 0xe389aa4 in /usr/bin/clickhouse
8. Poco::Net::TCPServerConnection::start() @ 0xe3b9a4b in /usr/bin/clickhouse
9. Poco::Net::TCPServerDispatcher::run() @ 0xe3b9ecd in /usr/bin/clickhouse
10. Poco::PooledThread::run() @ 0x105d05b7 in /usr/bin/clickhouse
11. Poco::ThreadImpl::runnableEntry(void*) @ 0x105cc3bc in /usr/bin/clickhouse
12. ? @ 0x105cdd5d in /usr/bin/clickhouse
13. start_thread @ 0x76db in /lib/x86_64-linux-gnu/libpthread-2.27.so
14. clone @ 0x12188f in /lib/x86_64-linux-gnu/libc-2.27.so

2021.03.15 22:43:24.160307 [ 81 ] {} HTTPHandler: Code: 62, e.displayText() = DB::Exception: Syntax error: failed at position 658 (line 1, col 658): timestamp ORDER BY s0."timestamp"
FORMAT JSONCompact. Expected one of: LIMIT, WHERE, INNER, LIKE, GLOBAL NOT IN, AS, HAVING, IS, INTO OUTFILE, GROUP BY, OR, QuestionMark, BETWEEN, NOT LIKE, LEFT ARRAY JOIN, PREWHERE, AND, JOIN, Comma, alias, ORDER BY, array join, SETTINGS, UNION ALL, IN, ARRAY JOIN, FORMAT, Dot, WITH, NOT, Arrow, token, NOT IN, GLOBAL IN, table, table function, subquery or list of joined tables, Stack trace (when copying this message, always include the lines below):

0. Poco::Exception::Exception(std::__1::basic_string<char, std::__1::char_traits, std::__1::allocator > const&, int) @ 0x10542450 in /usr/bin/clickhouse
1. DB::Exception::Exception(std::__1::basic_string<char, std::__1::char_traits, std::__1::allocator > const&, int) @ 0x8f4272d in /usr/bin/clickhouse
2. ? @ 0xe0850a7 in /usr/bin/clickhouse
3. ? @ 0xd55e87d in /usr/bin/clickhouse`

[iSCJT]

Just bumping this to see if anyone might be able to assist now I'm a sponsor. I've tried multiple times across multiple environments over the past couple of weeks to get a self hosted deployment working without success. My reverse-proxy is all sorted and plausible is accessible on a sub domain but visits never get logged. As far as I can tell Clickhouse seems to be the problem. Any pointers or help would be much appreciated. Latest Clickhouse logs below. Thanks. Sean

2021.03.28 23:06:21.822438 [ 46 ] {} Application: Listen [::]:8123 failed: Poco::Exception. Code: 1000, e.code() = 0, e.displayText() = DNS error: EAI: Address family for hostname not supported (version 21.3.2.5 (official build)). If it is an IPv6 or IPv4 address and your host has disabled IPv6 or IPv4, then consider to specify not disabled IPv4 or IPv6 address to listen in <listen_host> element of configuration file. Example for disabled IPv6: <listen_host>0.0.0.0</listen_host> . Example for disabled IPv4: <listen_host>::</listen_host>
2021.03.28 23:06:21.823023 [ 46 ] {} Application: Listen [::]:9000 failed: Poco::Exception. Code: 1000, e.code() = 0, e.displayText() = DNS error: EAI: Address family for hostname not supported (version 21.3.2.5 (official build)). If it is an IPv6 or IPv4 address and your host has disabled IPv6 or IPv4, then consider to specify not disabled IPv4 or IPv6 address to listen in <listen_host> element of configuration file. Example for disabled IPv6: <listen_host>0.0.0.0</listen_host> . Example for disabled IPv4: <listen_host>::</listen_host>
2021.03.28 23:06:21.823594 [ 46 ] {} Application: Listen [::]:9009 failed: Poco::Exception. Code: 1000, e.code() = 0, e.displayText() = DNS error: EAI: Address family for hostname not supported (version 21.3.2.5 (official build)). If it is an IPv6 or IPv4 address and your host has disabled IPv6 or IPv4, then consider to specify not disabled IPv4 or IPv6 address to listen in <listen_host> element of configuration file. Example for disabled IPv6: <listen_host>0.0.0.0</listen_host> . Example for disabled IPv4: <listen_host>::</listen_host>
2021.03.28 23:06:21.823929 [ 46 ] {} Application: Listen [::]:9004 failed: Poco::Exception. Code: 1000, e.code() = 0, e.displayText() = DNS error: EAI: Address family for hostname not supported (version 21.3.2.5 (official build)). If it is an IPv6 or IPv4 address and your host has disabled IPv6 or IPv4, then consider to specify not disabled IPv4 or IPv6 address to listen in <listen_host> element of configuration file. Example for disabled IPv6: <listen_host>0.0.0.0</listen_host> . Example for disabled IPv4: <listen_host>::</listen_host>
2021.03.28 23:06:39.646288 [ 87 ] {4d658787-4e53-4845-a152-ff40b82ae8ef} executeQuery: Code: 62, e.displayText() = DB::Exception: Syntax error: failed at position 658 ('timestamp') (line 1, col 658): timestamp ORDER BY s0."timestamp"
FORMAT JSONCompact. Expected one of: UNION, LIMIT, WHERE, WINDOW, INNER, LIKE, GLOBAL NOT IN, end of query, HAVING, AS, DIV, IS, GROUP BY, INTO OUTFILE, OR, QuestionMark, BETWEEN, OFFSET, NOT LIKE, MOD, LEFT ARRAY JOIN, PREWHERE, AND, JOIN, Comma, alias, ORDER BY, array join, SETTINGS, IN, ARRAY JOIN, ILIKE, FORMAT, Dot, NOT ILIKE, WITH, NOT, Arrow, table, table function, subquery or list of joined tables, token, NOT IN, GLOBAL IN (version 21.3.2.5 (official build)) (from 172.18.0.5:37719) (in query: SELECT s0."hostname", s0."domain", s0."user_id", s0."session_id", s0."start", s0."duration", s0."is_bounce", s0."entry_page", s0."exit_page", s0."pageviews", s0."events", s0."sign", s0."utm_medium", s0."utm_source", s0."utm_campaign", s0."referrer", s0."referrer_source", s0."country_code", s0."screen_size", s0."operating_system", s0."operating_system_version", s0."browser", s0."browser_version", s0."timestamp" FROM "sessions" AS s0 INNER JOIN (SELECT s0."session_id" AS "session_id", max(s0."timestamp") AS "timestamp" FROM "sessions" AS s0 WHERE (s0."timestamp" >= now() - INTERVAL 3600 SECOND) GROUP BY s0."session_id") AS s1 USING session_id, USING timestamp ORDER BY s0."timestamp" FORMAT JSONCompact), Stack trace (when copying this message, always include the lines below):

0. DB::parseQueryAndMovePosition(DB::IParser&, char const*&, char const*, std::__1::basic_string<char, std::__1::char_traits, std::__1::allocator > const&, bool, unsigned long, unsigne
   d long) @ 0x1184fcb6 in /usr/bin/clickhouse
1. ? @ 0xf12c0ae in /usr/bin/clickhouse
2. DB::executeQuery(DB::ReadBuffer&, DB::WriteBuffer&, bool, DB::Context&, std::__1::function<void (std::__1::basic_string<char, std::__1::char_traits, std::__1::allocator > const&, std::__1::basic_string<char, std::__1::char_traits, std::__1::allocator > const&, std::__1::basic_string<char, std::__1::char_traits, std::__1::allocator > const&, std::__1::basic_string<char, std::__1::char_traits, std::__1::allocator > const&)>) @ 0xf130a78 in /usr/bin/clickhouse
3. DB::HTTPHandler::processQuery(DB::Context&, DB::HTTPServerRequest&, DB::HTMLForm&, DB::HTTPServerResponse&, DB::HTTPHandler::Output&, std::__1::optionalDB::CurrentThread::QueryScope&) @ 0xf84675a in /usr/bin/clickhouse
4. DB::HTTPHandler::handleRequest(DB::HTTPServerRequest&, DB::HTTPServerResponse&) @ 0xf84a8ee in /usr/bin/clickhouse
5. DB::HTTPServerConnection::run() @ 0xf8d35ff in /usr/bin/clickhouse
6. Poco::Net::TCPServerConnection::start() @ 0x11f7ccbf in /usr/bin/clickhouse
7. Poco::Net::TCPServerDispatcher::run() @ 0x11f7e6d1 in /usr/bin/clickhouse
8. Poco::PooledThread::run() @ 0x120b4df9 in /usr/bin/clickhouse
9. Poco::ThreadImpl::runnableEntry(void*) @ 0x120b0c5a in /usr/bin/clickhouse
10. start_thread @ 0x9609 in /usr/lib/x86_64-linux-gnu/libpthread-2.31.so
11. __clone @ 0x122293 in /usr/lib/x86_64-linux-gnu/libc-2.31.so

2021.03.28 23:06:39.647225 [ 87 ] {4d658787-4e53-4845-a152-ff40b82ae8ef} DynamicQueryHandler: Code: 62, e.displayText() = DB::Exception: Syntax error: failed at position 658 ('timestamp') (line 1, col 658): timestamp ORDER BY s0."timestamp"
FORMAT JSONCompact. Expected one of: UNION, LIMIT, WHERE, WINDOW, INNER, LIKE, GLOBAL NOT IN, end of query, HAVING, AS, DIV, IS, GROUP BY, INTO OUTFILE, OR, QuestionMark, BETWEEN, OFFSET, NOT LIKE, MOD, LEFT ARRAY JOIN, PREWHERE, AND, JOIN, Comma, alias, ORDER BY, array join, SETTINGS, IN, ARRAY JOIN, ILIKE, FORMAT, Dot, NOT ILIKE, WITH, NOT, Arrow, table, table function, subquery or list of joined tables, token, NOT IN, GLOBAL IN, Stack trace (when copying this message, always include the lines below):

0. DB::parseQueryAndMovePosition(DB::IParser&, char const*&, char const*, std::__1::basic_string<char, std::__1::char_traits, std::__1::allocator > const&, bool, unsigned long, unsigned long) @ 0x1184fcb6 in /usr/bin/clickhouse
1. ? @ 0xf12c0ae in /usr/bin/clickhouse
2. DB::executeQuery(DB::ReadBuffer&, DB::WriteBuffer&, bool, DB::Context&, std::__1::function<void (std::__1::basic_string<char, std::__1::char_traits, std::__1::allocator > const&, std::__1::basic_string<char, std::__1::char_traits, std::__1::allocator > const&, std::__1::basic_string<char, std::__1::char_traits, std::__1::allocator > const&, std::__1::basic_string<char, std::__1::char_traits, std::__1::allocator > const&)>) @ 0xf130a78 in /usr/bin/clickhouse
3. DB::HTTPHandler::processQuery(DB::Context&, DB::HTTPServerRequest&, DB::HTMLForm&, DB::HTTPServerResponse&, DB::HTTPHandler::Output&, std::__1::optionalDB::CurrentThread::QueryScope&) @ 0xf84675a in /usr/bin/clickhouse
4. DB::HTTPHandler::handleRequest(DB::HTTPServerRequest&, DB::HTTPServerResponse&) @ 0xf84a8ee in /usr/bin/clickhouse
5. DB::HTTPServerConnection::run() @ 0xf8d35ff in /usr/bin/clickhouse
6. Poco::Net::TCPServerConnection::start() @ 0x11f7ccbf in /usr/bin/clickhouse
7. Poco::Net::TCPServerDispatcher::run() @ 0x11f7e6d1 in /usr/bin/clickhouse
8. Poco::PooledThread::run() @ 0x120b4df9 in /usr/bin/clickhouse
9. Poco::ThreadImpl::runnableEntry(void*) @ 0x120b0c5a in /usr/bin/clickhouse
10. start_thread @ 0x9609 in /usr/lib/x86_64-linux-gnu/libpthread-2.31.so
11. __clone @ 0x122293 in /usr/lib/x86_64-linux-gnu/libc-2.31.so
    (version 21.3.2.5 (official build))

[ukutaht]

Hey, thanks for sponsoring!

I'm pretty sure that these Clickhouse logs are just noise. I get the same logs on an instance that works perfectly fine. I just haven't done the work to suppress noisy logs.

First things first - on the site you are tracking, can you see a successful POST to yourplausible.com/api/event with status 202?

If you're not sure you can send the URL of the site being tracked so I can verify, either publicly or send it to [email protected].

[iSCJT]

You legend, thanks Uku. That put me on the right path to fixing it, It was getting blocked due to a mixed-content warning. I had http rather than https in my plausible-conf.env file. should have thought to check the network traffic for issues.

Thanks again, I'll drop you guys a proper message at some point. Proud to be a sponsor, I think the whole ethos behind plausible and how you run it is spot on. I'm also hoping to try and contribute something back when I get some time, maybe I'll read up on Clickhouse and try taming that noise!

[ukutaht]

Awesome @iSCJT! Would be great to have you as a contributor as well!