UVMS: unable to import or create new Packages "ConstraintViolationException"

Products

• CA Automic Dollar Universe

Issue/Introduction

UVMS with Derby database does not allow anymore to Import or Create new Packages.

CPU usage of the UVMS Java process is abnormally high.

In uvserver.log we find the following kind of errors:

Problem occurred during package save: could not insert: [com.orsyp.central.jpa.jpo.PackageElementEntity]; SQL [insert into UNI_UV_PACKAGE_ELEMENTS (CONFLICT_RESOLUTION, CONTENT, NAME, OBJECT_TYPE, PACKAGE_ID, ID) values (?, ?, ?, ?, ?, ?)]; constraint [null]; nested exception is org.hibernate.exception.ConstraintViolationException: could not insert: [com.orsyp.central.jpa.jpo.PackageElementEntity] 

Or:

|ERROR| pool-x-thread-y | com.orsyp.request.file.FilePutRequestHandler$1 | Problem occured while creating package from zip file
org.springframework.dao.DataIntegrityViolationException: Could not execute JDBC batch update; SQL [insert into UNI_UV_PACKAGE_ELEMENTS (CONFLICT_RESOLUTION, CONTENT, NAME, OBJECT_TYPE, PACKAGE_ID, ID) values (?, ?, ?, ?, ?, ?)]; constraint [null]; nested exception is org.hibernate.exception.ConstraintViolationException: Could not execute JDBC batch update

Caused by: java.sql.SQLException: The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'PK_UNI_UV_PACKAGE_ELEMENTS' defined on 'UNI_UV_PACKAGE_ELEMENTS'.

 

In the Packages Menu, all new Packages have the status "Import Error".

 

Environment

Release : 6.x

Component : Univiewer Management Server

Cause

On UNI_UV_PACKAGE_ELEMENTS table, ID Indexing Derby stategy was set to auto, changed to Identity to fix the issue.

Resolution

Immediate Workaround:

Delete manually all last imported / created packages and then restart UVMS. 

 

Long Term Workaround  on UVMS 6.9.x:

In order to fix completely the issue, we need to recreate the table UNI_UV_PACKAGE_ELEMENTS.

To do so, please apply the following Procedure with caution as it could definitely corrupt the UVMS database.

 

1. Transfer the attached files rundbfile_script and rabo_clean_up.sql  to the UVMS folder <uvms_folder>/app/bin 

Modify the first line of the rabo_clean_up.sql to adapt it to your UVMS name and path.

 

2. Login to the server and verify that you have at least twice current UVMS derby database free space in the filesystem and 8GB of free RAM.

 

3. Give execution permissions to the two files

chmod a+x rundbfile_script rabo_clean_up.sql

 

4. Stop UVMS:

 

5. Backup current UVMS database,ie:

cd /appl/orsyp/univiewer_server/uvms_MgtServer/data/data

tar czvf uvmsdb.taz uvmsdb

 

6. Launch the following to compress and fix the tables UNI_UV_PACKAGE_FILES, UNI_UV_PACKAGE_ELEMENTS and UNI_UV_PACKAGES

./rundbfile_script rabo_clean_up.sql

7.a If it all goes well, no errors should be displayed and you can start UVMS.

7.b If it produces errors, revert to the previous database backup and then start UVMS again and send me the output of the command and derby.log:

cd /appl/orsyp/univiewer_server/uvms_MgtServer/data/data

tar xvf uvmsdb.taz

 

Solution:

Update to a fix version listed below or a newer version if available.

Fix version(s): 
Component: Univiewer.Management.Server
Dollar Universe 6.10.71 - Released May 2021

Additional Information

Since UVMS 6.10.71 the correction is included for New Installations and Upgrades when Derby Database is used.

During the UVMS upgrade, the recreation and compression of the Package related Database Tables will be performed with the correct indexing format on Derby Databases only.

Please note that this make take some time in case many Package Elements exist.