Loading RA WS REST Solution throws: ORA-00001: unique constraint on PK

Products

CA Automic Workload Automation - Automation Engine CA Automic One Automation

Issue/Introduction

When trying to load the RA Webservice REST solution using the ucybdbld utility, the load fails and the following is in the load log file:

20220419/184428.158 - U00038164 Loading RA Solution.
20220419/184428.173 - U00038257 Version check result:
Version of the RA Solution: '4.6.1'.
Version of database objects: '12.3.5+hf.2.build.1614598643055'.

20220419/184430.861 - U00038257 Version check result:
Version of the RA Solution: '4.6.1'.
Version of database objects: '12.3.5+hf.2.build.1614598643055'.

20220419/184430.861 - U00038149 Generating RA object 'WEBSERVICEREST'.
20220419/184430.877 - U00038144 Deleting existing RA Solution 'WEBSERVICEREST'.
...
20220419/184439.666 - U00038145 RA Solution 'WEBSERVICEREST' has been deleted successfully.
20220419/184439.728 - U00038167 Generating RA Agent 'WEBSERVICEREST'
20220419/184439.775 - U00038168 RA Agent 'WEBSERVICEREST' successfully loaded.
20220419/184439.775 - U00038151 RA object 'WEBSERVICEREST' successfully created.
20220419/184439.775 - U00038152 Generating RA Subcomponent 'REST'.
20220419/184439.822 - U00038156 RA Subcomponent 'REST' successfully generated.
20220419/184439.837 - U00038157 Generating RA connection 'RESTCONNECTION'.
20220419/184439.837 - U00038150 A database error occurred: 'OCIStmtExecute', 'ERROR ', '', 'ORA-00001: unique constraint (AUTOMIC.PK_OVW) violated'.
20220419/184440.025 - U00038067 ABORTING due to error!

Restart the program when cause of error has been corrected.

Cause

Running a database=4 trace shows the following delete statement being run which deletes the CONN.WEBSERVICEREST.RESTCONNECTION from UC_OBJECT_TEMPLATE in client 0:

20220420/142013.844 - delete from OVW where dbms_lob.substr( OVW_Value1, 200, 1 ) = 'CONN.WEBSERVICEREST.RESTCONNECTION' and OVW_OH_Client = 0

This should delete the CONN.WEBSERVICEREST.RESTCONNECTION entry from the UC_OBJECT_TEMPLATE variable and it returns that it was successful. The last thing the load does is run the statement:

20220420/142021.762 - INSERT INTO OVW (OVW_OH_Idnr, OVW_VValue, OVW_OH_Client, OVW_VRName, OVW_Value1) VALUES (114, 'CONN.WEBSERVICEREST.RESTCONNECTION', 0, 'FREI', 'CONN.WEBSERVICEREST.RESTCONNECTION')

which will insert the value back into the variables object. This is where the load fails with the message:

20220420/142021.762 - U00038150 A database error occurred: 'OCIStmtExecute', 'ERROR ', '', 'ORA-00001: unique constraint (AUTOMIC.PK_OVW) violated'.

This same message will occur if the template variable has the key already in it even if you run the statement outside of Automic.

Environment

Release : 12.3

Component :

Resolution

Ultimately this issue was caused by having a UC_OBJECT_TEMPLATE variable objects in another client. Check for other UC_OBJECT_TEMPLATE variables with the following statement: select OH_NAME, OH_CLIENT from oh where oh_name = 'UC_OBJECT_TEMPLATE'

This should return the client that has the other UC_OBJECT_TEMPLATE