Can no longer update library database with sqlite3

Plex Media Server Metadata & Adding Files
,
1

Server Version#: 1.22.1.4200
Player Version#: N/A

I can no longer manually update the com.plexapp.plugins.library.db with sqlite3.
Any attempt to edit the database exits with “Error: unknown tokenizer: collating”.
Using a gui like sqlitebrowser produces a similar error.

(Libraries are updated ok when new content is added, btw.)

I just want to know how to get around the tokenizer error when manipulating the db from cli.
Any hints?
jim

3

The necessary magic to use Plex’s SQLite is here:

I believe this is associated with improvements in how full-text searching is implemented.

There’s a new index, index_title_sort_icu. And two new virtual full-text-search tables, fts4_metadata_titles_icu and fts4_tag_titles_icu - that’s where the collating add-on tokenizer is being used.

Things I haven’t tested:

I wonder if dropping index_title_sort_icu and the fts4*icu virtual tables and associated triggers would work, if done correctly. Similar to how the Repair a Corrupt Database instructions have you drop index_title_sort_naturalsort.

There’s a big group of schema migrations associated with these. They all start with 500000000000 so it might be possible to do.

4

This

Exactly what I was looking for.
Many Thanks!

jim

6

Hello, would someone be able to provide details steps & commands to modify the added_at after these new changes please? My server version is also 1.22.1.4200.

Thanks

7

Code snippet:

#!/bin/bash
SQLITE=/usr/lib/plexmediaserver/Plex\ Media\ Server
DB=./com.plexapp.plugins.library.db
cd /var/lib/plexmediaserver/Library/Application\ Support/Plex\ Media\ Server/Plug-in\ Support/Databases
"$SQLITE" --sqlite $DB "UPDATE metadata_items SET added_at = datetime(originally_available_at, '+1 days') WHERE id like 'YOUR-XML-ID';"

Should be reasonably self-explanatory.

jim

1 Like
8

Is there a way to do this on Windows?
I’ve looked everywhere and can’t find any solutions to this. I also don’t see sqlite3.exe in any of the plex folders.

10

I too am having issues. This was a great workaround to modify newly added, older movies. I now plan to halt any new additions to the library until I’m able to do this again. This changed immediately after Plex was recently updated. I’m using version 1.22.1.4228.

Any help would be appreciated!

11

Can you explain how what @Volts said resolved your issue? Some of that looks greek to me.

1 Like
12

I have edited my database using DB Browser for SQLite.

I edit it to add EAC3 7.1 channel for the audio source as Plex will only display EAC3 5.1, even when 8 channels are present in the source.

13

Plex is now using extensions to SQLite. So instead of the OS-provided SQLite program, you need to use the one built into Plex.

/path/to/Plex\ Media\ Server --sqlite /path/to/com.plexapp.plugins.library.db

4 Likes
14

In Windows, in Plex install dir, I can only see a sqlite3_plex.dll file but not a sqlite3_plex.exe or a sqlite3.exe file.

From time to time I run this procedure: Repair a Corrupt Database | Plex Support

How should I do it from now on?

15

Exactly my question too.

17

Have tried on Mac at command line, got an initial message about address in use. Shutdown the app, ran ./Plex\ Media\ Server\ --sqlite ‘library location of DB’ ‘UPDATE STATEMENT’ but which would not return any error messages but start up the server and not much else. Used to find being able to run the command within DB Browser For SQLite very useful to keep the library tidy.

18

When I use DB Browser for SQLite, I have to display the hidden folders (using CMD+SHIFT+.), then I navigate to:

Library/Application Support/Plex Media Server/Plug-In Support/Databases

I can’t get to the database any other way (Mac OS 10.12.6 Sierra).

19

I’ve been using sqlite3.exe with PowerShell on Windows to update my Plex DB for a while now and it stopped working after a recent update.
I found this post and based on @Volts comment I was able to figure out how to get it working again using the Plex EXE.
Here is a basic command format I use on Windows and with PowerShell in case anyone is interested:

$PlexDB = “C:\Users\PLEX\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 = “SQLITE STATEMENT HERE”

& “$PlexEXE” “-sqlite” $PlexDB $SQL

5 Likes
20

Thank you!! This works! Just change the \PLEX\ in $PlexDB = “C:\Users\PLEX\AppData\Local\Plex Media Server\Plug-in to your user directory!

1 Like
21

Many thanks.

I currently have sqlite.exe in the same dir as the plex DBs and have several batch files with statements like these:

sqlite3 com.plexapp.plugins.library.db "PRAGMA integrity_check"

sqlite3 com.plexapp.plugins.library.db .dump > dump.sql

So the solution would be to change it to this?

“C:\Program Files (x86)\Plex\Plex Media Server\Plex Media Server.exe” -sqlite com.plexapp.plugins.library.db "PRAGMA integrity_check"

“C:\Program Files (x86)\Plex\Plex Media Server\Plex Media Server.exe” -sqlite com.plexapp.plugins.library.db .dump > dump.sql

Is the syntax OK?

1 Like
22

I eventually discovered by deleting the trigger “fts4_metadata_titles_after_update_icu” while having the DB open in DB Browser, I could execute UPDATE “main”.“metadata_items” SET “added_at”=“originally_available_at” and then do a manual scan. Post scan I just ran the Create DDL for the trigger again:
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

2 Likes
23

I’ve been having issues trying to run this via Windows CMD. If I run the following command, nothing happens and the prompt just returns back to normal (it in fact looks like it just fires up another instance of the PMS process), querying the DB after I can see nothing updated. Are any other windows admins still having trouble?

“C:\Program Files (x86)\Plex\Plex Media Server\Plex Media Server.exe” --sqlite “C:\Users\david\AppData\Local\Plex Media Server\Plug-in Support\Databases\com.plexapp.plugins.library.db” “update metadata_items set user_fields = ‘lockedFields=305’ where id = 488795 and metadata_type = 8”

24

The forum likes to use “pretty” curly quotes - just to verify, in the command you’re typing, you’re using nice oldschool square quotes?

Does a simple query work?

"select * from metadata_items order by id desc limit 3"
"select * from metadata_items where id = 488795"
1 Like