Wagtail adding new collection: Integrity Error, UNIQUE constraint failed

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?

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.