Thanks Chuck,
My database is now 267MB. Though I had a go about a different way to get it repaired.
The SQL Query to look for multiple-IVA record problem:
select count(metadata_item_id),metadata_item_id from metadata_relations group by metadata_item_id order by count(metadata_item_id) desc limit 20;
114695|137623
98307|11999
57351|111941
63|112456
58|139966
58|139977
54|75439
51|139849
50|75368
50|75378
50|75379
50|77494
50|115766
50|140084
50|954968
50|955032
50|957052
50|957440
50|958860
50|959195
So I had 3 bad entries. I queried each item directly and confirmed that they had been added multiple times.
I saved the SQL script that you posted and run it and I had no change in the database size and it was finished in only few seconds. I created a SQL script that I could specify the specific ID that was provided here:
But even that did not work. I found the query to identify which where the problem titles (would have been better to actually get the file name):
sqlite> select id, title, guid, refreshed_at from metadata_items Where id = '137623';
137623|Logan|plex://movie/5d776abffb0d55001f54faa3|2021-10-06 00:12:22
sqlite> select id, title, guid, refreshed_at from metadata_items Where id = '11999';
11999|Alien - Directors Cut|plex://movie/5d7768254de0ee001fcc83a5|2021-11-29 06:35:16
sqlite> select id, title, guid, refreshed_at from metadata_items Where id = '111941';
111941|Logan|plex://movie/5d776abffb0d55001f54faa3|2021-09-26 23:33:46
Interesting that the files that had issues where files that Plex merged into a single entry that I had to split apart. Alien - Directors Cut and Alien - Theatrical Cut where merged into one item along with Logan and Logan Noir as well. I have three different sized versions for these movies, one Native 4K HDR for local viewer and streaming versions 2K HDR and 720p (from 1080p source).
I shut down PMS, and pulled all the versions of Alien Theatrical, Alien Director’s Cut, Logan, and Logan Noir out of their respective libraries. I started PMS back up. Then kicked off a scan of each library and waited for it to complete … until it hit the entry that was the problem, then the Activity spinner on the Web UI just kept spinning. Watching the log though, was more encouraging as I saw constant stream of Deleting entries occurring. Tailing the log and grepping just for Deleting I saw about 3-10 entries per second being deleted. This ran for several hours.
When it stopped, I checked to see of those three bad entries were gone. The database was still about the same size. I ran that SQL script against the database and it shrunk all the way down to 267MB from the 600-2000MB it had grown to.
So I think I’m good now. Not sure if the duplicate merged entries caused this problem. I’ve been installing the PMS Beta versions as they have been released. Though it would be nice if there was a Stable channel instead of just Public.
