Plausible fails to connect to clickhouse even with correct CLICKHOUSE_DATABASE_URL

[xrl]

I am spinning up plausible+clickhouse inside of Kubernetes using argo-cd. I have one argo application to manage the plausible helm release (homemade chart), and another argo application to manage the clickhouse helm release (bitnami clickhouse).

Here are the application definitions:

apiVersion: argoproj.io/v1alpha1
kind: Application
metadata:
  name: plausible
  namespace: argo
spec:
  project: default
  source:
    repoURL: https://github.com/my-org/plausible.git
    targetRevision: HEAD
    path: charts/plausible
    helm:
      releaseName: plausible
  destination:
    server: https://kubernetes.default.svc
    namespace: plausible
---
apiVersion: argoproj.io/v1alpha1
kind: Application
metadata:
  name: plausible-clickhouse
  namespace: argo
spec:
  project: default
  source:
    repoURL: https://charts.bitnami.com/bitnami
    targetRevision: 3.4.4
    chart: clickhouse
    helm:
      releaseName: clickhouse
  destination:
    server: https://kubernetes.default.svc
    namespace: plausible

I have confirmed that clickhouse+plausible have happy pods:

% k get pods
NAME                          READY   STATUS    RESTARTS   AGE
clickhouse-5b498cb688-fwqfr   1/1     Running   0          6d20h
clickhouse-shard0-0           1/1     Running   0          8h
clickhouse-shard0-1           1/1     Running   0          21h
clickhouse-shard0-2           1/1     Running   0          21h
clickhouse-shard1-0           1/1     Running   0          16h
clickhouse-shard1-1           1/1     Running   0          21h
clickhouse-shard1-2           1/1     Running   0          21h
clickhouse-zookeeper-0        1/1     Running   0          21h
clickhouse-zookeeper-1        1/1     Running   0          16h
clickhouse-zookeeper-2        1/1     Running   0          8h
plausible-8587857b77-pd424    1/1     Running   0          18h

And the services are laid out like this:

% k get svc 
NAME                            TYPE        CLUSTER-IP       EXTERNAL-IP   PORT(S)                                        AGE
clickhouse                      ClusterIP   10.100.94.202    <none>        8123/TCP,9000/TCP,9004/TCP,9005/TCP,9009/TCP   10d
clickhouse-headless             ClusterIP   None             <none>        8123/TCP,9000/TCP,9004/TCP,9005/TCP,9009/TCP   21h
clickhouse-zookeeper            ClusterIP   10.100.69.11     <none>        2181/TCP,2888/TCP,3888/TCP                     21h
clickhouse-zookeeper-headless   ClusterIP   None             <none>        2181/TCP,2888/TCP,3888/TCP                     21h
plausible                       ClusterIP   10.100.220.100   <none>        8000/TCP                                       9d

And plausible is talking to the clickhouse service like this:

% k get pod plausible-8587857b77-pd424 -o yaml
kind: Pod
metadata:
  name: plausible-8587857b77-pd424
  namespace: plausible
spec:
  containers:
  - env:
    - name: BASE_URL
      value: https://plausible.my-thing.com
    - name: DISABLE_REGISTRATION
      value: "true"
    - name: POSTGRES_PORT
      value: "5432"
    - name: DATABASE_URL
      valueFrom:
        secretKeyRef:
          key: POSTGRES_URL
          name: plausible-postgres-direct-secret
    - name: CLICKHOUSE_DATABASE_URL
      value: http://default:$admin-password-from-the-helm-created-secret@clickhouse:8123/plausible
    envFrom:
    - secretRef:
        name: plausible-postgres-direct-secret
    image: plausible/analytics:v2.0.0-rc.2
    livenessProbe:
      failureThreshold: 3
      httpGet:
        path: /
        port: http
        scheme: HTTP
      periodSeconds: 10
      successThreshold: 1
      timeoutSeconds: 1
    name: plausible
    ports:
    - containerPort: 8000
      name: http
      protocol: TCP
    readinessProbe:
      failureThreshold: 3
      httpGet:
        path: /
        port: http
        scheme: HTTP
      periodSeconds: 10
      successThreshold: 1
      timeoutSeconds: 1
    resources:
      limits:
        cpu: "2"
        memory: 10Gi
      requests:
        cpu: "2"
        memory: 10Gi

but when I look at the logs for plausible I get an infinite stream of these lines:

01:37:35.801 [error] Ch.Connection (#PID<0.3392.0>) failed to connect: ** (Ch.Error) Code: 81. DB::Exception: Database plausible doesn't exist. (UNKNOWN_DATABASE) (version 23.5.3.24 (official build))

but if I query the clickhouse service using curl I can see the database:

# echo 'SHOW DATABASES' | curl 'http://default:$admin-password-from-the-helm-created-secret@clickhouse:8123/' --data-binary @-
INFORMATION_SCHEMA
default
information_schema
plausible
system

Why is plausible unable to acccess the plausible database from clickhouse?

[ruslandoga]

👋 @xrl

Is the curl request made from plausible container? Can you try a request that queries a table from plausible database?

[walkerrandolphsmith]

19:50:29.312 [error] GenServer Plausible.Session.WriteBuffer terminating
** (Ch.Error) Code: 516. DB::Exception: : Authentication failed: password is incorrect, or there is no user with such name.

If you have installed ClickHouse and forgot password you can reset it in the configuration file.
The password for default user is typically located at /etc/clickhouse-server/users.d/default-password.xml
and deleting this file will reset the password.
See also /etc/clickhouse-server/users.xml on the server where ClickHouse is installed.

: while executing 'FUNCTION dictHas('plausible.acquisition_channel_paid_sources_dict' :: 47, lower(utm_source) : 15) -> dictHas('plausible.acquisition_channel_paid_sources_dict', lower(utm_source)) UInt8 : 82'. (AUTHENTICATION_FAILED) (version 24.12.6.70 (official build))

    (ecto_sql 3.12.1) lib/ecto/adapters/sql.ex:1096: Ecto.Adapters.SQL.raise_sql_call_error/1
    (stdlib 6.2.2) gen_server.erl:2393: :gen_server.try_terminate/3
    (stdlib 6.2.2) gen_server.erl:2594: :gen_server.terminate/10
    (stdlib 6.2.2) proc_lib.erl:329: :proc_lib.init_p_do_apply/3
Last message: :tick

Note that from the resource running clickhouse I get the following

plausible-plausible-analytics-events-db-6bf7d6c46f-bf42t:/# clickhouse-client --user user --password 'XXX' --query="SHOW DATABASES"
plausible

[ruslandoga]

Hm, that's a weird place to get an error :)

I wonder if you could try connecting "without" creds since it seems to have worked in #3070 (reply in thread) (I am assuming those commands were executed from within plausible container)

CLICKHOUSE_DATABASE_URL="http://plausible-plausible-analytics-events-db:8123/plausible"

Other than that, and double checking the password, I don't know what could be wrong ...

---

If nothing works out you could try starting with colocated, no auth, default db setup in https://github.com/plausible/community-edition and building from there, maybe it would be easier to pinpoint the problem then.

[walkerrandolphsmith]

clickhouse-client --user non_default_user --password='password' --query="SHOW DATABASES"
plausible

clickhouse-client --query="SHOW DATABASES"
plausible
plausible_events_db
...

@ruslandoga, Once I use http://plausible-plausible-analytics-events-db:8123/plausible_events_db it works.

[walkerrandolphsmith]

I ran GRANT ALL ON plausible_events_db.* TO my_user; and it started working 🥳

[walkerrandolphsmith]

@xrl I bet this might be the answer to your original question. Want to try and let us know?

[kashalls]

So, bitnami's clickhouse chart for whatever reason wont replicate datatbases from one shard to another. I brought it down to 1 shard and everything worked for whatever reason. I've been trying to get plausible to work with it and I can't even get the container to start right now.

clickhouse service uses a loadbalancer to all the shards. When plausible connects it picks a shard. The shard the db was created on might not be the same one it reads from and vise versa.

[kashalls]

Right. I did the same thing recommended above for both: https://github.com/kashalls/home-cluster/blob/main/kubernetes/apps/default/plausible/app/helmrelease.yaml#L38

[ruslandoga]

Can please you check the exit status and logs of the init container? It seems to have failed if there is no salts table when plausible container starts up. Seems like BASE_URL env var might be missing from the init container.

[kashalls]

Added that env. the initContainer is showing as exit code 0 and is completed. Does not seem like that command is wanting to create/migrate the database.

[ruslandoga]

Have you tried args written like this?

args:
  - /entrypoint.sh db createdb && /entrypoint.sh db migrate

I don't know much about YAML, but the last working configuration for k8s had this exact formatting, so it might be worth trying out.

Here's what GPT wrote about that difference

Yes, there are some key differences between the two YAML snippets.

1. Structure and Parsing:

   • In the first snippet:

     command: ['/bin/sh', '-c']
     args: ['/entrypoint.sh', 'db', 'createdb', '&&', '/entrypoint.sh', 'db', 'migrate']
     

     Both command and args have lists of individual arguments. The shell command (/bin/sh -c) expects a single string argument that represents the command to be run. In the provided format, it's not receiving a full single string command.

   • In the second snippet:

     command:
       - "/bin/sh"
       - "-c"
     args:
       - /entrypoint.sh db createdb && /entrypoint.sh db migrate
     

     The structure is more appropriate for the shell command. The args is correctly giving a single string to the shell command as an argument.

2. Execution Implications:

   • In the first snippet:
     The way args is structured suggests that each item is a separate argument. It means that if passed to a command, the shell will try to execute /entrypoint.sh with the following arguments: db, createdb, &&, /entrypoint.sh, db, migrate. The shell won't treat && as a control operator, but rather as an argument. This would likely cause an error because the shell doesn't interpret && correctly.

   • In the second snippet:
     The command args is correctly structured as a single string: /entrypoint.sh db createdb && /entrypoint.sh db migrate, which the shell (/bin/sh -c) can interpret and execute correctly.

In summary, the second YAML snippet is correct if you aim to execute /entrypoint.sh db createdb followed by /entrypoint.sh db migrate. The first one will likely result in an error due to the incorrect interpretation of the args.

[kashalls]

That was it. Its running now,