sql syntax error after docker-compose pull

[Jyggafey]

Hi, we are using the default docker-compose setup available at https://github.com/plausible/hosting
That worked fine for the last 3 months.
After a VM reboot and a docker-compose pull, the main app hangs and does not start up properly.
docker-compose up --detach gives us the following syntax error in the log:

plausible_1            | Loading plausible..
plausible_1            | Starting dependencies..
plausible_1            | Starting repos..
plausible_1            | create Plausible.Repo database if it doesn't exist
plausible_db_1         | 2021-03-09 13:05:12.057 UTC [35] ERROR:  database "plausible_db" already exists
plausible_db_1         | 2021-03-09 13:05:12.057 UTC [35] STATEMENT:  CREATE DATABASE "plausible_db" ENCODING 'UTF8'
plausible_1            | create Plausible.ClickhouseRepo database if it doesn't exist
plausible_1            | Creation of Db successful!
plausible_1            | Loading plausible..
plausible_1            | Starting dependencies..
plausible_1            | Starting repos..
plausible_1            | Running migrations for Elixir.Plausible.Repo
plausible_1            | Running migrations for Elixir.Plausible.ClickhouseRepo
plausible_1            | Migrations successful!
plausible_1            | Loading plausible..
plausible_1            | Starting dependencies..
plausible_1            | Starting repos..
plausible_1            | Admin user already exists. I won't override, bailing
plausible_events_db_1  | 2021.03.09 13:05:17.244087 [ 85 ] {9a754a31-2570-4209-90ed-ac9d4cf5c5e3} <Error> DynamicQueryHandler: Code: 62, e.displayText() = DB::Exception: Syntax error: failed at position 658 ('timestamp') (line 1, col 658): timestamp ORDER BY s0."timestamp"
plausible_events_db_1  |  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):
plausible_events_db_1  |
plausible_events_db_1  | 0. DB::parseQueryAndMovePosition(DB::IParser&, char const*&, char const*, std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, bool, unsigned long, unsigned long) @ 0x114d9116 in /usr/bin/clickhouse
plausible_events_db_1  | 1. ? @ 0xeeb7bee in /usr/bin/clickhouse
plausible_events_db_1  | 2. DB::executeQuery(DB::ReadBuffer&, DB::WriteBuffer&, bool, DB::Context&, std::__1::function<void (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> > const&, 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> > const&)>) @ 0xeebc552 in /usr/bin/clickhouse
plausible_events_db_1  | 3. DB::HTTPHandler::processQuery(DB::Context&, Poco::Net::HTTPServerRequest&, HTMLForm&, Poco::Net::HTTPServerResponse&, DB::HTTPHandler::Output&, std::__1::optional<DB::CurrentThread::QueryScope>&) @ 0xf55fb74 in /usr/bin/clickhouse
plausible_events_db_1  | 4. DB::HTTPHandler::handleRequest(Poco::Net::HTTPServerRequest&, Poco::Net::HTTPServerResponse&) @ 0xf563b81 in /usr/bin/clickhouse
plausible_events_db_1  | 5. Poco::Net::HTTPServerConnection::run() @ 0x11b2a0f5 in /usr/bin/clickhouse
plausible_events_db_1  | 6. Poco::Net::TCPServerConnection::start() @ 0x11b5fe2f in /usr/bin/clickhouse
plausible_events_db_1  | 7. Poco::Net::TCPServerDispatcher::run() @ 0x11b61841 in /usr/bin/clickhouse
plausible_events_db_1  | 8. Poco::PooledThread::run() @ 0x11c988e9 in /usr/bin/clickhouse
plausible_events_db_1  | 9. Poco::ThreadImpl::runnableEntry(void*) @ 0x11c9474a in /usr/bin/clickhouse
plausible_events_db_1  | 10. start_thread @ 0x9609 in /usr/lib/x86_64-linux-gnu/libpthread-2.31.so
plausible_events_db_1  | 11. clone @ 0x122293 in /usr/lib/x86_64-linux-gnu/libc-2.31.so
plausible_events_db_1  |  (version 21.2.5.5 (official build))

Which seems a bit odd, are the images tagged with latest not compatible with each other?
here is the output from docker-compose images

           Container                     Repository           Tag       Image Id       Size
---------------------------------------------------------------------------------------------
plausible_mail_1                  bytemark/smtp              latest   cd5c77c3bcd8   130.3 MB
plausible_plausible_1             plausible/analytics        latest   213327b74c3b   149 MB
plausible_plausible_db_1          postgres                   12       85f1c84fe307   314.1 MB
plausible_plausible_events_db_1   yandex/clickhouse-server   latest   eeafa0a212bf   556.7 MB

[ukutaht]

I get the same error logged when booting up on latest but the system does end up booting to a working state. So I'm not sure why your app is hanging, I cannot reproduce on my end.

[Jyggafey]

Ok, the issue was not directly related to the plausible setup. We are using shorewall to manage iptables rules and the version that we had deployed interfered with the rules that docker manages at boot. After we updated to shorewall version 5.2.4 and everything works as expected.

[Jyggafey]

just fyi when i test with the newly pinned clickhouse version i get this error.
That file has not changed since plausible was first installed, do you have an idea what that means?

plausible_events_db_1  | 2021.03.10 16:28:18.254530 [ 1 ] {} <Error> Application: Caught exception while loading metadata: Code: 62, e.displayText() = DB::Exception: Syntax error (in file /var/lib/clickhouse/metadata/system.sql): failed at position 19 (line 1, col 19): UUID 'ee046b70-06a8-45cb-a4f2-d3ebd2a7014c'
plausible_events_db_1  | ENGINE = Atomic
plausible_events_db_1  | . Expected one of: storage definition, ENGINE, ON, Stack trace (when copying this message, always include the lines below):
plausible_events_db_1  |
plausible_events_db_1  | 0. Poco::Exception::Exception(std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, int) @ 0x10542450 in /usr/bin/clickhouse
plausible_events_db_1  | 1. DB::Exception::Exception(std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, int) @ 0x8f4272d in /usr/bin/clickhouse
plausible_events_db_1  | 2. ? @ 0xe0850a7 in /usr/bin/clickhouse
plausible_events_db_1  | 3. ? @ 0xd5799d9 in /usr/bin/clickhouse
plausible_events_db_1  | 4. DB::loadMetadataSystem(DB::Context&) @ 0xd57c024 in /usr/bin/clickhouse
plausible_events_db_1  | 5. DB::Server::main(std::__1::vector<std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> >, std::__1::allocator<std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > > > const&) @ 0x8fc5fde in /usr/bin/clickhouse
plausible_events_db_1  | 6. ? @ 0x10464907 in /usr/bin/clickhouse
plausible_events_db_1  | 7. DB::Server::run() @ 0x8f97045 in /usr/bin/clickhouse
plausible_events_db_1  | 8. mainEntryClickHouseServer(int, char**) @ 0x8f8de23 in /usr/bin/clickhouse
plausible_events_db_1  | 9. main @ 0x8ee9799 in /usr/bin/clickhouse
plausible_events_db_1  | 10. __libc_start_main @ 0x21b97 in /lib/x86_64-linux-gnu/libc-2.27.so
plausible_events_db_1  | 11. _start @ 0x8ee902e in /usr/bin/clickhouse
plausible_events_db_1  |  (version 20.3.8.53 (official build))

[ukutaht]

Hmm yeah it looks like downgrading the server doesn't work. You should probably keep the newer version your database was created with.

[Jyggafey]

Would you recommend that we pin clickhouse to the version we have right now? Im not familiar with the clickhouse upgrade path / breaking changes policy.