In an upgraded CA SDM 14.1 environment, running "pdm_load -r -f tucson.dat" to delete a row in ca_state_province fails with: The DELETE statement conflicted with the REFERENCE constraint "ca_location_fk04".
Article ID: 4747
Updated On:
Products
Issue/Introduction
The post-installation instructions for cumulative patch #3 (14.1.03) for CA Service Desk Manager (CA SDM) 14.1 includes a step to run the command, "pdm_load -r -f tucson_delete.dat".
When the environment has been upgraded from CA SDM 12.6 to CA SDM 14.1, the command may fail and the following block of messages would be written to the stdlog:
12/06 14:23:29.64 sdmsrv001 sql_agent 3636 SIGNIFICANT sql_agent.c 245 STARTUP of sql_agent:mdb:dbload-#932:
12/06 14:23:29.97 sdmsrv001 sql_agent 3636 ERROR sqlclass.c 1010 SQL Execute failed: [Microsoft OLE DB Provider for SQL Server] [ SQL Code=3621 SQL State=01000] The statement has been terminated.; [Microsoft OLE DB Provider for SQL Server] [ SQL Code=547 SQL State=23000] The DELETE statement conflicted with the REFERENCE constraint "ca_location_fk04". The conflict occurred in database "mdb", table "dbo.ca_location", column 'state'.
12/06 14:23:29.98 sdmsrv001 sql_agent 3636 ERROR sqlclass.c 1011 Clause (DELETE FROM ca_state_province WHERE id = ?) Input (<id:int>1)
12/06 14:23:29.98 sdmsrv001 sql_agent 3636 ERROR sqlsrvr.c 651 Execution failed. MISC_DB_ERROR
12/06 14:23:29.98 sdmsrv001 dbload 932 ERROR dbintf.c 745 Db callback event:6 row:1 error:15
12/06 14:23:29.98 sdmsrv001 dbload 932 ERROR dbintf.c 761 SELECT #creation_user , #last_update_user , #version_number , #inactive , #id FROM ca_state_province
12/06 14:23:29.98 sdmsrv001 dbload 932 ERROR dbintf.c 757 Db callback event:5 error:1
12/06 14:23:29.99 sdmsrv001 dbload 932 ERROR dbintf.c 761 SELECT #creation_user , #last_update_user , #version_number , #inactive , #id FROM ca_state_province
12/06 14:23:30.99 sdmsrv001 dbload 932 SIGNIFICANT dbload.c 766 Dbload Completed
Cause
The foreign key constraint "ca_location_fk04", which is defined for the table "ca_location", is preventing the deletion of a specified row that exists in the ca_state_province table. This is because one or more rows in ca_location reference the row in ca_state_province.
For example, for SQL Server, the Key, ca_location_fk04, is defined according to the following SQL commands:
ALTER TABLE [dbo].[ca_location] WITH CHECK ADD CONSTRAINT [ca_location_fk04] FOREIGN KEY([state])
REFERENCES [dbo].[ca_state_province] ([id])
ALTER TABLE [dbo].[ca_location] CHECK CONSTRAINT [ca_location_fk04]
The row that is attempted to be deleted is specified in the "tucson_delete.dat". That file contains:
#This migration file is to delete itil and non itil data.
###############################################################################
TABLE ca_state_province
id creation_user last_update_user version_number
{ "1", "acme", "acme", "1" }
In the mdb database, the row in ca_state_province would be similar to the following:
id | inactive | symbol | creation_user | creation_date | last_update_user | last_update_date | version_number | description | exclude_registration | delete_time | tenant |
1 | 0 | null | acme | 1081787612 | acme | 1081787612 | 1 | null | NULL | NULL | NULL |
Environment
Resolution
1. Take a database backup of the ca_location and the ca_state_province tables of the mdb database.
2. Determine the Locations which are referencing the State/Province that matches the entry that the pdm_load command is attempting to delete.
For example, in a Windows environment, you could run the following command:
pdm_extract -f "select id, location_name, state from ca_location where state=1" > ca_location_orig.out
3. If there are only a few locations, update those via the CA SDM web browser GUI by editing the location and updating the State/Province field to a different value.
Alternatively, for a bulk update, you could update the rows in the ca_location table that are returned in the previous step using pdm_load.
Important! Be sure that you understand the risks of using pdm_load before you use it. See Additional Information section of this technical document.
To use pdm_load to make the change, you could perform the following steps:
For example, on Windows, first run this command: copy ca_location_orig.out ca_location_new.out
Then, edit the file, ca_location_new.out, and, for each row, change the "1" that is in the third column to "".
For example, for the location "loc1" entry, change:
{ "18503A79B9C1614784F920514E30A71C" ,"loc1" ,"1" }
to:
{ "18503A79B9C1614784F920514E30A71C" ,"loc1" ,"" }
Next, run pdm_load to load the NULL value into the "state" column of the corresponding rows in the location table using the following command: pdm_load -a -f ca_location_new.dat
4. Re-run the post-installation step, in particular, the re-run command: pdm_load -r -f tucson_delete.dat
Additional Information
The post-installation steps should guide you as to the location of the file, "tucson_delete.dat" When you run the pdm_load command as shows in this technical document, the current directory must contain the file.
Before you run pdm_load, please review the "Important!" note that is included on the following page: pdm_load--Add, Update, and Delete Database Records
Feedback
