Relation "discourse_automation_automations" restoring from 3.3beta1 to 3.5beta7

Product Support Self-hosting

AI-generated summary

A user attempts to restore a Discourse database from version 3.3.0.beta1 to 3.5.0.beta7 but encounters a PG::DuplicateTable error for the discourse_automation_automations table. The issue arises because a migration from 2019 (create_automations_table.rb) is being re-applied, even though the table already exists. The user tries various solutions, including deleting migrations, skipping them, and manually inserting entries into schema_migrations, but none resolve the problem. The user eventually restores the original database and concludes the issue may not be common, suggesting the topic can be closed. pfaffman discusses the challenge, while Moin raises a potential plugin conflict, which is dismissed. tobiaseigen suggests closing the topic.

1

Trying to restore a database from

    <meta name="generator" content="Discourse 3.3.0.beta1-dev - https://github.com/discourse/discourse version a4022f69f31af611bc0e21258b0ccb77dec38ba3">

to current. I copied the same app.yml and am restoring (new VM and trying to sidestep the pg upgrade, though maybe I should try that instead of the restore)

I’m getting this error:

PG::DuplicateTable: ERROR:  relation "discourse_automation_automations" already exists

Seems like another case of some migration getting checked back in and this site got built during those few commits.

I don’t know what to do, though. Maybe I delete the migration that tries creating this table?

Old database is on 20240122015630 and new is 20250617085536.

I see this in the new source:

db/migrate/20190601000001_create_automations_table.rb:    create_table :discourse_automation_automations do |t|

But somehow this migration doesn’t know not to do that one? That table has the same fields on both. Maybe I just delete that migrations from 2019?

2

I think 3.3beta1 was before the plugin was included in core in April. Could this lead to any issues - for example, due to the plugin now being included in core while still also being installed separately via app.yml?

3

That’s a good idea, but that’s not it. I’m not including it again. I wonder if maybe I should have tried adding it (but I don’t see it in a quick look). My current idea is to drop all of the automations tables (they are empty) on the old site, make a new backup and trying restoring that.

4

Well, and then it failed because discourse_automations_automations was missing. :person_shrugging:

5

So I removed these:

20210108134117_add_pending_pms_table.rb
20210512090204_add_timestamps_to_automations.rb
20210530122323_move_triggers_to_fields.rb
20211119103353_adds_discourse_automation_user_global_notice.rb
20230412120414_add_prefers_encrypt_field_to_pending_pms.rb

and probably should have removed 20190601000001_create_automations_table.rb, but instead I added unless table exists code (I think when I deleted it I had another problem?).

And I finally got it to restore.

And when I rebuild, I get the same errors when the database tries to migrate.

So somehow I guess I need to convince rails that these migrations have, uh, run already. So then I tried this:

INSERT INTO schema_migrations(version) VALUES
('20190601000001'),
('20210108134117'),
('20210512090204'),
('20210530122323'),
('20211119103353'),
('20230412120414');

And that didn’t work either.

I think maybe it’s just those ones frrom 2021 I need to skip? Or maybe 202105?

But the version that’s running is from January 2024.

I was able to restore the first database (before I’d mucked with the schema_migrations) on the old server, so at least I’m back where I started. I guess I need to know how to look at the commits and migrations in some ways that I don’t.

6

Can we close this topic? Or move it to Development or #installation:hosting if you want to keep it open ended?

7

Yeah. You can close it. I’m unclear if it’ll ever happen to anyone else so that this may or may not be useful.