So I just did the Plex Dance on a whole library of 700+ files.
It was probably overkill and in hindsight I shouldn’t have done it that way… but I did and so I’m trying to deal with the consequences of my choices.
The main one being that every one of my files is listed in my library as “Added 31 minutes ago”.
Is there a tool or utility out there that is capable of mass-updating the stored XML data for this single library?
Ideally I’d like to mass update the “date added” to match the file create data of each of the files it’s pointing at.
Plan-B is /facepalm and never go anywhere near “Sort: Date Added” again for the next year or so.
Thanks in advance.
The XML data is part of an SQLITE3 database, which makes editing it directly a questionable idea. Plus I needed to do a LOT of files, not really practical one by one.
I downloaded a SQLite utility (in my case, DB Browser for SQLite).
I shutdown the Plex Server application - so the database wasn’t in use.
Then used DB Browser to connect to the SQLite database used by Plex. (Stored in %LOCALAPPDATA%\Plex Media Server\Plug-in Support\Databases\com.plexapp.plugins.library.db as part of my Windows setup).
After that, it was a case of finding out which section ID was assigned to my Library by using the Browse Data panel in DB Browser to view the library_sections table.
Then using another of the fields on the database to refresh the date added for all the entries in that library.
UPDATE metadata_items SET added_at = originally_available_at where library_section_id = '2'
Apparently originally_available_at matches the filename’s “last modified” date.
Whilst this isn’t a tool which actually checks the file dates stored by the operating system, it’s close enough for what I wanted. Hopefully it might help someone in a similar situation.
Just did it and basically it put date added = date movie was released. That’ll work, it was better than what I had after rebuilding my plex server everything was added on one day, but wish I knew a way to make the date added = date of file. Sometimes I add movies that are older than others and it’s always nice to sort by what’s new.
DISCLAMER: Use select first to know what you are changing.
Thess queries should help:
Show all invalid items:
SELECT originally_available_at FROM metadata_items WHERE added_at > date(‘now’) AND originally_available_at IS NOT NULL;
Update invalid items:
UPDATE metadata_items SET added_at = originally_available_at WHERE added_at > date(‘now’) AND originally_available_at IS NOT NULL;
You can also change the date to a specific date like ‘2018-12-31’
I had some of them that are missing originally_available_at so to fix those:
UPDATE metadata_items SET added_at = ‘2018-12-31’ WHERE added_at > date(‘now’) AND originally_available_at IS NULL;
PS:
In order to find the database I used find /var -name com.plexapp.plugins.library.db
I opened it using sqlite3 com.plexapp.plugins.library.db
I had this with entire tv series showing up as recently added (making to impossible to see what actually had been recently added) which has persisted over all versions of plex for the last 2+ years. I tried the plex dance, I reloaded plex from scratch and reloaded my entire operating system (not to fix plex but effectively making plex a very clean install). I am not savvy with SQL, but however and whatever this script does, it fixed my entire issue.
After all this time, I had the biggest sigh of relief. I was on the verge of switching to different server software. I would think that plex could easily ensure that dates should never exceed the current date and time. I am not a programmer, so maybe there is a deeper issue.
I also used DB Browser for SQLite. The only thing I would add is that you need so save and close DB Browser before restarting Plex. It may be stupid, but Im not brilliant and tried to load plex before exiting,
correct