Amazing reply, I truly appreciate that!
I will give it a go in the next couple of days and report back. Thanks again!
so got my wsl2 updated and installed ubuntu 20.04, got sqlite3 installed, i can now at least VIEW the data… however, my updates do not seem to be taking affect…
Thoughts? here are my commands I tried:
max@steel:/DBs$ sqlite3 com.plexapp.plugins.library.db "SELECT name, sql FROM sqlite_master WHERE type='trigger' AND tbl_name='metadata_items' AND name LIKE '%update%';"
fts4_metadata_titles_before_update_icu|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
fts4_metadata_titles_after_update_icu|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
max@steel:/DBs$ sqlite3 com.plexapp.plugins.library.db "DROP TRIGGER fts4_metadata_titles_before_update_icu;"
max@steel:/DBs$ sqlite3 com.plexapp.plugins.library.db "DROP TRIGGER fts4_metadata_titles_after_update_icu;"
#display full record
max@steel:/DBs$ sqlite3 com.plexapp.plugins.library.db "SELECT * from metadata_items where id = 398109"
398109|8||1|com.plexapp.agents.imdb://tt0118655?lang=en|1|Austin Powers: International Man of Mystery|Austin Powers: International Man of Mystery||New Line Cinema|7.09999990463257||If he were any cooler, he'd still be frozen, baby!|As a swingin' fashion photographer by day and a groovy British superagent by night, Austin Powers is the '60s' most shagadelic spy, baby! But can he stop megalomaniac Dr. Evil after the bald villain freezes himself and unthaws in the '90s? With the help of sexy sidekick Vanessa Kensington, he just might.|||PG-13||1||5640000|metadata://posters/com.plexapp.agents.imdb_4749113a9f89ec546bfca28b2b840300baf0a40d|metadata://art/com.plexapp.agents.imdb_ad7b23821f130a482a647aa0a9d974b0f255fe43|||lockedFields=9|Crime|Sci-Fi||Jay Roach|Mike Myers|Mike Myers|Elizabeth Hurley|Michael York|1997-05-02 00:00:00|||2021-03-30 18:15:26|1997|2021-03-30 18:11:04|2021-03-30 18:11:04|2021-03-30 18:16:15||USA|at%3AaudienceRatingImage=rottentomatoes%3A%2F%2Fimage%2Erating%2Eupright&at%3AratingImage=rottentomatoes%3A%2F%2Fimage%2Erating%2Eripe&ex%3AhasPremiumPrimaryExtra=1&ex%3AprimaryExtraKey=%2Flibrary%2Fmetadata%2F398114&pv%3AartBlurHash=LWI%7DPHs%3AXoR%2Co%23j%40V%40WX2vj%5BTJWB&pv%3AblurHashesChangedAt=4534736&pv%3AthumbBlurHash=TWPFbSIr_9wvnUrt_8xrM-z%7BXMOT|548b148927b6e453202db2cc0bee3f5cc652b83f|7.69999980926514|4534719|4534736|
#select just added_at
max@steel:/DBs$ sqlite3 com.plexapp.plugins.library.db "SELECT added_at from metadata_items where id = 398109"
2021-03-30 18:11:04
#attempt RyanMJean's command (added in ID to not affect all)
max@steel:/DBs$ sqlite3 com.plexapp.plugins.library.db "UPDATE metadata_items SET added_at = originally_available_at WHERE id = 398333 and originally_available_at IS NOT NULL;"
#unchanged
max@steel:/DBs$ sqlite3 com.plexapp.plugins.library.db "SELECT added_at from metadata_items where id = 398109"
2021-03-30 18:11:04
# i know originally is not null, so removed that
max@steel:/DBs$ sqlite3 com.plexapp.plugins.library.db "UPDATE metadata_items SET added_at = originally_available_at WHERE id = 398333;"
#unchanged
max@steel:/DBs$ sqlite3 com.plexapp.plugins.library.db "SELECT added_at from metadata_items where id = 398109"
2021-03-30 18:11:04
#tried a spefific value in the past
max@steel:/DBs$ sqlite3 com.plexapp.plugins.library.db "UPDATE metadata_items SET added_at = '2020-03-28 13:50:43' WHERE id = 398333;"
#unchanged
max@steel:/DBs$ sqlite3 com.plexapp.plugins.library.db "SELECT added_at from metadata_items where id = 398109"
2021-03-30 18:11:04
1 Like
@maxsteel, hmmm. It looks like your update commands are being done to id# 398333, but your selects are on id# 398109. Could your problem really be that simple?
1 Like
YES! OMG, my BAD! That’s what I get for having way too many notepads open! Also over the last few days I removed that 398333 
Thank you for being a second set of eyes!
EDIT: confirmed tested and now that movie is not showing! yay! 
However, for something so simple it shouldn’t be this complicated.
I was reading in the plex reddit about doing this using web calls. Someone was able to figure that out. I’ve been playing with this and am close. I just need to figure out how to get a valid token and it might actually work with 1 curl command or in a browser for that matter.
1 Like
@maxsteel, yay! Glad you got it working. Just don’t forget to add those triggers back (if you haven’t already). The nice thing about these commands is that, if it’s something you’ll be doing with any regularity, you can put them in a shell script to execute all at once, which should help make it a bit simpler to do.
Totally!! Thanks for all your help!
For anyone on a Synology, I’ve poked around a bit today and the command you need for CLI access is:
"/volume1/@appstore/Plex Media Server/Plex Media Server" --sqlite "/volume1/Plex/Library/Application Support/Plex Media Server/Plug-in Support/Databases/com.plexapp.plugins.library.db"
That opens up an interactive sqlite interface where you can run whatever queries you need.
3 Likes
Jim, I’ve tried the code in your snippet (Raspbian on a Raspberry): it works fine until the “Update”: I get an “Error: attempt to write a readonly database”. I’ve also tried after stopping the plexmediaserver: no changes.
Any idea?
Thank you! This worked for me!
Thank you so so much ! This worked for me. Using “Plex Media server --sqlite” instead my sqlite3 everything works fine without any collating problems (icu_root or tokenizer)… I’ll need to update all my library… but in some hours I’ll have my renaming_plex_bd_system running again 
Has anybody done this on FreeNas/TrueNas or freeBSD, please?
I just can not find any sqlite3 within the Plug-in Jail.
Edit:
I’ve done things to the DB in the past, on Debian using the sqlitebrowser, never had problems so far. Now im getting the unknown tokenizer: collating
Cheers
The suggestions to use --sqlite work on FreeBSD & FreeNAS. What command you using? What results are you getting?
Also the latest PMS 1.23 Plex Pass versions include another binary for launching SQLite directly.
This seems like an issue with PATH or simply a wrong program name
root@plex:/Plex Media Server # "Plex Media server" --sqlite com.plexapp.plugins.library.db "UPDATE metadata_items SET "added_at" = "originally_available_at" WHERE "added_at" LIKE '2021-05-23 10%' AND "originally_available_at" IS NOT NULL;"
Plex Media server: Command not found.
root@plex:/Plex Media Server #
Certainly. The current directory isn’t in $PATH on most *nixes.
./Plex\ Media\ Server --sqlite …
There are possibly quoting issues too - doubles can’t nest within doubles.
Any ole mp3/media player let’s you add files on top of a play list, at the end of a play list…
because people do know what they wanna to go where — sometimes 
Only PLEX does not give us an option to SET the file timestamp (originally_available_at) as added_at 
Anyway, as for TrueNAS 12 iocage jail this will get you SQLite to work (use your own statement):
root@plex:/Plex Media Server/Plug-in Support/Databases # service plexmediaserver stop
root@plex:/Plex Media Server/Plug-in Support/Databases # /usr/local/share/plexmediaserver/Plex\ Media\ Server --sqlite com.plexapp.plugins.library.db "SELECT added_at, originally_available_at FROM metadata_items WHERE added_at LIKE '2021-05-23 10%' AND originally_available_at IS NOT NULL LIMIT 6"
2021-05-23 10:04:01|2018-10-30 11:23:30
2021-05-23 10:04:01|2018-10-30 15:19:36
2021-05-23 10:04:01|2018-10-31 11:37:26
2021-05-23 10:04:01|2018-10-31 13:25:04
2021-05-23 10:04:01|2018-10-31 13:25:19
2021-05-23 10:04:02|2018-11-14 19:02:50
This has worked for me:
# Be save
service plexmediaserver stop
# This is the location for Plex Media Server SQLite Databases
cd '/Plex Media Server/Plug-in Support/Databases'
# This is the location for Plex Media Server Program/Executable on TrueNAS iocage jails
/usr/local/share/plexmediaserver
# Try a simple SELECT
/usr/local/share/plexmediaserver/Plex\ Media\ Server --sqlite com.plexapp.plugins.library.db \
"SELECT * FROM metadata_items LIMIT 2"
#
#Better do a quick count on how many rows are affected
/usr/local/share/plexmediaserver/Plex\ Media\ Server --sqlite com.plexapp.plugins.library.db \
"SELECT COUNT(*) FROM metadata_items WHERE added_at LIKE '2021-05-23 10%' AND originally_available_at IS NOT NULL"
Returned 4301 rows
# DANGER DANGER doing the UPDATE STATEMENT
/usr/local/share/plexmediaserver/Plex\ Media\ Server --sqlite com.plexapp.plugins.library.db \
"UPDATE metadata_items SET added_at = originally_available_at WHERE added_at LIKE '2021-05-23 10%' AND originally_available_at IS NOT NULL;"
#
# Now do a quick count again on how many rows are affected
/usr/local/share/plexmediaserver/Plex\ Media\ Server --sqlite com.plexapp.plugins.library.db \
"SELECT COUNT(*) FROM metadata_items WHERE added_at LIKE '2021-05-23 10%' AND originally_available_at IS NOT NULL"
Returned 31 rows
# To get an idea what those files are… (BTW here this has to do with how Nextcloud does instant uploads from my phone, it is okay-ish)
/usr/local/share/plexmediaserver/Plex\ Media\ Server --sqlite com.plexapp.plugins.library.db \
"SELECT added_at, originally_available_at, title FROM metadata_items WHERE added_at LIKE '2021-05-23 10%' AND originally_available_at IS NOT NULL "
2021-05-23 10:16:14|2021-05-23 10:16:14|Video 21-05-04 08-11-33 6159
2021-05-23 10:16:14|2021-05-23 10:16:14|Video 21-05-04 15-21-01 6161
2021-05-23 10:16:14|2021-05-23 10:16:14|Video 21-05-04 15-59-37 6162
2021-05-23 10:16:14|2021-05-23 10:16:14|Video 21-05-09 10-05-12 6191
2021-05-23 10:16:14|2021-05-23 10:16:14|Video 21-05-18 19-02-59 6282
2021-05-23 10:16:17|2021-05-23 10:16:17|Video 21-04-24 08-40-14 6103
2021-05-23 10:16:17|2021-05-23 10:16:17|Video 21-04-27 11-29-57 6104
2021-05-23 10:16:21|2021-05-23 10:16:21|Video 21-04-28 15-21-46 6106
2021-05-23 10:16:32|2021-05-23 10:16:32|Video 21-01-24 09-52-27 5589
2021-05-23 10:16:38|2021-05-23 10:16:38|Video 21-01-24 16-15-47 5598
2021-05-23 10:16:38|2021-05-23 10:16:38|Video 21-01-24 16-15-47 5599
2021-05-23 10:16:38|2021-05-23 10:16:38|Video 21-01-27 20-42-37 5609
2021-05-23 10:16:38|2021-05-23 10:16:38|Video 21-01-28 12-14-33 5611
2021-05-23 10:16:39|2021-05-23 10:16:39|Video 21-01-29 13-13-52 5617
2021-05-23 10:16:40|2021-05-23 10:16:40|Video 21-03-13 17-55-51 5855
2021-05-23 10:16:43|2021-05-23 10:16:43|Video 21-02-12 09-34-41 5738
2021-05-23 10:16:43|2021-05-23 10:16:43|Video 21-02-13 13-11-51 5747
2021-05-23 10:16:44|2021-05-23 10:16:44|Video 21-02-13 16-37-50 5748
2021-05-23 10:16:44|2021-05-23 10:16:44|Video 21-02-14 09-32-39 5757
2021-05-23 10:16:44|2021-05-23 10:16:44|Video 21-02-14 15-02-03 5763
2021-05-23 10:16:47|2021-05-23 10:16:47|Video 20-11-06 21-59-08 5185
2021-05-23 10:16:47|2021-05-23 10:16:47|Video 20-11-16 18-05-08 5206
2021-05-23 10:16:47|2021-05-23 10:16:47|Video 20-11-21 17-05-20 5218
2021-05-23 10:16:47|2021-05-23 10:16:47|Video 20-11-22 12-56-08 5245
2021-05-23 10:16:47|2021-05-23 10:16:47|Video 20-11-22 12-57-44 5246
2021-05-23 10:16:48|2021-05-23 10:16:48|Video 20-11-28 10-33-44 5280
2021-05-23 10:16:50|2021-05-23 10:16:50|Video 20-12-12 21-33-10 5361
2021-05-23 10:16:51|2021-05-23 10:16:51|Video 20-12-13 11-07-02 5364
2021-05-23 10:16:53|2021-05-23 10:16:53|Video 20-12-24 11-45-56 5405
2021-05-23 10:17:01|2021-05-23 10:17:01|Video 20-12-31 19-31-15 5475
2021-05-23 10:17:21|2021-05-23 10:17:21|Video 20-04-09 10-58-39 3861
Hope this will help others
Cheers
3 Likes
Thanks so much @RyanMJean! I am running Plex on Debian in a PVE Unprivileged Container on my NUC and typically edit a copy of the Database on my Windows Desktop using 'SQLite Database Browser ’ before overwriting it on the server. Your instructions worked like a charm!
So is a future updated version of SQLite for Windows going to be able to work around this or are we stuck using the PLEX version of sqlite via command line forever now?
https://sqlitebrowser.org might be still largely usable.
2 Likes
Standard SQLite works for queries, and for updates to most tables.
It can’t be used to update metadata_items or or the tags tables, which have the language extensions / triggers / full-text search.
