Can no longer update library database with sqlite3

Plex Media Server Metadata & Adding Files
,
25

Hey!

Yep, using old school square single quote. I tried with updating a field with an integer just to make sure, and no result

Select queries work fine (though no console output, I have to dump the output out to a file)

26

I can’t really get it to work on Windows.

  • I run PMS as a service.
  • I stop PMS with the service icon in the tray
  • I have a batch file with this statement

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

I run it and Plex starts and opens the Web interface.

Wen I used to run with sqlite3.exe, the command prompt window would display, as expected:
ok

Perhaps something wrong with the syntax?

Edit: Could I ask for an update to this page: Repair a Corrupt Database | Plex Support ?

1 Like
27

Hello, I have tried the bash script method using the script below. Not sure what else i am missing.

#!/bin/bash
SQLITE=/share/CACHEDEV2_DATA/.qpkg/PlexMediaServer/Plex\ Media\ Server
DB=./com.plexapp.plugins.library.db
cd /share/CACHEDEV2_DATA/.qpkg/PlexMediaServer/Library/Plex\ Media\ Server/Plug-in\ Support/Databases
“$SQLITE” --sqlite $DB “UPDATE metadata_items SET added_at = originally_available_at WHERE added_at > ‘2021-23-01’ and library_section_id in (1,2,3,6,7,8)”

I get the output below. The location are definetly correct as i can CD to them manually.

: No such file or directorymedia/plexdate.sh: line 4: cd: /share/CACHEDEV2_DATA/.qpkg/PlexMediaServer/Library/Plex Media Server/Plug-in Support/Databases
: No such file or directorymedia/plexdate.sh: line 5: /share/CACHEDEV2_DATA/.qpkg/PlexMediaServer/Plex Media Server

28

Hi,

I want update the PLex Database from Python script and I get the error unknown tokenizer: collating.

Is there a solution to update database without using the Plex --sqlite command ?

Thanks.

1 Like
29

That’s the same issue I’m facing. Running the PLex Media Server.exe with the --sqlite parameter just seems to fire up Plex if I run any kind of insert or update to the DB. Select queries can be output though

@bpalloni you mentioned you managed to get it to work via powershell, are you able to write to the DB? Is there something we’re missing?

30

Yes, I also initially tried running this with “–sqlite” (2 dashes) on Windows CMD and all it does is launch the Plex executable and does not update the database. So I tired with just one dash “-sqlite” and this is what works for me.
Yes, I’m able to update and write to my database with all kinds of different statements using this.
Here are some examples:

From Windows Command Prompt:

“C:\Program Files (x86)\Plex\Plex Media Server\Plex Media Server.exe” -sqlite “com.plexapp.plugins.library.db” “UPDATE metadata_items SET title = ‘New Title’ WHERE id = ‘100’”

I prefer Powershell in my script, so you just have to add the ‘&’ before this to indicate it’s a command.
From Windows Powershell:

& “C:\Program Files (x86)\Plex\Plex Media Server\Plex Media Server.exe” -sqlite “com.plexapp.plugins.library.db” “UPDATE metadata_items SET title = ‘New Title’ WHERE id = ‘100’”

If you are running this script from the same directory as the database, you don’t need to full path to the database, but if the script is somewhere else then you do need to full path.

Hope this helps.

3 Likes
31

That was definitely it. Thanks a lot!

The only thing I can not see is output to the console.
When running the old way:
sqlite3 com.plexapp.plugins.library.db "PRAGMA integrity_check"

The statement returns
ok

But with the new way there is no output to the console.

32

Yeah same for me, it does not give back any output when running this way. I use SELECT statements in my script to first read the database and then update based out the contents. When using the “Plex Media Server.exe -sqlite” it does not display any results to the console, so I’m still using sqlite3.exe for the SELECT parts and only using the new way when I need to update.

33

Perfect, I tried with the single dash before when experimenting last night, and I think I locked the DB up which is why I couldn’t get it to up date. Tried it now with the full syntax and it’s going through

Time to update my scripts!

34

Hi all!

I hope I am in the correct post! I’m new to plex and have made the most of my music library, so I’m looking at all the possibilities.

I want to update the user_fields field of the metadata_items table and I always get the error “Error: unknown tokenizer: collating”. I have tried with db browser and dbeaver mapping the drive with WinSshFS, and from the linux client itself, but I always get the same error. I have the latest version of Plex installed on Linux raspberrypi 5.10.17-v7l +. I am frustrated, can someone help me?

Thanks in advance!:slight_smile:

35

for ubuntu i had to use

sudo /usr/lib/plexmediaserver/Plex\ Media\ Server --sqlite “/var/lib/plexmediaserver/Library/Application Support/Plex Media Server/Plug-in Support/Databases/com.plexapp.plugins.library.db”

then my sqlite commands no longer complained about collating

3 Likes
36

and for those DOCKER fans, this should work

#!/bin/bash
# if script name is plexsql.sh then
# usage is - ./plexsql.sh "select something from some_table where something = something_else"
# 
mkdir -p /opt/plexsql
sqlplex="/opt/plexsql/Plex\ Media\ Server --sqlite"
docker stop plex
docker cp plex:/usr/lib/plexmediaserver/ /opt/plexsql
cd "/opt/plex/Library/Application Support/Plex Media Server/Plug-in Support/Databases"
cp com.plexapp.plugins.library.db com.plexapp.plugins.library.db.original
"$sqlplex" com.plexapp.plugins.library.db "$1"

#and if you want to do something other than select statements use this instead
#cp com.plexapp.plugins.library.db com.plexapp.plugins.library.db.original
#"$sqplex" com.plexapp.plugins.library.db "DROP index 'index_title_sort_naturalsort'"
#"$sqplex" com.plexapp.plugins.library.db "DELETE from schema_migrations where version='20180501000000'"
#"$sqplex" com.plexapp.plugins.library.db "$1"

…basically

3 Likes
39

Thanks a lot! Works like a charm! Could you explain to me what is the reason it works this way? Sorry for my english:)

40

Volts pretty much summed it up a few posts back why it works this way.

1 Like
41

Thanks! This got me pointed in the right direction. I also had to delete the trigger ‘fts4_metadata_titles_before_update_icu’. After deleting both triggers, I was able to execute my update statement. Then recreated both triggers by running the DDLs for both.

42

Thanks for the update. Would you be able to share the steps? I would love to manually update the database once again

43

In DB Browser, delete the two triggers, and write (commit) the change:
fts4_metadata_titles_before_update_icu
fts4_metadata_titles_after_update_icu

Run your metadata update SQL statements. For example, to chronologically reorder:
UPDATE metadata_items
SET added_at = originally_available_at
WHERE library_section_id = 25;

Then recreate the two triggers, then commit:

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

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

As always, as a precaution, shut down Plex, and make a copy of your DB first before making edits. I have no idea what would happen if Plex started updating libraries after you delete your triggers, but probably nothing good. :slight_smile:

Also noticed that, for those who use UpdateTool for IMDB ratings, the code was recently fixed to do exactly the same thing with those triggers.

7 Likes
44

Thanks! It works!

45

Thanks! This is exactly what I needed!!

46

Thanks, I managed to fix my “Recently Added Movies” too, after radarr messed with all my movie titles which of-course has a knock on effect in Plex.

I used DB Browser for Mac, and after following your instructions I was able to recreate Recently Added to work from the movie aired date rather than the date added to my system.

I did have to do the procedure twice because after the first attempt, I had 4 movies at the front of the list still out of sequence because they had issues with their metadata, so I fixed those manually and then re-processed the DB.