If I update to any server version above 1..40.2 the upgrade runs apparently successfully but then fails to open on corrupt database - “Plex Media Server was unable to open its media database”.
Before updates I always backup and run an integrity check plus a “clean up” rebuild and these were clean each time I’ve tried upgrading.
Running the checks on the .db file which Plex cannot open give the same results - no problems flagged in the .db file, it just won’t open on the new binaries.
I’ve tried doing the full low level export/import to rebuild the library - exactly the same result. Any binaries up to 1.40.2 work fine, as soon as I go higher I get the “cannot open database” messages.
Other background info:
the original problem startedon my old Windows 10 media server where it had been running for many years. As the machine was dying and I had a W11 media server built and ready to run I created a fresh install of Plex and moved the libraries across, resetting ownerships etc where needed. I’ve also checked the registry entries. The whole installation works fine except I can’t upgrade it.
My database file is nearly 1Gb in size. Its grown gradually over the years.
I had a vaguely similar problem a couple of years back moving from 1.22 - that time this forum helped me identify the root issue (change in SQLlite which I had missed) but I cannot find anything similar in notes for the releases above 1.40.2 (and to be explicit, 1.40.5 does not work).
I’m really hoping I’ve missed something obvious or missed a configuration change required to get beyond 1.40.2 but haven’t found it yet. I’m very keen to avoid having to build a new library from scratch as I have many metadata edits/labels in the existing library and AIUI there is no way to preserve those manual edits and restore them to a new library.
TIA
Then you have done all you can to recover physical corruption and would need to drop back to the last backup generated by Plex. Some data would be better than starting over, but that is pretty much where you are at.
I used that low level method initially to rebuild the database (after the regular “Vacuum” and “Reindex” commands hadn’t solved the problem). I had exactly the same issue when trying to open after that upgrade past 1.40.2.
Do you think its a logical corruption which has occurred at some point since 1.40.2 and its just chance that it happened at that point? Presumably none of the physical rebuilds will solve this but what kind of activities would trigger a logical corruption (I’ve only ever updated anything using the plex interfaces)?
This has been going on for quite a while so the normal backups will have expired since this started (I have 30 days retention).
I do still have an old copy of the last backup I took before the change which significantly expanded the size of the DB. Its about a year old, presumably I’d need to identify and install the matching binaries and hope that the downgrade worked before reupgrading?
I suppose also I’m wondering what kind of logical corruption on a physically clean DB would be picked up only after this change? ie is it possible its a very old logical corruption which has simply never been triggered by a previous upgrade?
It would be great if we could save custom metadata in a separate file or tag it independently rather than in the main plex schema.
I didn’t read your whole post, but did you try PlexDBRepair?
A lot of the problems from an upgrade is usually people stopping the upgrade and then corrupting their database by restoring a backup improperly. At any point did you replace the database files and you didn’t delete the -wal and -shm files?
Even if you upgrade with each release it would be difficult to pin down when the actual problem originated.
Cut and paste is a likely culprit for introducing unprintable characters into a field.
If you have had your server for 5 years or more and switched agents from legacy to new and back coupled with customizations then there is a small chance it was introduced unintentionally.
Any backup of the database files needs to be done while Plex is not running, otherwise they will be corrupted. The backup files I am talking about are the one’s Plex creates every three days for:
com.plexapp.plugins.library.db
com.plexapp.plugins.library.blobs.db
You do not need to recover the old binaries, just replace the above files with a known Plex backup and start Plex, it will attempt to upgrade them.
I’ve always removed the -wal/-shm files and then run an integrity check and rebuild after restore. If an upgrade fails I restore the last clean copy before retrying.
I’m not familiar with PlexDBRepair, is it able to identify issues persisting after the low level rebuild? (.output db-recover.sqlite
.recover
.read db-recover.sqlite)?
The database file is sitting on a Windows 11 media server and as far as I can see the utility doesn’t support Windows. I do have MacOS and Linux options available but not sure how migratable the Plex DB format is. At this stage I’m willing to try outside bets
Thank you for taking the time for the detailed reply.
The server has been running for over ten years through many upgrades. Had the odd glitch in the past but nothing which couldn’t be fixed by restoring the last pre-upgrade back ups. My backups are mostly the the backups created by the Plex utility every three days with a few “extras” taken when Plex is shut down at key points such as pre upgrade, or after a particularly large data update.
I don’t think its a c&p issue. The metadata I’m worried about relates to photos, recordings and videos of the family type which I store on Plex with notes to make them available to wider family. My method is manual entry of the note into Plex when the media is catalogued because I wanted to be careful to follow the interface just in case of data corruption :D. I do also copy the notes into a spreadsheet kept on the NAS device with the media so I have a “logical” copy but its a huge amount of manual data entry to repeat. It is possible I’ve switched agents a few times over the years as I also have ripped CDs/DVDs stored on the system. All that said - in ten years I must have missed something at some point.
I’ll see if I can restore the old db next - as you say that will save some data and its better than nothing.
It would be good to have segregated metadata one day.
Just coming back as I have now restored and have everything up and running.
So it appears the reason I couldn’t not find a corruption in the .db file was due to the source of the issue being in the blobs.db.
I usually follow the restore method of deleting. -wal and -shm files then restoring the library and libraryblobs .db files.
I went back to the very old backup to re-upgrade and restore but had no blobs file so rebuilt from the library.db alone then upgraded. This worked all the way up to the latest upgrade but of course lost a lot of data.
Just for the hell of it I then restarted using the most recent backup but not its libraryblobs file. This also worked. I tried a couple of other backups as well and the problem is definitely in the libraryblobs file and not the main library. TBH I didn’t think the backup would backup a damaged file so presumably there was some logical corruption in the libraryblob file.
Anyways - this means I’ve lost very little data compared to the horror of going back over a year. I’ve always restored both files together but I guess it makes sense to be able to restore solely from the main library.db file.
Anyways I thought I’d drop this here in case someone has similar issues in the future and might be better off just restoring from the one file! .
I’m having the exact same problem, same server version, same error. I’ve also tried all the recommended steps, including the batch file repair. I’ve tried to update a few times and after banging my head into the desk, revert back to the last working server version.
If it’s not too much to ask, could you detail a bit more how you fixed the issue? I tried opening a fresh install, then copying in my most recent DB backup with and without the blob file, but I’m still getting the same error.
Can you install the latest version and provide the log after pms tries to start. This might identify the issue with the database. If it’s a logical error as the op mentioned the only fix would be to manually fix the error. If it’s a duplicate id, which is my guess, it can be fixed.
I’ve just reinstalled the latest server version and here’s the log. It does appear to be an error with some data as it’s failing when doing a select from metadata_item_settings. Seems to be missing a column? I did try looking into the logs before, but I think I was looking at the wrong file. This time I backed up all the older logs and moved them before installing.
I fixed it! Finding the specific log error was the key. I did two things, based on the errors in the log.
Added column user_clear_logo_url to the metadata_items table. Not sure if this was needed, but figured it couldn’t hurt.
This query was failing for a malformed JSON
error - SELECT id FROM metadata_item_settings WHERE extra_data → ‘pv:SyncedViewAt’ IS NOT NULL or extra_data → ‘pv:SyncedRatedAt’ IS NOT NULL. There were only about 10 records in this table where the extra_data field was not null and 5 of them weren’t json format. I updated those records to NULL and the server started without error and is now up and running.
