Clementine creates duplicates in library

[Clementine-Issue-Importer]

From apgrant2 on April 19, 2011 03:33:37

What steps will reproduce the problem? 1.Using Clementine over time 2.Updating Music library automatically 3.Perhaps it is a copying songs to device issue? What is the expected output? What do you see instead? It is expected to have only one song appear in Clementine when there is only one such song on my hard disk however Clementine seems to be creating multiple copies of songs. I haven't been able to narrow down the issue to any one event. It just seems that over time a duplicate is added. This goes for every album in my library. Some have 5 dupes. Others have 10 dupes. Though none have more than 10. What version of the product are you using? On what operating system? I'm using Clementine 0.7 r3146 and QT 4.70. This being used on Ubuntu 10.10. Please provide any additional information below. The dupes began at two and have grown larger over time until they've reached 10 dupes per song on some albums. When I use another music player such as Banshee or Rhythmbox there are no dupes. Also, when I check my Music folder for dupes there are no dupes. I've been searching the forums and such for any leads but haven't found any. Also, I've checked this site for someone reporting the same kind of issue and have also come up empty. If I makes duplicatesam mistaken in any way please advise. I want nothing more than to continue to use Clementine.

Attachment: Selection_005.png

Original issue: http://code.google.com/p/clementine-player/issues/detail?id=1785

[mxa]

@crissimaster that would be two more different bugs I suppose. Try to make repeatable cases for each of them and file separately?

[falkartis]

I have the same problem Here. I have no clue how they appear but this is how I check they are there:

select count(c) from (
    select count(ROWID) as c from songs group by filename
);

This gives me 66948 and if I run:

select count(ROWID) from songs;

I get 133238. Dividing the two numbers gives 1.990171 this means that almost every song is duplicated on the database with IDENTICAL PATHS. Running another query:

select count(ROWID) as c, cast(filename as TEXT), *
from songs group by filename order by c desc;

The counts c I get for the first 3 rows are 44, 38, 28 and 2 for all the others.

[mxa]

I can't believe this bug did not get more attention since 2013. I kind of got used to append an album to the playlist and then use the "remove duplicates from playlist" to not having to listen to every song twice.

[mxa]

@falkartis can you try downloading the free album in this issue I created? https://github.com/clementine-player/Clementine/issues/5654

[falkartis]

Yes, I'm downloading it. How I add it to Clementine? do I copy it to my music folder? or do I drag and drop it from the downloads? or some other way?

[mxa]

@falkartis I just put it in the music folder, Clementine watches it and adds it to the database.

[falkartis]

Edit: don't use my procedure, it's inefficient, use this one: https://github.com/clementine-player/Clementine/issues/1785#issuecomment-444776343

Ok, done. It adds it only once. Nice music by the way.

I'm currently working on a sql query I use to remove all the duplicates.

delete from songs where filename in (
select filename from songs group by filename having count(ROWID) > 1 limit 1
) limit 1;

But the problem is that in this way only one entry can be deleted at once. The solution is running this sql thousands of times. To do so I've put this sql on a file and runed it repeatedly with watch -n 0.01. I also copied the database to a tmpfs (a RAM filesystem) to avoid reading and writing to the HDD. on the sql file I also added this 3 lines to monitor the progress:

select count(c) from(select count(ROWID) as c from songs group by filename having count(ROWID) > 1);
select count(c) from(select count(ROWID) as c from songs group by filename);
select cast(filename as TEXT) from songs group by filename having count(ROWID) > 1 limit 1;

when the first number reaches 0 I'll stop the watch and copy the database file back to it's location replacing the old one.

WARNING: this procedure is slow and destructive, run it only if you understand what all this means, and backup your database before.

[mxa]

I suppose this bug and possibly https://github.com/clementine-player/Clementine/issues/3657 and https://github.com/clementine-player/Clementine/issues/5908 are related. I found a workaround: Running the "organize files" tool (to make the id3 tags match exactly the folder structure) seems to remove the double or triple entries in the library.

[slawo1605]

What seems to have sorted this for me was having just one folder in the music library.

[vf1sveritech]

2018 and this is still an issue....just did a fresh library new scan and duplicates. duplicates everywhere!

[mvhconsult]

BUMP. Confirmed to appear in 1.3.1 Win10. I even managed to get triplicate entries in the database :(

[lcarlsen]

I ran into this problem a couple weeks ago. In case it's helpful to someone else, here's the sql I used to examine the issue and then clean it up.

-- examine duplicates
select s1.rowid
from songs s1
left join songs s2 on s1.filename = s2.filename and s1.rowid != s2.rowid
where s1.rowid > s2.rowid
and s2.rowid is not null
and s2.rowid != s1.rowid
order by s1.filename, s1.rowid;
-- look for duplicates in playlists
select p.name, s.album, s.title, s.filename
from playlist_items pi
left join playlists p on pi.playlist = p.rowid
left join songs s on pi.library_id = s.rowid
where library_id in (
    select s1.rowid
    from songs s1
    left join songs s2 on s1.filename = s2.filename and s1.rowid != s2.rowid
    where s1.rowid > s2.rowid
    and s2.rowid is not null
    and s2.rowid != s1.rowid
);
-- delete duplicates from playlists
delete from playlist_items where library_id in (
    select s1.rowid from songs s1
    left join songs s2 on s1.filename = s2.filename and s1.rowid != s2.rowid
    where s1.rowid > s2.rowid
    and s2.rowid is not null
    and s2.rowid != s1.rowid
);
-- delete duplicates
delete from songs where row_id in (
    select s1.rowid
    from songs s1
    left join songs s2 on s1.filename = s2.filename and s1.rowid != s2.rowid
    where s1.rowid > s2.rowid
    and s2.rowid is not null
    and s2.rowid != s1.rowid
);

The advantage over the solution from falkartis is that I only had to run this once. This worked for me because most of my duplicate songs had a higher row_id than the entry I wanted to keep. I first manually removed the bad/duplicate entries with lower id.

[falkartis]

Yeha! this solution looks much better. I can't try it because (luckily) there are no new duplicated entries in my database.

[lord-aerion]

@lcarlsen Thank you so much for this! This issue has been a thorn in my side for many years, leaving me between a rock and a hard place, with Clementine being a pain to use and Amarok being awful since the redesign.

I had to replace row_id with rowid, however, for the delete duplicates to work:

…
-- delete duplicates
delete from songs where rowid in (
    select s1.rowid
    from songs s1
    left join songs s2 on s1.filename = s2.filename and s1.rowid != s2.rowid
    where s1.rowid > s2.rowid
    and s2.rowid is not null
    and s2.rowid != s1.rowid
);
…

I still don't understand why this issue, six and a half years since it was first reported, still hasn't been fixed.

It should not require a workaround that is well and truly beyond the capabilities or understanding of ordinary users.

I, for one, have no knowledge whatsoever of SQLite, and had to do a substantial amount of reading up on SQLite in order to fix the "Error: no such column: row_id" problem I encountered while trying to execute these commands, or even on how to use SQLite at all and open the database in the first place. And I'm far from a novice computer user.

Without your post I would never have been able to fix this extremely annoying problem.

[brinbrin62]

Hello.

I'm using Clementine 1.4.0~rc1+dfsg-1 (the version available on my Linux Mint). And I have a bazillion of duplicates, triplicates, quadriplicate (if that's a word) on my playlists. I tried the series of Sqlite3 statements above using DB Browser for Sqlite 3.11.2 with no success. The two first SELECT statements return no results, as if the issue was not in the DB itself.

Frankly, I'm puzzled. Even the track count is the triple+ of what it should be (45k+ instead of 12k).

When I try to remove the duplicates via the menu in Clementine, I see all the memory of the computer, then the swap being eaten up until the systems kills Clementine.

Anyone experiencing the same issues ?

Best regards,

Brinbrin

[mxa]

I think clementine is more or less abandoned. I moved to Strawberry and have no more such issues.

[brinbrin62]

I tried Strawberry, but with no smart playlist, and no ratings, it's of no use for me. Sorry to hear this about clementine. I already saw Banshee die. A music player is such an central software that it's like a friend. As a workaround for my issue, I wiped out the playlists (Ctrl-K) and re-asked for them, and all the duplicated have disappeared. But each time I add some new music, it goes havoc. I guess I'll make do.

[lord-aerion]

First RIP Amarok (technically still going, but the UI change in Amarok 2 simply sucks), now RIP Clementine. I gave up on Clemetine months ago. Issues remain unresolved for years. Importing my Amarok ratings never worked. A shame, cos it's still one of the best music players on Linux.

I've now switched to a mix of Cantata + MPD and Logitech Media Server + several Pi's running piCorePlayer.

So long, Clementine, thanks for the good times. Hopefully someone will revive it and fix the problems. It'd be a shame to see it disappear, even if I've stopped using it.

[brinbrin62]

And Rhythmbox UI is ugly AF. Quod Libet is even worse. Audacious is too simple.

[brinbrin62]

What is the repository for cantata ? ubuntuhandbook1/cantata do not support the latest linux Mint version...

[lord-aerion]

No idea, I'm afraid. I use Arch Linux which has it in the repose.

[lord-aerion]

And Rhythmbox UI is ugly AF. Quod Libet is even worse. Audacious is too simple.

Really, try Cantata + MPD. I had been avoiding an MPD based setup because of perceived difficulty, plus the habit of using full blown clients like Amarok, Clementine, and n my Wijdows days MediaMonkey.

Should have tried MPD years earlier. Oh, the benefit of hindsight.

[brinbrin62]

It was there : https://community.linuxmint.com/software/view/cantata I'll switch to Cantata. It has smart playlists and supports ratings, even if none of the ratings from clementine appear. I'll do with it. thanks a lot for the Tip.

[fernando0010]

I, for one, have no knowledge whatsoever of SQLite, and had to do a substantial amount of reading up on SQLite in order to fix the "Error: no such column: row_id" problem I encountered while trying to execute these commands, or even on how to use SQLite at all and open the database in the first place. And I'm far from a novice computer user.

Without your post I would never have been able to fix this extremely annoying problem.

Thanks a lot @lcarlsen and @lord-aerion , your fix worked like a charm. All duplicates gone, no file statistics lost.

[jthulhu]

I had this issue too, where all my tracks were duplicated. When I tried a full library rescan, a new copy appeared for each track! So, instead, I have removed the directory where I store my music from the library, clicked on "Apply", re-added it, clicked on "Apply" and voilà! It didn't even do a full rescan, it worked instantly. Hope it helps.

[waltherwatz]

Thx to @TheBlackBeans !! This was the tip of the year - don't understand why it works - but it does; all other (including full rescan) failed, but now my duplicates have vanished (now).