Share:|

This post will share information regarding recent improvements in BladeLogic Server Automation (BSA) designed to greatly reduce the likelihood of referential integrity errors occurring during the cleanupDatabase command. We will discuss the referential integrity errors and options for applying the solution to your environment depending on the version of BSA you have installed.


Problem Summary

 

Many BSA customers who run the cleanupDatabase CLI command will be familiar with referential integrity errors, such as the below examples, appearing in their appserver logs.

 

Oracle:

 

The reference SUpdateServerPropertyJob.SUpdateServerPropertyJob could not be resolved. Cause: java.sql.SQLIntegrityConstraintViolationException: ORA-02292: integrity constraint (BLADELOGIC.FK458_JOB) violated - child record found

 

Caused by: java.sql.SQLIntegrityConstraintViolationException: ORA-02292: integrity constraint (BLADELOGIC.FK674_JOB_RUN) violated - child record found

 

Caused by: java.sql.SQLIntegrityConstraintViolationException: ORA-02292: integrity constraint (BLADELOGIC.FK499_JOB) violated - child record found

 

SQL Server:

 

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The DELETE statement conflicted with the REFERENCE constraint "FK2403_JOB". The conflict occurred in database "BladeLogic", table "dbo.batch_job_job".

 

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The DELETE statement conflicted with the REFERENCE constraint "FK1_COMP_ENTRY_RESULT". The conflict occurred in database "bladelogic", table "dbo.comp_entry_result", column 'compliance_rule_result_id'.

 

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The DELETE statement conflicted with the REFERENCE constraint "FK1_RSLVDSYSPKCDC_JOB_RESULT". The conflict occurred in database "bladelogic", table "dbo.prov_job_rslvd_sys_pkg_cdc", column 'result_id'.

 

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The DELETE statement conflicted with the REFERENCE constraint "FK767_COMPONENT". The conflict occurred in database "bladelogic",

 

 

 

The name of the constraint listed in the error message can vary greatly but the error message will always contain one of the following depending on the DB Type:

 

Oracle: ORA-02292: integrity constraint violated

SQL Server: The DELETE statement conflicted with the REFERENCE constraint

 

 

Root Cause

 

The root cause of these referential integrity errors during cleanup can usually be attributed to order of deletion issues. There are occasional, and very specific, cases where errors such as these can occur during cleanupDatabase due to other reasons ( e.g. BSA job activity while cleanup is running, Smart Group issue KA404005) but the vast majority of these errors occur due to an attempt to delete a record from Table A which is still referenced by records in Table B.

 

Solution

 

The solution to these issues is to modify the foreign key constraints which encounter this issue most frequently to perform cascading deletes. A foreign key with "On Delete Cascade" means that if a record in the parent table is deleted, then the corresponding records in the child table will automatically be deleted. BMC now provides SQL scripts, for both Oracle and SQL Server, which modify these constraints accordingly to help avoid these issues during cleanupDatabase.

 

 

Obtaining the Solution

 

BSA 8.5:

BSA 8.5.1 contains most of the constraint modifications out of the box. Some additional constraints were modified after BSA 8.5.1 was released. So BSA 8.5.1 customers should still run the appropriate 85 SQL script for their database platform to pick up the complete list of constraint modifications. The solution can be applied by downloading the appropriate zip file for your database type and following the instructions in the readme.txt file. The steps involve running a single SQL script on the bladelogic schema/db however this does require the BSA Appservers to be stopped while the script is being run. The script does not typically take more than a few minutes to complete.

 

8.5.X Oracle: ftp://ftp.bmc.com/pub/BSACleanupConstraints/85/alter85xConstraints_oracle.rar

 

8.5.X SQL Server: ftp://ftp.bmc.com/pub/BSACleanupConstraints/85/alter85xConstraints_sqlserver.rar

 

 

 

BSA 8.3.3:

BSA 8.3.3 contains a subset of the constraint modifications out of the box. See the "Complete List of Modified Constraints" section below for specifics. However, some additional constraints were modified after BSA 8.3.3 was released. So BSA 8.3.3 customers should still run the appropriate 83 SQL script for their database platform to pick up the complete list of constraint modifications. The solution can be applied by downloading the appropriate zip file for your database type and following the instructions in the readme.txt file. The steps involve running a single SQL script on the bladelogic schema/db however this does require the BSA Appservers to be stopped while the script is being run. The script does not typically take more than a few minutes to complete.

 

8.3.X Oracle: ftp://ftp.bmc.com/pub/BSACleanupConstraints/83/alter83xConstraints_oracle.rar

8.3.X SQL Server: ftp://ftp.bmc.com/pub/BSACleanupConstraints/83/alter83xConstraints_sqlserver.rar

 

 

 

BSA 8.3.0, 8.3.1 or 8.3.2: BSA 8.3 versions prior to 8.3.3 will not have any of the modified constraint definitions out of the box. The solution can be applied by downloading the appropriate zip file for your database type and following the instructions in the readme.txt file. The steps involve running a single SQL script on the bladelogic schema/db however this does require the BSA Appservers to be stopped while the script is being run. The script does not typically take more than a few minutes to complete.

 

8.3.X Oracle: ftp://ftp.bmc.com/pub/BSACleanupConstraints/83/alter83xConstraints_oracle.rar

8.3.X SQL Server: ftp://ftp.bmc.com/pub/BSACleanupConstraints/83/alter83xConstraints_sqlserver.rar

 

 

BSA 8.2.X: BSA 8.2 will also not have any of the required constraint modifications out of the box. The solution can be applied by downloading the appropriate zip file for your database type and following the instructions in the readme.txt file. The steps involve running a single SQL script on the bladelogic schema/db however this does require the BSA Appservers to be stopped while the script is being run. The script does not typically take more than a few minutes to complete.

 

8.2.X Oracle: ftp://ftp.bmc.com/pub/BSACleanupConstraints/82/alter82xConstraints_oracle.rar

8.2.X SQL Server: ftp://ftp.bmc.com/pub/BSACleanupConstraints/82/alter82xConstraints_sqlserver.rar

 

 

Complete List of Modified Constraints:

 

The following 26 constraints are fixed "Out of the Box" in BSA 8.3.3:

 

FK458_JOB

FK674_JOB_RUN

FK683_JOB_RUN

FK1_COMP_ENTRY_RESULT

FK1_COMP_ENTRY_RESULT_DETAIL

FK653_COMPONENT

FK2449_AUDIT_OBJECT

FK1034_AUDIT_OBJECT_PART

FK1025_AUDIT_OBJECT_PART

FK1014_AUDIT_OBJECT_PART

FK2654_JOB

FK2658_JOB

FK2328_JOB_RUN

FK2_PATCHING_JOB_RUN_PART

FK269_COMPONENT_EXCEPTION

FK2403_JOB

FKX3_DEPOT_OBJECT_LOCATION

FK2262_JOB_RUN -> Oracle Only

FK2119_JOB_RESULT

FK1_JOB_RUN_EVENT

FK2652_SCHEDULE

FK767_COMPONENT

FK648_COMPONENT

FK248_COMPONENT

FK51_BL_VALUE

FK1346_BLGROUP

 

The following additional constraints are not fixed out of the box in BSA 8.3.3 but are fixed both in the standalone SQL scripts for 8.2.X and 8.3.X and also fixed out of the box in 8.5 SP1:

FK499_JOB

FK1_VGPACKAGE_VMNIC_INFO

FK1_RSLVDSYSPKCDC_JOB_RESULT

FK459_JOB

FK249_CR_GROUP_RESULT

FK1014_AUDITRSLT_2_RSLT

FK1450_SCHEDULE

FK305_DEPOT_OBJECT
FK1_JOB_RUN*
FK1013_AUDIT_OBJECT_PART*

 

* - Added to package on 06/10/2014. If previous version of the package has been applied, the latest package can be downloaded and rerun without any issue to pick up the two additional indexes.

 

Conclusion

 

Applying these constraint modifications to your BSA Database is important to prevent referential integrity errors during the BSA cleanupDatabase process. A referential integrity error itself does not cause any DB problems but it does prevent cleanup from running to completion therefore preventing DB and FileServer space from being reclaimed.
If a referential integrity error matching one of the constraints listed above is encountered, the appropriate fix can be downloaded and applied as described above (note: does require BSA Appservers to be stopped). Likewise, these can be applied proactively to avoid such errors in the future. Applying these scripts to BSA 8.2.X or 8.3.X will not affect the upgrade to either 8.3.3 or 8.5.X and no steps need to be taken differently during those upgrades as a result of previously running these scripts.
If a constraint violation is encountered during cleanup with a constraint which is not listed in the above list, then please let us know via a support ticket. The zip files in the FTP locations referenced above will be updated should any modifications be released by BMC.

 

Please also see the following Pulse article on a related topic - how to avoid database locking when running BSA DB cleanup. After applying the constraint medications described in this article, it is important to also apply the new indexes described in this secondary article.

 

I hope this post provides the information you need to prevent and resolve referential integrity errors during the BSA cleanupDatabse command. If you have any questions or comments on this topic, please feel free to use the comments section below. Also, please take a moment to rate the article so we can continue to monitor which topics and formats users find most useful.


Join the Customer Support Community and give us feedback if our efforts in Communities are helpful, and how we can better serve you.
Connect with BMC Support Team over Chat sessions

To see more like this, see BMC BladeLogic Support Blogs