Can no longer update library database with sqlite3

Plex Media Server Metadata & Adding Files
,
127

I mean that the DBI:SQLite module accesses a SQLite DB file directly; it doesn’t go through a sqlite3 command-line binary, so you can’t swap in the Plex SQLite binary as a replacement.

There’s no Perl DBI::PlexSQLite available.

I’m not aware of any work to identify what the Plex SQLite customizations are. Something similar would be nice for the DB Browser for SQLite utility, for GUI users.

130

Sorry for the late replay -

I generally like to stop plex when doing DB edits.
And when the docker container for plex is down, you can not use the method you describe.

1 Like
131

I don’t think it’s necessary to stop Plex to modify display info on individual metadata entries. Fields like added_at are safe.

I do stop Plex if I’m doing anything more complicated, but those are rare occasions. Modifying libraries, or changing the relationships between database entities.

132

Exactly. For just modifying values in metadata_items, stopping Plex is a waste of time. I’ve done more edits than I can count this way, and nothing bad ever happened.

And if the container is down, I make sure it’s up first. If it’s down that means Plex is unavailable, which makes any db changes irrelevant anyway.

133

You can stop plex in the container and then do the updates.

In short, for the official plexinc/pms-docker here’s how you do it (very simple):

  1. Login to the docker container docker exec -it {plexcontainername} /bin/bash
  2. There’s a bash file in the root dir, plex_service.sh that is a short wrapper for the -s6-svc commands for plex. So to bring the service down: ./plex_service.sh -d -u for up, or -r for restart.
134

Getting the “unknown tokenizer: collating” error lead me here when trying to use DB Browser with some old instructions. is there a specific command I can run now to update an item’s added_at value? I have its ID. Thank you

135

@ourcore, there are lots of examples above! What have you tried? What platform are you using?

Note to others - Plex 1.26.x has changed the datatype of datetime columns. Plex is now storing a unix-style seconds-since-1970-epoch time.

Here’s a web toy for converting this format: https://www.epochconverter.com/

The sqlite strftime() function can be used in UPDATE statements to convert to epoch seconds on the fly; modify the YYYY-MM-DD and ABC as you would have before.

UPDATE metadata_items SET added_at=strftime('%s','YYYY-MM-DD') where id = ABC;

And the sqlite datetime() function can be used to convert epoch seconds to normal dates for display:

SELECT id,title,datetime(added_at,'unixepoch') FROM metadata_items WHERE id = ABC;
2 Likes
136

Perfect, thank you! I’m on macOS, so I think I just need help opening the database in the shell because the examples I’ve seen are for Windows, NAS, or Docker, so the directories are different. Thanks again

137

This page shows where the Plex SQLite command is on each platform -

And there’s a link to where the database files are located there too.

1 Like
138

Thanks, but when I run a SELECT query in sqlite, I get “Error: no such table: metadata_items”. Not sure what I’m missing

140

OMG! Amazing, thank you so much, this worked perfectly! I changed it slightly as I wanted to update the “Recently Added” to match the original release date of the movies so that the latest showed first (after moving a bunch of files and merging db’s…etc).
FWIW if anyone else wants to do the same, just edit the sql cmd to (using your DB ID of course):
UPDATE metadata_items SET added_at = originally_available_at WHERE library_section_id = 34;

141

What command are you executing?

My suspicion is that “Error: no such table: metadata_items” means the Plex database wasn’t specified and wasn’t opened.

142

I’ve tried a few commands:
"/Applications/Plex Media Server.app/Contents/MacOS/Plex SQLite" "~/Library/Application Support/Plex Media Server/Plug-in Support/Databases/com.plexapp.plugins.library.db" "SELECT * FROM metadata_items"

"/Applications/Plex Media Server.app/Contents/MacOS/Plex SQLite" "com.plexapp.plugins.library.db" "SELECT * FROM metadata_items"

And with "PRAGMA integrity_check" instead of the SELECT. I also went straight into "/Applications/Plex Media Server.app/Contents/MacOS/Plex SQLite", but wasn’t sure how to select the database from there.

143

The tilde ~ isn’t expanded inside double quotes.

There are a variety of alternatives. I like using $HOME instead.

"/Applications/Plex Media Server.app/Contents/MacOS/Plex SQLite" "$HOME/Library/Application Support/Plex Media Server/Plug-in Support/Databases/com.plexapp.plugins.library.db" "SELECT * FROM metadata_items"

1 Like
144

Ah, of course! Thank you. I ran this, but there was no output or change:
"/Applications/Plex Media Server.app/Contents/MacOS/Plex SQLite" "$HOME/Library/Application Support/Plex Media Server/Plug-in Support/Databases/com.plexapp.plugins.library.db" "UPDATE metadata_items SET added_at=strftime('%s','2018-04-20') WHERE id = 297858;"

145

If the update statement is successful there won’t be any output. The simplest way to confirm the changes is to run additional SELECT <blah> WHERE id = 1234 commands before and after the UPDATE.

If you Refresh the page in Plex you should see any changes. Are you viewing the library By Date Added?

The strftime() conversion to epoch seconds is necessary on 1.26+, but not on 1.25 or earlier. Earlier versions are happy with YYYY-MM-DD HH:MM:SS format.

146

Thanks, I got the command to work, but my records have updated_at, not added_at. I updated that date to something a few years ago and I can see it took effect when I SELECT it, but it’s still at the front in my Plex.

147

updated_at and added_at are 2 different fields.
1 is for when the item was added to the database and the other for when it was last updated.

148

I figured, but when I try to SELECT it, it says it doesn’t exist:
"/Applications/Plex Media Server.app/Contents/MacOS/Plex SQLite" "$HOME/Library/Application Support/Plex Media Server/Plug-in Support/Databases/com.plexapp.plugins.library.db" "SELECT added_at FROM media_items WHERE id = 297858;"

Error: no such column: added_at

And created_at is NULL for every record.

149

you sure your loading the correct database?

this is what i ran on my plex server (running on synology nas)

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”