Mongo to Postgresql data migration is unsuccessful with SQLSTATE 22001 error

Mongo to Postgresql migration fails when you migrate oauthtoken mongo collection data to oauthtoken table.

Symptoms

The following error message is displayed in the ibm-iam-operator pod logs:

ERROR: value too long for type character varying(64) (SQLSTATE 22001)

{"level":"error","ts":"2024-06-17T21:26:44Z","logger":"migration_worker","msg":"Failed to INSERT into table","MongoDB.DB":"OAuthDBSchema","MongoDB.Collection":"OauthToken","table":"oauthdbschema.oauthtoken","error":"ERROR: value too long for type character varying(64) (SQLSTATE 22001)","stacktrace":"github.com/IBM/ibm-iam-operator/migration.insertOauthTokens\n\t/home/prow/go/src/github.com/IBM/ibm-iam-operator/migration/migration.go:440\ngithub.com/IBM/ibm-iam-operator/migration.MongoToV1\n\t/home/prow/go/src/github.com/IBM/ibm-iam-operator/migration/migration.go:330\ngithub.com/IBM/ibm-iam-operator/migration.(*Migration).Run\n\t/home/prow/go/src/github.com/IBM/ibm-iam-operator/migration/migration.go:194\ngithub.com/IBM/ibm-iam-operator/migration.Migrate\n\t/home/prow/go/src/github.com/IBM/ibm-iam-operator/migration/migration.go:212"}

{"level":"error","ts":"2024-06-17T21:26:44Z","logger":"migration_worker","msg":"Migration of oauthdbschema.oauthtoken not successful","MongoDB.DB":"OAuthDBSchema","MongoDB.Collection":"OauthToken","failedCount":29,"successCount":0,"error":"encountered errors that prevented the migration of documents","stacktrace":"github.com/IBM/ibm-iam-operator/migration.insertOauthTokens\n\t/home/prow/go/src/github.com/IBM/ibm-iam-operator/migration/migration.go:457\ngithub.com/IBM/ibm-iam-operator/migration.MongoToV1\n\t/home/prow/go/src/github.com/IBM/ibm-iam-operator/migration/migration.go:330\ngithub.com/IBM/ibm-iam-operator/migration.(*Migration).Run\n\t/home/prow/go/src/github.com/IBM/ibm-iam-operator/migration/migration.go:194\ngithub.com/IBM/ibm-iam-operator/migration.Migrate\n\t/home/prow/go/src/github.com/IBM/ibm-iam-operator/migration/migration.go:212"}

Resolving the problem

To resolve the issue, increase the username column size for the oauthtoken table in the oauthdbschema schema.

Create a new deployment.

apiVersion: apps/v1
kind: Deployment
metadata:
  name: cert-test
spec:
  replicas: 1
  selector:
    matchLabels:
      app: webtest
  template:
    metadata:
      labels:
        app: webtest
    spec:
      containers:
        - image: ghcr.io/cloudnative-pg/webtest:1.6.0
          name: cert-test
          volumeMounts:
            - name: secret-volume-root-ca
              mountPath: /etc/secrets/ca
            - name: secret-volume-app
              mountPath: /etc/secrets/app
          ports:
            - containerPort: 8080
          env:
            - name: DATABASE_URL
              value: >
                sslkey=/etc/secrets/app/tls.key
                sslcert=/etc/secrets/app/tls.crt
                sslrootcert=/etc/secrets/ca/ca.crt
                host=common-service-db-rw
                dbname=im
                user=im_user
                sslmode=require
            - name: SQL_QUERY
              value: SELECT 1
          readinessProbe:
            httpGet:
              port: 8080
              path: /tx
      volumes:
        - name: secret-volume-root-ca
          secret:
            secretName: im-datastore-edb-secret
            defaultMode: 0600
        - name: secret-volume-app
          secret:
            secretName: im-datastore-edb-secret
            defaultMode: 0600

Connect to the common-service-db database with the cert-test pod.

$ oc exec -it cert-test-<pod name>  -- bash
1000700000@cert-test-<pod name>:/$ psql 'sslkey=/etc/secrets/app/tls.key sslcert=/etc/secrets/app/tls.crt sslrootcert=/etc/secrets/ca/ca.crt host=common-service-db-rw dbname=im user=im_user sslmode=require'
psql (10.21 (Ubuntu 10.21-0ubuntu0.18.04.1), server 16.2)
WARNING: psql major version 10, server major version 16.
         Some psql features might not work.
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

Run the SQL statement to change the username column size.

ALTER TABLE oauthdbschema.oauthtoken ALTER COLUMN username TYPE VARCHAR(100);

Restart the ibm-iam-operator pod.
```
oc delete ibm-iam-operator-<pod name>
```
Delete cert-test deployment.
```
oc delete deployment cert-test
```