Table 'ssg_testUpgrade.work_queue' doesn't exist

Products

• CA API Gateway

Issue/Introduction

While trying to deploy a container gateway connected to existing ssg and OTK databases, the following error is observed:

Starting Liquibase at 14:23:48 (version 4.5.0 #52 built at 2021-09-27 16:19+0000)

Unexpected error running Liquibase: Migration failed for change set upgrade_10.0.00.xml::dropForeignKeyConstraint_workqueue::gateway:

     Reason: liquibase.exception.DatabaseException: Table 'ssg_testUpgrade.work_queue' doesn't exist [Failed SQL: (1146) ALTER TABLE ssg_testUpgrade.work_queue DROP FOREIGN KEY work_queue_security_zone]

For more information, please use the --logLevel flag

ERROR - Failed to create or update the Gateway's database.

Environment

Gateway 10.X (upgrading from Gateway 9.4)

Cause

The work_queue table doesn't exist in GW 10.X but it did exist in 9.X. The way the DB upgrade works is a little odd because it uses information in a little-known table called DBCHANGELOG. It's assumed that the information about the work_queue table was already removed.

Resolution

As a workaround, you can create the work_queue table and try the upgrade again. Before performing these steps, take a backup of your database.

Please do the following:

1. Log in to mysql
# mysql ssg

2. Create the table
mysql> CREATE TABLE work_queue ( goid binary(16) NOT NULL, version int(10) NOT NULL, name varchar(128) NOT NULL, max_queue_size int(11) NOT NULL, thread_pool_max int(11) NOT NULL, reject_policy varchar(32) NOT NULL, security_zone_goid binary(16) DEFAULT NULL, PRIMARY KEY (goid), UNIQUE KEY index_work_queue_name (name), KEY work_queue_security_zone (security_zone_goid), CONSTRAINT work_queue_security_zone FOREIGN KEY (security_zone_goid) REFERENCES security_zone (goid) ON DELETE SET NULL ON UPDATE NO ACTION ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

This will create the work_queue table and the foreign key, allowing the database Upgrader to remove the key (and probably the table), and record it in DBCHANGELOG table of MySQL.