library.timesplayed occasionally get completely reset

[Owen Williams]

I have had a problem recently (main branch) where occasionally the play counts get totally reset. I am not sure if this is related to mixxx crashing or some other bug, but this is a significant data-loss situation for me because I use the play counts when picking what to play.

[Owen Williams (ywwg)]

(specifically, this is the library.timesplayed value)

[ronso0 (ronso0)]

It gets reset after you played a track, or do you notice zero plays for tracks you definitely played before (like they're in some history playlist)?

Btw, maybe related, I recently noticed tracks with 'Played' checked directly after starting a session. I did reset those tracks manually in the database and didn't notice it again afterwards.

[Uwe Klotz (uklotzde-deactivatedaccount)]

The values of timesplayed can be restored in a similar manner like last_played_at, see the migration to version 36:

UPDATE library SET timesplayed=(
        SELECT COUNT(PlaylistTracks.track_id)
          FROM PlaylistTracks
          JOIN Playlists ON PlaylistTracks.playlist_id=Playlists.id
          WHERE PlaylistTracks.track_id=library.id
          AND Playlists.hidden=2);

[Uwe Klotz (uklotzde-deactivatedaccount)]

It looks like last_played_at gets lost, too. No idea why and I won't work on it.

[Owen Williams (ywwg)]

My entire database of 12 years occasionally gets zeroed out. Luckily I have backups so I was able to restore the values.

If the "played" box is checked on startup, it means mixxx crashed and didn't have a chance to reset those values.

That SQLITE statement is a partial solution, older versions of mixxx didn't save history playlists. Here's how I restored the values from my backup:

attach database "/path/to/mixxxdb.sqlite.backup" as olddb;

(this only updates values if they are larger in the backup than they are in the current db)

UPDATE
      library
SET timesplayed = (SELECT ol.timesplayed
                  FROM olddb.library ol
                  WHERE ol.id = library.id
    AND ol.timesplayed > library.timesplayed)
WHERE id IN (
 SELECT l.id
 FROM library l
 INNER JOIN olddb.library ol
 ON l.id=ol.id
 WHERE ol.timesplayed > l.timesplayed);

[Owen Williams (ywwg)]

I suspect this has to do with crashes, but I'm not sure how. It's possible there's some bad sqlite with something like a "update library set timesplayed=0 where [empty whereclause]".

[Owen Williams (ywwg)]

Oh I think it might be this change:

https://github.com/mixxxdj/mixxx/commit/0c555663035#diff-1778803df6a6852a1b029d65cfee5c4971c163812cea4838859a31fd335f6d0dR2272

https://github.com/mixxxdj/mixxx/pull/4152

The timing of this PR roughly coincides with when I started getting problems with this.

[Owen Williams (ywwg)]

If Uwe is not going to work on this my suggestion would be to remove this updateNotPlayed clause since it's highly suspect and I don't understand why it is needed.

[Owen Williams (ywwg)]

Based on the call stack, removing a history playlist or removing a track from a history playlist could trigger this bug.

[Uwe Klotz (uklotzde-deactivatedaccount)]

Probably some special case handling for empty track lists is missing?

[Uwe Klotz (uklotzde-deactivatedaccount)]

https://github.com/mixxxdj/mixxx/pull/4578

[Swiftb0y (swiftb0y)]

Mixxx now uses GitHub for bug tracking. This bug has been migrated to:
https://github.com/mixxxdj/mixxx/issues/10617