[BUG] Cannot delete or update a parent row: a foreign key constraint fails - when deleting custom plugin

[ebugge]

Description

I have a custom plugin that I am unable to delete from my page. The error returned says:

MySQLdb._exceptions.IntegrityError: (1451, 'Cannot delete or update a parent row: a foreign key constraint fails (`oekf$default`.`oekf_contactinfo`, CONSTRAINT `oekf_contactinfo_cmsplugin_ptr_id_e68f18e6_fk_cms_cmsplugin_id` FOREIGN KEY (`cmsplugin_ptr_id`) REFERENCES `cms_cmsplugin` (`id`))’)

Error seems to occur when the system tries to delete the plugin from the cms_cmsplugin table, although it has not deleted the actual plugin from my custom table oekf_contactinfo. I am not sure why the row in the custom table exists at this point… Should it have been deleted previously?

This is the part of the stack trace where the deletion occurs.

File "/home/oekf/.virtualenvs/oekfpy39/lib/python3.9/site-packages/cms/admin/placeholderadmin.py", line 1065, in delete_plugin
plugin.delete()
File "/home/oekf/.virtualenvs/oekfpy39/lib/python3.9/site-packages/cms/models/pluginmodel.py", line 484, in delete
super().delete(*args, **kwargs)

This is my custom plugin definition:

class ContactInfo(CMSPlugin):
    redirect_title = models.CharField(max_length=50)
    some_field = models.CharField(max_length=50, null=True)
    some_other_field = models.CharField(max_length=50, null=True)

class ContactInfoPlugin(CMSPluginBase):
    model = ContactInfo
    render_template = "components/contact_info.html"
    name = _("Contact_info")
    cache = False
    
    def render(self, context, instance, placeholder):
        
        context['instance'] = instance

        # context being constructed with business logic… this code has been removed on purpose

        return context

plugin_pool.register_plugin(ContactInfoPlugin)

Steps to reproduce

1. On my page, navigate to the plugin overview for that page
2. Select to delete the custom plugin
3. "Error 500” occurs in a ‘toast’ at the top of the page and the plugin is not deleted.

Expected behaviour

I expect that the plugin contents can be deleted from the CMS.

Actual behaviour

See description and steps to reproduce.

Screenshots

None relevant

Additional information (CMS/Python/Django versions)

Python: 3.9
Django CMS: 3.10.1
Django: 3.2.21

I am running the application on Pythonanywhere.

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.
• [ x] No, I only want to report the issue.

[fsbraun]

@ebugge Thanks for bringing this to attention. Deleting plugins should not be an issue. I certainly cannot reproduce the issue. To clarify the situation:

1. Does deleting instances of other plugins (with data) work fine?
2. Does deleting other instances of your custom plugin work, or does the issue exist for all instances of the custom plugin?
3. Am I correct to assume that the plugin's model does not contain foreign keys, or many-to-many relationships?

Can you make the following check in the database:

• Any double entries in the contact info table oekf_contactinfo for cmsplugin_ptr_id field?

[ebugge]

1. Deletion of other plugin types work fine (fx. TextPlugin).
2. So far, I have only had one instance of the plugin on my site. But if I add one more on another page and try to delete it immediately after, I get an error 500. So the problem seems to exist for all instances of the plugin.
3. Yes, that is correct. It only contains three CharFields.

If I understand you last question correctly, then no, there are no double entries in the cmsplugin_ptr_id field of my oekf_contactinfo table. These are all the values of that field in the table:

I have two languages activated on the site, thus, I would expect this table to hold only two rows: One for each language for the single instance of the plugin (screenshot taken before I added one more instance). Isn’t it strange that there are more than two rows?

[fsbraun]

You have separate copies for published and draft content. So there can be up to twice as many instances as you might expect.

[ebugge]

I cannot reproduce the error locally. I took a backup of my database on Pythonanywhere, restored it on my own computer and tried deleting the plugin - this works perfectly. I am also able to create a new page with an instance of the plugin, and then delete the plugin again. Not sure what to conclude from this...

[fsbraun]

Have you tried "restoring" the database on your production environment? (Export, delete, recover)

[ebugge]

How do you mean restoring on production? Make a new copy of the database and point to that one? I only have one environment on Pythonanywhere and that is production.

[fsbraun]

I meant - if you are happy to take the risk

• dump the database content
• delete the database
• create a new database and
• import the dumped content.

Since after importing at your localhost the issue was gone, it might be that this also fixes the issue with the production database.

[fsbraun]

@ebugge Any news on this?

[ebugge]

Sorry, no. I meant to ask you: Any ideas on how this should fix the issue? I am kinda reluctant to do a full delete-and-restore, I must say

[fsbraun]

Can you share a database excerpt of the corresponding plugin tree (cms_cmspluings - the plugin which causes the problems and its parents, siblings and children)?

[fsbraun]

Did you at any point run the ./manage.py cms delete_orphaned_plugins command? There's a know issue with it: #7814

[ebugge]

Can you share a database excerpt of the corresponding plugin tree (cms_cmspluings - the plugin which causes the problems and its parents, siblings and children)?

@fsbraun Something like this?

mysql> select * from oekf_contactinfo;
+------------------+------------------------+------------+------------------+
| cmsplugin_ptr_id | redirect_title         | some_field | some_other_field |
+------------------+------------------------+------------+------------------+
|             3113 | Some English title     | A          | C                |
|             3231 | Some English title     | A          | C                |
|             3633 | Some Danish title.     | B          | D                |
|             3638 | Some Danish title      | B          | D                |
|             3639 | Some other Danish title| B          | D                |
|             3643 | Some Danish title      | B          | D                |
+------------------+------------------------+-------------+-----------------+

mysql> select * from cms_cmsplugin where plugin_type = 'ContactInfoPlugin';
+------+----------+----------+-------------------+----------------------------+----------------------------+-----------+----------------+-------+----------+------+
| id   | position | language | plugin_type       | creation_date              | changed_date               | parent_id | placeholder_id | depth | numchild | path |
+------+----------+----------+-------------------+----------------------------+----------------------------+-----------+----------------+-------+----------+------+
| 3113 |        0 | en       | ContactInfoPlugin | 2020-08-30 13:12:02.518330 | 2020-08-30 13:12:02.531132 |      NULL |             20 |     1 |        0 | 00HU |
| 3231 |        0 | en       | ContactInfoPlugin | 2020-08-30 13:12:02.518330 | 2020-11-29 12:54:02.736997 |      NULL |             29 |     1 |        0 | 00JN |
| 3633 |        0 | da       | ContactInfoPlugin | 2023-05-15 21:54:45.356437 | 2023-05-15 21:54:45.374848 |      NULL |              5 |     1 |        0 | 00OP |
| 3638 |        1 | da       | ContactInfoPlugin | 2023-05-28 17:08:28.289123 | 2023-05-28 17:08:44.121114 |      NULL |             29 |     1 |        0 | 00OT |
| 3639 |        0 | da       | ContactInfoPlugin | 2024-01-05 15:06:11.063785 | 2024-01-05 15:06:11.069757 |      NULL |            128 |     1 |        0 | 00OU |
| 3643 |        1 | da       | ContactInfoPlugin | 2023-05-28 17:08:28.289123 | 2024-03-21 14:33:16.867376 |      NULL |             20 |     1 |        0 | 00OX |
+------+----------+----------+-------------------+----------------------------+----------------------------+-----------+----------------+-------+----------+------+

mysql> select * from cms_placeholder where id in (20, 29, 5, 128);
+-----+--------------+---------------+
| id  | slot         | default_width |
+-----+--------------+---------------+
|   5 | clipboard    |          NULL |
|  20 | page_content |          NULL |
|  29 | page_content |          NULL |
| 128 | page_content |          NULL |
+-----+--------------+---------------+

mysql> select * from cms_page_placeholders where placeholder_id in (20, 29, 5, 128);
+----+---------+----------------+
| id | page_id | placeholder_id |
+----+---------+----------------+
| 14 |       7 |             20 |
| 22 |      11 |             29 |
| 74 |      37 |            128 |
+----+---------+----------------+

Did you at any point run the ./manage.py cms delete_orphaned_plugins command? There's a know issue with it: #7814

I don’t remember whether I have executed delete_orphaned_plugins previously. If I understand the other issue correctly, I need to upgrade to CMS 4 before it will work correctly?

[ebugge]

Looking at the source code (cms/models/pagemodel.py) this is the section that causes the exception. Lines 607-617 in the version of the Django CMS code that I am using.

_clear_placeholders() is invoked during publish. When it tries to delete the CMSPlugin objects of the placeholder (in my case, this is the page content section, id = 20) it fails because the table representing my custom plugin (oekf_contactinfo) references the CMSPlugin table (cms_cmsplugin) via a foreign key.

def _clear_placeholders(self, language=None):
    from cms.models import CMSPlugin

    placeholders = list(self.get_placeholders())
    placeholder_ids = (placeholder.pk for placeholder in placeholders)
    plugins = CMSPlugin.objects.filter(placeholder__in=placeholder_ids)

    if language:
        plugins = plugins.filter(language=language)
    models.query.QuerySet.delete(plugins)
    return placeholders

I guess it makes sense that there is a foreign key from the custom table to the CMSPlugin table?

CONSTRAINT `oekf_contactinfo_cmsplugin_ptr_id_e68f18e6_fk_cms_cmsplugin_id` FOREIGN KEY (`cmsplugin_ptr_id`) REFERENCES `cms_cmsplugin` (`id`)

I don’t understand how this does not work on Pythonanywhere when it works locally with the same database. I have checked again locally: When I delete an instance of the custom plugin, it is deleted and both the row in oekf_contactinfo and the row in cms_cmsplugin (the foreign key) are deleted. On Pythonanywhere the same action causes an exception.

Any ideas @fsbraun?

[fsbraun]

I have no idea why this happens for our pythopnanywhere database.

A bit of a background: When subclassing CMSPluginModel Django creates a pointer in your plugin's model to the CMSPluginModel. Deleting an instance of your custom plugin model should delete both database entries.

My suggestion would be to try fixing this by

1. Manually deleting the custom plugin model instance from oekf_contactinfo.
2. Run ./manage.py cms delete_orphaned_plugins - this should remove the remaining "ghost" plugin while keeping all tree info intact.
3. Run ./manage.py cms fix_tree just to be on the safe side.
4. Try creating and deleting a new instance if your custom plugin to ensure that the issue was for this particular instance.

[ebugge]

@fsbraun I tried your suggestion. As the plugin is easy to re-create, I deleted all rows of this plugin in both tables:

DELETE FROM oekf_contactinfo;
DELETE FROM cms_cmsplugin WHERE plugin_type = 'ContactInfoPlugin’;

This was followed by the two manage.py commands.

After that, I was able to insert a new instance of the plugin on the page and publish without error. But if I try to delete the plugin instance again via the editor interface, I get the same exception.

[fsbraun]

This indicates that it is not the database state which is causing the error.

What database does pythonanywhere use (and which version)? Django has dropped support for some older MySql versions recently, I believe.

[ebugge]

It should be supported for the Django version I am using.

Django: 3.2.21
MySQL: 5.7.44