Logging in to the VMware vRealize Automation fails with the error: An Internal error has occurred

Products

• VMware Aria Suite

Issue/Introduction

Symptoms:

• Cannot log in to VMware vRealize Automation (formerly known as VMware vCloud Automation Center for Server)
• Logging in to the VMware vRealize Automation (formerly known as VMware vCloud Automation Center for Server) fails
• You see the error:
  
  Internal Error
  An Internal error has occurred. If the problem persists, please contact your system administrator.
  
  
• In the VMware vRealize Automation Appliance log file, you see entries similar to:
  
  ERROR: type "hstore" does not exist
  Unsuccessful: create table cat_resource
  Unsuccessful: create table cat_request
  Unsuccessful: create table work_item
  
  These error happens at the time of VMware vRealize Automation startup.
  
  
• The catalina.out file located at /var/log/vcac/catalina.out reports errors similar to:
  
  14:07:10,370 [tomcat-http--3189] [work-item] ERROR org.hibernate.engine.jdbc.spi.SqlExceptionHelper.logExceptions:144 - ERROR: relation "work_item" does not exist
  Position: 55
  14:07:10,374 [tomcat-http--3189] [work-item] ERROR com.vmware.vcac.platform.service.rest.resolver.ApplicationExceptionHandler.handleUnexpectedException:860 - ERROR: relation "work_item" does not exist
  Position: 55; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: ERROR: relation "work_item" does not exist
  Position: 55
  org.springframework.dao.InvalidDataAccessResourceUsageException: ERROR: relation "work_item" does not exist
  Position: 55; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: ERROR: relation "work_item" does not exist
  
  
• You see the tables work_item, cat_request, and cat_resource within the VMware vRealize Automation database.

Environment

VMware vCloud Automation Center for Server 6.0.x

Cause

This issue occurs when an external database is being used and the hstore extension is not created. This extension is required when creating some of the vCAC tables.

Resolution

To resolve this issue, create the hstore extension in the VMware vRealize Automation (formerly known as VMware vCloud Automation Center for Server) database.

To create the hstore extension:

1. Log in to the external Postgres database using the pgAdmin tool.
2. Run this SQL statement in the pgAdmin console to connect to the VMware vRealize Automation database:
   
   \connect "DB_NAME";
   
   Note: DB_NAME should be changed to VMware vRealize Automation database name.
   
   
3. Run this SQL statement in the pgaAmin console to create the hstore extension:
   
   create extension hstore;
   
   
4. Restart the VMware vRealize Automation (formerly known as VMware vCloud Automation Center for Server) Appliance.
   
   Note: You may have to wait for 15 to 20 minutes for the server to reboot.