중복 키 오류("index post hotlinked media on post id and url md5")로 업그레이드 실패

[axfelix (Alex Garnett)]

Had an automatic rebuild fail, looks like variations on this issue:

2022-05-03 15:30:54.370 UTC [1262] discourse@discourse LOG:  duration: 1707.909 ms  statement: INSERT INTO post_hotlinked_media (post_id, url, status, upload_id, created_at, updated_at)
        SELECT
          post_id,
          obj.key AS url,
          'downloaded',
          obj.value::bigint AS upload_id,
          pcf.created_at,
          pcf.updated_at
        FROM post_custom_fields pcf
        JOIN json_each_text(pcf.value::json) obj ON true
        JOIN uploads ON obj.value::bigint = uploads.id
        WHERE name='downloaded_images'

ERROR: duplicate key value violates unique constraint "index_post_hotlinked_media_on_post_id_and_url_md5"

Had rebuilt less than a month ago with no errors so whatever introduced this seems to have been recent.

[axfelix (Alex Garnett)]

seems to be something to do with this recent commit?

db/migrate/20220428094026_create_post_hotlinked_media.rb

@david

[david (David Taylor)]

Yep that’s it. I have a fix in the pipeline, should be merged in the next few minutes. Thanks for reporting @axfelix

https://github.com/discourse/discourse/pull/16611

[axfelix (Alex Garnett)]

Thanks, that was fast!

Do I need to do anything special to pull this down? I’m still getting Caused by: ActiveRecord::RecordNotUnique: PG::UniqueViolation: ERROR: duplicate key value violates unique constraint "index_post_hotlinked_media_on_post_id_and_url_md5" when running a launcher rebuild, even after doing a Docker prune.

[david (David Taylor)]

The fix just hit tests-passed a few seconds ago. If you rebuild now, it should work much better. Let us know how it goes.

[david (David Taylor)]

We found one more place which can cause this error. So if the first fix didn’t help, this one should

https://github.com/discourse/discourse/commit/1c9d8b4999cd7ab885cc06d6c981511318008bb7

(now live in tests-passed)

[pfaffman (Jay Pfaffman)]

On my development instance it does successfully migrate the database, but if I restore a backup from 2022-04-27-153103-v20220407195246.tar.gz it fails:

                                                                                [361/9020]
Migrating the database...                                                                               EXCEPTION: /home/pfaffman/src/discourse-repos/discourse/lib/discourse.rb:126:in `exec': Failed to migrat
e database.
Ignoring /home/pfaffman/src/discourse-repos/discourse/db/schema_cache.yml because it has expired. The cu
rrent schema version is 20220407195246, but the one in the schema cache file is 20220428094027.
rake aborted!
StandardError: An error has occurred, this and all later migrations canceled:

followed by some more of that with some of these:

ActiveRecord::StatementInvalid: PG::DuplicateObject: ERROR:  type "hotlinked_media_status" already exist

[axfelix (Alex Garnett)]

Just chiming in to say that the first fix worked for us after it passed tests!

[david (David Taylor)]

@axfelix

the first fix worked

Great, thanks for confirming!

@pfaffman

but if I restore a backup

I opened a PR with a fix for that one:

https://github.com/discourse/discourse/pull/16624

[pfaffman (Jay Pfaffman)]

Finished!
[SUCCESS]
Restore done.

Thanks!