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?

[xrl]

No, however, it was made from the same scope DNS and network wise.

[xrl]

Someone mentioned I might want a full DNS name since erlang may not respect Kubernete's resolv.conf with ndots and name hierarchy expansion.

[ruslandoga]

No, however, it was made from the same scope DNS and network wise.

It might make things clearer if you try it from the container.

Someone mentioned I might want a full DNS name since erlang may not respect Kubernete's resolv.conf with ndots and name hierarchy expansion.

It doesn't seem like plausible has any problem resolving and connecting to clickhouse. That error is a response from ClickHouse.

[xrl]

From inside the plausible container I can use wget:

~ $ wget http://default:[email protected]:8123/plausible
Connecting to clickhouse.plausible.svc.cluster.local:8123 (10.100.94.202:8123)
wget: server returned error: HTTP/1.1 404 Not Found

Does the clickhouse URL need to be a valid URL or does plausible parse the URL and remove the path element and use the path/dbname elsewhere?

[ruslandoga]

does plausible parse the URL and remove the path element and use the path/dbname

Yes, it's done by the underlying library, Ecto. The URL is parsed into a structure with database, username, password, host, port, etc. fields which are then used in our ClickHouse client.

I'm not very familiar with wget, but with curl you can just put these parameters into the query string:

$ curl 'http://clickhouse:8123/?database=plausible&username=default&password=yNd6jhJEFN' -d 'SELECT count(*) FROM events_v2' -v

[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,