Error: "Exception while communicating with the database:The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "$usm_r_r000029af00000000". The conflict occurred in database "mdb", table "dbo.usm_rule_event_type", column 'event_type_id'"

Products

• CA Service Management - Asset Portfolio Management
• CA Service Management - Service Desk Manager

Issue/Introduction

When installing CA Service Catalog 17.1 and came into an error when configuring CA Service Catalog Components.

The following error appears in seeddata.log:

ERROR Exception while communicating with the database:The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "$usm_r_r000029af00000000". The conflict occurred in database "mdb", table "dbo.usm_rule_event_type", column 'event_type_id'.
com.microsoft.sqlserver.jdbc.SQLServerException: The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "$usm_r_r000029af00000000". The conflict occurred in database "mdb", table "dbo.usm_rule_event_type", column 'event_type_id'.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:217)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1635)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:426)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:372)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:6276)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1794)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:184)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:159)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate(SQLServerPreparedStatement.java:315)
at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:98)
at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:98)
at com.ca.usm.datasource.DBJDBC.update(DBJDBC.java:1233)
at com.ca.usm.datasource.DBSource.executeUpdate(DBSource.java:1527)
at com.ca.usm.datasource.DBSource.executeUpdate(DBSource.java:1502)
at com.ca.usm.util.seeddata.SeedDataImporter.enableFkAndTriggers(SeedDataImporter.java:351)
at com.ca.usm.util.seeddata.SeedDataImporter.importSeeddata(SeedDataImporter.java:626)
at com.ca.usm.util.seeddata.SeedDataImporter.main(SeedDataImporter.java:595)
2019/05/16 11.12.43.477 INFO Successfully loaded the seeddata

Environment

CA Service Catalog 17.1

Cause

Missing data in the usm_rule_event_type

Resolution

1. Compare the data in usm_rule_event_type with the data of a test environment in the same release version. There must be missing data. Create a new SQL Script that contains only the missing data.

2. Take a backup of the usm_rule_event_type table of the mdb.

3. Get into MDB via SQL mananagement studio and run the script from step 1 to add the missing data.

4. Stop catalog service

5. Delete seeddata.log from C:\Program Files\CA\Service Catalog\logs\install

6. Get into MDB via SQL mananagement studio and run the following

exec USM_TOGGLE_FK_TRIGGER_SP 'ALL','DISABLE'

7. Then double-click usm.cmd to invoke catalog's command line , go to scripts folder (ex: cd scripts) , and run that seeddata command again :
seeddata.bat -n "C:\Program Files\CA\Service Catalog\config.properties" -o "C:\Program Files\CA\Service Catalog\config.properties" "C:\Program Files\CA\Service Catalog\view\scripts\MDB\SQL"

Wait 5 minutes

8. Check seeddata.log to make sure it doesn't throw any error.

9. then go back to MDB via SQL mamanagement studio , run the following:

exec USM_TOGGLE_FK_TRIGGER_SP 'ALL','ENABLE'