A few questions:
One, is there any real value to having a Where clause of “added_at > datetime(‘now’)”? It seems like this would only trigger if the added_at field is currently in the future, which it generally shouldn’t be.
Two, how do you handle updates where originally_available_at is null? That can happen whenever a media item isn’t matched, and has made for headaches in the past.
Three, could your script problem be as simple as needing to specify the .db extension on the file?
i have few entries where the year 2098 has been used in the “added_at” field, thus they are always stuck at the beginning of the “recently added tv shows” row.
this script will correct that.
yes. the command required the .db to be added to it.
fully working command to fix my specific issue is
"/var/packages/Plex Media Server/target/Plex SQLite" "com.plexapp.plugins.library.db" "update metadata_items set added_at=originally_available_at where added_at > datetime('now');"
this has now corrected those items with the 2098 year
Is there still no way to edit “metadata_items” with DB Browser for SQLite? Why is the only option the command line, where you can easily screw up your database file with a wrong character?
I have two episodes of a show that are the wrong date, causing the entire season to be shown on Plex and Tautulli as “added 2 months ago”, when in actuality it was added 7+ years ago along with the rest of the episodes…
The modification dates on my files are correct. The bug is with Plex. I confirmed that the “added_at”, “created_at”, and “updated_at” for the 2 episodes in question, are all identical in the Plex database.
not without modifying the database structure first
you can just as easily screw it up using DB Browser, so i don’t see any more risk in either method.
use the command i posted above if you wanted to amend the added_at date. (modify the SQL query to suit your specific use case).
The OP only has 2 entries he wants to change so it would be easier to manually edit those 2 fields versus running an update query.
Unfortunately, due to the changes Plex has made to the database, DB Browser just won’t work.
Alternatively, change the system date on your server and Plex Dance those 2 episodes. The added_at date will then be the date you changed the system to. Don’t forget to change it back afterwards.
the point i was making, the same SQL query would be run using both methods, so the risk is the same regardless.
i did suggest editing the SQL query i used to suit their particular use case.
Sure, using SQL commands carries the same risk regardless which method is used. My point, which is what I think the OP was trying to say, is that using a query to edit 2 fields isn’t necessary and being able to edit them directly by just editing the field, has much less risk.
Not even comparable. Using a GUI and being able to see all the tables in the database, easily filtering columns, makes editing a breeze. Terminal is not even comparable and is the worst option for a scenario like this. I already gave my reason above. On top of that, copying and paste commands in a terminal is extremely risky.
That command you posted above won’t work. I don’t want them changed from “2 months” ago to today’s date. I wanted them to have the same date as every other episode in the season, which was 7+ years ago… Only these 2 episodes are out of order.
Since you can view the data in DB browser, you can look up their IDs. You can then use the following:
“update metadata_items set added_at=‘2021-11-11 00:00:00’ where id=123;”
Change the date and id to the what you need. Repeat for the other episode.
2 Likes
Thanks, that worked. I still hope for changes in the future allowing me to use DB Browser for SQLite.
For anyone on Ubuntu LTS 20.04 using the Plex Snap package, this was my full command:
sudo /snap/plexmediaserver/current/Plex\ Media\ Server --sqlite “/var/snap/plexmediaserver/common/Library/Application Support/Plex Media Server/Plug-in Support/Databases/com.plexapp.plugins.library.db” “update metadata_items set added_at=‘2013-05-27 17:19:01’ where id=7193”
Unfortunately, that’s probably never going to happen. SQLite allows custom features to be added to a database, which is what Plex has done. DB editors are not able to cope with this.
I’m running Plex in Docker on a Synology , in a container called PlexMediaServer, and had to make a few edits. But thanks a ton for posting this.
/volume2/Plex/plexsql$ cat plexsql.sh
#!/bin/bash
# if script name is plexsql.sh then
# usage is - ./plexsql.sh "select something from some_table where something = something_else"
#
sqlplex="/volume2/Plex/plexsql/plexmediaserver/Plex Media Server"
sudo docker stop PlexMediaServer
sudo docker cp PlexMediaServer:/usr/lib/plexmediaserver/ /volume2/Plex/plexsql
cd "/volume2/Plex/Library/Application Support/Plex Media Server/Plug-in Support/Databases"
cp com.plexapp.plugins.library.db com.plexapp.plugins.library.db.bak
"$sqlplex" --sqlite com.plexapp.plugins.library.db "$1"
that’s why i said adjust the query to suit your needs. jeez!
I just wanted to say thank you for such clear instructions. I’m in windows and kept getting the tokenizer error and I used your red SQL commands in DB browser. It seems to have fixed the problem. Hogans Heros and Get Smart are no longer most recently added!
1 Like
i dont mind the plex sqlite for cmds, except i have to give the full path name. and like previously pointed out we don’t have to remove the triggers and re-add triggers to get rid of the tokenizer error and allow us to update all fields. although many thanks for those who came up with a solution at the time. 
—example on a windows machine for others below------------
--------where plex sqlite is----------
“C:\Program Files (x86)\Plex\Plex Media Server\Plex SQLite.exe”
---------where the database is you want to edit------------------
“C:\Users\user\AppData\Local\Plex Media Server\Plug-in Support\Databases\com.plexapp.plugins.library.db”
-------------commands for sqlite--------------------------------------
" UPDATE metadata_items SET created_at = added_at WHERE library_section_id = 24;"
the first 2 sections are always the same unless you change to a new location.
the 3rd section is your commands sent to the database. i just copy past allot in text document.
-----------------so all put together on one line---------------------------
“C:\Program Files (x86)\Plex\Plex Media Server\Plex SQLite.exe” “C:\Users\user\AppData\Local\Plex Media Server\Plug-in Support\Databases\com.plexapp.plugins.library.db” " UPDATE metadata_items SET created_at = added_at WHERE library_section_id = 24;"
i haven’t touched a database since 1995 in high school till yesterday, and now im wondering why i have avoided databases for so long, there not to bad. although i have allot to learn still im sure.
I don’t understand the point of your script. I do read or write actions by simply using:
docker exec -it plexmediaserver "/usr/lib/plexmediaserver/Plex Media Server" --sqlite "/config/Library/Application Support/Plex Media Server/Plug-in Support/Databases/com.plexapp.plugins.library.db"
Then if desired, alias that into plexsql or whatever:
$ plexsql "SELECT DISTINCT parent_id FROM metadata_items WHERE metadata_type = 4;"
198
257
I’m new to databases, too, and would like to play with the Plex library, for instance to clean up genre tags and so on.
I am using Perl with the DBI:Sqlite module to access and manipulate the db. Of course, I ran into the problem that plex uses its own flavor of sqlite, so for the time being I resorted to the “drop trigger, create trigger” workaround to make things work.
My question is: Is there a way to make DBI use the plex sqlite binary to avoid this trick?
No, the DBI accesses a database file directly.
Well, not really. DBI is a universal interface to let perl communicate with different databases. For each type of database, you load the appropriate module such as DBI:Sqlite, and that translates the generic DBI-commands into specific database commands.
So the question would be: Is there something like DBI:Plex-Sqlite, or is it possible to write it oneself? Or put differently: what is really different about the plex-flavor of sqlite?
