Old Media file (image/audio/video) URL from remote server cannot be updated locally if remote server change their S3 URL

[sandycorzeta]

💡 Summary

As of Misskey.io maintenance several days ago, some of media files that are attached to the old notes before its maintenance isn't accessible to be seen on my own server. I noticed this due misskey.io S3 server changed their domain from media.misskeyusercontent.com to media.misskeyusercontent.jp. Is there a way to update the URL domain of the media files from old posts in mass locally?

i also found that this could relate to #9067 bug too.

🥰 Expected Behavior

New Origin URL of attached media should be propagated to other server when changed even its an old posts.

🤬 Actual Behavior

Attached media from old posts coming from remote server not updated and cause the media file not found/accessible. Although when requesting a new updated information from remote server manually per profile, seems doesn't change/update anything.

📝 Steps to Reproduce

No response

💻 Frontend Environment

* Model and OS of the device(s):
* Browser:
* Server URL:
* Misskey:

🛰 Backend Environment (for server admin)

* Installation Method or Hosting Service:
* Misskey: 2024.3.1
* Node: 
* PostgreSQL: 
* Redis:
* OS and Architecture:

Do you want to address this bug yourself?

• [ ] Yes, I will patch the bug myself and send a pull request

[tomakun]

Hi @sandycorzeta,

Somebody posted a solution online for remote server admins who wish to manually fix this issue on their server. Here's the proposed solution, provided by @minneyar@fuzzyfox.social:

if you've got access to the psql console for your #misskey / #sharkey instance's postgres database. Just open up the console and run this:

UPDATE drive_file 
    SET url = REPLACE(url, 'media.misskeyusercontent.com', 'media.misskeyusercontent.jp'),
        uri = REPLACE(uri, 'media.misskeyusercontent.com', 'media.misskeyusercontent.jp'),
        src = REPLACE(src, 'media.misskeyusercontent.com', 'media.misskeyusercontent.jp');

(Obligatory disclaimer: you should not trust SQL snippets given to you by random people on the internet, make sure you understand what the above is doing before you run it, and make a backup first)

If you're stuck on #Mastodon, run this instead:

UPDATE media_attachments SET remote_url = REPLACE(remote_url, 'media.misskeyusercontent.com', 'media.misskeyusercontent.jp');

It may take several minutes to run if you've got a big database, but that should fix everything.

I should note that I strongly feel like the longterm solution should come from Misskey.io themselves as they seem to have carelessly changed a key domain without implementing a redirect, thus breaking their assets everywhere.

I hope this helps!

[tomakun]

Misskey.io管理方々へ Message addressed to Misskey.io admins:

先日Misskey.ioにてアセットドメインが変わった結果、リモートサーバ（Misskey, Mastodonなど）側でドメイン変更前の画像が正しく読み込まれなくなり（表示されない）、かなり大きい損害になってしまっています。

media.misskeyusercontent.com から media.misskeyusercontent.jp

上記はリダイレクトなしで実行されてしまっているのでしょうか？



現状、リモートサーバ側で解決するためにDB上のURLをReplaceし始めている管理者もいるようですが、この問題はそちらで解決する予定はありますでしょうか？ 

 

よろしくお願い致します。

[sandycorzeta]

Hi @sandycorzeta,

Somebody posted a solution online for remote server admins who wish to manually fix this issue on their server. Here's the proposed solution, provided by @minneyar@fuzzyfox.social

i've seen this instruction the other day, and it is actually quite an easy fix to run. Unfortunately when i try to run it, it cost me a long await of query (at pgAdmin) and 2 hours is not enough as i have about a million records on my instance (see this). I've checked the htop and it doesn't eat that much of CPU/RAM to run the update, but long await still ruin the day if i decided to make a downtime for a day just to handle this problem. On the other hand, when i try to run this with Misskey services still running, it blocks the inbox query and makes everything gets delayed. So, yeah... quite a pain to handle.

Thanks for the heads up, by the way @tomakun 👍.