The other day I decided to restructure my photo and home video libraries. I switched from flat directories to a nested directory approach (YEAR/MONTH). This allows me to trigger partial library scans for performance reasons.
So, I went about my merry way adding over one hundred or so directories to my Plex photo library and walked away to let the server do it's thing. The next morning I noticed something - my media was no longer in the correct order. My home page 'Recently Added' section was all over the map. Looks like dumping all of the directories at once wasn't a great idea...but I really didn't want to do it again.
After a little research, I discovered that I could just edit the Plex sqlite file (com.plexapp.plugins.library.db). So, here's what I did:
1) Stop the Plex server
You don't want anything writing to this file while we're modifying it. My server's on Ubuntu 18.04, so I ran this:
sudo systemctl stop plexmediaserver
2) Make a copy of the DB file
Don't mess around here, just copy the file somewhere.
sudo cp "/var/lib/plexmediaserver/Library/Application Support/Plex Media Server/Plug-in Support/Databases/com.plexapp.plugins.library.db" /tmp/com.plexapp.plugins.library.db.bak
3) Export DB file to your local machine
I'm not feeling very confident running these commands in a terminal. I want to see and inspect the data before I put the file back, so I'm editing this in a GUI.
# Run this from your local machine (assuming you have ssh access to your plex server) rsync --protect-args -P "plex:/var/lib/plexmediaserver/Library/Application Support/Plex Media Server/Plug-in Support/Databases/com.plexapp.plugins.library.db" D:\Desktop\plex.db
4) Edit the SQLite file in editor of your choosing
I quickly found and downloaded DB Browser for SQLite. Why? It showed up first in Google and it looked OK. It's fine, but I removed it afterwards.
I loaded the db file in my soon-to-be-deleted editor and poked around a bit.
select * from metadata_items
I decided I wanted to change the added_at field to match the originally_available_at field. This field seems to match the creation date as far as I can tell.
update metadata_items set added_at = originally_available_at where originally_available_at is not null and library_section_id = 4 -- my photo library id
After inspecting the results, this looked pretty good so I ran the same command for my home video library and wrote the changes to the DB file.
5) Put the SQLite file back
First I had to rsync it to the /tmp directory so I could enter the server and move it with the correct privileges.
# From my local machine rsync --protect-args -P D:\Desktop\plex.db "plex:/tmp/com.plexapp.plugins.library.db"
# On the Plex server sudo -u plex mv /tmp/com.plexapp.plugins.library.db "/var/lib/plexmediaserver/Library/Application Support/Plex Media Server/Plug-in Support/Databases/com.plexapp.plugins.library.db" # Just making sure the permissions are set (they probably already are) sudo chmod 644 "/var/lib/plexmediaserver/Library/Application Support/Plex Media Server/Plug-in Support/Databases/com.plexapp.plugins.library.db" # Restart the Plex service sudo systemctl start plexmediaserver
That's it. It actually worked. I didn't even have to re-scan the libraries.