Mastodon: PG::DuplicateTable: ERROR: relation "index_tags_on_name_lower" already exists

[andreaspeters]

Expected behaviour

DB Migration from 3.0.0 to 3.0.1 should work without error.

Actual behaviour

Well, I'm migrating from the dockers version of mastodon 3.0.0 to 3.0.1 and getting the error message

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

Other failures I could not find. To be honest, I'm not 100% sure, if this failure already coming with the migration to 3.0.0.

[nightpool]

We primarily use GitHub as a bug and feature tracker. For usage questions, troubleshooting of deployments and other individual technical assistance, please use one of the resources below:

• https://discourse.joinmastodon.org
• #mastodon on irc.freenode.net

[Vertux]

@nightpool How can you be sure that this issue isn't a bug ?

I just upgraded my instance from v2.9.3 --> v3.0.1 and run into the same issue:

```RAILS_ENV=production bundle exec rails db:migrate
Migrating to AddCaseInsensitiveIndexToTags (20190726175042)
== 20190726175042 AddCaseInsensitiveIndexToTags: migrating ====================
-- execute("CREATE UNIQUE INDEX CONCURRENTLY index_tags_on_name_lower ON tags (lower(name))")
rails aborted!
StandardError: An error has occurred, all later migrations canceled:

PG::DuplicateTable: ERROR: relation "index_tags_on_name_lower" already exists
: CREATE UNIQUE INDEX CONCURRENTLY index_tags_on_name_lower ON tags (lower(name))
```
I do not want to open a new ticket for this problem.

[bzg]

I just ran into the same problem. I cannot find anything relevant on https://discourse.joinmastodon.org

Any hint appreciated!

[Vertux]

I just ran into the same problem. I cannot find anything relevant on https://discourse.joinmastodon.org

Any hint appreciated!

@bzg This worked for me.

[z411]

Same issue v2.9.3 --> v3.0.1, affected both by this and #12516

PG::DuplicateTable: ERROR: relation "index_tags_on_name_lower" already exists
PG::UndefinedObject: ERROR: index "index_tags_on_name" does not exist

I had to comment out these two lines.

https://github.com/tootsuite/mastodon/blob/c6287d58552f5036c8d089561966794017ab9ee8/db/migrate/20190726175042_add_case_insensitive_index_to_tags.rb#L18-L19

Thanks to ThibG as well.

[bzg]

Thanks for the hints, it's working now.

[ThibG]

With at least 4~5 different people hitting that issue, there's definitely something weird going on.

It seems to me that this migration has already run, which shouldn't be possible, at least with the current state of the migration scripts (migration scripts are all tested as part of continuous integration, now, and they didn't hit that issue).

My only guess for now, given that people also report “PG::UndefinedObject: ERROR: index "index_tags_on_name" does not exist” (#12516) but not for the hashtag_search_index index is that:

• for some reason, they never had the index_tags_on_name index
• they ran the migration script once, it failed, for the above reason, and for some reason (disable_ddl_transaction?) left the new index in place
• they re-ran it and posted only that last error

[ThibG]

I'd be very interested in the output of the following postgres queries for anyone having been affected by this bug:

• \d+ tags
• \d+ favourites
• \d+ notifications

And if you have backups from before a failed update, that would also be interesting to see…

[m1dor1]

I have a similar problem, but in a different way. I don't want to open a new issue so I just write here.

Every time I upgrade Mastodon with db:migrate (Docker version), it shows up:

igrating to CreateCustomFilters (20180628181026)
== 20180628181026 CreateCustomFilters: migrating ==============================
[strong_migrations] DANGER: No lock timeout set
-- create_table(:custom_filters)
rails aborted!
StandardError: An error has occurred, this and all later migrations canceled:

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

If you encounter a similar problem, please check on this table (change custom_filter to your duplicate table name):

# docker-compose exec -u postgres db bash
bash-5.0$ psql
postgres=# SELECT * FROM "custom_filters";

If this table is empty, you can drop it and rerun db:migrate again:

postgres=# DROP TABLE "custom_filters";
DROP TABLE

If this table is not empty, it means you have performed this migration successfully before. So you can just add it to the schema_migrations table (change 20180628181026 to your duplicate table timestamp):

postgres=# INSERT INTO "schema_migrations" (version) VALUES ('20180628181026');
INSERT 0 1
postgres=# SELECT * FROM "schema_migrations";

By the way, if you drop the table accidentally, there's no way to undo it. If you have a backup file generated by pg_dump, you can copy it to db and use pg_restore to restore the specific table:

# docker ps # get the db container id
# docker cp ./xxxx-xx-xx.dump containerid:/yourpath/xxxx-xx-xx.dump
# docker-compose exec -u postgres db bash
bash-5.0$ pg_restore -t "custom_filters" -f - /yourpath/xxxx-xx-xx.dump | psql
bash-5.0$ rm /yourpath/xxxx-xx-xx.dump

Hope it helps.

[andreaspeters]

Hi,

I could solve the issue by myself. I found the create table in one of the files under "db/migration". Just remove these item and the migration script could finish his job. :-)

Thanks to everyone and stay save,
Andreas