TDM GTREP DB Migration Fails when Starting the Portal Service after upgrading from 4.8 to 4.9 - Validate failed: Detected applied migration not resolved locally

Products

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

Issue/Introduction

We are upgrading TDM from 4.8 to 4.9. After the installation of all components, We are receiving this error in the TDM Portal startup.log file:
SEVERE: Schema migration failed with exit code '4'.

WARNING|wrapper|20-09-04 10:07:59|YAJSW: yajsw-stable-12.14
WARNING|wrapper|20-09-04 10:07:59|OS   : Windows Server 2012 R2/6.3/amd64
WARNING|wrapper|20-09-04 10:07:59|JVM  : AdoptOpenJDK/1.8.0_212/C:\Program Files\CA\CA Test Data Manager Portal\jre/64
INFO|wrapper|20-09-04 10:07:59|start delay: 0
INFO|wrapper|20-09-04 10:07:59|started process with pid 7508
INFO|7508/0|20-09-04 10:08:00|Sep 04, 2020 10:08:00 AM com.ca.tdm.tomcat.Bootstrap runSchemaMigrations
INFO|7508/0|20-09-04 10:08:00|INFO: Performing schema migrations
INFO|7508/0|20-09-04 10:08:00|Sep 04, 2020 10:08:00 AM com.ca.tdm.tomcat.Bootstrap runSchemaMigrations
INFO|7508/0|20-09-04 10:08:00|INFO: Executing 'cmd.exe /C C:\Program Files\CA\CA Test Data Manager Portal\schema-management\bin\schema-management.bat -m -w 300'
INFO|7508/0|20-09-04 10:08:02|Sep 04, 2020 10:08:02 AM com.ca.tdm.tomcat.Bootstrap run
INFO|7508/0|20-09-04 10:08:02|SEVERE: Schema migration failed with exit code '4'.
INFO|7508/0|20-09-04 10:08:02|com.ca.tdm.tomcat.ProcessingException: Schema migration failed with exit code '4'.
INFO|7508/0|20-09-04 10:08:02| at com.ca.tdm.tomcat.Bootstrap.runSchemaMigrations(Bootstrap.java:87)
INFO|7508/0|20-09-04 10:08:02| at com.ca.tdm.tomcat.Bootstrap.run(Bootstrap.java:52)
INFO|7508/0|20-09-04 10:08:02| at com.ca.tdm.tomcat.Bootstrap.main(Bootstrap.java:40)
INFO|7508/0|20-09-04 10:08:02| at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
INFO|7508/0|20-09-04 10:08:02| at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
INFO|7508/0|20-09-04 10:08:02| at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
INFO|7508/0|20-09-04 10:08:02| at java.lang.reflect.Method.invoke(Method.java:498)
INFO|7508/0|20-09-04 10:08:02| at org.rzo.yajsw.app.WrapperJVMMain.executeMain(WrapperJVMMain.java:60)
INFO|7508/0|20-09-04 10:08:02| at org.rzo.yajsw.app.WrapperJVMMain.main(WrapperJVMMain.java:43)
INFO|7508/0|20-09-04 10:08:02|
INFO|wrapper|20-09-04 10:08:03|restart process due to default exit code rule
INFO|wrapper|20-09-04 10:08:03|restart internal RUNNING controller killed restart handler
INFO|wrapper|20-09-04 10:08:03|stopping process with pid/timeout 7508 315000
INFO|wrapper|20-09-04 10:08:03|process exit code: 1

Looking at the TDMSchemaManagementStartup.log we see an error that states the 'SQLServer does not support setting the schema for the current session. Default schema NOT changed to dbo', which is odd because our gtrep already has the default schema set to dbo.
2020-09-04 14:08:02.136 UTC [INFO ] ---                           o.f.c.i.u.VersionPrinter:  Flyway 4.2.0 by Boxfuse
2020-09-04 14:08:02.151 UTC [INFO ] ---                         o.f.c.i.d.DbSupportFactory:  Database: jdbc:sqlserver://<Servername>:1433;useBulkCopyForBatchInsert=false;cancelQueryTimeout=-1;sslProtocol=TLS;jaasConfigurationName=SQLJDBCDriver;statementPoolingCacheSize=0;serverPreparedStatementDiscardThreshold=10;enablePrepareOnFirstPreparedStatementCall=false;fips=false;socketTimeout=0;authentication=NotSpecified;authenticationScheme=nativeAuthentication;xopenStates=false;sendTimeAsDatetime=true;trustStoreType=JKS;trustServerCertificate=false;TransparentNetworkIPResolution=true;serverNameAsACE=false;sendStringParametersAsUnicode=true;selectMethod=direct;responseBuffering=adaptive;queryTimeout=-1;packetSize=8000;multiSubnetFailover=false;loginTimeout=15;lockTimeout=-1;lastUpdateCount=true;encrypt=false;disableStatementPooling=true;databaseName=gtrep;columnEncryptionSetting=Disabled;applicationName=Microsoft JDBC Driver for SQL Server;applicationIntent=readwrite; (Microsoft SQL Server 13.0)
2020-09-04 14:08:02.286 UTC [INFO ] ---                  o.f.c.i.c.SqlScriptFlywayCallback:  Executing SQL callback: beforeValidate
2020-09-04 14:08:02.646 UTC [INFO ] ---                     o.f.c.i.d.s.SQLServerDbSupport:  SQLServer does not support setting the schema for the current session. Default schema NOT changed to dbo
2020-09-04 14:08:02.651 UTC [ERROR] ---                               c.ca.tdm.schema.Main:  Migration failed: 'FlywayException: Validate failed: Detected applied migration not resolved locally: 4.8.0.9'
org.flywaydb.core.api.FlywayException: Validate failed: Detected applied migration not resolved locally: 4.8.0.9
 at org.flywaydb.core.Flyway.doValidate(Flyway.java:1065)
 at org.flywaydb.core.Flyway.access$100(Flyway.java:72)
 at org.flywaydb.core.Flyway$1.execute(Flyway.java:975)
 at org.flywaydb.core.Flyway$1.execute(Flyway.java:971)
 at org.flywaydb.core.Flyway.execute(Flyway.java:1464)
 at org.flywaydb.core.Flyway.migrate(Flyway.java:971)
 at com.ca.tdm.schema.SchemaMigrator.migrate(SchemaMigrator.java:90)
 ... 2 common frames omitted
Wrapped by: com.ca.tdm.schema.ProcessingException: Migration failed: 'FlywayException: Validate failed: Detected applied migration not resolved locally: 4.8.0.9'
 at com.ca.tdm.schema.SchemaMigrator.migrate(SchemaMigrator.java:92)
 at com.ca.tdm.schema.Main.run(Main.java:92)
 at com.ca.tdm.schema.Main.main(Main.java:55)
2020-09-04 14:08:02.651 UTC [ERROR] ---                               c.ca.tdm.schema.Main:  Check log file C:\ProgramData\CA\CA Test Data Manager Portal\logs\TDMSchemaManagementStartup.log

Environment

TDM Portal 4.8.x, 4.9.x

Component : CA Test Data Manager

Cause

An issue happened when upgrading from 4.8.X to 4.9.X and the schema was not update correctly.

Resolution

NOTE: Prior to getting Engineering involved, the DBA made a modification to the user account which is accessing the gtrep database.
They added the db_owner permission, which didn't help the issue in of itself, as restarting the Portal service afterwards still failed.
As did the attempt to run a repair on the gterp database, and manually running the database migration.

Engineering joined the Webex, and reviewed the gtrep database's dbo.schema_version table, and the dbo.gtrep_entity_relationship table.
Both tables look correct.

Engineering deleted row 17 from the dbo.schema_version table, since this was the entry that is reported in the error - 4.8.0.9 entity_relationship desc (script V4.8.0.9_entity_relationship_desc.sql) from the gtrep db.
Example:

Example SQL command:
delete from schema_version where installed_rank =17

Then ran a repair from the command line:
PORTAL_INSTALL\schema-management\bin>schema-management.bat -r

Example steps:
1) Open a Command Prompt as Administrator
2) navigate the PORTAL_INSTALL\schema-management\bin directory. ( by default C:\Program Files\CA\CA Test Data Manager Portal\schema-management\bin)
3) run the repair operation:schema-management.bat -r
4) start the portal service

The repair was successful, but ended with the same 'SQLServer does not support setting the schema for the current session. Default schema NOT change to dbo'.


After restarting the CA Test Data Manager Portal service, Portal started without issues.

The assumption is deleting the entity relationship desc entry, and running the repair on the gtrep database, corrected the issue.

Additional Information

Engineering then modified the application.property file with the following entry to enable debug logging for Flyway:
'logging.level.org.flywaydb.core.Flyway=debug'.

The thought here is to get additional debugging to see what is the cause of the error.  

 

NOTE: Make sure if you are upgrading and at the same time the existing gtrep has been moved to a new database, to verify the C:\Program Files\CA\CA Test Data Manager Portal\conf\application.properties reflects the new database name.