[Bug] MySQL connection with TLS/SSL is broken

[esn89]

What are you really trying to do?

Using the settings here:
https://github.com/temporalio/helm-charts/pull/411/files
And here:
https://github.com/temporalio/helm-charts/blob/main/charts/temporal/values/values.postgresql.yaml

to connect to MySQL over TLS

Describe the bug

I currently have this as my values.yaml:

server:
  config:
    persistence:
      default:
        driver: "sql"
        sql:
          driver: "mysql8"
          host: "1.2.3.4"
          port: 3306
          database: "temporal"
          user: "root"
          existingSecret: "tmp"
          maxConns: 20
          maxIdleConns: 10
          maxConnLifetime: "1h"
          tls:
            enabled: true
            enableHostVerification: true
            caFile: /var/ssl/certs/server-ca.crt
            certFile: /var/ssl/certs/client-cert.pem
            keyFile: /var/ssl/certs/client-key.pem
      visibility:
        driver: "sql"
        sql:
          driver: "mysql8"
          host: "1.2.3.4"
          port: 3306
          database: "temporal_visibility"
          user: "root"
          existingSecret: "tmp"
          maxConns: 20
          maxIdleConns: 10
          maxConnLifetime: "1h"
          tls:
            enabled: true
            enableHostVerification: true
            caFile: /var/ssl/certs/server-ca.crt
            certFile: /var/ssl/certs/client-cert.pem
            keyFile: /var/ssl/certs/client-key.pem
    additionalVolumes:
      - name: mysql-tls
        secret:
          secretName: mysql-tls
    additionalVolumeMounts:
      - name: mysql-tls
        mountPath: /var/ssl/certs
elasticsearch:
  enabled: false
prometheus:
  enabled: false
grafana:
  enabled: false
cassandra:
  enabled: false
postgresql:
  enabled: false
mysql:
  enabled: true

And in the additionalVolumes, it is a kubernetes secret which looks like this:

apiVersion: v1
data:
  client-cert.pem: <base64 stuff>
  client-key.pem: <base64 stuff>
  server-ca.pem:  <base64 stuff>
kind: Secret
metadata:
  name: mysql-tls
  namespace: temporal
type: Opaque

Minimal Reproduction

When I install it like so:

helm install temporal temporalio/temporal -f vvv.yaml --version 0.46.2 --namespace temporal

The pods go into CrashLoopBackOff and the error message I see is:

[Fx] Error returned: received non-nil error from function "go.temporal.io/server/temporal".ServerOptionsProvider
	/home/runner/work/docker-builds/docker-builds/temporal/temporal/fx.go:184:
sql schema version compatibility check failed: unable to read DB schema version keyspace/database: temporal error: no usable database connection found
[Fx] ERROR		Failed to initialize custom logger: could not build arguments for function "go.uber.org/fx".(*module).constructCustomLogger.func2
	/home/runner/go/pkg/mod/go.uber.org/fx@v1.22.0/module.go:292:
failed to build fxevent.Logger:
could not build arguments for function "go.temporal.io/server/temporal".init.func8
	/home/runner/work/docker-builds/docker-builds/temporal/temporal/fx.go:1004:
failed to build log.Logger:
received non-nil error from function "go.temporal.io/server/temporal".ServerOptionsProvider
	/home/runner/work/docker-builds/docker-builds/temporal/temporal/fx.go:184:
sql schema version compatibility check failed: unable to read DB schema version keyspace/database: temporal error: no usable database connection found
Unable to create server. Error: could not build arguments for function "go.uber.org/fx".(*module).constructCustomLogger.func2 (/home/runner/go/pkg/mod/go.uber.org/fx@v1.22.0/module.go:292): failed to build fxevent.Logger: could not build arguments for function "go.temporal.io/server/temporal".init.func8 (/home/runner/work/docker-builds/docker-builds/temporal/temporal/fx.go:1004): failed to build log.Logger: received non-nil error from function "go.temporal.io/server/temporal".ServerOptionsProvider (/home/runner/work/docker-builds/docker-builds/temporal/temporal/fx.go:184): sql schema version compatibility check failed: unable to read DB schema version keyspace/database: temporal error: no usable database connection found.

I disabled TLS on my database and was able to connect without any of the TLS settings, but one of our requirements is to use TLS. So I doubt that it is a CloudSQL GCP issue.

What other settings am I missing?

Furthermore, is the example as shown here ONLY for PostgreSQL and MySQL is not supported?

Edit: someone here said that they got it working with MySQL, but I can't seem to.

Environment/Versions

Helm Chart: 0.46.2

[luiscosta18]

Hello @esn89

I'm using aws rds postgresl 16 (rds.force_ssl is enabled by default since v15). In values.yaml I've:

tls:
   enabled: true
   caFile: <global-bundle.pem>

And temporal is able to connect to the database.
Can you please use temporal-admin-tools image tag corresponding to your temporal version, execute the schema migrations using temporal-sql-tool (with parameters --tls
--tls-cert-file <ca_file> --tls-cert-file <cert_file> --tls-key-file <key_file> --pl mysql8), as described here
https://docs.temporal.io/self-hosted-guide/upgrade-server, for both temporal +temporal_visibility dbs. Then restart temporal pods and see if the error remains. Use the latest version of helm chart.

[robholland]

As @luiscosta18 was able to get this working I shall close this issue, but please re-open if you still have problems when using the v1 rc1 of the helm chart or higher.