uniq_ao_pos index constraint violation may occur when editing archival objects

Description

Description

When editing an archival object, a database constraint violation of the "uniq_ao_pos" index in the "archival_object" table may occur when saving the object, giving an error message similar to the following in the GUI:

{{
translation missing: - translation missing: validation_errors.database_integrity_constraint_conflict_javacommysqljdbcexceptionsjdbc4mysqlintegrityconstraintviolationexceptionduplicate_entryroot@/repositories/2/resources/1608-500for_keyuniq_ao_pos
}}

While the error message is superficially similar to that described in (https://archivesspace.atlassian.net/browse/AR-1326), the fundamental cause is different, as it occurs in ArchivesSpace v2.7.0.

As indicated in James Bullen's April 5, 2017 comment:

In 2.0 we have refactored how positions are handled and resequencing is no longer required, so that config option has been disabled.

Due to the v2.0 refactoring, neither of the fixes mentioned in (i.e., resequencing on startup, or using the "https://github.com/archivesspace/resync" plugin) are relevant.

Upon investigation, the problem appears to be the following:

In the "backend/app/model/mixins/tree_nodes.rb" file, there is a distinction between "logical position" and "physical position" of an archival object. The "physical position" is the actual value in the "position" field in the "archival_object" table in the database. This value contains "gaps" to enable insertion of elements without having to change all the other elements to preserve the order of the object in the list. The "logical position" is the "gapless" position of the object in the list, used by the API.

When generating the JSON object from the database in the "sequel_to_jsonmodel" method, the "physical position" retrieved from the database is stored in the "json.position" field. This is replaced in the method by the "logical position", and the logical position is what is returned by the API. When the object is updated, the "json.position" field returned from the form still has the logical position. In the "update_from_json" method in "tree_nodes.rb", the object is saved to the database, where the JSON contains the logical position, not the physical position.

An example of this that we've seen occur is the following. In our database, the "physical position" of Archival Object #538301 is "501500", which has a "logical position" of "500". When attempting to save Archival Object #538301, the "json.position" field sent to update the database contains "500" (the logical positon), which conflicts with Archival Object #537801, which has a physical position of "500".

This problem only affects specific records because of the gaps in the physical position. As more records get added to the system, a logical/physical position conflict becomes more likely.

Implemented Fix

The underlying problem is the use of the logical position in the save to the database in the "update_from_json" method of "tree_nodes.rb", i.e. line 176:

{{
obj = super(json, extra_values, apply_nested_records)

if json.position

  1. Our incoming JSON wants to set the position. That's fine
    set_position_in_list(json.position)
    end
    }}

The first change is to modify this code, pulling out the "json.position" into a "logical_position" variable, and then modifying the JSON to use the physical position before it is sent to the database. The "logical_position" variable is then used to set the position in the list, i.e.:

{{

  1. Save the logical position to use in setting the position further on.
    logical_position = json.position

  1. Reset json.position to physical position before saving to the database
    json.position = self.position
    obj = super(json, extra_values, apply_nested_records)

if logical_position

  1. Our incoming JSON wants to set the position. That's fine
    set_position_in_list(logical_position)
    end
    }}

Setting the physical position in the JSON before the database save ensures that the constraint violation won't occur.

A minor wrinkle is that when the "set_position_in_list" method is run, the "position" field for the archival object in the database will be modified, because the "attempt_set_position_in_list" method thinks the position is already occupied (which it is, but by the object being updated). The existing code then moves the record into a "gap" in the physical positions which is not necessary. This issue is corrected by adding the following after line 66 in the "attempt_set_position_in_list" method, which simply skips any position change if we are already at the correct logical position:

{{

  1. If we are already are the correct logical position, do nothing
    return if (target_logical_position == current_logical_position)
    }}

Environment

None

Activity

Show:
dsteelma-umd
February 6, 2020, 1:13 PM

Changed submitted as Pull Request #1795 -

dsteelma-umd
February 7, 2020, 1:04 PM

Just an update, as I don’t have permissions to fix the Issue Description:

The final code in the pull request has the following in the “update_from_json” methon in the “backend/app/model/mixins/tree_nodes.rb”:

It was necessary to change the “json.position” field back to the logical position after the write to the database, as the "doesn't break node sequencing" test in the “backend/spec/component_transfer_spec.rb” file seems to depend on the position being the logical position.

 

Joshua Shaw
June 24, 2020, 1:30 PM

Without building the app from scratch (not really feasible in the testing time constraint, though if I manage to squeeze out some time I’ll give it a shot), this isn’t testable at the moment, since you need db access to view the AO record to see what’s happening in the db. Conceptually (and reviewing the code) the changes make sense, though.

Joshua Shaw
June 29, 2020, 3:35 PM

Tested against the release candidate and can at least verify that updating an AO does not change the position value in the db.

Assignee

dsteelma-umd

Reporter

dsteelma-umd

Labels

Fix versions

Priority

Minor
Configure