SQL Errors on migrate a fresh install

[sarensabertooth]

Issue summary

SQL Error when trying to migrate django.db.utils.OperationalError: near ")": syntax error

How to reproduce?

Freshly installed a wagtail project trying to isolate the issue. Followed the install instructions then tried to migrate

Technical details

• python3, django 3.0, wagtail 2.9

[sarensabertooth]

It took me two days to figure out what was meant with migrating a non wagtailtrans site. I kept thinking I missed something and there was a boilerplate for wagtailtrans sites that could be installed in place of a regular wagtail project. So I deleted all my pages and suddenly it just works. Maybe add this little detail to the docs.

[marteinn]

Thanks for filing an issue @sarensabertooth! Reading your comment I think its unclear what the docs are missing, can you please elaborate? I assume this is the documentation we're talking about? https://wagtailtrans.readthedocs.io/en/latest/migrating.html

[sarensabertooth]

@marteinn It would help if it was explained that with no pages setup in the admin panel, the whole migration process becomes easier. However, I basically solved my issue, but when trying to create new pages, I don't get my structblocks displayed at all, and on further migrations, the fatal error I first stated gets thrown again. I decided to move away from this CMS, it's too tedious to install a very basic translation feature which doesn't even seem to be compatible with a fresh install of the latest wagtail version, and it should come preinstalled to begin with. Also lacks complex layouts (asymmetrically positioned elements, think fashion/architect websites), not everyone wants to run a blog where everything is positioned vertically and completely parallel. I need it to be like WP Gutenberg with large images and easy to edit for clients (representing the content as it will be on the frontend). This would at least mean implementing 2 and 3 column structblocks, which seems to be a nightmare in itself. And much too little docs on achieving any of what a modern/skilled webdesigner needs.

[marteinn]

Thanks for the feedback @sarensabertooth! There is a lot of information to unpack in your comment, some off-topic from this issue and library, but I will try to answer the best I can.

Regarding docs, I will try to do a couple of test installs and have your scenario in mind and see if the docs can be improved.

(Off topic begin) Regarding complex layouts and page building in general, there is a document called "The Zen of Wagtail" (https://docs.wagtail.io/en/v2.9/getting_started/the_zen_of_wagtail.html) where we explain the though behind Wagtail and what sets it apart.

The problem with layout focused page builders and inline-editing, as I see it, is that you often limit yourself to a single-channel approach, there is only one channel (for instance a website) that will consume and display the data. The problem arises if you also have to expose data through an API or have data in your page builder that needs to be sent to a external system (like a CRM), then you need to extract data from your page builder which is modeled for showing a layout.

All platforms are build with a different mindset/different ways of solving problems and if your need is to do large parts of page building within the CMS with inline editing and have no multichannel need, Wordpress with Gutenberg (as you mentioned) could be a good choice, perhaps along with Polylang?

If you have more questions/comments regarding Wagtail, you can reach out on our Slack, its a much better forum for these type of discussions, you can find it here https://docs.wagtail.io/en/v2.9/support.html?highlight=support#slack.

[jkevingutierrez]

Hi,

I agree with @sarensabertooth, it would be good to have an easier way to implement wagtailtrans, or maybe more guidance on the documentation

I was trying to add this package to a fresh Wagtail installation, but I got that SQL error. django.db.utils.OperationalError: near ")": syntax error

If this is something complex to add to a fresh Wagtail site, I can't imagine how it would be to add it to a complex site.

[jkevingutierrez]

There are a some things that I found, that could be useful:

1. When I ran python manage.py makemigrations it generates something like this:

operations = [
       migrations.RemoveField(
            model_name='homepage',
            name='page_ptr',
        ),
        migrations.AddField(
            model_name='homepage',
            name='translatablepage_ptr',
            field=models.OneToOneField(auto_created=True, default='ptr', on_delete=django.db.models.deletion.CASCADE, parent_link=True, primary_key=True, serialize=False, to='wagtailtrans.TranslatablePage'),
            preserve_default=False,
        ),
]

The order here seems to be important, and it should be:

operations = [
      migrations.AddField(
            model_name='homepage',
            name='translatablepage_ptr',
            field=models.OneToOneField(auto_created=True, default='ptr', on_delete=django.db.models.deletion.CASCADE, parent_link=True, primary_key=True, serialize=False, to='wagtailtrans.TranslatablePage'),
            preserve_default=False,
        ),
      migrations.RemoveField(
            model_name='homepage',
            name='page_ptr',
        ),     
]

2. After changing the order I was getting a different error:

django.db.utils.IntegrityError: NOT NULL constraint failed: new__home_homepage.page_ptr_id

But after checking the migration guide, I saw that you need to add some manual queries, like:

operations = [
        migrations.RunSQL(
            """
            BEGIN;

            -- Remove constraints so we can edit our table
            ALTER TABLE pages_homepage DROP CONSTRAINT pages_homepage_pkey CASCADE;

            -- Add ``translatablepage_ptr_id`` field and copy the ``page_ptr_id`` content
            ALTER TABLE pages_homepage ADD COLUMN translatablepage_ptr_id INTEGER UNIQUE;
            UPDATE pages_homepage SET translatablepage_ptr_id=page_ptr_id;

            -- Insert the required values in ``wagtailtrans`` table
            INSERT INTO wagtailtrans_language (code, is_default, position, live) SELECT 'en', 't', 0, 't' WHERE NOT EXISTS (SELECT code FROM wagtailtrans_language WHERE code='en');
            INSERT INTO wagtailtrans_translatablepage (translatable_page_ptr_id, canonical_page_id, language_id) SELECT translatablepage_ptr_id, NULL, 1 FROM pages_homepage;

            -- Add required indexes and constraints
            ALTER TABLE pages_homepage ADD CONSTRAINT pages_homepage_translatablepage_ptr_id_e5b77cf7_fk_wagtailtrans_translatable_page_id FOREIGN KEY (translatablepage_ptr_id) REFERENCES wagtailtrans_translatablepage (translatable_page_ptr_id) DEFERRABLE INITIALLY DEFERRED;
            ALTER TABLE pages_homepage ALTER COLUMN translatablepage_ptr_id SET NOT NULL;
            ALTER TABLE pages_homepage ADD PRIMARY KEY (translatablepage_ptr_id);

            -- Remove old page_ptr column
            ALTER TABLE pages_homepage DROP COLUMN IF EXISTS page_ptr_id;

            COMMIT;
            """,
            state_operations=[
                migrations.AddField(
                    model_name='homepage',
                    name='translatablepage_ptr',
                    field=models.OneToOneField(auto_created=True, on_delete=django.db.models.deletion.CASCADE, parent_link=True, primary_key=True, serialize=False, to='wagtailtrans.TranslatablePage'),
                    preserve_default=False,
                ),
                migrations.RemoveField(
                    model_name='homepage',
                    name='page_ptr',
                ),
            ]
        ),
    ]

3. After replacing my migration file, I was getting another error

django.db.utils.OperationalError: cannot start a transaction within a transaction

To fix this, I removed the BEGIN; and the COMMIT; lines in the query

4. After removing BEGIN; and COMMIT; I was getting a different error:

django.db.utils.OperationalError: near "DROP": syntax error

As per https://stackoverflow.com/a/1884893/4508187 it seems that sqlite doesn't support alter table drop constraint

It would be good to have some documentation about how to make this work on sqlite, or if not possible, to specify that sqlite can't be used in the example provided