DB Upgrade failing during 11.1 upgrade

Products

• CA API Gateway

Issue/Introduction

While trying to upgrade one of the virtual gateway nodes from version 11.0 to 11.1 , we were able to install both the Layer7_API_PlatformUpdate_v11.1.00-Debian-17707.L7P and the Layer7_API_Gateway_Debian_v11.1.00-17707.L7P patches successfully. Though while upgrading the database the following error was received:

'liquibase.exception.MigrationFailedException: Migration failed for change set ssg-base.xml::add_pk_ID_AUTHOR_FILENAME::gateway:
Reason: liquibase.exception.DatabaseException: Duplicate entry 'update_ssg_version-gateway-upgrade_9.0.01.xml' for key 'DATABASECHANGELOG.PRIMARY' [Failed SQL: (1062) ALTER TABLE ssg_testUpgrade.DATABASECHANGELOG ADD PRIMARY KEY (ID, AUTHOR, FILENAME)]'

Environment

CA API Gateway 11.0 

Cause

A few files in the DATABASECHANGELOG table got corrupted specifically these two files: upgrade_9.0.01.xml and upgrade_9.0.01.xml

========

More customers reported the same issue, the root cause is, the old table has no primary key, so it allows duplicated ID+AUTHOR+FILENAME in the table, then when it tries to build the primary key on those columns during the DB upgrade, it fails as the primary key must be unique. (also cannot be null)

Here is the SQL to verify the data, (run it on ssg database)

-- check if the primary key is null

select * from DATABASECHANGELOG where ID is null or AUTHOR is null or FILENAME is null;

-- check if the primary key is duplicated

select count(*) DUP,ID,AUTHOR,FILENAME from DATABASECHANGELOG group by ID,AUTHOR,FILENAME having DUP>1;

 

Resolution

So we did the following .

1. Connect to Mysql
2. use ssg;
3. show tables;
4. describe DATABASECHANGELOG;
5. select liquibase, id from databasechangelog;
6. select * from DATABASECHANGELOG limit 1;
7. select filename, md5sum from DATABASECHANGELOG;

After that it was found out that the below two files inside the DATABASECHANGELOG was null (upgrade_9.0.01.xml ,upgrade_9.0.01.xml), so we deleted it and after that the database upgrade was successful.