I’ve looked at some of the other topics, including some FAQs on trying to repair the database, but I’m still not sure what the best way forward is.
My current db is 174 GB, which seems massive because I probably have under 20k shows/movies combined. I’m pretty sure something is wrong because my routine db backups stopped completing a while ago (probably talking months) and just recently I restarted Plex and it couldn’t start back up, while spamming “database is locked” messages.
I can’t guarantee my older backups are intact so I’d like to start fresh, but still somehow retain my watched history. Is it possible to pick out the data I want using sqlite commands?
Alternatively, my integrity check command (see https://support.plex.tv/articles/201100678-repair-a-corrupt-database/) has been running close to 24 hours and there seems to be no signs of it completing. Should I forgo that and just try to run the repair commands instead? Is there really any hopes of recovering this?
Yeah I stopped Plex before running the check, and I do have backup database every 3 days turned on. But what are the chances that it’s been corrupted for a while? Or can I use the last .db file that doesn’t have a -tmp associated with it?
Okay, I will give that a shot. One more question, do you know if it’s worth trying to run the export on the latest db? Or is it likely that the watched history is messed up? I remember it being fairly accurate last time I was able to access the db
Try running the following SQL statement on your database, with PMS stopped: SELECT * FROM statistics_media WHERE account_id IS NULL;
This will return any rows in your DB’s statistics_media table where the account_id is blank.
A few months back several folks reported issues with their databases growing in what seemed to be an exponential manner with relatively little activity. In several of those cases it turned out that invalid entries were being written to the table above, with no account_id.
It was possible to recover those databases by deleting these invalid entries. If you decide to do so, please make a backup of your DB first, just in case.
Thanks, I’ll try that out. Luckily I already backed up my DB to run the integrity check
Edit: So it’s definitely what you described. I ran the select * and it ran for a while before I stopped it (went into the 3.1m for what I assume is the id). I’m running a count * on my backup while I delete from my normal to see if I can restore functionality in my db. Is there a specific version of Plex that had this issue? I did upgrade to the latest version before my DB wouldn’t start.
Edit 2: Did a count * on my backup and I got 1,810,704,192 rows
Edit 3: Tried to do a couple of different methods for deleting data, but trying to run VACUUM was with “database or disk is full” messages. I decided to just nuke everything and start from scratch, but exporting my watch history data first. I read that this bug has been fixed as of 20.2 so hopefully I don’t run into this issue again. Thanks everyone for your help!
