Migration failed upgrading to latest from ~3 weeks ago

Installation
1

1 migrations failed!

Failed to migrate default
#<StandardError: An error has occurred, all later migrations canceled:

PG::UniqueViolation: ERROR:  duplicate key value violates unique constraint "index_user_emails_on_email"
DETAIL:  Key (lower(email::text))=(talk@remobjects.com<mailto:talk@remobjects.com) already exists.
>
/var/www/discourse/vendor/bundle/ruby/2.7.0/gems/rack-mini-profiler-2.3.3/lib/patches/db/pg.rb:110:in `exec'
/var/www/discourse/vendor/bundle/ruby/2.7.0/gems/rack-mini-profiler-2.3.3/lib/patches/db/pg.rb:110:in `async_exec'
/var/www/discourse/vendor/bundle/ruby/2.7.0/gems/activerecord-6.1.4.1/lib/active_record/connection_adapters/postgresql/database_st



== 20211201221028 MigrateEmailToNormalizedEmail: migrating ====================
-- execute("UPDATE user_emails\nSET normalized_email = REPLACE(REGEXP_REPLACE(email,'([+@].*)',''),'.','') || REGEXP_REPLACE(email, '[^@]*', '')\nWHERE (normalized_email IS NULL OR normalized_email <> (REPLACE(REGEXP_REPLACE(email,'([+@].*)',''),'.','') || REGEXP_REPLACE(email, '[^@]*', '')))\n  AND (id >= -2 AND id < 7155)\n")
Migrating secondsite
Docker Manager: FAILED TO UPGRADE
#<RuntimeError: RuntimeError>

Does anyone know what I can do to get past this?

2

Oh … we have seen this before on major version upgrades of PG. @saj recently worked on something here.

The long story is that on some PG upgrades the DB collation changes underneath you and you due to libc changing stuff. You have no choice but to reindex.

What happened to you is that:

  1. You did a major upgrade
  2. Your index become somewhat corrupt
  3. A second insert of a user for talk@remobjects.com was made - you now have a duplicate row despite the index disallowing it.
  4. We touch the row and it highlights this issue cause it can not update an unrelated column

Fix is:

  • Find the user ids of the 2 users sharing the talk@remobjects.com email.
  • Either delete or rename the email on one of them
  • Proceed as planned
  • Do a full db reindex urgently to catch other issues
3

Your solution did solve it but the “upgrade” already passed (e no more updates); do I need to do anything special to rerun these migrations? I did ofc delete the dupe emails.

4

Yes, be sure to run a db reindex

Try:

REINDEX DATABASE VERBOSE

Critical that you do so to ensure your indexes are all in good shape.

1 Like
5

Is there any way to check that a forum is “healthy” from time to time, before running into this sort of problem?

6

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.