Upgrade Fails with SQLIntegrityConstraintViolationException for Duplicate Repository Entry

Issue

When upgrading from Liferay DXP 7.2 to a newer version (7.4+ or quarterly releases), the upgrade process fails with an SQLIntegrityConstraintViolationException. The error message in the logs indicates a duplicate entry for a key in the Repository table.

com.liferay.portal.kernel.upgrade.UpgradeException: java.sql.SQLIntegrityConstraintViolationException: Duplicate entry '...' for key 'Repository.IX_60C8634C'

This issue is caused by duplicate database entries for temporary file repositories (com.liferay.portal.kernel.util.TempFileEntryUtil). The upgrade process attempts to update these rows, which conflicts with a unique constraint on the table.

Environment

Source Version: Liferay DXP 7.2
Target Version: Liferay DXP 7.4+, 2024.Q1+

Resolution

The duplicate entries correspond to temporary repositories that are safe to remove before running the upgrade.
These repositories are often empty and will be automatically regenerated by the system if required.
Index IX_60C8634C (groupId, name, portletId) was remove in 7.4 from Repository table, and and IX_97B21AA (groupId, name, portletId, ctCollectionId) created instead.

Before proceeding, create a full backup of your database and document library.

Follow these steps to resolve the issue:

In the pre-upgrade Liferay DXP 7.2 database, identify the duplicate repository IDs using the following SQL query:
```
SELECT repositoryId FROM Repository WHERE name = 'com.liferay.portal.kernel.util.TempFileEntryUtil';
```
In the Liferay instance (before running the upgrade tool), navigate to Control Panel > Server Administration > Script.

Select Groovy as the language and execute the following script for each repositoryId found in the previous step. Replace 12345678L with an actual ID from your query result.

import com.liferay.portal.kernel.service.RepositoryLocalServiceUtil;
import com.liferay.portal.kernel.exception.PortalException;

// Replace with the repository ID to delete
long repositoryIdToDelete = 12345678L; 

try {
    RepositoryLocalServiceUtil.deleteRepository(repositoryIdToDelete);
    out.println("Successfully deleted repository with ID: " + repositoryIdToDelete);
} catch (PortalException e) {
    out.println("Error deleting repository with ID: " + repositoryIdToDelete + ". It might have been already deleted or does not exist.");
    e.printStackTrace(out);
}

After deleting all identified duplicate repository entries, run the database upgrade process again.