DB Upgrade failing during 11.1 upgrade

Products

• CA API Gateway

Issue/Introduction

While trying to upgrade one of the virtual gateway node from version 11.0 to 11.1 , we were able to install both Layer7_API_PlatformUpdate_v11.1.00-Debian-17707.L7P & Layer7_API_Gateway_Debian_v11.1.00-17707.L7P successfully. Though while upgrading database version we're getting below error: 

'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

11.0 

Cause

Few files in the DATABASECHANGELOG got corrupted specifically this two files. upgrade_9.0.01.xml ,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 primay key on those columns during DB upgrade, it failes as 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 primay key is null

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

-- check if the primvay 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.