Problem creating Postgres database: Only UTF8 and en_US.UTF8 are supported

[sechmann]

Hello

I've been trying to create a simple SQLInstance with a SQLDatabase with no luck.

The resources i try to apply:

apiVersion: sql.cnrm.cloud.google.com/v1alpha3
kind: SQLInstance
metadata:
  name: postgres-instance
  namespace: vegar
spec:
  databaseVersion: POSTGRES_9_6
  region: europe-north1
  settings:
    tier: db-custom-1-3840
    ipConfiguration:
      authorizedNetworks:
        - name: Other Project NAT ip
          value: xyz.ip.goes.here # Actual IP here
---
apiVersion: sql.cnrm.cloud.google.com/v1alpha3
kind: SQLDatabase
metadata:
  name: postgres-database
  namespace: vegar
spec:
  charset: utf8mb4
  collation: utf8mb4_bin
  instanceRef:
    name: postgres-instance

The SQLInstance seems to be created just:

...
Events:
  Type    Reason    Age                  From                    Message
  ----    ------    ----                 ----                    -------
  Normal  Updating  54m (x5 over 2d23h)  sqlinstance-controller  Update in progress
  Normal  UpToDate  54m (x6 over 2d23h)  sqlinstance-controller  The resource is up to date

The SQLDatabase however:

...
Events:
  Type     Reason        Age                From                    Message
  ----     ------        ----               ----                    -------
  Normal   Updating      10m (x5 over 64m)  sqldatabase-controller  Update in progress
  Warning  UpdateFailed  10m (x5 over 64m)  sqldatabase-controller  Update call failed: Error waiting to create Database:

The cnrm-controller-manager logs:

2019/10/28 12:19:55 [SQLDatabase vegar/postgres-database]: starting reconcile
2019/10/28 12:19:55 [DEBUG] matching ID aura-256308/postgres-instance/postgres-database to regex (?P<project>[^/]+)/(?P<instance>[^/]+)/(?P<name>[^/]+).
2019/10/28 12:19:55 [DEBUG] importing project = aura-256308
2019/10/28 12:19:55 [DEBUG] importing instance = postgres-instance
2019/10/28 12:19:55 [DEBUG] importing name = postgres-database
2019/10/28 12:19:55 [DEBUG] Waiting for state to become: [success]
2019/10/28 12:19:58 [WARN] Removing SqlDatabase "postgres-instance:postgres-database" because it's gone
2019/10/28 12:19:58 [SQLDatabase vegar/postgres-database]: creating/updating underlying resource
2019/10/28 12:19:58 [DEBUG] Locking "google-sql-database-instance-aura-256308-postgres-instance"
2019/10/28 12:19:58 [DEBUG] Locked "google-sql-database-instance-aura-256308-postgres-instance"
2019/10/28 12:19:58 [DEBUG] Creating new Database: map[string]interface {}{"charset":"utf8mb4", "collation":"utf8mb4_bin", "instance":"postgres-instance", "name":"postgres-database"}
2019/10/28 12:19:58 [DEBUG] Waiting for state to become: [success]
2019/10/28 12:20:00 [DEBUG] Unlocking "google-sql-database-instance-aura-256308-postgres-instance"
2019/10/28 12:20:00 [DEBUG] Unlocked "google-sql-database-instance-aura-256308-postgres-instance"
ERROR: Error waiting to create Database:

2019/10/28 12:20:00 could not apply: Error waiting to create Database:
{"level":"error","ts":1572265200.6573868,"logger":"kubebuilder.controller","msg":"Reconciler error","controller":"sqldatabase-controller","request":"vegar/postgres-database","error":"Update call failed: Error waiting to create Database: \n","stacktrace":"cnrm.googlesource.com/cnrm/vendor/github.com/go-logr/zapr.(*zapLogger).Error\n\t/go/src/cnrm.googlesource.com/cnrm/vendor/github.com/go-logr/zapr/zapr.go:128\ncnrm.googlesource.com/cnrm/vendor/sigs.k8s.io/controller-runtime/pkg/internal/controller.(*Controller).processNextWorkItem\n\t/go/src/cnrm.googlesource.com/cnrm/vendor/sigs.k8s.io/controller-runtime/pkg/internal/controller/controller.go:217\ncnrm.googlesource.com/cnrm/vendor/sigs.k8s.io/controller-runtime/pkg/internal/controller.(*Controller).Start.func1\n\t/go/src/cnrm.googlesource.com/cnrm/vendor/sigs.k8s.io/controller-runtime/pkg/internal/controller/controller.go:158\ncnrm.googlesource.com/cnrm/vendor/k8s.io/apimachinery/pkg/util/wait.JitterUntil.func1\n\t/go/src/cnrm.googlesource.com/cnrm/vendor/k8s.io/apimachinery/pkg/util/wait/wait.go:133\ncnrm.googlesource.com/cnrm/vendor/k8s.io/apimachinery/pkg/util/wait.JitterUntil\n\t/go/src/cnrm.googlesource.com/cnrm/vendor/k8s.io/apimachinery/pkg/util/wait/wait.go:134\ncnrm.googlesource.com/cnrm/vendor/k8s.io/apimachinery/pkg/util/wait.Until\n\t/go/src/cnrm.googlesource.com/cnrm/vendor/k8s.io/apimachinery/pkg/util/wait/wait.go:88"}

I don't know if this matters, but the cnrm-controller pod is able to communicate with the instance:

$ ip=$(kubectl get sqlinstances postgres-instance -o jsonpath='{.status.ipAddress[0].ipAddress}')
$ kubectl exec -it -n cnrm-system cnrm-controller-manager-0 -- nc -v $ip 5432
35.228.x.y (35.228.x.y:5432) open

And this is where my troubleshooting adventure ends, I don't really know how to go on with the pieces of information I've gathered. I feel the error messages I've been given are quite vague.

Are there are other logs i could check out? Why is the call to "wait.Until" failing? "Reconcile error"?

[AlexBulankou]

@VegarM , I believe the problem is charset and collation. Only UTF8 and en_US.UTF8 are supported for Cloud SQL Postgres DB at creation. I believe this is the same issue described in details here.
CC @rileykarson

When I ran your configuration without specifying charset and collation, it succeeded. We will also track improving diagnostic messages so it is clear why it failed.