[ Summary ]
If an Alfresco 4.0.2 server had auditing enabled for cmischangelog and entries were deleted from auditing then the server was upgraded to 4.2.X and the propTablesCleanupTrigger script is triggered it will fail with a duplicate key value violates unique constraint database error.
[ Reproduction Steps ]
1. Install Alfresco 18.104.22.168
2. Configure Auditing using the following properties:
3. Create a folder and a file any where in the repository
4. Verify that there are two entries in the database that have the same value for val_psv_id using the following queries:
Find the CMISChangelog App ID
Plug in the ID column value from the CMISChangeLog row above for <ID>
If there are not 2 entries with the same val_psv_id go back Folder/Content Creation Step. In tests on a vanilla system there were plenty of them.
5. Clear all Audit Entries using http://localhost:8080/alfresco/service/api/audit/clear/CMISChangeLog?fromTime=<TimeBeforeServerCreated>\&toTime=<CurrentTime>
curl -u admin:admin -d '' "http://localhost:8080/alfresco/service/api/audit/clear/CMISChangeLog?fromTime=1427278479000\&toTime=1427368719000"
- this should return the number of 'cleared' entries.
6. Stop Alfresco
7. Install 4.2.4
8. Point 4.2.4 to 22.214.171.124 Database and Content Store
9. Install 4.2.4 License
10. Open JConsole and connect to Alfresco
11. Manually Trigger propTablesCleanup
Alfresco -> Schedule -> DEFAULT -> MonitoredCronTrigger -> propTablesCleanupTrigger -> executeNow()
propTablesCleanupTrigger fails to execute properly under specified upgrade path, error is produced (see attached log)
propTablesCleanupTrigger should execute regardless of upgrade path
Per Partner Investigation: What this demonstrates is that rows exist in the alf_prop_xxx tables such that the queries in the propcleaner SQL script result in duplicate rows.
The fix should be as simple as adding a unique keyword to the queries.
Full Investigation attached as Investigation.txt