Liquibase Database Exception Causing Database Schema Upgrade Failure

Products

• CA API Gateway

Issue/Introduction

Migration failed for change set upgrade_x.y.z.xml::create_published_service_properties::gateway:Reason: liquibase.exception.DatabaseException: Error executing SQL CREATE TABLEssg_testUpgrade.published_service_property (published_service_goid BINARY(16) NOT NULL, name VARCHAR(128) NOTNULL, value MEDIUMTEXT NOT NULL): Table 'published_service_property' already exists

Environment

11

Resolution

The workaround consists of several steps. You will compare the DATABASECHANGELOG entries with
entries belonging to another baseline Gateway of the same version. If there isn't another existing Gateway database
available to serve as a baseline for comparison, you'll need to install a new Gateway in a different server (separate from
the production server) for this purpose. For example, if you’re upgrading from version 10.1 to 11 of the Gateway, perform
a fresh installation of Gateway version 10.1 in a separate server. Next, compare the DATABASECHANGELOG table from
the freshly installed SSG Gateway version 10.1 database with the one from the Gateway that is experiencing upgrade
difficulties:
On both Gateway MySQL databases, run:
# use ssg;
# select count(*) from DATABASECHANGELOG;
If the returned count numbers differ between the two databases, it’s likely the DATABASECHANGELOG was corrupted in
the problematic/production Gateway.
You’ll want to rectify this by copying the change log entries from the database of a baseline Gateway or freshly installed
Gateway and replacing the log entries from the database of the problematic Gateway:
1. Backup the production database which contains the Gateway schema you wish to upgrade.
2. On the baseline or fresh installation of the Gateway of the same version, run the following command:
# mysqldump ssg DATABASECHANGELOG > /home/ssgconfig/dbchangelog_rows.sql
3. Open dbchangelog_rows.sql and locate the following line:
INSERT INTO 'DATABASECHANGELOG' VALUES (...);
4. Copy the entire line including the contents inside (...).
5. Stop the production Gateway before making changes to the database:
# service ssg stop
6. From the MySQL client, run:
# use ssg;
# delete * from DATABASECHANGELOG;
7. Paste the INSERT INTO ‘DATABASECHANGELOG’ VALUES (...) ; line into the command line to run.
8. Run the following command to save your changes to the production database that you are upgrading:
# commit;
9. Restart the production Gateway with the following command:
# service ssg start
10. Continue to upgrade the production database schema using the SSG Config Menu as before.