1 Liquibase upgrade failure: duplicate permissible values in CATISSUE_PERMISSIBLE_VALUE
- 1.1 Root cause
- 1.2 Recommended fix
- 1.3 What to do after the upgrade

Liquibase upgrade failure: duplicate permissible values in `CATISSUE_PERMISSIBLE_VALUE`

This upgrade failure occurs because OpenSpecimen v12.1 adds a unique constraint on permissible values using the combination (PUBLIC_ID, VALUE). The issue is caused by a duplicate VALUE within the same PUBLIC_ID set, not by a duplicate primary key or duplicate public key. If the same value already exists more than once in a dropdown set, Liquibase cannot create the constraint and application startup fails.

Error example

Caused by: liquibase.exception.MigrationFailedException: Migration failed for change set db/12.1/schema.xml::Unique key constraint on PV::vpawar
Reason: liquibase.exception.DatabaseException: Duplicate entry 'specimen_unit-mL' for key 'catissue_permissible_value.OS_PERMISSIBLE_VALUE_UQ'
Failed SQL: ALTER TABLE ... CATISSUE_PERMISSIBLE_VALUE ADD CONSTRAINT OS_PERMISSIBLE_VALUE_UQ UNIQUE (PUBLIC_ID, VALUE)

Root cause

The table CATISSUE_PERMISSIBLE_VALUE contains duplicate VALUE entries within the same permissible value set. In the reported case, PUBLIC_ID = 'specimen_unit' identifies the dropdown/PV set, and the duplicate value is mL, which exists more than once for that set.

Prior versions allowed such inconsistent data to exist. Starting from v12.1, OpenSpecimen enforces uniqueness for permissible values, so the upgrade stops until the duplicate data is cleaned up.

Typical duplicate pattern: two or more rows have the same PUBLIC_ID and the same VALUE. Example: PUBLIC_ID = 'specimen_unit' and VALUE = 'mL'.

Common misunderstanding: In the error text, Duplicate entry 'specimen_unit-mL' is MySQL showing the duplicate key value for the new unique index. It does not mean PUBLIC_ID or the primary key is duplicated. It means the combination of the PV set specimen_unit and the value mL already exists more than once.

Before making any changes, take a backup of the CATISSUE_PERMISSIBLE_VALUE table.

Recommended fix

Step 1: Back up the table

Create a backup of CATISSUE_PERMISSIBLE_VALUE using your standard database backup process before modifying any records.

Step 2: Identify duplicate permissible values

Run the following query to find duplicates by PUBLIC_ID and VALUE:

SELECT
  public_id,
  value,
  COUNT(*) AS cnt
FROM catissue_permissible_value
GROUP BY public_id, value
HAVING COUNT(*) > 1;

If you want to inspect the specific duplicate mentioned in the error, run:

SELECT
  identifier,
  public_id,
  value
FROM catissue_permissible_value
WHERE public_id = 'specimen_unit'
  AND value = 'mL'
ORDER BY identifier;

Step 3: Rename duplicate rows and keep one unchanged

Use the following query to preserve the row with the maximum IDENTIFIER for each duplicate combination and rename the remaining older rows by appending _dup:

UPDATE CATISSUE_PERMISSIBLE_VALUE pv
JOIN (
  SELECT PUBLIC_ID, VALUE, MAX(IDENTIFIER) AS max_id
  FROM CATISSUE_PERMISSIBLE_VALUE
  GROUP BY PUBLIC_ID, VALUE
  HAVING COUNT(*) > 1
) dup
  ON pv.PUBLIC_ID = dup.PUBLIC_ID
 AND pv.VALUE = dup.VALUE
SET pv.VALUE = CONCAT(pv.VALUE, '_dup')
WHERE pv.IDENTIFIER != dup.max_id;

If more than one duplicate exists for the same value, you can also rename them with more distinct suffixes such as _dup1, _dup2, _1, and _2. The main requirement is that each (PUBLIC_ID, VALUE) pair becomes unique.

Step 4: Verify duplicates are removed

Re-run the duplicate check:

SELECT
  public_id,
  value,
  COUNT(*) AS cnt
FROM catissue_permissible_value
GROUP BY public_id, value
HAVING COUNT(*) > 1;

This query should return no rows for the upgrade to proceed successfully.

Step 5: Restart Tomcat and resume the upgrade

After the data cleanup is complete:

Ensure the application server is stopped if required by your upgrade process.
Start Tomcat again.
Allow Liquibase to rerun the pending changeset.
Confirm that the unique constraint is created successfully and the application starts normally.

What to do after the upgrade

If renamed duplicate values are not meant to be used by end users:

Export and archive the duplicate PVs after the upgrade.
Keep historical data intact.
Ensure only the intended active value remains visible for future data entry.

Resolution: Liquibase Upgrade Failure Due to Duplicate Permissible Values

Liquibase upgrade failure: duplicate permissible values in CATISSUE_PERMISSIBLE_VALUE