Workflow tokens are not removed on schedule - PurgeWorkflowTokenAdaptor finds 0 workflow tokens for purging

Products

• VMware Aria Suite

Issue/Introduction

Symptoms:

• Maximum number of preserved runs per workflow is modified below previously configured values.
  • server.log fails to find any tokens:

2018-05-29 00:00:00.284+0200 [vcoSystemTaskScheduler-3] INFO {} [PurgeWorkflowTokenAdaptor] Purging workflow tokens done (in 283). 0 tokens purged
2018-05-29 01:00:00.170+0200 [vcoSystemTaskScheduler-4] INFO {} [PurgeWorkflowTokenAdaptor] Purging workflow tokens done (in 167). 0 tokens purged
2018-05-29 02:00:00.241+0200 [vcoSystemTaskScheduler-2] INFO {} [PurgeWorkflowTokenAdaptor] Purging workflow tokens done (in 239). 0 tokens purged
2018-05-29 03:00:00.101+0200 [vcoSystemTaskScheduler-4] INFO {} [PurgeWorkflowTokenAdaptor] Purging workflow tokens done (in 98). 0 tokens purged

• The workflow tokens are not removed on schedule and the following exception traces are observed the server.log file:
  java.lang.UnsupportedOperationException: query result offset is not supported
          at org.hibernate.dialect.pagination.TopLimitHandler.processSql(TopLimitHandler.java:50)
          at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1904)

• The database.hibernate.dialect value is set to "org.hibernate.dialect.SQLServerDialect" within /etc/vco/app-server/vmo.properties

Environment

VMware vRealize Orchestrator 7.4.x
VMware vRealize Automation 7.4.x

Cause

• The correct value for Microsoft SQL Server 2012 and above is: org.hibernate.dialect.SQLServer2012Dialect
• The used Hibernate SQL dialect is not proper - it is an older version. As a result SQL queries are not supported by the Hibernate database management engine.

Resolution

Modify vmo.properties and resolve cluster synchronization:

• For each member in the vRealize Orchestrator cluster:

1. SSH into each virtual appliance
2. Edit /etc/vco/app-server/vmo.properties
3. Change database.hibernate.dialect = org.hibernate.dialect.SQLServer2012Dialect

• Resolve cluster desynchronization in ControlCenter:

1. Ensure all partcipating cluster nodes have the updated dialect settings in vmo.properties
2. Secure Shell (SSH) to any node within the cluster
3. Execute:

/var/lib/vco/tools/configuration-cli/bin/vro-configure.sh sync-local

Note: Wait a few minutes as ControlCenter should issue a restart of all vco-server services.  Monitor the server.log for restart details.
If monitored through ControlCenter, the status check will swap to SHUTDOWN temporarily as services are restarted.