[BUG] Plugin duplicate entry for key cms_plugin_path caused be DB collations about django-cms

[aacimov]

Can you manually unpublish and publish those two pages?

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-paths and could publish them.

After they were published I ran cms publisher-publish and it raised Integrity error on a different page. My guess is to unpublish all, run python -m manage cms fix-tree --fix-paths and then run cms publisher-publish. This should recreate all messed up keys I suppose.

Are you using static placeholders on those (or other) pages? It seems that the cms publisher-publish command ignores static placeholders. This might be fine, but it's better to check.

I use only 1 - Footer text on all pages. I think this should not be an issue.

[aacimov]

I've setup a branch of quickstart that has the same software versions reported here;

https://github.com/django-cms/django-cms-quickstart/tree/chore/recreate-7743

I've created a page through the wizard and then added a plugin and moved the default text plugin in to it. No errors so far.

@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_cmsplugin column path in 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.

[aacimov]

@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.

[aacimov]

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_ci but 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-treebeard team 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.

[aacimov]

So I'm not the only one that adds useful commands as options to run from admin 😆 I've been running a "clear cache" command like that for years.

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.

Great to hear that this has been resolved, thanks @fsbraun!

Oh definitely! @fsbraun FTW

[fsbraun]

https://www.youtube.com/watch?v=gd5uJ7Nlvvo Minute 32 or so.

[aacimov]

https://www.youtube.com/watch?v=gd5uJ7Nlvvo Minute 32 or so.

I don't know about you but I just watched it and experienced mixed emotions LOL. It's educational, funny and frustrating haha.

[fsbraun]

@aacimov Thanks for reporting this. Indeed, this is difficult to reproduce. 3.11.4 runs extremely stable.

Can you post some examples of duplicate path entries, including the plugins' placeholder_id, language and position?

There are only few scenarios I can think of which might lead to this situation:

• Concurrent editors (or one editor with multiple windows) are regularly working on the same page, and one editor makes a change at the same time as the second editor. I would expect the editors already getting error messages, but maybe there is a racing condition that leaves the database in an inconsistent state.
• There is code in your project that makes automatic changes to the plugins (like adding plugins) without using the official API in cms.api.

Might any of this fit your situation?

As a side remark, to remedy the first point, django CMS 4 has optimized the page tree (getting rid of path) and introduced optional locking.

[aacimov]

@fsbraun Thank you for your quick response.

DB screenshot below. The thing is, there is no duplicate record visible, I assume when creating a new plugin reference things go "south".

Concurrent editors (or one editor with multiple windows) are regularly working on the same page, and one editor makes a change at the same time as the second editor. I would expect the editors already getting error messages, but maybe there is a racing condition that leaves the database in an inconsistent state.

There are concurrent editors (2 in total) but each time the issue happened only one was working. Neither time they complained about the errors - only when they are disallowed to save it (Integrity error raised).

There is code in your project that makes automatic changes to the plugins (like adding plugins) without using the official API in cms.api

No automatic changes are made in our code, all of our plugins are basically custom querysets from our apps called as CMS plugins, very low-end logic :)

As a side remark, to remedy the first point, django CMS 4 has optimized the page tree (getting rid of path) and introduced optional locking.

The database is already full, so upgrading to v4 is an option but please advise whether I should use djangocms-4-migration as suggested in the docs or there is a better approach?

This will be my last resort since I am eager to find out why the issue is arising. Maybe the VPS is failing on HW so something messes up, could also be a MySQL bug, if I get to the bottom of this before upgrade I will definitely let everyone know.

So, I assume there is no way I could just re-generate the path keys? I thought on writing a manage command but don't know the pattern. If you can point me to this logic in django-cms code it would really be helpful.

Thanks a lot.

[fsbraun]

I will need to look into this further. I believe the fix-tree management command does regenerate the path keys. But then again, from your screenshot they seem to be ok. The key duplication only happens in the process of publishing.

Can you try this?

$ python -m manage shell
>>> from cms.models import CMSPlugin, TreeNode
>>> TreeNode.fix_tree(fix_paths=True)
>>> CMSPlugin.fix_tree(fix_paths=True)

If this helps, we might need an --fix-paths option to django CMS 3.11.

[aacimov]

Just tried it.

TreeNode.fix_tree(fix_paths=True)went through

CMSPlugin.fix_tree(fix_paths=True) raised TypeError: fix_tree() got an unexpected keyword argument 'fix_paths'

[fsbraun]

Improvement?

[aacimov]

Unfortunately no :(

The craziest thing just happened though. I deleted the cms_cmsplugin record with the "duplicated" key (01NK) and its respected plugin record (django_text_ckeditor_text in this case) but I am still getting the same Integrity error with now deleted key (01NK).

I even dumped the DB and cannot find the record. No caching is used whatsoever. Tried deleting the __pycache__ folder as well.

I am testing locally so I can mess with the DB but this is a mindblowing hell :) I will continue tomorrow since my brain is half operational at the moment.

[fsbraun]

Can you try super(CMSPlugin, CMSPlugin).fix_tree(fix_paths=True) and after that CMSPlugin.fix_tree()?

CMSPlugin overwrites the fix_tree method but changes the signature...

[aacimov]

You are a wizz, we have progress. It just published the page.

I did run ./manage.py cms publisher-publish --unpublished after and this raised Integrity error with a different key (00LK) but we are moving.

If you have any more ideas feel free but I will have to answer tomorrow, my apologies :)

[fsbraun]

Sure! One more thought came along: Do you get an output with one of the following commands?

>>> CMSPlugin.find_problems()
>>> TreeNode.find_problems()

By the way: Which django-treebeard version are you using? If 4.5, then you need to upgrade. It is known to have a bug. You need to upgrade before fixing anything.

If you feel adventurous, you can pip install git+https://github.com/django-cms/django-cms@fix/plugin-paths#egg=django-cms and then run

$ python -m manage cms fix-tree --fix-paths

to see if you get an improvement.

See #6980

[aacimov]

 CMSPlugin.find_problems()

 TreeNode.find_problems()

This returned all empty even on a "broken" DB.

By the way: Which django-treebeard version are you using? If 4.5, then you need to upgrade. It is known to have a bug. You need to upgrade before fixing anything.

I am using treebeard 4.7 which was upgraded from 4.5 (I am pretty much certain). Maybe 4.5 broke it and it just continued messing up even with the new one. I do recall reading about the issue and then upgrading. But it was after this happened for the first time.

If you feel adventurous, you can pip install git+https://github.com/django-cms/django-cms@fix/plugin-paths#egg=django-cms and then run

$ python -m manage cms fix-tree --fix-paths

I did pull a fix and getting django.core.management.base.CommandError: Error: unrecognized arguments: --fix-paths

to see if you get an improvement.

See #6980

[fsbraun]

OK. I am not sure the 4.5 is reparable. But I would try repeating the process. Instead of the patch you can try both of the following (which should be identical to the patch):

• super(CMSPlugin, CMSPlugin).fix_tree(fix_paths=True) followed by CMSPlugin.fix_tree()
• CMSPlugin.fix_tree(destructive=True) (despite its scary name identical to fix_paths=True)

If you prefer to run the patch: Have you tried uninstalling django-cms before installing from GitHub? You can check if it worked by looking at python -m manage cms fix-tree --help. This should include the --fix-paths option:

% python -m manage cms fix-tree --help
usage: manage.py cms fix-tree [-h] [--noinput] [-v {0,1,2,3}] [--settings SETTINGS] [--pythonpath PYTHONPATH] [--traceback] [--no-color] [--force-color] [--skip-checks] [--fix-paths]

Repairing Materialized Path Tree for Pages

options:
  -h, --help            show this help message and exit
  --noinput             Tells Django CMS to NOT prompt the user for input of any kind.
  -v {0,1,2,3}, --verbosity {0,1,2,3}
                        Verbosity level; 0=minimal output, 1=normal output, 2=verbose output, 3=very verbose output
  --settings SETTINGS   The Python path to a settings module, e.g. "myproject.settings.main". If this isn't provided, the DJANGO_SETTINGS_MODULE environment variable will be used.
  --pythonpath PYTHONPATH
                        A directory to add to the Python path, e.g. "/home/djangoprojects/myproject".
  --traceback           Raise on CommandError exceptions
  --no-color            Don't colorize the command output.
  --force-color         Colorize the command output.
  --skip-checks         Skip the checks.
  --fix-paths           Fix paths for pages and plugins - takes a long time

The patch tries repairing paths for both pages and plugins.

[aacimov]

After uninstalling django-cms and installing from #egg the command works but still getting Integrity error with publisher-publish --unpublished.

I even tried converting the DB from MySQL to Postgres "by the book" using dumpdata

python manage.py dumpdata --all --natural-foreign > dump.json

previously creating tables and doing sqlflush on PGDB, but filer is trippin' then which could be related:

django.db.utils.IntegrityError: Problem installing fixtures: insert or update on table "filer_image" violates foreign key constraint "filer_image_file_ptr_id_3e21d4f0_fk_filer_file_id" DETAIL: Key (file_ptr_id)=(1) is not present in table "filer_file".

The thing is file_ptr_id is present in the DB.

I know I am probably driving you crazy but trust me I'm even crazier ;)

[fsbraun]

Before going to filer, I'd love to see if we can get the tree fixed.

Do you know which page publisher-publish --unpublished fails? Can you publish this page manually? Can you share the plugin tree for 00LK (just like for 01NIbefore)?

[aacimov]

All of the pages are actually published.

This is the DB record with failing key 00LK:

And this is the admin screenshot with 3 pages marked in red (language is Croatian, ignore it).

NOTE:
I found out that when I delete these pages publisher-publish command works. I am only concerned what if I recreate those pages and after a while we experience the error again.

[fsbraun]

Since these pages are published, cms publisher-publish should not do anything. Can you manually unpublish and publish those two pages? If so, I fear that we are now onto a different problem with cms publisher-publish (a command that I frankly have used only very rarely). How important is that to you?

Are you using static placeholders on those (or other) pages? It seems that the cms publisher-publish command ignores static placeholders. This might be fine, but it's better to check.

[fsbraun]

One more thought: Can you try unpublishing / publishing some other pages, too?Just to see if those are stable.

[aacimov]

STEP FORWARD: I did unpublish of all pages, ran python -m manage cms fix-tree --fix-paths and published them successfully with cms publisher-publish.

After that, I tried randomly unpublishing/publishing the pages and all went well but after adding a plugin to a page, running cms publisher-publish still raises MySQLdb.IntegrityError: (1062, "Duplicate entry '00LK' for key 'cms_cmsplugin.cms_cmsplugin_path_4917bb44_uniq'")

When manually publishing it seems to be working. No problem for me if the cms publisher-publish does not work, as long as the DB works without duplicating keys but I really don't know how to be absolutely sure, I guess we could wait and see.

[aacimov]

Dear @fsbraun,

Unfortunately the issue is still there. Client experienced the Integrity error 3 times today. I have fixed it with the command but it is happening again randomly.

Any thoughts?

[aacimov]

OK today my client reported the issue is definitely still there. The biggest problem is that now even with the manage.py command the issue is still persistent. That was not the case before.

Now raising:
(1062, "Duplicate entry '00LK' for key 'cms_cmsplugin.cms_cmsplugin_path_4917bb44_uniq'") Exception Location: /<path_to_lib>/python3.8/site-packages/MySQLdb/connections.py

Any help would be very much appreciated.

P.S. pip install git+https://github.com/django-cms/django-cms@fix/plugin-paths#egg=django-cms not working. I can see it has been merged to development but would appreciate the install url. Tnx

[fsbraun]

You can now install it using pip install git+https://github.com/django-cms@develop#egg=django-cms. The patch's branch probably got deleted upon merge. Any side information on what happened just before the error came up again?

[marksweb]

I've setup a branch of quickstart that has the same software versions reported here;

https://github.com/django-cms/django-cms-quickstart/tree/chore/recreate-7743

I've created a page through the wizard and then added a plugin and moved the default text plugin in to it. No errors so far.

There was this issue with the regular sized dialog though;

[aacimov]

You can now install it using pip install git+https://github.com/django-cms@develop#egg=django-cms. The patch's branch probably got deleted upon merge. Any side information on what happened just before the error came up again?

URL was not working but it works as follows:
git+https://github.com/django-cms/django-cms@develop#egg=django-cms

Just pointing out if anyone else is having the issue and needs the --fix-paths option.

Regarding the side information on how the issue was raised again:

1. User created a new page (deeper in the tree - 3rd level)
2. User added a TextPlugin with data (could published)
3. User added a PicturePlugin with image reference which went through but could not publish the page - Integrity error raised
4. After this happened even with the --fix-paths option from manage.py command the issue is not resolved and is persistent
5. User cannot continue editing since any other pages now doesn't accept neither adding plugins - Integrity error raised on all future tries for adding a plugin

What I tried (different steps. each step is described):

1. Delete all the plugins from the page causing the issue, publish the page went through
2. fix-tree --fix-paths, add same plugins on a blank page - TextPlugin works, PicturePlugin does not
3. Delete the page, create the page again, add plugins - TextPlugin works, PicturePlugin does not
4. Move the page to a root level with the plugins in place, publish unsuccessful

Note: 00LK is always the faulting key (attaching screenshot) - seems like it is "stuck" on this key and cannot continue the sequence (screenshots below). The TextPlugin with this key is the first plugin in a newly created page

[fsbraun]

Is the picture plugin a child of the text plugin? A text plugin with data is a text plugin with html text filled in, correct? The picture plugin has a reference to a django-filer image, if I understand correctly. I'll run a test.

[aacimov]

Is the picture plugin a child of the text plugin?

No, PicturePlugin is added after the TextPlugin as standalone.

A text plugin with data is a text plugin with html text filled in, correct? The picture plugin has a reference to a django-filer image, if I understand correctly. I'll run a test.

Correct. TextPlugin is with html text filled in (just an h1). PicturePlugin is referenced to a filer image.

[fsbraun]

I cannot reproduce so far. The only pattern I see is that it is always the 00LK value that causes the issue. Could there be orphaned plugins that the fix-tree misses?

What's the output of ./manage.py cms delete-orphaned-plugins?

[aacimov]

All good. The output says:

0 uninstalled plugins
0 plugins with unsaved instances

I already tried that command before the --fix-paths update, everything was fine back then as well.

Just a thought, could all the paths in the cms_cmsplugin table be emptied and then recreated somehow?
In some of my craziest ideas back when we first started discussing (on one of a test dbs of course) I tried but the command did not re-fill the column. I don't know if they are any how related to anything else except for uniqueness.

[fsbraun]

One more poke in the dark: What happens if you do ./manage.py cms fix-tree without the --fix-paths option?

[aacimov]

One more poke in the dark: What happens if you do ./manage.py cms fix-tree without the --fix-paths option?

Same error raised. Integrity error (00LK)

[fsbraun]

@aacimov Well, I now have a hint 💡:

Croatian collations might recognize diphthongs ("li, lj, lk") differently. That would explain why the problematic keys are 00NK, 00LK which the database considers as 3 letters not four and sorts them accordingly. I assume that this is incompatible with django-treebeard which needs certain sorting behavior to make tree queries.

Can you manually change the collation of the cms_cmsplugin table to utf8mb3_general_ci? (Actually, the path field should suffice.)

[aacimov]

Holy cow now the thing works. You are a champ!

I did change the collation to utf8mb4_general_ci though since it should work still and my whole DB is utf8mb4 but it works as expected.

Can only the cmsplugin table be left with this collation?

Should this issue be considered for transfer to django-treebeard developers? It did raise a big bug so just a reasonable consideration.

I never though my own language would give me such a headache :')

[fsbraun]

Does it work now?

Collations are tricky! I believe you only have to change the path fields. I would do it for both the plugin table and the page table.

I would raise this with the django-treebeard team. Maybe they know (just we did not), but maybe not. They say this in the docs:
https://django-treebeard.readthedocs.io/en/stable/mp_tree.html#treebeard.mp_tree.MP_Node.alphabet

[fsbraun]

Great, it seems to work for now! I am happy, I could help! I am also glad, that there was a simple solution!

I haven't looked into things on the django-treebeard side. But they might just add db_collation="utf8_general_ci" to their CharField. Though I am not sure how consistent the collations are across databases.

[marksweb]

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).

So I'm not the only one that adds useful commands as options to run from admin 😆 I've been running a "clear cache" command like that for years.

Great to hear that this has been resolved, thanks @fsbraun!