wagtail: Django "flush" management command fails because of "wagtailsearch_editorspick" table

the simplest solution for me was to add "wagtail.contrib.search_promotions", to my INSTALLED_APPS ->

INSTALLED_APPS = [
    "home",
    "search",
    "wagtail.contrib.search_promotions",
    "wagtail.contrib.forms",
    "wagtail.contrib.redirects",
    "wagtail.contrib.settings",
    # ... more modules

in addition i learned a nifty little trick while reading this issue. you are able to deliver everything before a deadline easily: just move the ticket to the next milestone. works since 1.3 reliably

We faced the same problem, but in our case it was just that our test suite started to fail when integrating wagtail into our existing django project. Here is a workaround for someone that want to use wagtail, but not include wagtailsearchpromotions. Please note that this might/will cause problems if you later on want to use wagtailsearchpromotions, as the wagtailsearch_editorspick no longer exists, and the wagtailsearchpromotions initial migration will/might fail. Note that I haven’t tried that path, but I suspect it might fail.

This migration needs to be added to your project. Please note the danger of this migration as well, in case you already have data in wagtailsearch_editorspick as it will remove related rows.

class Migration(migrations.Migration):
    dependencies = [
        ('publicpages', '0001_initial'),  # change this to fit your need
        ('wagtailsearch', '0003_remove_editors_pick'),
    ]

    operations = [
        migrations.RunSQL('DROP TABLE IF EXISTS wagtailsearch_editorspick CASCADE;'),
    ]

As most of you, I had this problem while integrating wagtail in a existing project. After reading this thread I found out that only my Selenium tests were failing and that was due to cascade not being specified. I simply overrode the _fixture_teardwon() to force allow_cascade when the flush command was called.

        # Allow TRUNCATE ... CASCADE and don't emit the post_migrate signal
        # when flushing only a subset of the apps
        for db_name in self._databases_names(include_mirrors=False):
            # Flush the database
            inhibit_post_migrate = (
                self.available_apps is not None or
                (   # Inhibit the post_migrate signal when using serialized
                    # rollback to avoid trying to recreate the serialized data.
                    self.serialized_rollback and
                    hasattr(connections[db_name], '_test_serialized_contents')
                )
            )
            call_command('flush', verbosity=0, interactive=False,
                         database=db_name, reset_sequences=False,
                         allow_cascade=True,
                         inhibit_post_migrate=inhibit_post_migrate)

I ran into the same issue where the table wagtailsearch_editorspick did not live in my database but was defined as a model. So when I ran manage.py flush, I hit the same error

CommandError: Database cssdjangotest couldn’t be flushed. Possible reasons:

• The database isn’t running or isn’t configured correctly.
• At least one of the expected database tables doesn’t exist.
• The SQL was invalid. Hint: Look at the output of ‘django-admin sqlflush’. That’s the SQL this command wasn’t able to run.

Looking at the output of ./manage.py sqlflush you can see the SQL statement that django tries to run.(note that I have wagtail integrated with another application called css_portal).

BEGIN; TRUNCATE “css_cms_tier1page”, “wagtailimages_rendition”, “css_portal_invoice”, “django_admin_log”, “django_content_type”, “css_cms_standardpage”, “wagtailcore_page”, “django_session”, “css_cms_homepage”, “wagtailimages_image”, “wagtaildocs_document”, “wagtailsearch_query”, “wagtailcore_site”, “css_portal_profile”, “wagtailembeds_embed”, “wagtailsearch_querydailyhits”, “auth_group”, “wagtailcore_groupcollectionpermission”, “taggit_tag”, “auth_permission”, “wagtailcore_pageviewrestriction_groups”, “wagtailcore_collectionviewrestriction”, “django_ses_sesstat”, “css_cms_tier2page”, “css_portal_customerdomain”, “wagtailcore_collection”, “taggit_taggeditem”, “wagtailcore_collectionviewrestriction_groups”, “css_portal_invoiceauditfile”, “wagtailusers_userprofile”, “wagtailforms_formsubmission”, “auth_user_groups”, “wagtailcore_pagerevision”, “wagtailcore_grouppagepermission”, “wagtailcore_pageviewrestriction”, “css_portal_customer”, “css_portal_cloudaccount”, “auth_user”, “auth_group_permissions”, “css_portal_dynamodbri”, “auth_user_user_permissions”, “wagtailredirects_redirect”; …

When I execute that command in the dbshell using ./manage.py dbshell I can see the problem (and the suggested fix).

psql (10.5 (Ubuntu 10.5-0ubuntu0.18.04), server 10.4) SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off) Type “help” for help.

cssdjangotest=> TRUNCATE “css_cms_tier1page”, “wagtailimages_rendition”, “css_portal_invoice”, “django_admin_log”, “django_content_type”, “css_cms_standardpage”, “wagtailcore_page”, “django_session”, “css_cms_homepage”, “wagtailimages_image”, “wagtaildocs_document”, “wagtailsearch_query”, “wagtailcore_site”, “css_portal_profile”, “wagtailembeds_embed”, “wagtailsearch_querydailyhits”, “auth_group”, “wagtailcore_groupcollectionpermission”, “taggit_tag”, “auth_permission”, “wagtailcore_pageviewrestriction_groups”, “wagtailcore_collectionviewrestriction”, “django_ses_sesstat”, “css_cms_tier2page”, “css_portal_customerdomain”, “wagtailcore_collection”, “taggit_taggeditem”, “wagtailcore_collectionviewrestriction_groups”, “css_portal_invoiceauditfile”, “wagtailusers_userprofile”, “wagtailforms_formsubmission”, “auth_user_groups”, “wagtailcore_pagerevision”, “wagtailcore_grouppagepermission”, “wagtailcore_pageviewrestriction”, “css_portal_customer”, “css_portal_cloudaccount”, “auth_user”, “auth_group_permissions”, “css_portal_dynamodbri”, “auth_user_user_permissions”, “wagtailredirects_redirect”; ERROR: cannot truncate a table referenced in a foreign key constraint DETAIL: Table “wagtailsearch_editorspick” references “wagtailcore_page”. HINT: Truncate table “wagtailsearch_editorspick” at the same time, or use TRUNCATE … CASCADE.

When I run the same command with “CASCADE;” at the end, it works.

TRUNCATE “css_cms_tier1page”, … “css_portal_dynamodbri”, “auth_user_user_permissions”, “wagtailredirects_redirect” CASCADE;

The django flush.py management command on line 34 (of django 2.1 anyway) has built in support to allow the cascade option.

34 allow_cascade = options.get('allow_cascade', False)

But it is a “stealth option” and set to False by default. You cannot currently specify something like --allow_cascade=True on the ./manage.py flush command line.

So, using the django instructions for over-riding a management module I copied flush.py to myapp/management/commands/flush.py and inserted the following in the add_arguments() function (at line 26)

parser.add_argument(
    '--allow_cascade', action='store', dest='allow_cascade', default=False,
        help='Adds "CASCADE" option to TRUNCATE command if supported by db backend. Default=False.',
)

And now, I can simply run ./manage.py flush --allow_cascade=True and the flush works perfectly.

Hope this helps someone!

Any updates on the implementation, I am having trouble running tests with fixtures?

To minimize the damage at this point, a solution might be to remove the FK constraint on the wagtailsearch_editorspick table in an additional migration in the wagtailsearch app, turning the field into a simple integer field, and to restore that constraint in the wagtailsearchpromotions migrations.

@GabrielBogo As @chosak mentioned, adding “wagtail.contrib.search_promotions” to INSTALLED_APPS in your test environment should serve as a workaround.

Thanks, @gasman - that worked

@GabrielBogo As @chosak mentioned, adding "wagtail.contrib.search_promotions" to INSTALLED_APPS in your test environment should serve as a workaround.

Working towards a proper solution… The migrations that brought about the current situation are:

• wagtailsearch.0001_initial: creates the wagtailsearch_editorspick table
• wagtailsearch.0003_remove_editors_pick: removes the EditorsPick model from the wagtailsearch application state but leaves the wagtailsearch_editorspick table in the database as an unmanaged table
• wagtailsearchpromotions.0001_initial: renames the wagtailsearch_editorspick table to wagtailsearchpromotions_searchpromotion and modifies the wagtailsearchpromotions application state to ‘adopt’ that model

All of these migrations have been around since Wagtail 1.1, and at this point we can reasonably assume that nobody is going to upgrade directly from Wagtail 1.1 to 4.x without deploying any intermediate version (or, at least, if they’re foolhardy enough to do that, the onus is on them to devise their own migration plan), so we can dismiss the possibility of a project being in some historical state where migrations are only partially applied, or where a wagtailsearch_editorspick table contains meaningful data.

This means that any prospective bugfix will need to handle all of these initial states:

1. No migrations applied - i.e. a newly-created project immediately before running ./manage.py migrate
2. wagtailsearch migrations applied but not wagtailsearchpromotions - i.e. a fully migrated project that doesn’t have wagtail.contrib.search_promotions in INSTALLED_APPS. These have an unmanaged wagtailsearch_editorspick table and are affected by the bug described here.
3. Both wagtailsearch and wagtailsearchpromotions migrations applied - i.e. a fully migrated project with wagtail.contrib.search_promotions in INSTALLED_APPS. These have a wagtailsearchpromotions_searchpromotion table managed by Django, and no wagtailsearch_editorspick table. They are not affected by this bug (since the flush command knows about all tables that exist in the database).

From these, we want to arrive at a state where wagtailsearch_editorspick does not exist, and wagtailsearchpromotions_searchpromotion exists if and only if wagtail.contrib.search_promotions is in INSTALLED_APPS. Additionally:

1. In the post-bugfix state, adding wagtail.contrib.search_promotions to INSTALLED_APPS and running migrate must still successfully create the wagtailsearchpromotions_searchpromotion table.

My first pass at a solution would be:

• Change wagtailsearchpromotions.0001_initial to create searchpromotion as a new table, rather than renaming editorspick
• Change wagtailsearch.0003_remove_editors_pick to do nothing
• Add a new wagtailsearch.0007 migration to drop the editorspick table and model

However, this fails on scenario 3, because then the wagtailsearch.0007 migration will end up trying to delete a table that doesn’t exist. I expect there’s a way to specify “drop table if it exists” to work around that, but it feels like I’m missing a more elegant solution - if we got into this mess without any conditional logic, surely we can get out of it again?

I don’t know if this is useful information or not, but I only hit this when using LiveServerTestcase. TestCase and the WagtailTestCase don’t trigger it for me. Assuming it is related to TransactionTestCase rollback optimizations.

Thanks @peterb154. This worked for me:

from django.core.management.commands.flush import Command as BaseFlushCommand


class Command(BaseFlushCommand):
    def add_arguments(self, parser):
        super().add_arguments(parser)
        parser.add_argument(
            '--allow-cascade', action='store_true', dest='allow_cascade', default=False,
                help='Adds "CASCADE" option to TRUNCATE command if supported by db backend. Default=False.',
        )

I’m having this same issue whenever I try and run tests here is my output. This is making it so I can’t run any tests that work with Page models.

I really need a fix for this or we might have to stop using wagtail

======================================================================
ERROR: test_init_without_project (biz_content.tests.test_forms.CheckListFormTestCase)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/home/mikela/code/syracuse_biz_portal/biz_content/tests/test_forms.py", line 17, in test_init_without_project
    self.assertQuerysetEqual(form_qs, qs)
  File "/home/mikela/.conda/envs/syracuse/lib/python3.5/site-packages/django/test/testcases.py", line 966, in assertQuerysetEqual
    values = list(values)
TypeError: 'DeferringRelatedManager' object is not iterable

======================================================================
ERROR: test_init_without_project (biz_content.tests.test_forms.CheckListFormTestCase)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/home/mikela/.conda/envs/syracuse/lib/python3.5/site-packages/django/db/backends/utils.py", line 62, in execute
    return self.cursor.execute(sql)
psycopg2.NotSupportedError: cannot truncate a table referenced in a foreign key constraint
DETAIL:  Table "wagtailsearch_editorspick" references "wagtailsearch_query".
HINT:  Truncate table "wagtailsearch_editorspick" at the same time, or use TRUNCATE ... CASCADE.


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/home/mikela/.conda/envs/syracuse/lib/python3.5/site-packages/django/core/management/commands/flush.py", line 67, in handle
    cursor.execute(sql)
  File "/home/mikela/.conda/envs/syracuse/lib/python3.5/site-packages/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
  File "/home/mikela/.conda/envs/syracuse/lib/python3.5/site-packages/django/db/utils.py", line 95, in __exit__
    six.reraise(dj_exc_type, dj_exc_value, traceback)
  File "/home/mikela/.conda/envs/syracuse/lib/python3.5/site-packages/django/utils/six.py", line 685, in reraise
    raise value.with_traceback(tb)
  File "/home/mikela/.conda/envs/syracuse/lib/python3.5/site-packages/django/db/backends/utils.py", line 62, in execute
    return self.cursor.execute(sql)
django.db.utils.NotSupportedError: cannot truncate a table referenced in a foreign key constraint
DETAIL:  Table "wagtailsearch_editorspick" references "wagtailsearch_query".
HINT:  Truncate table "wagtailsearch_editorspick" at the same time, or use TRUNCATE ... CASCADE.


During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/home/mikela/.conda/envs/syracuse/lib/python3.5/site-packages/django/test/testcases.py", line 217, in __call__
    self._post_teardown()
  File "/home/mikela/.conda/envs/syracuse/lib/python3.5/site-packages/django/test/testcases.py", line 924, in _post_teardown
    self._fixture_teardown()
  File "/home/mikela/.conda/envs/syracuse/lib/python3.5/site-packages/django/test/testcases.py", line 960, in _fixture_teardown
    inhibit_post_migrate=inhibit_post_migrate)
  File "/home/mikela/.conda/envs/syracuse/lib/python3.5/site-packages/django/core/management/__init__.py", line 119, in call_command
    return command.execute(*args, **defaults)
  File "/home/mikela/.conda/envs/syracuse/lib/python3.5/site-packages/django/core/management/base.py", line 399, in execute
    output = self.handle(*args, **options)
  File "/home/mikela/.conda/envs/syracuse/lib/python3.5/site-packages/django/core/management/commands/flush.py", line 77, in handle
    six.reraise(CommandError, CommandError(new_msg), sys.exc_info()[2])
  File "/home/mikela/.conda/envs/syracuse/lib/python3.5/site-packages/django/utils/six.py", line 685, in reraise
    raise value.with_traceback(tb)
  File "/home/mikela/.conda/envs/syracuse/lib/python3.5/site-packages/django/core/management/commands/flush.py", line 67, in handle
    cursor.execute(sql)
  File "/home/mikela/.conda/envs/syracuse/lib/python3.5/site-packages/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
  File "/home/mikela/.conda/envs/syracuse/lib/python3.5/site-packages/django/db/utils.py", line 95, in __exit__
    six.reraise(dj_exc_type, dj_exc_value, traceback)
  File "/home/mikela/.conda/envs/syracuse/lib/python3.5/site-packages/django/utils/six.py", line 685, in reraise
    raise value.with_traceback(tb)
  File "/home/mikela/.conda/envs/syracuse/lib/python3.5/site-packages/django/db/backends/utils.py", line 62, in execute
    return self.cursor.execute(sql)
django.core.management.base.CommandError: Database test_syracuse_biz_portal couldn't be flushed. Possible reasons:
  * The database isn't running or isn't configured correctly.
  * At least one of the expected database tables doesn't exist.
  * The SQL was invalid.
Hint: Look at the output of 'django-admin sqlflush'. That's the SQL this command wasn't able to run.
The full error: cannot truncate a table referenced in a foreign key constraint
DETAIL:  Table "wagtailsearch_editorspick" references "wagtailsearch_query".
HINT:  Truncate table "wagtailsearch_editorspick" at the same time, or use TRUNCATE ... CASCADE.