Upgrade fails on duplicate key error ("index post hotlinked media on post id and url md5")

Bug
1

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.

4 Likes
3

seems to be something to do with this recent commit?

@david

2 Likes
4

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

9 Likes
5

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.

6

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.

5 Likes
7

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

(now live in tests-passed)

4 Likes
8

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
1 Like
9

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

2 Likes
10

Great, thanks for confirming!

I opened a PR with a fix for that one:

4 Likes
11 
Finished!
[SUCCESS]
Restore done.

Thanks!

2 Likes