[DavionCA]

If I don't care about rebuilding the libraries and watched/unwatched data should I just dump the databases and start over? I'm not very familiar with MySQL (I do more work in MS SQL) so I'm not sure what the best way to do this would be.

Or would you like logs and such to try to work this as a bug? I've learned to live with this issue, but would rather not if I don't have to. I love XBMC and fixing this would make it perfect for my uses (which I think are pretty typical)..

[jmarshall]

See my posts: the first step is to look in your current database and check that there is logic behind the duplicates appearing. Once we know that we might be able to figure out how it happened and whether or not there is a problem.

Cheers,
Jonathan

[Peekz]

One thing I actually noticed is that certain shows would duplicate the 1st episode in a season when a new show is found for that season. Very odd

[DavionCA]

Ok, I've found one of the duplicated episodes and found that in the "episodes" table they are listed 4 times, all with different "idEpisode" values. All other data for each entry is identical to the others. A quick visual of the other rows show other episodes duplicated in the same manner.

Are there other tables I should be looking at to find more duplicated data?

[jmarshall]

Read my posts: http://forum.xbmc.org/showthread.php?tid...pid1105137

[DavionCA]

I should clarify I guess. In the files table there is only a single entry for a particular file (tested by sorting the table by the strFilename). When I reference that file's idFile in the episodes table there are multiple entries for that idFile, each with identical data except for the idEpisode.

So if I look at the episodes table I'll see 4 entries with the same idFile (in this case 7510) but each with different idEpisode values (45868, 47994, 48073, 48709).

You said that somewhere the four values (idFile, idPath, strFilename, and strPath) are going to be different but I cannot find anywhere where that's the case. Is there anywhere other than in the episode and files tables that I should be looking to find those entries?

[Tuxie]

I got this problem using the builtin sqlite db on XBMCbuntu. My quickfix was to just remove all but one episode per file from the database:

$ sqlite3 ~/.xbmc/userdata/Database/MyVideos60.db
sqlite> delete from episode where idepisode in (select e2.idepisode from episode e1 join episode e2 using (idfile) where e2.idepisode > e1.idepisode);

[DavionCA]

Ok, there must be some kind of bug within XBMC. I deleted the database and rebuilt all my videos from scratch. I am getting the exact same behavior as before. Some shows/seasons have duplicated listings for the exact same episode (dupes added every time "Scan for new episodes" or "Update Library" is run). Running the above query that Tuxie provided does remove the dupes (thanks Tuxie!), but this is a bandage on the problem and doesn't prevent the dupes from re-appearing.

This also doesn't appear to be restricted to only Windows releases either.

At the very least it would be nice to add something like this to the "Clean Library" function until this gets completely resolved.

[phreaq]

create or replace view duplicateepisodes as select max(e.idEpisode) idEpisode from tvshow s join tvshowlinkepisode se on s.idShow = se.idShow join episode e on se.idEpisode = e.idEpisode join files f on e.idFile = f.idFile group by f.strFilename having count(e.idEpisode) > case when f.strFilename regexp '[0-9]+x[0-9][0-9]-[0-9]+x[0-9][0-9]' then 2 when f.strFilename regexp '[0-9]+x[0-9][0-9]-[0-9][0-9]' then 2 else 1 end;
create or replace view duplicateepisodedetails as select s.idShow, s.c00 showName, e.idEpisode, e.c12 season, e.c13 episodeNumber, e.c00 episodeName, p.strPath path, f.strFilename fileName from duplicateepisodes de join episode e on de.idEpisode = e.idEpisode join tvshowlinkepisode se on e.idEpisode = se.idEpisode join tvshow s on se.idShow = s.idShow join files f on e.idFile = f.idFile join path p on f.idPath = p.idPath;
create or replace view unlinkedfiles as select idFile from files f where f.idFile not in (select idFile from episode) and f.idFile not in (select idFile from movie);
delete from tvshowlinkepisode where idEpisode in (select idEpisode from duplicateepisodes);
delete from episode where idEpisode in (select idEpisode from duplicateepisodes);
delete from files where idFile in (select idFile from unlinkedfiles);

when I run this in HeidiSQL I get the following error:

SQL Error (1443): The definition of table 'unlinkedfiles' prevents operation DELETE on table 'files'.

[OK]

any thoughts?

[Betard]

when I run this in HeidiSQL I get the following error:

SQL Error (1443): The definition of table 'unlinkedfiles' prevents operation DELETE on table 'files'.

[OK]

any thoughts?

I get the exact same sql error... I think my duplicate episode issue is because one of my machines had the Logass within the filepath, so I had a few hundred entries with that and also appeared url encoded. So they resolved to the exact same filepath, but were clearly different definitions. This may be the result of having connected my MacBook Pro to the DB, but I'm not a 100% sure if that is the cause.

I ran slightly modified version of sql statements 'scottt732' supplied, followed by performing a "clean" on the library seemed to resolve my issue.

[dugan26]

I am having the same issue (duplicate TV shows on some drives) however I AM in deed showing two different paths on the Info section - one SMB and one Y:\TV Shows -

How do I get rid of the SMB?

I have deleted all my sources and re-added only the mapped drives - no good
I deleted the SMB mappings in the file: mediasources.xml - no good
I Cleaned Library (Setting, Video, Clean Library - no good
Also deleted duplicate shows - then updated library and they came back as duplicates - no good

What else do i need to do?

Many thanks in Advance

[dugan26]

I found a similar post with a problem not exactly the same but anyway by deleting myvideos*.db that cleared my library - then i just set content again - waited for everything to load up and my sbm shares were finally gone

No more duplicate TV Shows ...

Post: http://forum.xbmc.org/showthread.php?tid=123789

[Jaapi]

Ran into the same problem as soon as I upgraded to Eden. Some shows work as they used to, but many show duplicate files.
However this happens only when in library mode, not when I go to my shows through "files".

[NeS_BCN]

I have an experience related to this problem with duplicates which I think will be interesting to share with you.

I currently have a central MySQL database which is accessed by several Xbmc 11 clients (eden). This is working flawlessly.

Recently I have been very interested in the new alpha versions of xbmc 12 (frodo), which is why I have started installing new clients with the latest release cycle (august + xbmcbuntu). The problem with the duplicates has started when I re-added the sources within these new clients (after the database was 'upgraded' to frodo).

The reason why I now have duplicates of everything is because the path differs slightly for each item: the original path contains smb://user:password@server/path, whilst the duplicate path contains only smb://server/path. (this issue has been pointed before)

This has so far not been picked up by the database cleaning function and therefore I have now duplicates for each item in the database. Removing the 're-added' sources removes the duplicates (as expected), but this also means that I can't add/scrape new items.

I haven't been able to re-add the sources in a way that results in the paths being written in the same way. Frodo uses a different procedure when dealing with password-protected smb paths, which is storing the user/password in passwords.xml . I haven't found a way around this, but as I think it is a more elegant solution than storing the user/password with *every* item, I will probably give up and re-scan the whole library, unless someone here comes up with a usable solution.

Any thoughts?

[rimster]

i ran into this problem as well with the august cycle, didn't look into it too much then, thought i'd just wait for a version where it's fixed. Just installed the September cycle, and it seems it's still a problem.

I was running 11.0 stable just fine with MySQL on my NAS as a central storage for a couple of PC's running XBMC. After upgrading to the new version all of a sudden all the entries in my TV Show's seem to have doubled. Movies seem to be unaffected. The doubling occurred after the upgrade script ran... that's why i find this statement:

You need to look in your database and grab out the idFile and idPath (from the files table), strFileName and strPath for each duplicate item.

Somewhere they'll be different. This is your problem.

quite asinine to be frank. How can this be my problem if the only difference is the installation of the new version? I'm guessing there's a bug in one of the upgrade scripts, but judging from that statement this developer doesn't seem to want to find it.

Here's 2 lines from the episodeview in the 69 db to show you an example of the duplicate entries:

idfile idPath
5356 3160
10352 3160

strfilename:
rar://smb%3a%2f%2fMYNAS%2fQmultimedia%2fseries%2fhow%2ei%2emet%2eyour%2emother%2fseason%2e06%2fHow%2eI%2eMet%2eYour%2eMother%2eS06E10%2eDVDRip%2eXviD%2dREWARD%2fhow%2ei%2emet%2eyour%2emother%2es06e10%2edvdrip%2exvid%2dreward%2erar/how.i.met.your.mother.s06e10.dvdrip.xvid-reward.avi
strfilename:
rar://smb%3a%2f%2fMYNAS%2fQmultimedia%2fseries%2fhow.i.met.your.mother%2fseason.06%2fHow.I.Met.Your.Mother.S06E10.DVDRip.XviD-REWARD%2fhow.i.met.your.mother.s06e10.dvdrip.xvid-reward.rar/how.i.met.your.mother.s06e10.dvdrip.xvid-reward.avi

strpath:
smb://MYNAS/Qmultimedia/series/how.i.met.your.mother/season.06/How.I.Met.Your.Mother.S06E10.DVDRip.XviD-REWARD/
strpath:
smb://MYNAS/Qmultimedia/series/how.i.met.your.mother/season.06/How.I.Met.Your.Mother.S06E10.DVDRip.XviD-REWARD/


Hope this helps to find the problem.

p.s. looking through the episodeview i noticed that of the duplicate entries, one is holding the playcount & lastplayed data and the other the dateadded. Furthermore there are differences in the duplicates on episode.c18 and c19.