GTREP database migration failure after upgrading from TDM 4.8.0 to TDM 4.9.1

Products

• CA Test Data Manager (Data Finder / Grid Tools)

Issue/Introduction

We have upgraded our on-premise TDM Server from TDM 4.8.0 to TDM 4.9.1, and see the following errors when the gtrep database migration is executed:

2021-01-19 20:09:05.803 UTC [INFO ] ---                                o.f.c.i.c.DbMigrate:  Current version of schema [XXXXX]: << Empty Schema >>

2021-01-19 20:09:05.803 UTC [WARN ] ---                                o.f.c.i.c.DbMigrate:  outOfOrder mode is active. Migration of schema [DM_TDM2012] may not be reproducible.

2021-01-19 20:09:05.806 UTC [INFO ] ---                                o.f.c.i.c.DbMigrate:  Migrating schema [XXXXX] to version 4.6.0.0 - schemas

2021-01-19 20:09:05.818 UTC [ERROR] ---                                o.f.c.i.c.DbMigrate:  Migration of schema [XXXXX] to version 4.6.0.0 - schemas failed! Changes successfully rolled back.

2021-01-19 20:09:05.823 UTC [ERROR] ---                               c.ca.tdm.schema.Main:  Migration failed: 'DbMigrate.FlywayMigrateSqlException:

Migration V4.6.0.0__schemas.sql failed

--------------------------------------

SQL State  : S0001

Error Code : 2714

Message    : There is already an object named 'pk_gtrep' in the database.

Location   : db/migration/gtrep/sqlserver/V4.6.0.0__schemas.sql (C:\Program Files\CA\CA Test Data Manager Portal\schema-management\file:\C:\Program Files\CA\CA Test Data Manager Portal\schema-management\lib\TDMGtrepSchema-4.9.100.4.jar!\db\migration\gtrep\sqlserver\V4.6.0.0__schemas.sql)

Line       : 2

Statement  : CREATE SCHEMA [pk_gtrep]

Note: We also need to migrate our on-premise TDM environment to the AWS platform.

Environment

TDM 4.9+

Cause

The reason for this error message is the 'schema_version' table in the GTREP db does not contain the 4.6.* patches entries!

This is an unusual issue, which has been known to happen if your gtrep repository has been upgraded consistently via the legacy Datamaker process (TDM 4.8.0 and older), instead of the current TDM portal process, which was introduced in TDM 4.8.1(as a patch). 

Because you upgraded directly from 4.8.0 to 4.9.1, you missed that critical patch that would have updated the 'schema_version' table with all the additional 4.6.* entries.

Resolution

Engineering has created a SQL script that will reset the gtrep database 'schema_version' table with the entries up to patch 4.8.0.8. 

 

For this process to work correctly, please follow the steps below.

1. Make sure that all TDM processes are stopped, i.e. TDM Portal, OrientDB, Datamaker not running.
   
   
2. Take a backup of the GTREP database.
   
   
3. Take a backup of the 'schema_version' table
   • Use gtrep;
   • Select *into dbo.schema_version2 from schema_version;
   • Select * from dbo.schema_version2;
     
     
4. Now that you have taken the db backup and also made a backup of the table, we need to delete the contents of the 'schema_version' table with the following command
   • Delete from schema_version;
   • Select * from schema_version;
     
     
5. The schema_version table now is empty, so please execute the attached SQL script that will load the necessary entries needed.
   
   
6. Verify that the number of rows in the table is 19 via
   • Select count(*) from dbo.schema_version;
     
     
7. Startup the OrientDB service.
   
   
8. Startup the TDM portal service to start the GTREP upgrade process.
   
   
9. Copy the necessary files to your GTREP_test configuration as well.

 

Additional Information

If you are migrating an existing TDM installation to another machine or platform:

1. Stop the TDM portal and OrientDB services.
   
   
2. Verify that the SQL Server instance and the user credentials are correct in the TDM Portal 'application.properties' file.
   
   
3. Connect to the SQL Server instance to make sure that the user account <USER> is the db owner (dbo), and the default schema is dbo.
   Note: The same change was made for the replication user as well. We also gave the user read/write, as well DDLadmin rights.
   
   
4. Delete the contents of the 'schema_version' table and modified the 'installed by user' from gtrep (default) to <USER>.
   
   
5. Copied all the OrientDB databases to the new machine, or new platform (AWS) installation.
   
   
6. Start up the OrientDB service.
   
   
7. Startup the TDM Portal service, which upgraded the gtrep repository and completed the normal startup process for TDM Portal.
   
   
8. Log into the TDM Portal and verified that all the functionality, such as all the self-service tiles are present.
   
   
9. (Optional) Connect to Datamaker and verify all the projects/versions were present.
   
   
10. (Optional) Verify the configuration of the TDoD (GTService). See https://knowledge.broadcom.com/external/article?articleId=139056 for more information.

 

If you have also moved your SQL Server instance, which hosts your GTREP repository database, to a new machine, or platform, you may see a 'Mismatch Service Name' error thrown when launching Datamaker. If you do, refer to https://knowledge.broadcom.com/external/article?articleId=5439 for steps to resolve this issue.