The one part I can’t figure out is where the executable is for a Synology install. Anyone have that?
Edit: thanks, @yooniverse! That was exactly what I needed to make it work. I used a Select statement to get the trigger’s SQL straight from the DB, rather than wondering whether I’d type it correctly… But yeah, all-set!
I am using sqlite3 in python to update my imdb ratings in database. After the latest update I’m also getting unknow tokenizer error: collating.
Does it mean I can no longer use sqlite3 in python to update my database?
Is there any work around?
Appreciate if somebody can point me to the right direction.
Thanks
So, what I learned is that there are two ways to go about this.
So, how did I go about method #2? Here’s how: (my example is for updating the added_at date of media, which modifies the ‘metadata_items’ table, so adjust as necessary from the commands I’m listing here) This command would be run from the shell session at the same folder as the database file. Also, I’m going to assume you already stopped Plex and made any necessary backup.
$> sqlite3 com.plexapp.plugins.library.db "SELECT name, sql FROM sqlite_master WHERE type='trigger' AND tbl_name='metadata_items' AND name LIKE '%update%';"
$> sqlite3 com.plexapp.plugins.library.db "DROP TRIGGER fts4_metadata_titles_before_update_icu;"
$> sqlite3 com.plexapp.plugins.library.db "DROP TRIGGER fts4_metadata_titles_after_update_icu;"
$> sqlite3 com.plexapp.plugins.library.db "UPDATE metadata_items SET added_at = originally_available_at WHERE originally_available_at IS NOT NULL;"
$> sqlite3 com.plexapp.plugins.library.db "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;"
$> sqlite3 com.plexapp.plugins.library.db "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;"
Something to consider about the “delete & restore triggers” approach is that the triggers keep the full-text search system updated.
originally_available_at should be safe - the triggers & FTS tables don’t reference it.
Just don’t modify title, title_sort, or original_title while the triggers aren’t in place.
Thanks for your quick response @RyanMJean.
I did follow the steps you recommended but it didn’t resolve my issue.
Looks like the problem I’m facing is the conflict between sqlite3 used in python code versus what’s already built in Plex Media Server. Or maybe something else that I can’t figure out.
I’ve been using this python code: GitHub - Joost1991/plex-imdb-updater: Updates movies and shows with recent IMDB rating to update my imdb ratings which was working perfectly until recently after I updated my Plex Media Server.
Now when I run the code I get the following error:
Database Error: unknown tokenizer: collating
I did lot of search and finally found this forum’s dicussions the most relevant.
I event followed the instructions to repair my database although I don’t think it’s corrupted.
Once again, thanks so much for the information provided.
I’ll keep digging to see if I can find the root cause of this error.
Cheers
Yeah, I can see why that would get caught up, and it looks like that project hasn’t been updated in a while so it may be defunct. There’s several DB Update commands buried in utils/db.py that would get caught up in the new full-text indexing triggers. In theory, disabling the triggers before running the IMDB updater then reenabling them after would work, especially since the updates don’t run afoul of Volts’s observation above, though that relies on making changes to the live-running copy and coordinating those changes between your commands and the program.
Alternately, you could modify the python script to wrap all the update commands appropriately (since all updates are to the same metadata_items table and all invoke the same db_execute method from line 94, you could theoretically insert two drop-trigger commands just before line 96 and the two create-trigger commands right after line 96), but that’s done at your own risk.
Thanks, I’ll give it a try tomorrow.
Much appreciate it!
Cheers
@Volts & @RyanMJean - is there any way you can post a simple step-by-step tutorial for users who need to do the command line process to modify the added_at date? I tried following this:
$PlexDB = “C:\Users\Administrator\AppData\Local\Plex Media Server\Plug-in Support\Databases\com.plexapp.plugins.library.db”
$PlexEXE = “C:\Program Files (x86)\Plex\Plex Media Server\Plex Media Server.exe”
$SQL = "UPDATE metadata_items SET added_at = datetime(originally_available_at) WHERE id like 'YOUR-XML-ID';"
& “$PlexEXE” “-sqlite” $PlexDB $SQL
but I seem to get lost around the 'YOUR-XML-ID" part. I do plan to save this as a .ps1, and run from PS. Forgive my ignorance, but an extra step would be that I would like to modify specific movies… not all of the added_at fields at once. Do you have any insight on that method?
@RyanMJean, thank you once again for your help.
I finally fixed the issue as per your recommendation but by adding a new python class in db.py to drop and then create the trigger commands only for database UPDATE commands because SELECT doesn’t really need those extra steps.
I’m hoping Plex’s team can soon launch a TV Shows agent to add imdb the ratings at episode level so that we don’t have to manipulate the database.
Cheers
Begs to question why we have to do this, seems like a lot of people like to edit these fields, especially the added_at field… sigh…
That being said when I run (I’m in the directory where the DB is):
"C:\Program Files (x86)\Plex\Plex Media Server\Plex Media Server.exe" -sqlite "com.plexapp.plugins.library.db" "SELECT * from metadata_items where id = 398333"
nothing seems to happen for me. the prompt just comes back… scratches head…
Plex is now using language collation extensions to SQLite to better facilitate searching.
Is there a metadata_item with id 398333? If not, no results will be returned.
"select * from metadata_items order by metadata_items.id desc limit 1;"
Oh yeah I tried many… that was just one I picked for my example.
I also tried other commands and they all come back to the prompt with nothing.
I assume you’re running it from a command prompt in the “Databases” directory - so it finds the .db file?
Any difference if you don’t provide a query, to launch it interactively?
"C:\Program Files (x86)\Plex\Plex Media Server\Plex Media Server.exe" -sqlite "com.plexapp.plugins.library.db"
Are these extensions available in dll/so form in plex installs or are they statically linked? I mean I can deal with a CLI as much as the next guy but I prefer db software (ex sqlitebrowser) so I don’t make mistakes on one offs, but most can only load extensions from dynamically linked libraries, instead of searching executables for the relevant symbols.
Sorry, weekend got in the way 
Yes I am in the same dir as the db’s. I tried your interactive command, and same deal. Nothing all it does is just gives a blank line, then the prompt comes back without delays…
Not that I’m aware of. Plex ships with a number of dll/so files, but I have no idea what’s built into the Plex Media Server binary vs. the libraries, or if you could somehow attach them to a different SQLite host app.
@maxsteel, can you just run a straight-up sqlite3 command on the DB? Assuming you’re working from a terminal that has it installed, if you just run the command sqlite3 com.plexapp.plugins.library.db from the DB’s directory, does it log into the DB at all? (If it does work, you can use .exit [note the period] to logout of it). If so, you should be able to do the method I outlined in this post. While it’s not as clean as making edits through Plex’s built-in sqlite instance, it does the trick.
If you can’t do that, then you probably don’t have a standalone version of sqlite installed. There’s one for Windows that works in CMD and PowerShell, and it’s really easy if you run linux (or a linux distro via WSL).
Just to be clear, are you saying i need sqlite installed for the internal Plex one to work?
Or is your idea separate from the Plex embedded option?
I know I don’t currently have sqlite installed in Windows as far as I know, but that’s doable in any of those 3 options… most likely I’d pick WSL2
I’m technical, just very little DBA experience.
From what I’ve read I should be able to script what I want to do, that will be very handy!
I will check out your post and report back after I try it. Thanks again!
@maxsteel, the internal one doesn’t need any separate install of sqlite - it’s already baked in to the Plex install, after all. But if you want to interface with the DB without using Plex as the intermediary, you need to install sqlite to do so. The thing is, you’ve mentioned repeated problems getting the calls to the built-in one to work, so I figured maybe trying direct would work where the old method was failing.
On my setup, I have two ways. My Plex runs on a Synology NAS, so merely logging in via SSH gives me a linux shell with sqlite installed. While for when I access things directly over the network shares, I also have an Ubuntu instance running in WSL2 on my Win10 machine, which I interact with via Windows Terminal. One command of apt get sqlite3 in Ubuntu got everything installed and ready to go. For those not using WSL, sqlite also has Windows executables that install so that you can run the commands from the classic Command Prompt shell or from Windows PowerShell.
Since you indicated you’d probably be able to use WSL, I’d suggest giving it a try. If you can connect that way, you’ll have full ability to do whatever edits you might need. The only challenge is that, due to the update to full-text indexing of certain fields, you have to work around the table triggers to do anything that way. It’s annoying, but it does work really well.
That’s unfortunate. Do we know which version jump caused the change? I want to avoid it for the time being.
