Negative unwatched counts

Plex Media Server Desktops & Laptops
1

In my TV collection I have 8 TV shows that have a negative unwatched count.

If I mark those shows as unwatched I get an unwatched count that doesn’t match the total number of episodes. If I mark them as watched, I get a negative watch count.

Somehow, plex is mis-counting the episode watched status.

This is only happening on one user (the primary user).

Is there some way of completely resetting the watched status for a show? (deleting the rows from the DB for instance?)

I’m running Windows PMS Version 1.7.2.3878

Thanks

2

@doh said:
Is there some way of completely resetting the watched status for a show? (deleting the rows from the DB for instance?)

Performing the Plex Dance should take care of that.

But before you do it, you should verify that your Plex database is in good health:

  1. activate debug logging (not ‘verbose’!)
  2. quit Plex Server
  3. wait 1 minute
  4. start Plex Server
  5. wait 3 minutes
  6. fetch log files

Inspect the Plex Media Server.log file and seek for lines containing corrupt or malformed

3

Hi
I’ve checked the logs and there are no corrupt or malformed errors.
I’ve performed the Plex Dance for the affected shows and it’s still not fixed it.
I have shows (such as The 100) where I have done the dance, re-added the shows, and when I mark the entire series as read, it still shows up in Unread shows.
Taking another show (Pretty Little Liars), Season 4 has 24 episodes. If I mark them as unwatched, PMS shows an unwatched count of 0. If I mark them as watched it shows the count as -24. I can perform the dance on this season, and when I add them back in the situation remains the same.
Other than totally re-creating my PMS DB, any ideas?
Thanks

4

I got word from @“MovieFan.Plex” that this particular error may occur after an import of ‘watched’ status into a non-empty database.
The import causes duplicate datasets for all those items which which were already in the database when the import was performed.
The remedy is manual de-duplication.
Please PM @“MovieFan.Plex” and send him your Plex DB file.

5

I got word from @“MovieFan.Plex” that this particular error may occur after an import of ‘watched’ status into a non-empty database.
The import causes duplicate datasets for all those items which which were already in the database when the import was performed.
The remedy is manual de-duplication.
Please PM @“MovieFan.Plex” and send him your Plex DB file.

If you know what SQL is and you want to try yourself, it is the table metadata_item_settings

6

Thanks - that helps a lot I think.
From looking at that table, would I be correct in presuming that I need to look for duplicates where there are multiple entries of the same guid for that particular user account_id?

7

I assume yes, but I don’t know for sure.
@“MovieFan.Plex” would know

8

@doh said:
Thanks - that helps a lot I think.
From looking at that table, would I be correct in presuming that I need to look for duplicates where there are multiple entries of the same guid for that particular user account_id?

That is correct. Personally, I would just delete the earlier entries. Are you familiar with SQL to do this? This can be accomplished with 1 sql statement.

9

That is correct. Personally, I would just delete the earlier entries. Are you familiar with SQL to do this? This can be accomplished with 1 sql statement.

I am “ok” with SQL. If you can give me an example of the SQL statement I’m sure I can get it sorted. Thanks

10

I have recently run into this issue, and am not that savy with SQL. I have managed to create a query to find duplicates in the guid column of the database, but am not comfortable enough to create a delete statement. Would you be able to provide this statement you’re referring to to safely delete the duplicates? I would greatly appreciate the assistance! Thanks!

11

So I worked with someone to create the proper query to resolve my issues. My duplicates were caused by trying to migrate the database while also using the trakt.tv plugin, I believe, because all of the duplicates were specific to my account number of “1” where all of the other accounts sharing my server didn’t have the duplicates since they’re not linked to trakt.tv. I will say this is a use at your own risk situation, but for those as inexperienced at SQL as I am, this could help fix your problem.

To find and show the duplicates, I used this query in my DB browser. Make sure Plex is shut down before you start poking around in the library database.

SELECT *
FROM metadata_item_settings
WHERE id in
(SELECT MIN(id)
FROM metadata_item_settings
WHERE account_id = 1
GROUP BY guid
HAVING COUNT(guid) > 1)

After reviewing that output, and comparing it to the data in that table, it appeared to match on one of the duplicates. The next step was to delete them. Make sure you back up the database before you do this next step of course, and again, this is at your own risk if this query will work for your situation.

DELETE
FROM metadata_item_settings
WHERE id in
(SELECT MIN(id)
FROM metadata_item_settings
WHERE account_id = 1
GROUP BY guid
HAVING COUNT(guid) > 1)

Once I ran that, saved the database, and started Plex back up, all my negative unwatched counts were fixed!