Service Desk Manager r12.9 Cumulative #1 patch cannot be applied with an error RC=3

Products

• SUPPORT AUTOMATION- SERVER
• CA Service Desk Manager - Unified Self Service
• CA Service Desk Manager
• CA Service Management - Asset Portfolio Management
• CA Service Management - Service Desk Manager

Issue/Introduction

When applying the Cumulative patch which contains MDB upgrade or migrating Service Desk Manager (SDM), MDB upgrade fails. install_MDB.log file shows the following error messages. 

02-22,12:03:27 MDBTools install error=3
02-22,12:03:27 Setupmdb exit /B return code=3

Environment

Release:
Component: SDDB

Cause

Generally the error is caused when a row in a table pointing to non-existing data on another table.

e.g. If the following error message is written, it means there is a data in the contact_type field of the ca_contact table pointing to non-existing data in ca_contact_type table.

02-22,12:03:27 ERROR - ALTER TABLE [ca_contact] ADD CONSTRAINT [ca_contact_fk07] FOREIGN KEY ( [contact_type] ) REFERENCES [ca_contact_type] ( [id] )

Resolution

You need to remove orphaned data before you upgrade MDB as per below. 

1. Run a SQL query and check if there is a data in a table pointing to non-existing data in a referenced table. 

e.g. In this case, run the following query to check if data in ca_contact table pointing to non-existing data in ca_contact_type table.
SELECT * from ca_contact where contact_type IS NOT NULL AND contact_type NOT IN (SELECT id from ca_contact_type) 

2. If you find a data pointing to non-existing data, please modify it through the web interface, or other ways to collect the data, such as pdm_extract/pdm_load commands, if the data is not displayed in the web interface. 

3. Apply the Cumulative patch or migrate SDM again. 

Additional Information

TEC428520: How to extract data by date range using pdm_extract

TEC477556: How do we delete a single record from Service Desk in our Lab Environment?