Emby multi dupe version

[tobox 1]

Hi there,

Don't know how to remove dupe versions , there isn't exsit dupe videos in source folder. I try to  remove the media folder and rejoin and rescan , the matter still exsists. I guess the only way to do is to deal with library.db directly.

But i can't delete extra records in MediaItems table, how to solve this ,thanks!

 

 

 

[Abobader 3464]

Hello tobox,

** This is an auto reply **

Please wait for someone from staff support or our members to reply to you.

It's recommended to provide more info, as it explain in this thread:

Thank you.

Emby Team

[Luke 42079]

Hi there, how are your files named and organized?

[tobox 1]

4 hours ago, Luke said:

Hi there, how are your files named and organized?

Yes, either with tmdbid in folder or accurate nfo with video.

[tobox 1]

2 minutes ago, tobox said:

Yes, either with tmdbid in folder or accurate nfo with video.

[Happy2Play 9780]

Still not enough info, but since you are not using proper naming schemes for automatic grouping can assume you are using the plugin to group items, correct?

Look to be specific to specific versions.  Have you tried removing them from the library, full scann, readding?

At the same time other plugins can cause this also.  

Can you post a server log.

 

[tobox 1]

11 hours ago, Happy2Play said:

Still not enough info, but since you are not using proper naming schemes for automatic grouping can assume you are using the plugin to group items, correct?

Look to be specific to specific versions.  Have you tried removing them from the library, full scann, readding?

At the same time other plugins can cause this also.  

Can you post a server log.

 

Yes, i only use this script to group items: 

 

tried removing them from the library, full scann, readding?  >>  Yes, i tried many times but can't work , i see from library.db and the record has been written into MediaItems table and  many related tables.

 

Now it doesn't show any server log , and the new stuffs are working fine . Just want to remove old dupe versions(since it takes very long to rescan the whole media library. I can provide library.db if you can help to remove, thanks.

 

 

[tobox 1]

https://drive.google.com/file/d/1Yh--66qxU1XFFNpR1UriKmzPtjh6BUgM/view?usp=share_link

See if you staffs can find some solutions, thanks for help.

[tobox 1]

--can't done this because i  don't know how other tables are related, guess delete this can remove the dupe.

delete from MediaItems where ParentId='1207912';
delete from MediaItems where ParentId='1207911';

-and i guess also need to remove records in other related tables.
delete from MediaStreams2 where itemid in (select id from MediaItems where ParentId='1207912');
delete from MediaStreams2 where itemid in (select id from MediaItems where ParentId='1207911');

 

Not know too much , those are my thoughts to directly remove dupe.
 

[Happy2Play 9780]

4 hours ago, tobox said:

tried removing them from the library, full scann, readding?  >>  Yes, i tried many times but can't work

This should have cleared the entries from the library.  Did you check the database when you removed the items and did a scan of all libraries?

But yes, it is a pain clearing an item manually from the database as you have to remove the itemid data from each table it exists in to delete the mediaitem.

Don't know sql but took itemid 1215858 which is one of the "/mnt/emby/Emby_Home_CMCT/cmct_2k/007：幽灵党 (2015)/[007：幽灵党].Spectre.2015.BluRay.720p.x264.AC3-CMCT.mkv" versions and removed it.

DELETE FROM AncestorIds2 WHERE ItemId = 1215858;
DELETE FROM Chapters3 WHERE ItemId = 1215858;
DELETE FROM ItemLinks WHERE ItemId = 1215858;
DELETE FROM ItemPeople WHERE ItemId = 1215858;
DELETE FROM MediaStreams2 WHERE ItemId = 1215858;
DELETE FROM MediaItems WHERE Id = 1215858;

Would need to do this for each duplicate, just pick one of the itemids. 

Edited February 27, 2023 by Happy2Play

[tobox 1]

1 hour ago, Happy2Play said:

This should have cleared the entries from the library.  Did you check the database when you removed the items and did a scan of all libraries?

But yes, it is a pain clearing an item manually from the database as you have to remove the itemid data from each table it exists in to delete the mediaitem.

Don't know sql but took itemid 1215858 which is one of the "/mnt/emby/Emby_Home_CMCT/cmct_2k/007：幽灵党 (2015)/[007：幽灵党].Spectre.2015.BluRay.720p.x264.AC3-CMCT.mkv" versions and removed it.

DELETE FROM AncestorIds2 WHERE ItemId = 1215858;
DELETE FROM Chapters3 WHERE ItemId = 1215858;
DELETE FROM ItemLinks WHERE ItemId = 1215858;
DELETE FROM ItemPeople WHERE ItemId = 1215858;
DELETE FROM ItemLinks WHERE ItemId = 1215858;
DELETE FROM MediaStreams2 WHERE ItemId = 1215858;
DELETE FROM MediaItems WHERE Id = 1215858;

Would need to do this for each duplicate, just pick one of the itemids. 

Did you check the database when you removed the items and did a scan of all libraries? ==> Yes, when i remove the items and scan again, these same video still show dulicate version. I guess the only way is to totally remove all the data and reinstall emby and rescan all of libraries. But it wll take weeks to do since i have  a huge library.

 

 

any sqls to batch remove the duplicate for once if possible, that would save my time lol

Edited February 27, 2023 by tobox

[Happy2Play 9780]

23 minutes ago, tobox said:

Did you check the database when you removed the items and did a scan of all libraries? ==> Yes, when i remove the items and scan again, these same video still show dulicate version. I guess the only way is to totally remove all the data and reinstall emby and rescan all of libraries. But it wll take weeks to do since i have  a huge library.

That does not make sense if you removed the item they can't still appear.  Can you post a server log when you did this as it will show the items being removed from the database.

 

26 minutes ago, tobox said:

any sqls to batch remove the duplicate for once if possible, that would save my time lol

Might be but I don't know querying to show items with the same PATH.  As all multi-versioning is considered duplicates.

[tobox 1]

19 minutes ago, Happy2Play said:

That does not make sense if you removed the item they can't still appear.  Can you post a server log when you did this as it will show the items being removed from the database.

 

Might be but I don't know querying to show items with the same PATH.  As all multi-versioning is considered duplicates.

That does not make sense if you removed the item they can't still appear ==》 Yes if i remove the item, it disappear. When i remove the video outside the folder and rescan, this item sure disapper. But if i join the video again and rescan, it still shows duplicate versions., the following shows the server log:

See it scans twice for a same video.

 

Might be but I don't know querying to show items with the same PATH.  As all multi-versioning is considered duplicates. ==> Yeaah at least i know how to remove item from database, i will try to write batch sqls to deal.

[Happy2Play 9780]

26 minutes ago, tobox said:

See it scans twice for a same video.

@Lukewould have to comment on that but will want to see the full server log with specific examples.  But will guess both entries were not removed from the database.

 

But looking at your database you have 24460 movies that are duplicated.  So roughly a quarter of your movies have duplicates.

Slight change to @cayarsquery group all same path media.

Execution finished without errors.
Result: 24460 rows returned in 4813ms
At line 1:
Select ID, Name, Path, ProductionYear from MediaItems where type=5 group by Path || ProductionYear having count(Path || ProductionYear) > 1;

I guess you could the take the IDs in that list to create a script to delete them all.

But with that many records I personally would rebuild the database.  But remember all your userdata is in that same database.  If you use the server configuration backup plugin then is is a easy userdata only restore.

Edited February 27, 2023 by Happy2Play

[Happy2Play 9780]

But can say the one I found in my library all I did was remove the media, RTM removed one and  a full scan to remove the other.

Spoiler

2023-02-27 07:26:54.342 Debug LibraryMonitor: New file refresher created for Z:\ServerFolders\Videos\Movies\Resident Evil Collection\Resident Evil, Welcome to Raccoon City (2021)
2023-02-27 07:27:54.341 Debug LibraryMonitor: Timer stopped.

2023-02-27 07:27:54.416 Info App: Removing item from database, Type: Movie, Name: Resident Evil: Welcome to Raccoon City, Path: Z:\ServerFolders\Videos\Movies\Resident Evil Collection\Resident Evil, Welcome to Raccoon City (2021)\Resident Evil, Welcome to Raccoon City (2021).mp4, Id: 446847

 

2023-02-27 07:30:46.055 Debug TaskManager: Executing Scan media library

2023-02-27 07:30:49.034 Info App: Removing item from database, Type: Movie, Name: Resident Evil: Welcome to Raccoon City, Path: Z:\ServerFolders\Videos\Movies\Resident Evil Collection\Resident Evil, Welcome to Raccoon City (2021)\Resident Evil, Welcome to Raccoon City (2021).mp4, Id: 446848

Then readding MediaProbeManager only runs once against the media.

Spoiler

2023-02-27 07:35:56.648 Info MediaProbeManager: ProcessRun 'ffprobe' Execute: Y:\Emby-Server\system\ffprobe.exe -i file:"Z:\ServerFolders\Videos\Movies\Resident Evil Collection\Resident Evil, Welcome to Raccoon City (2021)\Resident Evil, Welcome to Raccoon City (2021)-trailer.mp4" -threads 0 -v info -print_format json -show_streams -show_chapters -show_format -show_data

 

[Luke 42079]

Is anything actually duplicated in the database?

[Happy2Play 9780]

21 minutes ago, Luke said:

Is anything actually duplicated in the database?

Yes lets look at the first item in the query as this database has 24,460 dups.

[Luke 42079]

That's very odd. if you remove the entire parent folder from the library and do a full scan, do they both go away, or just one of them?

[Happy2Play 9780]

2 minutes ago, Luke said:

That's very odd. if you remove the entire parent folder from the library and do a full scan, do they both go away, or just one of them?

Appearently no but have not seen a server log.

6 hours ago, tobox said:

Did you check the database when you removed the items and did a scan of all libraries? ==> Yes, when i remove the items and scan again, these same video still show dulicate version. I guess the only way is to totally remove all the data and reinstall emby and rescan all of libraries. But it wll take weeks to do since i have  a huge library.

 

 

any sqls to batch remove the duplicate for once if possible, that would save my time lol

 

[Carlo 4561]

4 hours ago, Happy2Play said:

Yes lets look at the first item in the query as this database has 24,460 dups.

By any chance have the same directories been added to multiple libraries?

[Happy2Play 9780]

39 minutes ago, cayars said:

By any chance have the same directories been added to multiple libraries?

Don't know how to check that via the database but would appear so they are different ParentIds so 1149234 and 1149232 are a mirror of each other 4176 items.  But there other ParentIds.

 I know the one I found in my db has the same ParentId.

[Happy2Play 9780]

@toboxSince the plugin made the problem visible, do you have the same folder added to multiple libraries?  But removing the plugin should show you have items assigned in multiple libraries.

[tobox 1]

15 hours ago, Happy2Play said:

@toboxSince the plugin made the problem visible, do you have the same folder added to multiple libraries?  But removing the plugin should show you have items assigned in multiple libraries.

Nope i check carefully none same folder was added to multiple libraries. I set up a whole new server andd scan again, everything works fine. So i guess this prolem was something related to auto-group script. 

 

For those dupe in database,i would try to use your sqls to write a script to remove. That would save time to fully rescan. 

BTW, only some folders are dupe, many others work fine.

It's this library , some of folders scan dupe movies for same video, for thoes dupe folders, you remove them , scan ,rejoin them again ,scan , still dupe.

[tobox 1]

DELETE FROM AncestorIds2 WHERE ItemId in (Select ID from MediaItems where type=5 group by Path || ProductionYear having count(Path || ProductionYear) > 1)
DELETE FROM Chapters3 WHERE ItemId in (Select ID from MediaItems where type=5 group by Path || ProductionYear having count(Path || ProductionYear) > 1)
DELETE FROM ItemLinks WHERE ItemId in (Select ID from MediaItems where type=5 group by Path || ProductionYear having count(Path || ProductionYear) > 1)
DELETE FROM ItemPeople WHERE ItemId in (Select ID from MediaItems where type=5 group by Path || ProductionYear having count(Path || ProductionYear) > 1)
DELETE FROM MediaStreams2 WHERE ItemId in (Select ID from MediaItems where type=5 group by Path || ProductionYear having count(Path || ProductionYear) > 1)
DELETE FROM MediaItems WHERE Id in (Select ID from MediaItems where type=5 group by Path || ProductionYear having count(Path || ProductionYear) > 1)

 

the last sql would excute very very long and still can't end. The first 5 can be very fast within a secong.

[Luke 42079]

Did that help?