I’m wondering if developing a plug-in that will solve the below problem is possible:
I’ve developed an external script that I was thinking of releasing that takes two databases from two servers and synchronizes the watched status (including partial watched) via the matadata_item_settings table. I know it’s not best practice, but it’s the only way to do it without tokens for every user, and that table structure hasn’t changed in forever. I’ve done extensive testing however there is one issue, caching.
I have to stop the servers before applying updates, or sometimes the database corrupts. I’m guessing this is because Plex is caching data from that table and I think it is trying to write data after I have. I can’t say for sure since I can’t see the queries unfortunately.
That being said, if I recreated this script as a plug-in, is there a way to write to that DB table that would force Plex to refresh before writing data?
@fmstrat I’m going to try and convince you to reconsider
I think a prospect of getting the database corrupted might put off a fair portion of your users if you release your work to the public, however convenient it may look, using non-standard way to edit the database will always have risk of breaking something. Besides, getting the user to authenticate your plug-in is fairly easy, basically the user needs to go to a certain page on plex.tv site and enter a PIN code there your app will show them, and that’s all, then your app will get a token that it can store for the further use. Users nowadays understand the need for this for their security, it also gets them a feeling that they are in control and be more confident in using your app. You can find a number of other plug-ins here already do it for eg. self-update functions that needs the token for server restart, I’ve even done this once in NSIS installer (and their programming language is pretty crazy compared to those I’m used to). In conclusion, I don’t think you should worry about having to get a proper authentication for your app from the users.
@czukowski said: @fmstrat I’m going to try and convince you to reconsider
I was expecting this
I think a prospect of getting the database corrupted might put off a fair portion of your users if you release your work to the public, however convenient it may look, using non-standard way to edit the database will always have risk of breaking something.
So this isn’t an issue if the database is stopped. I’ve done extensive testing with thousands of records. I have a lot of experience with the Plex DB as I’ve been working with it since the open source days.
Besides, getting the user to authenticate your plug-in is fairly easy, basically the user needs to go to a certain page on plex.tv site and enter a PIN code there your app will show them, and that’s all, then your app will get a token that it can store for the further use. Users nowadays understand the need for this for their security, it also gets them a feeling that they are in control and be more confident in using your app. You can find a number of other plug-ins here already do it for eg. self-update functions that needs the token for server restart, I’ve even done this once in NSIS installer (and their programming language is pretty crazy compared to those I’m used to).
This is the rub. The app I have developed is not a user app, it’s an administrative one. I could use something like plex-sync (and have, even made the Docker container for it) but that’s a single user system. My solution synchronizes all users across the server, making failover possible. Using the DB also adds the ability to sync status even if you are partway into an episode with the GUID vs making hundreds of API calls.
I would actually love to build this as a feature for Plex Pass instead of external or a plug-in at all. Basically have the UI allow you to choose from servers you are logged into and then select one as a master. The back end code would be the same, but for a plug-in I’ll have to also write a listener and require Dynamic DNS.
In conclusion, I don’t think you should worry about having to get a proper authentication for your app from the users.
If there was some form of “administrative token” that allowed me to make API calls for any user with data on the server, I would totally agree with you. Barring that, it’s the only way to make the vision I have work that I can tell.
@fmstrat I must admit I don’t know the answer to the original question you’ve asked (should have mentioned that earlier too, I know)
I agree that direct DB access is more efficient than API. Still seems pretty dangerous though, the table structure could “suddenly” change and cause the data to be corrupted (seems not very likely, but I don’t think Plex devs wouldn’t give any heads up before doing so) and then there are those write conflicts (?) when you and Plex server use the DB at the same time. Maybe it’s not even cache, it might just hold the data in memory for some time during library scanning or something, and write something back based on a state that’s no longer true. Are you able to take DB snapshots, like every second or so? Maybe you could figure out what causes the conflicts based on the differences in snapshots when the corruption occurs… this seems like a long shot though.
I agree that direct DB access is more efficient than API. Still seems pretty dangerous though, the table structure could “suddenly” change and cause the data to be corrupted (seems not very likely, but I don’t think Plex devs wouldn’t give any heads up before doing so) and then there are those write conflicts (?) when you and Plex server use the DB at the same time.
Yea, I don’t see this happening. The table has been unchanged for many, many years. And likely even if it did happen the queries would just fail. I could even add a transaction to it so they all role back if any one query fails.
Maybe it’s not even cache, it might just hold the data in memory for some time during library scanning or something, and write something back based on a state that’s no longer true. Are you able to take DB snapshots, like every second or so? Maybe you could figure out what causes the conflicts based on the differences in snapshots when the corruption occurs… this seems like a long shot though.
It’s everything. For instance, if I mark an episode watched on one server, it doesn’t always write to the DB instantly. In all my testing the corruptions were very, very rare. That’s why I’m wondering if I executed the queries in Python as part of PMS (via a plugin) would it overcome the problem.
@czukowski said: @fmstrat so what actually happens to the database if it gets corrupted? Is it crashed and it’s impossible to open the file?
Yes. It’s easy to restore from one of the backup files in the Database directory. As stated though, this only happens if Plex is not stopped and a client writes to the DB at the same time. I’m actually testing some ways such as forcing a media scan to see if that refreshes the client’s cache.
So good news! After some further testing, I’m actually wondering if the caching has to do with sshfs or Docker. The restart may not be a requirement after all if I can sort that out.
EDIT
Nope, definitely Plex caching. If Plex is killed (or dies) it loses the most recent watch status.
Not sure where you are with this anymore, but this might possibly give you some insight into this: https://www.sqlite.org/wal.html It can at least possibly give you something to test.
I can’t say it would be a good idea to force a checkpoint with the automatic system running, but you could do that as a test to see if you then adding your information still causes the corruption.
Sqlite is not designed to be accessed directly over a network connection.
Sqlite should only be used by one application a time.
As such, I sadly consider your design is bad.
Consider using the api instead, and then in a setting file store the access token of the owner. You can then use that to get user tokens from plex. Tv
First, I’ve tried checkpoints but unfortunately since the library Plex is using is caching, the checkpoint from a separate client has no impact since the sqlite file has no knowledge of the write that has not yet been executed. (wow, triple negative)
Second, I don’t agree at all that sqlite should only be used by one application at a time. We’re not talking about v2 here, we are talking about v3, wherein one of the major updates was locking (https://www.sqlite.org/lockingv3.html). If Plex wasn’t caching, then a separate write should work just fine.
