Once the fix is released, the DB cleanup will be done during server maintenance. So you won’t see improvement immediately, but only on the next day.
P.S: it looks like the fix will be included in version 1.22.1.xxxx
1 Like
Here’s my latest checkin on this issue.
Mar 14, 2021 -The bug still exists as of version 1.21.1.3876. It’s been about 2 months since my last cleaning. My database file today was 844,116,992 bytes. I removed all the “account_id=NULL” rows from statistics_media and now it is 121,862,144 bytes.
Upgrading to Version 1.22.0.4163 of server on Linux. I’ll check back in a month or so!
I used to have two clients not signed in, a Sony Bravia Android TV, and an iPad client. Not using the TV’s Plex app anymore. So my only “device” that shows up in the NULL entries now is the iPad.
I’ve been dealing with this issue as well after becoming aware of it only when I was trying to migrate servers. Looks like I had 431 million records in my STATISTICS_MEDIA table where account_id IS NULL, causing my com.plexapp.plugins.library.db to grow to 42GB in size.
SELECT COUNT(*) FROM STATISTICS_MEDIA WHERE account_id IS NULL
431308995
I tried deleting the rows where account_id was null from the database but it was taking forever so I did the following:
CREATE TABLE STATISTICS_MEDIA_backup AS SELECT * FROM STATISTICS_MEDIA where account_id IS NOT NULL;
PRAGMA journal_mode = OFF;
DROP TABLE STATISTICS_MEDIA;
PRAGMA journal_mode = wal;
CREATE TABLE STATISTICS_MEDIA AS SELECT * FROM STATISTICS_MEDIA_backup;
I then did the standard export and import (i.e. Repair a Corrupt Database | Plex Support ) and my database is now gone from 42G to 372M.
This also means that the scheduled database optimization will actually finish instead of crashing.
Thanks for all the investigation in this thread that helped me clear up the 430 million records that were causing issue.
2 Likes
Looks like the fix may be in 1.22.1.4228.
From the release notes:
- (Statistics) Don’t store duplicate records in the statistics tables
Not exactly specific enough to know for sure it’s this issue, but sounds promising.
I don’t have time to test this weekend but will do it in the next couple weeks and post results.
Yes that was the fix
does the Fix auto-remove the 100s of Millions of unneeded records?
Yes, but only during the next server maintenance - i.e. not immediately.
Not sure if this is related to my manual database manipulation or not, but my Plex system now doesn’t ever clear out the “Now Playing” clients, even after they have stopped for a long time. As I don’t see any other reports of this issue, so I don’t think it is a widespread issue, and suspect it is related to either my server move or my database manipulation. I’m posting here just in case anyone else dealing with the parent SQLite database issue ends up with similar behaviour.
When does this maintenance happen? I have about 311k of these records with account_id = NULL, all from 1-1-2019 to 12-28-2019 that are still in the database for some reason.
What is your server software version?
1.22.2.4282. I deleted them from the database since I was having to fix some other issues as well, but I have the database file if you need it.
Okay it took me way longer to get to upgrading and cleaning the database, the last few months have been crazy.
Anyway, I’m upgrading from version 1.22.0.4163 to version 1.23.1.4602 today.
My database was 1,428,580,352 bytes. I cleaned it out before the upgrade, where it became 127,542,272 bytes.
I’ll watch for a while and ensure it stays small in the future! I suspect it will as I think with this upgrade I’m getting the fix, which will be nice.
1 Like
Well, I promised I report back and it’s been a month.
The database is looking good! It is remaining a reasonable size, and in the last 2 weeks has only grown a few 100 KB. No entries in statistics_media with a NULL account_id.
My wife DID log in our iPad though because we went on a vacation and needed remote access, so I should log it out again just to make sure, but for the first 2 weeks of the month it was logged out, and I have no null entries, so I’m pretty sure it’s fixed up! This is all while still running 1.23.1.4602 on 64 bit CentOS 7.
2 Likes
Glad this bug has been squashed for good!
This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.
