How do I Clean up the Database?

[Dibbes 514]

Over time my database got highly polluted with double entries due to moving machines, media locations, addition of .strm files for localised metadata and various plugins. How do I clean this up automatically, now that I cannot start over without removing play-states when restoring from backup?

Examples of multiple entries:

[rbjtech 5284]

Are the duplicates valid/working items ?

[Dibbes 514]

1 hour ago, rbjtech said:

Are the duplicates valid/working items ?

For movies, yes... these are the exact same files and when playing one, all are marked as watched.

Series is a little more complicated I just noticed now, I just found. When clicking on the duplicate series, I get the versions below. I can play each of them:

EDIT: Some of these are not even on the same physical drive, LUN, device or even the same library... curiouser and curiouser

Edited May 20, 2024 by Dibbes

[rbjtech 5284]

So first thing I would check is to 'split' the items - scroll down the details and you may see them joined.

Once 'split' then run a full scan plus metadata scan, then in database choose vacuum.  Then restart emby.

[Dibbes 514]

Yeah, that is something I thought of, but having to do that with 60k entries is not feasible...

Also, not every episode has the option:

Edited May 20, 2024 by Dibbes

[Neminem 1519]

@DibbesHow is you naming scheme ? and folder order ?

[Dibbes 514]

4 minutes ago, jaycedk said:

@DibbesHow is you naming scheme ? and folder order ?

Believe me, the naming scheme is in order. Folder -> Series Name (year) -> Season# -> Filename (See screenshot above for filename examples)

[Neminem 1519]

Ohh wow how did Emby FUBAR that.

I would try with series folder {Series TitleYear} [TvdbId={TvdbId}]

Something like this 

[Dibbes 514]

why the square brackets?

[Neminem 1519]

Not really sure, but it works for me  

And it was in the documentation

TV Naming | Emby Documentation

Example:

The Vampire Diaries (2009) [tmdbid=18165]

Emby supports the following tags with the host website for lookup.

[Dibbes 514]

That will wreak havoc with any PowerShell script I'm running against that folder structure... lol

Edited May 20, 2024 by Dibbes

[Neminem 1519]

Guess you could put " " around the path in powershell, like you do with spaces.

[Dibbes 514]

For just paths, that will work, but I use a lot of pattern matching in the scripts. Guess what PowerShell uses for that?

Anyway, getting off topic...

[rbjtech 5284]

' "include the quotes" ' - single quote will allow you to include the quotes in PS ..

Anyway yes back to the issue ...

2 hours ago, Dibbes said:

For movies, yes... these are the exact same files and when playing one, all are marked as watched.

Series is a little more complicated I just noticed now, I just found. When clicking on the duplicate series, I get the versions below. I can play each of them:

EDIT: Some of these are not even on the same physical drive, LUN, device or even the same library... curiouser and curiouser

Oh wow - so emby thinks these are all versions of Hawaii five-0 S10E21 ?

Sounds like the db is very corrupt ...

Does the backup/restore not let you restore the UserData table on a fresh library.db ?

I throught that's exactly what this did below - ie from a backup (made on your corrupt db), you can just restore the user playstates.

It's done by provider id - so it doesn't matter if it's a brand new db - the 'match' will still happen.  

Take a copy of all the db files (with emby shutdown) before even attempting this.

It's probably quick than trying to individually 'ungroup' all these items - plus who knows what other corruption exists down the line - fresh db is always the way to go imo.

 

Edited May 20, 2024 by rbjtech

[Dibbes 514]

2 hours ago, rbjtech said:

' "include the quotes" ' - single quote will allow you to include the quotes in PS ..

*grin* I think you pretty much know my scripts...

2 hours ago, rbjtech said:

Oh wow - so emby thinks these are all versions of Hawaii five-0 S10E21 ?

Sounds like the db is very corrupt ...

Yes... *sigh*

2 hours ago, rbjtech said:

Does the backup/restore not let you restore the UserData table on a fresh library.db ?

I've been reluctant to try this, tbh... Just setting up a new DB, scanning everything, etc., takes about 3 weeks... This purely due to the amount of MediaItems... I had hoped there was a quicker way

 

Ever since yesterday @pünktchen's plugin stopped populating the guide as well, out of the blue... 

[rbjtech 5284]

14 hours ago, Dibbes said:

I've been reluctant to try this, tbh... Just setting up a new DB, scanning everything, etc., takes about 3 weeks... This purely due to the amount of MediaItems... I had hoped there was a quicker way

You don't necessarily have to wipe the old and start fresh - just setup another instance of emby, running on a different port - but pointing to the same same libraries.   Let that scan the items in the background and when it's finished, then import the play state from the corrupt db - then move that over as the new instance.

If you unlink all the items in the current db, then its possible that would suffice, but I would be concerned about what else is corrupt - there is no way of knowing unfortunately.

[Dibbes 514]

On 5/21/2024 at 10:59 AM, rbjtech said:

You don't necessarily have to wipe the old and start fresh - just setup another instance of emby, running on a different port - but pointing to the same same libraries.   Let that scan the items in the background and when it's finished, then import the play state from the corrupt db - then move that over as the new instance.

If you unlink all the items in the current db, then its possible that would suffice, but I would be concerned about what else is corrupt - there is no way of knowing unfortunately.

Yeah, that's something I'll try and see... Thanks all!