Uploaded image for project: 'Service Packs and Hot Fixes'
  1. Service Packs and Hot Fixes
  2. MNT-13688

propTablesCleanupTrigger script fails to execute against database when server has been upgraded from a 4.0.2 instance that had Auditing Enabled for CmisChangeLog

    Details

    • Type: Service Pack Request
    • Status: Closed
    • Resolution: Fixed
    • Affects Version/s: 4.2.4
    • Fix Version/s: 4.2.5
    • Component/s: Auditing
    • Labels:
      None

      Description

      [ 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 4.0.2.47
      2. Configure Auditing using the following properties:
      audit.enabled=true
      audit.alfresco-access.enabled=true
      audit.cmischangelog.enabled=true
      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

      select aa.id, aa.app_name_id, sv.string_value
      from alf_audit_app aa, alf_prop_value pv, alf_prop_string_value sv
      where aa.app_name_id = pv.id and pv.long_value = sv.id;

      Plug in the ID column value from the CMISChangeLog row above for <ID>

      select ae.audit_time, ae.audit_app_id, l.*, 
          ksv.id as key_psv_id, ksv.string_value as key_string, vsv.id as val_psv_id, vsv.string_value as val_string
          from alf_prop_link l, alf_prop_value kpv, alf_prop_string_value ksv, alf_audit_entry ae, alf_prop_value vpv, alf_prop_string_value vsv
          where ksv.string_value in ('objectId', 'nodeRef')
          and l.key_prop_id = kpv.id
          and l.value_prop_id = vpv.id
          and kpv.long_value = ksv.id
          and vpv.long_value = vsv.id
          and l.root_prop_id = ae.audit_values_id
          and ae.audit_app_id = <ID>
          and kpv.persisted_type = 3
          and vpv.persisted_type = 3
          order by vsv.id desc, ae.audit_time asc;

      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>
      For example:
      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 4.0.2.47 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()

      Current Behavior
      propTablesCleanupTrigger fails to execute properly under specified upgrade path, error is produced (see attached log)

      Expected Behavior
      propTablesCleanupTrigger should execute regardless of upgrade path

      Additional Information
      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

        Attachments

          Structure

            Activity

              People

              • Assignee:
                closedbugs Closed Bugs (Inactive)
                Reporter:
                thines Trevor Hines
              • Votes:
                0 Vote for this issue
                Watchers:
                6 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:

                  Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - 0 minutes
                  0m
                  Logged:
                  Time Spent - 1 day, 5 hours, 30 minutes
                  1d 5h 30m

                    Structure Helper Panel