Fixing Future-dated metadata

Plex Media Server Metadata & Adding Files
1

After finding this excellently informative post by @omegadev: Fix for movies and tv shows stuck at the front of recently added

I think I may have streamlined this approach. In my case, the updated_at dates were fine, but the added_at dates were out of whack. You could easily replace updated_at with unixepoch() to make them added “right now”.

I did this on the DB live while Plex was running because I like to live dangerously. Don’t try this at home. Consult a medical professional before beginning treatment. etc…

Also note, those trigger definitions might change between versions. I fyou’re not using the Server version I listed at the bottom, use .schema metadata_items to copy their create statements somewhere safe and use those to put them back in place after your UPDATE statement.

DROP TRIGGER fts4_metadata_titles_before_update_icu;
DROP TRIGGER fts4_metadata_titles_after_update_icu;
UPDATE metadata_items SET added_at = updated_at WHERE added_at > unixepoch();
CREATE TRIGGER fts4_metadata_titles_after_update_icu AFTER UPDATE ON metadata_items BEGIN INSERT INTO fts4_metadata_titles_icu(docid, title, title_sort, original_title) VALUES(new.rowid, new.title, new.title_sort, new.original_title); END;
CREATE TRIGGER fts4_metadata_titles_before_update_icu BEFORE UPDATE ON metadata_items BEGIN DELETE FROM fts4_metadata_titles_icu WHERE docid=old.rowid; END;

Server Version#: 1.43.0.10162 (TrueNAS Docker Container)

2

Thanks for the shoutout @akraut . I still think its rather damming that this issue still exists and the plex team seems to be thumbing their nose all this time. at us. The only reason i didn’t use unixepoch() is it would be detrimental to my hypersensitive OCD. LMAO. Still good call.

3

Aside from doing deep digging on the disk for the ~800 entries that needed fixing to find “last modified file times” I decided that updated_at was a suitable proxy. But yeah, I hear ya. I wish it was more accurate to the time it landed on disk for the first time. Some of these are years old…

4

Definitely, i’ve got some files with created dates as far back as 2004 so i’m right there with ya. Anywhoo. Happy my post was able to help someone else sort through their stuck items. It took me a while to get to a bulletproof solution, at least for my needs, figured i’d share it with the community.