Help with editing Database

[dcuellar44]

Server Version#: 1.41.0.8930

I posted on the /plex reddit but I think this may be a better place to go to for support.

I’m trying to edit the database to update the “added_at” dates to what I have for “originally_available_at”. I’ve looked at a few guides and through some trial and error learned that I can’t just use sqlite, rather I need to use Plex SQLite.

Now I’m having trouble executing the command using Plex SQLite. Can someone help me put together the terminal command I could use in Unraid terminal?

Location of Plex SQLite: /mnt/user/appdata/binhex-plexpass/Plex Media Server/

Location of db I need to edit: /mnt/user/appdata/binhex-plexpass/Library/Application Support/Plex Media Server/Plug-in Support/Databases/com.plexapp.plugins.library.db

SQL I wish to execute: update metadata_items set added_at = originally_available_at where originally_available_at is not null and library_section_id = 2

I tried and just can’t seem to figure it out. Any help would be greatly appreciated.

Here is my attempt:

What am I doing wrong here? Been trying for the past few hours and my server has been down the whole time. I’m willing to send someone the db if they can do it for me. I don’t usually do things in terminal so this will probably be a one time thing.

[anon5074910]

Judging by your screenshot I think you are trying to do this with one command. Its multiple commands, so basically something like this …

[plex]-[dokuro]:~/Downloads$ "/usr/lib/plexmediaserver/Plex Media Server" --sqlite /home/plex/Downloads/com.plexapp.plugins.library.db
SQLite version 3.39.4 2022-09-29 15:55:41
Enter ".help" for usage hints.
sqlite> update metadata_items set added_at = originally_available_at where originally_available_at is not null and library_section_id = 2;
sqlite> .quit
[plex]-[dokuro]:~/Downloads$

I copied my db to ~/Downloads beforehand. Three commands here, 1) connect to the db, 2) update the db and 3) quit and save the changes.

[dcuellar44]

When I use --sqlite I get a message like below:

What am I doing wrong?

[dcuellar44]

I’m in root. Am I supposed to be in the plex container? Does plex need to be running?

I see you have [plex]-[dokuro]:~/Downloads$ so that is confusing me.

Also, you mentioned you copied the db to ~/Downloads beforehand. What does that command look like?

[anon5074910]

Mmmm, strange. What does "/usr/lib/plexmediaserver/Plex Media Server" --sqlite return (update for your path information). If the same error does "/usr/lib/plexmediaserver/Plex SQLite" open a sql prompt ?

There is nothing complex here [plex]-[dokuro]:~/Downloads$ This is user (plex), hostname (dokuro) and current path (~/Downloads$).

Maybe running as root is causing issues, I’d never do that but cannot speak to your specific setup.

[dcuellar44]

I’m making some progress. I ended up copying the database to another folder as well as the Plex SQLite. Once I did that I just changed the directories in my command and it at least got me into SQLite.

Now I get an error that I can’t open the database file. Any clues?

[anon5074910]

Looks like an access problem on the file. What permissions does it have.

[dcuellar44]

[dcuellar44]

Can you tell me what the ...> means?

[DTR]

...> is SQLite’s way of showing that you’re continuing the query from the previous line(s). Since your query is missing a semicolon at the end, SQLite thinks you want to continue your query on a new line, so is waiting for you to enter more data. Typing ; and pressing enter should execute the query.

[dcuellar44]

Thanks. That makes sense now. Thought it meant it was loading and got excited.

Seems like I am stuck here. I rebooted the server and still no luck.

[anon5074910]

Thanks @DTR I was away from my PC

Yeap @dcuellar44 your missing the ending ; from your sql statement. Once its completed then .quit to exit (remember the dot in front of quit).

[dcuellar44]

Ok. I got it. For some reason the db file I copied to a different location (using Krusader) was not working. I changed the directory back to the Databases folder and it worked on that db file. Here is what it looked like when it finally worked:

I hope this helps out others who were looking to do something similar to me.

I also want to thank both of you for helping me through this… @anon5074910 and @DTR. It was frustrating but a good learning experience and I would have pulled my hair out trying to figure it out on my own. Thank you so much!

[anon5074910]

Nice – glad you got sorted and learnt something along the way.