URL's to remote users profile pictures/avatars are still stuck to the local cache in the database after disabling "Cache remote files", as well as being stuck to existing object storage bucket URL's after changing bucket address

[S0ulf3re]

💡 Summary

After disabling (and deleting) the remote files cache and/or object storage, Misskey still attempts to retrieve some user avatars from the local cache or object instance that doesn't exist anymore, therefore leading to missing images on the frontend unless said avatar is already cached in the user's browser.

This first happened to me while I was setting up my instance two days ago. I was having difficulties setting up object storage and had to end up making a new bucket, and relied upon local cacheing while I was setting up the object storage. In the end, I had decided on having misskey pull media from remote servers and distributing my own media through object storage. However, by this time, I have already managed to network with multiple instances and have been subscribed to multiple relays. Even after selecting the 3 dots on a user profile, selecting "about", and then "update remote user info", the URL still doesn't update.

🥰 Expected Behavior

URL's for avatarUrl in the database should be automatically updated to the resource of the respective instance (i.e example.local/files/some-uuid-here or s3.yourproviderhere.example/path/to/avatarUrl-thumbnail becomes example.remote/path/to/userAvatar) whenever preferences for object storage or file caching are changed.

🤬 Actual Behavior

The original avatar URL doesn't get updated to the respective instance location. Some of the URL's are pointing to an old S3 bucket, and some of them pointing to the local cache still (which is a problem if you already deleted the local folder thinking misskey would update everything)

📝 Steps to Reproduce

1. Have Misskey's "Cache remote files" option enabled
2. Let Misskey federate for a while
3. Disable "Cache remote files"
4. Add a properly configured aws s3 bucket to misskey's object storage
5. Let Misskey federate again
6. Turn off Object storage and let Misskey delete everything
7. Turn it back on and change the bucket of the object storage to another properly configured AWS bucket
8. Some of the avatar URL's might be out of sync

📌 Environment

Misskey version: Calckey v12.118.1-calc.1 Your OS: (Client) macOS 12.5.1 (Server) Ubuntu 22.04 Your browser: Safari 15.6.1

Workarounds

• A highly inconvenient one (and certainly not a feasible solution for large instances), is to manually remove each remote user from the database and add them again so that the system grabs. Which is not very useful because it both unfollows all local users from that instance and requires writing their addresses somewhere else so you can add them again.

TL;DR: Misskey doesn't seem to care about remote avatar URL's whenever you make significant changes to your instance's object storage or remote file cache preferences.

[ybw2016v]

The reason of this problem might be that misskey will check if the image already exists in the database by comparing the md5 and the owner of the image when update the proflie and avatar of remote user. Misskey would do nothing except update the url and uri of the file in the database if the avatar of the remote doesn't change even if the cache files have been removed. So the solution to this problem is to check the drive_file table in the database to find the wrong url of the files and correct them.

[S0ulf3re]

Ok, what would you reccomend to navigate the database? I have absolutely no idea of how to use psql

[S0ulf3re]

Ok. So, by using the following SQL:

SELECT * FROM public.drive_file where url ~ 'thetooniverse.xyz'

I was able to alienate all the incorrect files down. And many (but not all) of their URL's and thumbnail Urls are very similar. There are 11,553 records. How should I go about updating them? Obviously, manually updating all of them by hand is infeasible. So what would be the best option?

[ghost]

@S0ulf3re did you update the URLs in the database? if so, can you post the command?