django-cms: [BUG] Plugin duplicate entry for key cms_plugin_path caused be DB collations
Description
Plugins are randomly getting a duplicate key for path:
django.db.utils.IntegrityError: (1062, "Duplicate entry '01NK' for key 'cms_cmsplugin.cms_cmsplugin_path_4917bb44_uniq'")
It happened 3 times so far and every time it gets harder to fix the error. The key to fixing is finding the plugin which has the duplicated path key and the one before so the sequence can continue as it should. Each of the 3 times it happened had different plugins involved (TextPlugin, our custom GalleryPlugin, ColumnPlugin (djangocms-column)).
After the issue arises, none of the plugins can be added to any of the pages. It always throws an error.
I did all the suggested steps in any similar cases - ./manage.py cms (check, fix-tree, delete-orphaned-plugins), deleted pages where the issue started happening etc. Manage command check says everything is ok.
I tried recreating the database, repairing the tables (InnoDB so it does recreate).
Steps to reproduce
- Add any of the plugins available to a certain page placeholder
- Save the plugin
- Publish the page where the plugin was added
- Page cannot be saved - IntegrityError at {website_url}/publish/
Expected behaviour
Expected behaviour is a successful page publish.
Actual behaviour
Integrity error is raised, page where the plugin is added is not published. None of the other pages won’t get published from this point on if a new plugin is added. Note that while only editing a page where the plugin is already in place works fine, only after adding a new plugin the page won’t get published.
Screenshots
Additional information (CMS/Python/Django versions)
- Django CMS 3.11.4
- Python 3.8.13
- Django 4.2.9
- MySQL 8.0.34.
I am wondering if there is a possibility to re-create all of the path keys in cms_cmsplugin table - column path since they are only a “uniqueness” identifier (as far as I can see while reverse engineering, maybe I am wrong).
It could be a long-shot reproducing the error since it happens to me on a non-regular basis. I am willing to share the DB without the sensitive data of course.
Do you want to help fix this issue?
- Yes, I want to help fix this issue and I will join #workgroup-pr-review on Slack to confirm with the community that a PR is welcome.
- No, I only want to report the issue.
About this issue
- Original URL
- State: closed
- Created 6 months ago
- Comments: 41 (19 by maintainers)
Tried just now. When I first unpublished those 3, and then tried to publish it ended up in a long request not doing nothing on each, so I suppose it didn’t work. Then I ran
python -m manage cms fix-tree --fix-pathsand could publish them.After they were published I ran
cms publisher-publishand it raised Integrity error on a different page. My guess is to unpublish all, runpython -m manage cms fix-tree --fix-pathsand then runcms publisher-publish. This should recreate all messed up keys I suppose.I use only 1 - Footer text on all pages. I think this should not be an issue.
I don’t know about you but I just watched it and experienced mixed emotions LOL. It’s educational, funny and frustrating haha.
https://www.youtube.com/watch?v=gd5uJ7Nlvvo Minute 32 or so.
To be honest this was my first time since the client was calling me regularly. I was trying to figure it out but it was just too stressful for me so I added the button haha.
Oh definitely! @fsbraun FTW
Seems to be working as expected, tried to fiddle with changing/deleting/adding plugins, no issues. Without using the
manage.py cms fix-tree --fix-paths(now I can remove the button for calling the command by the user from admin LOL).I will definitely ALTER both tables.
The docs are pointing out indeed, and makes sense in my case since I usually put
utf8mb4_general_cibut this is where I just had to put the Croatian collation because some of the custom plugin’s objects ordering has to be by Croatian graphs.I will let the
django-treebeardteam know the issue is present, although I am not sure how they could fix it easily. Slavic languages are somewhat special.I will leave the thread open just in case for few more days but I think we fixed it permanently.
No words can describe how thankful I am.
@fsbraun if it will make it easier for you I’ve created a private repo with no sensitive data. I can send an invite. DB is supplied as well. Media is (of course) excluded. Login details are supplied as well (different than the production version of course).
Admin will work in EN since it is a multilingual website. All of the code is in English of course.
@marksweb I appreciate the effort but I doubt the issue will be raised since it is a very specific situation related to treebeard. The project was initially set up with the buggy treebeard v4.5 which was upgraded along the way. Seems like the 4.5 caused the issue which did a permanent mess with the table’s
cms_cmsplugincolumnpathin the DB.I have at least 50 websites with Django CMS 3 and never ever experienced a similar issue, all with very similar plugins used (maybe not all but most of the plugins used on this project).
If it could help I could make a branch with all the sensitive data removed from the project and the corresponding DB with the issue present. I would be happy to share with you and @fsbraun if needed.