Known issue with Database Updates

Affected Releases (only applicable for updates that are released prior to 25/03/22)

The following releases are affected by the issue:

2021.2.2
2021.2.1
2021.2.0
2021.1.2
2020.3.6
2020.3.5
2020.2.7


The issue can occur with a censhare system that has been updated from a version 2020.1 and below to a version below 2021.1.0, if one of the above releases has been used.

On the 25th of March, 2021.2.3, 2021.1.2, 2020.3.6 and 2020.2.7 were repackaged and released to solve this error.

Updates starting after the 25th will not be affected, versions 2021.2.1, 2021.2.0 2021.2.2, 2020.3.5 were not repackaged as updates and new installations should always use the latest dot.dot version.

Problem

In the affected versions, a database update is located in the wrong update_pack directory. If you update from a version lower than or equal to censhare 2020.1, the updater sets the database.version value to 2021.1.0.000 . All subsequent database updates will then only be applied from version 2021.1.

Verification for versions before 2021.1.0

This can be verified by running CheckJDBC.sh. For all versions below censhare 2021.1., the following output displays:

...
...
database.version: 2021.1.0.0004
successfully done.

Verification for versions 2021.1.0 and later

If the system has been already updated to censhare 2021.1 and later, the issue can be verified by querying for the following asset relation type definition:

select count(*) from asset_rel_typedef where  key='user.marker.detectedobject.';
 count
-------
     0

If the Database Updater has skipped the other updates before, this asset_rel_typedef is missing and SQL will return count 0.

Solution

First ensure, that you have the database update files removed from the release:

rm -f /opt/corpus/css/database/update/update_pack_2020.1/files/*2020_3_maxversion-to-*.sql

Resolve for release 2020.2.7: Connect to your database and run the following SQL commands to correct the statements that have been made before.

ALTER TABLE ASSET_FEATURE DROP COLUMN VALUE_ID;
ALTER TABLE ASSET_REL_FEATURE DROP COLUMN VALUE_ID;
drop trigger asset_feature_value_id_tr;
drop trigger asset_rel_feature_value_id_tr;
UPDATE client_version cv SET version_pattern='2020.2.%' WHERE cv.version_pattern='2021.1.%' AND NOT EXISTS (SELECT 1 FROM client_version cv2 WHERE cv.type = cv2.type AND cv2.version_pattern = '2020.2.%');
UPDATE database_info SET value='2020.2.0.0001' WHERE key='database.content.version';
UPDATE database_info SET value='2020.2.0.0000' WHERE key='database.version';
COMMIT;

Resolve for release 2020.3.5 and 2020.3.6: Connect to your database and run the following SQL commands to correct the statements that have been made before.

ALTER TABLE ASSET_FEATURE DROP COLUMN VALUE_ID;
ALTER TABLE ASSET_REL_FEATURE DROP COLUMN VALUE_ID;
drop trigger asset_feature_value_id_tr;
drop trigger asset_rel_feature_value_id_tr;
UPDATE client_version cv SET version_pattern='2020.3.%' WHERE cv.version_pattern='2021.1.%' AND NOT EXISTS (SELECT 1 FROM client_version cv2 WHERE cv.type = cv2.type AND cv2.version_pattern = '2020.3.%');
UPDATE database_info SET value='2020.3.0.0002' WHERE key='database.content.version';
UPDATE database_info SET value='2020.3.0.0000' WHERE key='database.version';
COMMIT;

Resolve for versions 2021.1.0 and later: Even the releases include the wrong update file, this has no effect if you update to a version 2021.1 and later.