Server Version#: 1.31.3.6868
Player Version#: Version 4.100.1
<If providing server logs please do NOT turn on verbose logging, only debug logging should be enabled>
I’m doing a basic low level recovery on my database to hopefully correct an issue where the Plex Server started locking up every so often when adding movies. It occured perhaps once out of every 30 to 50 movies I would add with no explanation as to why - the poster for the movie would appear on the newly added list of movies and it would just non-stop spin showing it was reading in metadata or analyzing the file and the server would just becomes unresponsive after a minute or so and I have to kill the task in order to do anything.
Upon restarting the server, the new movie would still be in the newly added list, but I would have to tell Plex to update the metadata and all appeared ok. To try and resolve this random issue, I decided to do a full database recovery.
I did the usual database repair process (have done it a few times before) and suddenly I’m getting a weird result where SQLITE is saying it can’t read in the exported data file that was just created by the recover command literally 30 seconds ago. The file shows on disk and has data (not a 0 byte file) and there are no permission issues to it and nothing blocking (I can move the file around in file explorer no issues).
I tried deleting the dump file and restored the db file and did another recover and again to create the dump file and it is still not able to be read in when I re-initiate the db and try to read in the data. Can anyone provide any suggestions or ideas?
See the below screen shot for my third attempt at doing it and the error that results:
I tried specifying the full path to the dump file and still the same result.
SQLITE actually removes any backslashes from the file\path provided for the dump file. So it truncates the path to an invalid path. Looks like it will only load from the current directory. Unless you change the working directory in the SQLite session itself I suppose.
It looks like sqlite is treating it as an escape character. Does changing to forward-slashes (or escaping the backslashes, C:\\Users\\Administrator\\AppData\\Local\\Plex Media Server\\Plug-in Support\\Databases\\db-recover.sqlite) work?
Not sure what’s going then. I don’t have sqlite in front of me right now to do any experimenting/troubleshooting myself, so my only other advice would be to run the script mentioned above, which does the same steps (and then some) automatically, and might offer a smoother experience.
There is no need to specifiy path names if you use the SQLite executable which is included with Plex server. It will automatically go to the Plex data folder.
Side note: activating the “Administrator” Windows account is bad practice.
Running Plex server under an Admin account is bad practice.
Any ideas on why SQLite is not reading in the file I have created using the .recover command?? Or why it is reporting that it can’t open the existing file?
As the original screen shot shows no path was being specified, yet it claims it can not open the file that it clearly created moments before and is sitting there in the database folder, so it does exist.
Side note comment: In a corporate or data center environment this is true as it opens another vector of attack since the attacker would then be aware of the login name used on the system. However, on a private home systems/network, 99% of Windows home based OS’s automatically display the login name on the startup/login/unlock screen unless manually set to turn off this feature, so indicating this to any possible attacker makes no difference as using a different login name would still be shown and would have administrator level access to the system which is set by Windows during Windows install/setup of the new account. They still have to break through password checking. And a 20+ character alphanumeric and high ascii character password would take quite a while to brute force. For me personally, using the administrator account often avoids issues with access to system resources and files when doing work on my own systems and network.
This is not about password security, but about the risk of running an ordinary app (Plex) with administrator privileges.
Plex will function just fine if run in a restricted user account. Which reduces the risk of deeper damage in the case if an assumed flaw in Plex is exploited. (either from the outside/internet or via a corrupted media file)
As for the sqlite errors: try chuck’s script.
Make sure beforehand that there is plenty of free space on drive C:
And of course Plex server must be closed while performing the repair.
And don’t let some anti virus interfere.
Also, check the file size of the exported data. If it’s just a few KB, then the export has failed.
Plex may run without administrative rights, but a lot of the work I do on my home network requires admin rights. It’s just easier and less of a hassle to always be enabling admin rights via UAC to do specific things. I would tend to think many “power users” that run a Plex server disable UAC and have full local admin rights set on their own named account. But agreed that most basic users would not (and should not) use the administrator account or have full local admin rights on their own account if they are not aware of the risks.
360 GB free on the SSD C: drive.
Plex is not running, otherwise I would not be able to rename the original database file. (Except for the Plex Update service which doesn’t exit when I close Plex)
Anti-virus logs do not show the file being scanned or anything that would stop me or SQLite from accessing the file. If it was being “scanned” as a new file to the system, the anti-virus scan would also stop me from moving the dump file, yet I am able to move it from one folder to another and back so there are no file locks on it.
The data file is approximately 110% the size of my db file (ie: double the size of the original database plus another 10% - around 170MB).
I haven’t tried the script from github as I am not familiar with it and I’m always cautious of running anything I don’t know since you don’t know exactly what it is doing behind the scenes. Has that utility been vetted by Plex admins to ensure it is not doing anything questionable?
The batch file appears to have worked, however, I also noticed that the batch file uses different commands within Plex SQLite to complete these tasks when compared with the instructions on the Plex site for repairing the database.
It performs a .dump rather than a .recover of the raw data. And then redirects the data into the database file on the command line rather than using the .read command. If I had known the data can be redirected into the db file in this manner, I would have tried that previously when the .read command didn’t work.
All is working now though. So more knowledge gained for the next time I have an issue.
It is using the commands which appear further down on the support page, under “Low-Level Database Recovery”.
And it’s doing this not only with the main DB file, but also the additional blobs DB file.
Ah, I see it now. It uses .dump which is what was published in the instructions for older server versions. https://support.plex.tv/articles/201100678-repair-a-corrupt-database-1-22-0/
This will not backup the database in binary form. Instead, it will export it as a succession of SQL commands. The result is that the exported database can be much larger than the binary DB file. (which might be the actual reason why it isn’t recommended anymore)
You can look at the exported file with a text editor, if you’re interested. (although there are not that many which won’t crash when reading such a massive file)
