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]
|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".
Release : 6.x
Component : Univiewer Management Server
On UNI_UV_PACKAGE_ELEMENTS table, ID Indexing Derby stategy was set to auto, changed to Identity to fix the issue.
Delete manually all last imported / created packages and then restart UVMS.
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:
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
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:
tar xvf uvmsdb.taz
Update to a fix version listed below or a newer version if available.
Dollar Universe 6.10.71 - Released May 2021
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.