ActiveRecord::RecordNotUnique

[techleon]

Hi,

we keep getting this error in our production environment.
eg.
ActiveRecord::RecordNotUnique: Mysql2::Error: Duplicate entry '800291-800291-0' for key 'entry_anc_desc_idx': INSERT INTO entry_hierarchies (ancestor_id, descendant_id, generations) VALUES (800291, 800291, 0)

Here entry_hierarchies is the hierarchies table. We have tried several solutions and feel that this could be a bug in the gem, let us know if this is an issue

[UnConundrum]

I'm having a similar problem and can provide more detail. The application works fine in development using puma to serve the pages. In production we use nginx and passenger. We're using Rails 5.0 and Ruby 2.4.0 on both servers. Both use MySql 5.7.19. Development is a Mac running Sierra 10.12.6 and Production is running Ubuntu 17.04

The problem occurs regardless of the value of with_advisory_lock.

When I attempt to move an existing record into a newly created tree, I get the duplicate entry error. If the "existing record"/parent is created in the same transaction as the child, I can clearly see the input into _hierarchies when that is created as well as when the children are created. The problem is that at the end of the transaction, it creates a 2nd record in _hierarchies for the parent.

This was also a problem whenever I used Paren.add_child(child). I don't know why there would be a difference, but this was resolved by setting the parent_id for the child before the child is created. Since I'm trying to move an existing record, I can't use this trick.

Any suggestions would be greatly appreciated.

[gerrywastaken]

For me the stack trace (reduced to closure tree call) is:

["./closure_tree-7.0.0/lib/closure_tree/hierarchy_maintenance.rb:68:in `block in rebuild!'",
 "./closure_tree-7.0.0/lib/closure_tree/support.rb:112:in `block (2 levels) in with_advisory_lock'",
 "./closure_tree-7.0.0/lib/closure_tree/support.rb:112:in `block in with_advisory_lock'",
 "./closure_tree-7.0.0/lib/closure_tree/support.rb:111:in `with_advisory_lock'",
 "./closure_tree-7.0.0/lib/closure_tree/hierarchy_maintenance.rb:66:in `rebuild!'",
 "./closure_tree-7.0.0/lib/closure_tree/hierarchy_maintenance.rb:42:in `_ct_after_save'",
 "./closure_tree-7.0.0/lib/closure_tree/support.rb:169:in `block in create!'",
 "./closure_tree-7.0.0/lib/closure_tree/support.rb:169:in `tap'",
 "./closure_tree-7.0.0/lib/closure_tree/support.rb:169:in `create!'",
 "./closure_tree-7.0.0/lib/closure_tree/finders.rb:154:in `block in find_or_create_by_path'",
 "./closure_tree-7.0.0/lib/closure_tree/support.rb:112:in `block (2 levels) in with_advisory_lock'",
 "./closure_tree-7.0.0/lib/closure_tree/support.rb:112:in `block in with_advisory_lock'",
 "./closure_tree-7.0.0/lib/closure_tree/support.rb:111:in `with_advisory_lock'",
 "./closure_tree-7.0.0/lib/closure_tree/finders.rb:151:in `find_or_create_by_path'"]

error:

#<Mysql2::Error: Duplicate entry '4388-4388-0' for key 'tag_anc_desc_idx'>

Create:

Tag.find_or_create_by_path([
  {name: 'Grandparent', title: 'Sr.'},
  {name: 'Parent', title: 'Mrs.'},
  {name: 'Child', title: 'Jr.'}
])

[gerrywastaken]

@mceachen So I also figured out that all I need to do to get this to happen is run the same thing with a change of title.

# No problem the first time
Tag.find_or_create_by_path([
  {name: 'Yup', title: 'Yup'}
])

# Duplicate exception
Tag.find_or_create_by_path([
  {name: 'Yup', title: 'Yup'}
])

I also noticed that ClosureTree::Finders#find_by_path checks for the title to be the same when looking for existing tags, even though I suspect it should be only checking for tags with the same name.

[mceachen]

@gerrywastaken your duplicate exception doesn't isolate an error with title, as the name is duplicated. Perhaps you meant

# No problem the first time
Tag.find_or_create_by_path([
  {name: 'first', title: 'Yup'}
])

# Duplicate exception
Tag.find_or_create_by_path([
  {name: 'second', title: 'Yup'}
])

?

I also noticed that ClosureTree::Finders#find_by_path checks for the title to be the same

That'd be bad--where do you see that? https://github.com/ClosureTree/closure_tree/blob/master/lib/closure_tree/finders.rb#L136

[berislavbabic]

@mceachen Experiencing the same behaviour in our project, why does the rebuild method need to be create! instead of plain create here? https://github.com/ClosureTree/closure_tree/blob/master/lib/closure_tree/hierarchy_maintenance.rb#L68
I'm not sure if the issue lies within the delete_hierarchy_references method, which runs inside of a different transaction to delete the hierarchy entries. Since after_save runs in its own transaction, the hierarchy already created in that transaction won't be visible to https://github.com/ClosureTree/closure_tree/blob/master/lib/closure_tree/hierarchy_maintenance.rb#L97 before the after_save completes and the transaction completes.