Clickhouse query error

[VictorElHajj]

Hi! I've been struggling to get this running for a few hours now and finally reached something I've not been able to figure out. I get all the way to just after "Admin user created successful!" then clickhouse does this:

`plausible_events_db_1  | 2021.04.06 15:35:24.662756 [ 54 ] {3f871460-c2be-47f9-bc1e-e0dbe73638ea} <Error> executeQuery: 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 (version 21.3.2.5 (official build)) (from 172.20.0.4:37103) (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):
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) @ 0x1184fcb6 in /usr/bin/clickhouse
plausible_events_db_1  | 1. ? @ 0xf12c0ae 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&)>) @ 0xf130a78 in /usr/bin/clickhouse
plausible_events_db_1  | 3. DB::HTTPHandler::processQuery(DB::Context&, DB::HTTPServerRequest&, DB::HTMLForm&, DB::HTTPServerResponse&, DB::HTTPHandler::Output&, std::__1::optional<DB::CurrentThread::QueryScope>&) @ 0xf84675a in /usr/bin/clickhouse
plausible_events_db_1  | 4. DB::HTTPHandler::handleRequest(DB::HTTPServerRequest&, DB::HTTPServerResponse&) @ 0xf84a8ee in /usr/bin/clickhouse
plausible_events_db_1  | 5. DB::HTTPServerConnection::run() @ 0xf8d35ff in /usr/bin/clickhouse
plausible_events_db_1  | 6. Poco::Net::TCPServerConnection::start() @ 0x11f7ccbf in /usr/bin/clickhouse
plausible_events_db_1  | 7. Poco::Net::TCPServerDispatcher::run() @ 0x11f7e6d1 in /usr/bin/clickhouse
plausible_events_db_1  | 8. Poco::PooledThread::run() @ 0x120b4df9 in /usr/bin/clickhouse
plausible_events_db_1  | 9. Poco::ThreadImpl::runnableEntry(void*) @ 0x120b0c5a 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  |
plausible_events_db_1  | 2021.04.06 15:35:24.663069 [ 54 ] {3f871460-c2be-47f9-bc1e-e0dbe73638ea} <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) @ 0x1184fcb6 in /usr/bin/clickhouse
plausible_events_db_1  | 1. ? @ 0xf12c0ae 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&)>) @ 0xf130a78 in /usr/bin/clickhouse
plausible_events_db_1  | 3. DB::HTTPHandler::processQuery(DB::Context&, DB::HTTPServerRequest&, DB::HTMLForm&, DB::HTTPServerResponse&, DB::HTTPHandler::Output&, std::__1::optional<DB::CurrentThread::QueryScope>&) @ 0xf84675a in /usr/bin/clickhouse
plausible_events_db_1  | 4. DB::HTTPHandler::handleRequest(DB::HTTPServerRequest&, DB::HTTPServerResponse&) @ 0xf84a8ee in /usr/bin/clickhouse
plausible_events_db_1  | 5. DB::HTTPServerConnection::run() @ 0xf8d35ff in /usr/bin/clickhouse
plausible_events_db_1  | 6. Poco::Net::TCPServerConnection::start() @ 0x11f7ccbf in /usr/bin/clickhouse
plausible_events_db_1  | 7. Poco::Net::TCPServerDispatcher::run() @ 0x11f7e6d1 in /usr/bin/clickhouse
plausible_events_db_1  | 8. Poco::PooledThread::run() @ 0x120b4df9 in /usr/bin/clickhouse
plausible_events_db_1  | 9. Poco::ThreadImpl::runnableEntry(void*) @ 0x120b0c5a 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.3.2.5 (official build))

I looked at the two other discussion posts with similar issues (#842 and #816) but neither helped.

My docker compose

version: "3.3"
services:

  mail:
    image: bytemark/smtp
    restart: always

  plausible_db:
    image: postgres:12
    hostname: plausible_db
    volumes:
      - plausible_db:/var/lib/postgresql/data
    env_file:
      - postgres.env
    networks:
      - plausible-net
    ports:
      - 5432

  plausible_events_db:
    image: yandex/clickhouse-server:21.3.2.5
    volumes:
      - plausible_event:/var/lib/clickhouse
      - ./clickhouse/clickhouse-config.xml:/etc/clickhouse-server/config.d/logging.xml:ro
      - ./clickhouse/clickhouse-user-config.xml:/etc/clickhouse-server/users.d/logging.xml:ro
    ulimits:
      nofile:
        soft: 262144
        hard: 262144
    networks:
      - plausible-net

  plausible:
    image: plausible/analytics:latest
    command: sh -c "sleep 10 && /entrypoint.sh db createdb && /entrypoint.sh db migrate && /entrypoint.sh db init-admin && /entrypoint.sh run"
    depends_on:
      - plausible_db
      - plausible_events_db
      - geoip
      - mail
    volumes:
      - geoip:/geoip:ro
    environment:
      - GEOLITE2_COUNTRY_DB=/geoip/GeoLite2-Country.mmdb
    env_file:
      - plausible-conf.env
    networks:
      - plausible-net
      - web
    labels:
      - 'traefik.enable=true'
      - 'traefik.http.routers.plausible.rule=Host(`stats.website.tld`)'
      - 'traefik.http.routers.plausible.entrypoints=websecure'
      - 'traefik.http.routers.plausible.tls=true'
      - 'traefik.http.routers.plausible.tls.certresolver=le'
      - 'traefik.http.services.plausible.loadbalances.server.port=8000'

  geoip:
    image: maxmindinc/geoipupdate
    environment:
      - GEOIPUPDATE_EDITION_IDS=GeoLite2-Country
      - GEOIPUPDATE_FREQUENCY=168 # update every 7 days
    env_file:
     - geoip.env
    volumes:
      - ./geoip:/usr/share/GeoIP

volumes:
  geoip:
  plausible_db:
  plausible_event:

networks:
  plausible-net:
  web:
    external: true

Plausible conf env:

ADMIN_USER_EMAIL=[email]
ADMIN_USER_NAME=[username]
ADMIN_USER_PWD=[password]
BASE_URL=http:https://stats.website.tld
SECRET_KEY_BASE="long secret key"
DISABLE_REGISTRATION=true
DATABASE_URL=postgres:https://plausible:test@plausible_db:5432/plausible

The two clickhouse config files are the same as in the /hosting repo, no idea what is wrong as the error messages are not very descriptive.

[ukutaht]

What's actually wrong, are you getting a 500 error? Is so, please run with LOG_LEVEL=debug and show the logs of the actual failing request.

These specific error messages from Clickhouse are most likely a red herring.

[VictorElHajj]

I have

    labels:
      - 'traefik.enable=true'
      - 'traefik.http.routers.plausible.rule=Host(`stats.website.tld`)'
      - 'traefik.http.routers.plausible.entrypoints=websecure'
      - 'traefik.http.routers.plausible.tls=true'
      - 'traefik.http.routers.plausible.tls.certresolver=le'
      - 'traefik.http.services.plausible.loadbalances.server.port=8000'

on the plausible container (which has worked for many other services). I also set the same host name but with https in the BASE_URL env like in my first post.
my traefik config itself is

debugLevel = "DEBUG"
defaultEntryPoints = ["web", "websecure"]

[api]
    insecure = false
    dashboard = false

[providers]
    [providers.docker]
    exposedByDefault = false
    network = "web"

[entryPoints]
    [entryPoints.web]
    address = ":80"
    [entryPoints.web.http]
        [entryPoints.web.http.redirections]
            [entryPoints.web.http.redirections.entryPoint]
            to = "websecure"
            scheme = "https"
    [entryPoints.websecure]
    address = ":443"


[certificatesResolvers]
    [certificatesResolvers.le.acme]
    email = "[my email]"
    storage = "/acme.json"
    tlsChallenge = true

My plausible container also has the same external "web" network as the other services. It's also basically the same as the traefik example provided in plausible/hosting

[ukutaht]

Hmm. Do you understand where the 404 on https://stats.bogdanov.red is actually coming from? I was looking for the Server header in the response because if it's hitting the Plausible container, it would be set to Server: cowboy. So that's why I'm thinking it actually never hits the Plausible container.

Is this your actual config?

      - 'traefik.http.routers.plausible.rule=Host(`stats.website.tld`)'

Seems like the wrong generic host is configured, no?

[VictorElHajj]

Sorry I was trying to anonymize my setup. It is stats.bogdanov.red in the actual setup.

[VictorElHajj]

Okay... This was propably a traefik / docker issue rather than a plausible issue. Without changing anything I did docker network rm on my plausible network and then did docker-compose up again and it worked.... really strange.

But thanks for the help!! :)

[ukutaht]

Weird ¯_(ツ)_/¯

Glad it works now!