API query error: unsupported Unicode escape sequence (SQLSTATE 22P05)

[yonirab]

Pre-requisites

• I have double-checked my configuration
• I have tested with the :latest image tag (i.e. quay.io/argoproj/workflow-controller:latest) and can confirm the issue still exists on :latest. If not, I have explained why, in detail, in my description below.
• I have searched existing issues and could not find a match for this bug
• I'd like to contribute the fix myself (see contributing guide)

What happened? What did you expect to happen?

After upgrading from v3.5.6 to v3.5.11, we see the following error in the Argo UI whenever trying to list Succeeded or Failed workflows:

{"code":13,"message":"ERROR: unsupported Unicode escape sequence (SQLSTATE 22P05)"}: ERROR: unsupported Unicode escape sequence (SQLSTATE 22P05)

argo-server logs show the following:

ERROR 2024-10-06T09:08:57.605638209Z [resource.labels.containerName: argo-server] time="2024-10-06T09:08:57.605Z" level=info duration="135.318µs" method=GET path=index.html size=487 status=0
INFO 2024-10-06T09:08:57.993832698Z [resource.labels.containerName: argo-server] 2024/10/06 09:08:57 Session ID: 00001
INFO 2024-10-06T09:08:57.993891852Z [resource.labels.containerName: argo-server] Query: SELECT count(*) as total FROM "argo_archived_workflows" WHERE (("clustername" = $1 AND "instanceid" = $2) AND "namespace" = $3 AND not exists (select 1 from argo_archived_workflows_labels where clustername = argo_archived_workflows.clustername and uid = argo_archived_workflows.uid and name = 'workflows.argoproj.io/controller-instanceid') AND exists (select 1 from argo_archived_workflows_labels where clustername = argo_archived_workflows.clustername and uid = argo_archived_workflows.uid and name = 'workflows.argoproj.io/phase' and value in ('Succeeded')))
INFO 2024-10-06T09:08:57.993898457Z [resource.labels.containerName: argo-server] Arguments: []interface {}{"default", "", "default"}
INFO 2024-10-06T09:08:57.993901898Z [resource.labels.containerName: argo-server] Stack:
INFO 2024-10-06T09:08:57.993904741Z [resource.labels.containerName: argo-server] fmt.(*pp).handleMethods@/usr/local/go/src/fmt/print.go:673
INFO 2024-10-06T09:08:57.993911097Z [resource.labels.containerName: argo-server] fmt.(*pp).printArg@/usr/local/go/src/fmt/print.go:756
INFO 2024-10-06T09:08:57.993913723Z [resource.labels.containerName: argo-server] fmt.(*pp).doPrint@/usr/local/go/src/fmt/print.go:1211
INFO 2024-10-06T09:08:57.993916162Z [resource.labels.containerName: argo-server] fmt.Append@/usr/local/go/src/fmt/print.go:289
INFO 2024-10-06T09:08:57.993918601Z [resource.labels.containerName: argo-server] log.(*Logger).Print.func1@/usr/local/go/src/log/log.go:261
INFO 2024-10-06T09:08:57.993921542Z [resource.labels.containerName: argo-server] log.(*Logger).output@/usr/local/go/src/log/log.go:238
INFO 2024-10-06T09:08:57.993924791Z [resource.labels.containerName: argo-server] log.(*Logger).Print@/usr/local/go/src/log/log.go:260
INFO 2024-10-06T09:08:57.993928471Z [resource.labels.containerName: argo-server] github.com/argoproj/argo-workflows/v3/persist/sqldb.(*workflowArchive).CountWorkflows@/go/src/github.com/argoproj/argo-workflows/persist/sqldb/workflow_archive.go:264
INFO 2024-10-06T09:08:57.993931447Z [resource.labels.containerName: argo-server] github.com/argoproj/argo-workflows/v3/server/workflow.(*workflowServer).ListWorkflows@/go/src/github.com/argoproj/argo-workflows/server/workflow/workflow_server.go:193
INFO 2024-10-06T09:08:57.993934447Z [resource.labels.containerName: argo-server] github.com/argoproj/argo-workflows/v3/pkg/apiclient/workflow._WorkflowService_ListWorkflows_Handler.func1@/go/src/github.com/argoproj/argo-workflows/pkg/apiclient/workflow/workflow.pb.go:1826
INFO 2024-10-06T09:08:57.993936732Z [resource.labels.containerName: argo-server] github.com/argoproj/argo-workflows/v3/server/apiserver.(*argoServer).newGRPCServer.RatelimitUnaryServerInterceptor.func5@/go/src/github.com/argoproj/argo-workflows/util/grpc/interceptor.go:65
INFO 2024-10-06T09:08:57.993942795Z [resource.labels.containerName: argo-server] github.com/argoproj/argo-workflows/v3/server/apiserver.(*argoServer).newGRPCServer.ChainUnaryServer.func6.1.1@/go/pkg/mod/github.com/grpc-ecosystem/go-grpc-middleware@v1.3.0/chain.go:25
INFO 2024-10-06T09:08:57.993945407Z [resource.labels.containerName: argo-server] github.com/argoproj/argo-workflows/v3/server/auth.(*gatekeeper).UnaryServerInterceptor.func1@/go/src/github.com/argoproj/argo-workflows/server/auth/gatekeeper.go:98
INFO 2024-10-06T09:08:57.993948144Z [resource.labels.containerName: argo-server] github.com/argoproj/argo-workflows/v3/server/apiserver.(*argoServer).newGRPCServer.ChainUnaryServer.func6.1.1@/go/pkg/mod/github.com/grpc-ecosystem/go-grpc-middleware@v1.3.0/chain.go:25
INFO 2024-10-06T09:08:57.993951345Z [resource.labels.containerName: argo-server] github.com/argoproj/argo-workflows/v3/util/grpc.glob..func1@/go/src/github.com/argoproj/argo-workflows/util/grpc/interceptor.go:45
INFO 2024-10-06T09:08:57.993953807Z [resource.labels.containerName: argo-server] github.com/argoproj/argo-workflows/v3/server/apiserver.(*argoServer).newGRPCServer.ChainUnaryServer.func6.1.1@/go/pkg/mod/github.com/grpc-ecosystem/go-grpc-middleware@v1.3.0/chain.go:25
INFO 2024-10-06T09:08:57.993974087Z [resource.labels.containerName: argo-server] github.com/argoproj/argo-workflows/v3/server/apiserver.(*argoServer).newGRPCServer.PanicLoggerUnaryServerInterceptor.func4@/go/src/github.com/argoproj/argo-workflows/util/grpc/interceptor.go:26
INFO 2024-10-06T09:08:57.993976959Z [resource.labels.containerName: argo-server] github.com/argoproj/argo-workflows/v3/server/apiserver.(*argoServer).newGRPCServer.ChainUnaryServer.func6.1.1@/go/pkg/mod/github.com/grpc-ecosystem/go-grpc-middleware@v1.3.0/chain.go:25
INFO 2024-10-06T09:08:57.993985441Z [resource.labels.containerName: argo-server] github.com/grpc-ecosystem/go-grpc-middleware/logging/logrus.UnaryServerInterceptor.func1@/go/pkg/mod/github.com/grpc-ecosystem/go-grpc-middleware@v1.3.0/logging/logrus/server_interceptors.go:31
INFO 2024-10-06T09:08:57.993988269Z [resource.labels.containerName: argo-server] github.com/argoproj/argo-workflows/v3/server/apiserver.(*argoServer).newGRPCServer.ChainUnaryServer.func6.1.1@/go/pkg/mod/github.com/grpc-ecosystem/go-grpc-middleware@v1.3.0/chain.go:25
INFO 2024-10-06T09:08:57.993990595Z [resource.labels.containerName: argo-server] github.com/grpc-ecosystem/go-grpc-prometheus.init.(*ServerMetrics).UnaryServerInterceptor.func3@/go/pkg/mod/github.com/grpc-ecosystem/go-grpc-prometheus@v1.2.0/server_metrics.go:107
INFO 2024-10-06T09:08:57.993992913Z [resource.labels.containerName: argo-server] Error: upper: slow query
INFO 2024-10-06T09:08:57.993995607Z [resource.labels.containerName: argo-server] Time taken: 0.65064s
INFO 2024-10-06T09:08:57.993997870Z [resource.labels.containerName: argo-server] Context: context.Background
INFO 2024-10-06T09:08:57.994000109Z [resource.labels.containerName: argo-server] {}
ERROR 2024-10-06T09:08:58.003874930Z [resource.labels.containerName: argo-server] time="2024-10-06T09:08:58.003Z" level=error msg="finished unary call with code Internal" error="rpc error: code = Internal desc = ERROR: unsupported Unicode escape sequence (SQLSTATE 22P05)" grpc.code=Internal grpc.method=ListWorkflows grpc.service=workflow.WorkflowService grpc.start_time="2024-10-06T09:08:57Z" grpc.time_ms=665.575 span.kind=server system=grpc
ERROR 2024-10-06T09:08:58.003922336Z [resource.labels.containerName: argo-server] time="2024-10-06T09:08:58.003Z" level=info duration=666.353369ms method=GET path=/api/v1/workflows/default size=83 status=500

Note also the following:

argo-production=> select count(*) from argo_archived_workflows where workflow::text LIKE '%\u0000%';
 count
-------
  1459
(1 row)

argo-production=> 

Our DB for archived workflows is Postgres 9.6.

We have the following defined in workflow-controller-configmap

  persistence: |
    archive: true
    archiveTTL: 180d
    connectionPool:
      maxIdleConns: 100
      maxOpenConns: 0
      connMaxLifetime: 0s
    nodeStatusOffLoad: true
    postgresql:
      database: argo-production
      host: <Our DB host>
      passwordSecret:
        key: password
        name: argo-postgres-config
      port: 5432
      tableName: argo_workflows
      userNameSecret:
        key: username
        name: argo-postgres-config

Version(s)

v3.5.11

Paste a minimal workflow that reproduces the issue. We must be able to run the workflow; don't enter a workflows that uses private images.

This is in our production environment immediately after updating to 3.5.11

Logs from the workflow controller

kubectl logs -n argo deploy/workflow-controller | grep ${workflow}

Logs from in your workflow's wait container

kubectl logs -n argo -c wait -l workflows.argoproj.io/workflow=${workflow},workflow.argoproj.io/phase!=Succeeded

[Joibel]

My guess is that one of your workflows has non-ascii characters in it that are upsetting sqllite. It's unclear if they're actually an invalid unicode escape sequence.

Possibly in something like the description of the workflow.

Can you look at the workflows in the cluster and work out which one(s) it might be and attach (a redacted version) of them. Ideally in a zip or tar file so as not to lose any bad escape characters via copy and pasting into github.