0

I am using Wagtail with a lot of collections (one for each of > 100 users).
After renaming a collection via the admin interface, adding a new collection very often results in an integrity error, violating a unique constraint/duplicate key error.

I have reproduced the problem with a fresh Wagtail installation following the tutorial at https://docs.wagtail.org/en/v2.15.5/getting_started/tutorial.html, using Python 3.6, Django 3.2, Wagtail 2.15.5, SQLite:

  • Install Wagtail etc., after superuser creation start the server
  • Go to http://127.0.0.1:8000/admin, Settings, Collections
  • Click 'Add A Collection' and create for example the following collections, all children of the root collection:
    • A collection
    • M collection
    • Z collection
    • Test collection
  • Wagtail is setup such that collections are ordered by name alphabetically, the paths for each newly created collection are adjusted by Treebeard automatically, so "Test collection" gets sorted between the "M collection" and "Z collection".
  • Rename the "Test collection" to "A Test collection". The paths are not being updated.
  • Add new collection "A new collection" (under "Root" again).

When trying to add the latest collection, the Integrity error happens.
I think this fails because Treebeard still assumes an alphabetical ordering (which is not true anymore) and thus tries to move a collection to an already existing path.

In this example, the collection paths are:

sqlite> select id, name, path from wagtailcore_collection order by name;
5|A Test collection|00010003
2|A collection     |00010001
3|M collection     |00010002
1|Root             |0001
4|Z collection     |00010004

And the offending SQL statement attempted by Treebeard for reordering the collections is:

# env/lib/python3.6/site-packages/treebeard/mp_tree.py, line 145, in run_sql_stmts,
# cursor.execute(sql, vals)
# Class treebeard.mp_tree.MP_AddSiblingHandler

UPDATE "wagtailcore_collection" SET path='00010003'||SUBSTR(path, 9) WHERE path LIKE '00010002%'

Adding new collections with names that would be sorted after the "M collection" will work perfectly fine (e.g. "N collection", "O collection"), but everything that would be sorted before that results in the integrity error.

The problem may be related to following Treebeard issue: https://github.com/django-treebeard/django-treebeard/issues/145

The workaround suggested there using Collection.fix_tree(destructive=True) would solve the problem on a fresh installation, i.e. the collections are updated so that there paths match the alphabetical sorting again. But in my case, this removed all images and documents that were already assigned to the collections, so this is not an option for a real & live Wagtail instance.

Does anybody have a similar problem? Do you know a solution or work around for this?

Kristin
  • 155
  • 4

1 Answers1

2

Hmmm I do see this in my long running project but am surprised you can easily reproduce on a fresh project. Have you filed an issue on https://github.com/wagtail/wagtail?

Whenever I see this, I run the manage command that comes with Wagtail python manage.py fixtree --full. You will need the --full and that will take a while to complete. But I have not had any trouble with this deleting images or documents.

cnk
  • 981
  • 1
  • 5
  • 9
  • Splendid! You are right - `python manage.py fixtree --full` solves the issue. I wasn't aware that this also fixes collections, I should have thought about trying that. I had only tried it with `Collection.fix_tree(destructive=True)` directly in the Python shell, which, as the `destructive=True` indicates, can create some havoc. I'll file an issue for Wagtail, maybe someone will come up with an automatic solution that doesn't require the manual `fixtree` command from time to time. – Kristin Sep 15 '22 at 18:17
  • Probably worth upgrading your Django and Wagtail versions to current and seeing if the problem still exists. There's been a lot of changes to treebeard implementation since your versions. – Rich - enzedonline Sep 16 '22 at 08:53
  • I just tried it with Wagtail 4.0.1, Django 4.1.1 and Python 3.8. The problem still remains, the error is the same. – Kristin Sep 16 '22 at 12:47
  • If anyone is further interested in this problem: I've filed an issue at Wagtail's GitHub here: https://github.com/wagtail/wagtail/issues/9208 – Kristin Sep 22 '22 at 06:56