I think I mentioned this above somewhere, but when I experienced the issue a few months ago, in addition to removing the problematic entries from the database, I also performed a few other housekeeping chores.
I signed out all of my clients and signed in again the next time I used them.
I removed all “stale” entries from my authorized devices list.
I uninstalled all clients I wasn’t actively using. I’d been playing around with Plex for Mac, Plex Media Player, and Plex Amp but didn’t really use this, so they were uninstalled.
Correlation is not causation, but I’ve not had the issue recur since taking the steps above; it was
one-and-done for me. It may be worth your time to take an inventory of all the clients you’ve got installed and remove any you don’t regularly use. And prune your authorized devices of any older clients.
Ultimately I don’t think the server should be writing to statistics_media with a null account_id; that feels like an undocumented feature. I’d assumed that perhaps that was what the aforementioned fix was meant to prevent. But I guess there are cases where clients where are not signed in can play media, so maybe not.
[Edit]
I just noticed your other topic where you mention that you don’t sign in your local clients. So, my suggestions above may not be relevant to your case. Though it might explain why you’re seeing so many entries in statistics_media with no account_id.
Yes, exactly. I was actually planning to sign in my clients but figured I’d help test the fix of this and leave everything as it was, just to see if it fixes the issue when some clients aren’t signed in.
Android TV and IOS iPad clients, both not signed in, are the device_id’s cited in the entries with no account_id. I suspect if I signed them in, this would go away. Again I’m just keeping things this way to help debug - a little maintenance every few weeks while waiting for the fix is fine.
Also - I started leaving some clients signed out because they would lose their mind when my internet went out, which happens occasionally for an hour or more. Signed out clients still work when the internet is out - signed in clients don’t (and they hang when I try to sign them out AFTER the internet is gone…). I know, I’m probably testing a pretty edge case.
Just an update. This bug is still not fixed as of version 1.20.3.3437. Same thing as always, a bunch of null account entries in the statistics_media table.
Database size: 667,234,304 bytes (1.5 months since last cleaning). After ‘cleaning’: 120,583,168
I’ll keep upgrading and trying this out. I’m not signing in my iPad in purpose! But I have a feeling this bug isn’t really getting fixed? Because I was told it would have been a while back.
I wish Plex team had the transparency to have an outward facing issue system where we could log an issue like this and get updates on it. I assume it has been logged in their internal issues database, but again, I don’t think it’s getting fixed. I have low expectations that after my update to the latest version today it will be fixed. BUT - I’ll keep trying each new version.
At some point I’ll give up, sign all my clients back in, and clean up my database like suggested by @phillipswhere.
But not yet! I’m still too curious to see if this will actually get fixed… anyway, upgrading now to 1.21.0.3711!
And just in case any of you wonder. This little bug isn’t enough to make me stop loving Plex
Jan 11, 2021 -The bug still exists as of version 1.21.0.3711. My database file today was 569,151,488 bytes. I removed all the “account_id=NULL” rows from statistics_media and now it is 122,012,672 bytes.
Upgrading to Version 1.21.1.3876 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. The Sony TV has started to become flaky so I stopped using its “smart” features and apps and bought a Roku Ultra for my streaming. The Rokus just plain don’t work right if not signed into the plex account (another thing that I’d love to be fixed some day), so that’s signed in.
So my only “device” that shows up in the NULL entries now is the iPad. Still with just the one device there were PLENTY of these entries… over 400 MB of them.
I just removed a ton of these account_id=NULL entries from the statistics_media table. Not sure what is causing them. That table was by far the largest table in the database before, now it is somewhere in the middle.
For anyone that needs to remove these here are the steps:
Stop Plex
Open a command window and navigate to the Databases directory, in windows it is: C:\Users\your_name\AppData\Local\Plex Media Server\Plug-in Support\Databases
Create a backup copy of your com.plexapp.plugins.library.db file before doing anything. copy com.plexapp.plugins.library.db com.plexapp.plugins.library.db.backup
Open the database in sqlite3 sqlite3.exe com.plexapp.plugins.library.db
If you want to see how many rows have a NULL account id run the following command select count(*) from statistics_media where account_id is NULL;
Run the following command to delete the rows with NULL account_id delete from statistics_media where account_id is NULL;
Start Plex
In Plex go to Settings / Troubleshooting and click Optimize Database
I have about 50,000 photos on my Plex Server, and about 300 movies/TV episodes. No music as yet, but do plan to add 15,000 to 20,000 songs to my server sometime this year.
All my entries in “statistics_media” are correlated to devices that aren’t signed in to plex. Those devices aren’t used to look at pictures…
I’m guessing the “bigness” of my metadata_items is just due to having all those photos in my library.
I was wrong about the number of videos I have… 130 movies, about 780 TV episodes, and 600 “other” videos (family videos and the like). All in all about 1500 videos.
The issue with the Statistics_Media table and large number of Account_ID null entries has been identified and relates to use of the Guest account when playing back. A fix has been written and will get released shortly - it also has a DB update to remove the entries in the table with Account_ID of null
Hey that’s great! I’ll watch the update release notes, and will run the test with my logged out iPad for a month or so after seeing it come across. I’ll post any additional issues here, but thanks for the good news!
I’m wondering if the fix for this was released? I did an update today and my plex started crashing doing its DB optimize this morning. After doing a bunch of on research I stumbled on this thread and found this to be some part of the issue, either the update or just the size of the DB overall was the problem.
I’m currently running Version 1.22.0.4163 and previous version was over a month old (didn’t check before upgrading).
As a side note my DB was ~3.1gb before I removed the Null entries and now is 264mb, had 30 million entries. This issue has been going on for a while it appears as my backup from a year ago was still 1.3gb.
The only reason I’m posting about this is if the fix was released people might need to manually remove the entries before upgrading, or the deletion process might need to be handled differently as it was crashing during the optimize, and now runs fine after deleting the records manually.
It has only been 10 days since @sa2000 replied to this thread saying the issue has been identified, and a fix has been written.
The latest release from 3 days ago (Version 1.22.0.4163) however, doesn’t mention this in the release notes, so I can’t say if the fix is in this release or not. It is possible the fix was part of some other issue so wouldn’t be recognizable in the release notes.
It would be nice if we knew the issue # to look for in the release notes…
Oh, one other note. @sa2000 mentioned in his post (just read back a few posts) that the fix includes a DB update to delete the null entries.
I assume that this means that upgrading would automatically clean out the null entries. If that didn’t happen for you, then this release may not have the fix yet - assuming you upgraded to 1.22.0.4163 BEFORE you deleted the null entries. If you deleted them before upgrading, then there’s be no proof it didn’t have the fix.
@danielg1976 Yeah, it was just odd. I had issues with the Null entries and DB optimization -after- upgrading.
So to be clear
DB was fine prior to 1.22.0.4163 (unsure which version I was running prior to this didn’t check but it was a 2021 version) with null entries in it.
After upgrading to that version DB optimize crashed the plex media server.
I backed up the DB and removed Null records.
DB optimized fine after that with no more crashes.
I would be surprised as well if the fix was implemented that quickly as well, but I thought it worth while to mention it as it seemed it could be related.
I also wouldn’t be surprised if maybe the fix IS in there, but the automatic removal of null entries didn’t work and crashed your DB optimize. So maybe the fix has a bug Never can tell.
Never can tell.