library.timesplayed occasionally get completely reset

[mixxxbot]

Reported by: ywwg
Date: 2021-12-17T15:27:22Z
Status: Fix Committed
Importance: High
Launchpad Issue: lp1955159

---

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.

[mixxxbot]

Commented by: ywwg
Date: 2021-12-17T15:38:53Z

---

(specifically, this is the library.timesplayed value)

[mixxxbot]

Commented by: ronso0
Date: 2021-12-17T17:45:18Z

---

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.

[mixxxbot]

Commented by: uklotzde
Date: 2021-12-18T23:31:49Z

---

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);

[mixxxbot]

Commented by: uklotzde
Date: 2021-12-18T23:36:05Z

---

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

[mixxxbot]

Commented by: ywwg
Date: 2021-12-22T16:38:40Z

---

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);

[mixxxbot]

Commented by: ywwg
Date: 2021-12-22T16:39:39Z

---

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]".

[mixxxbot]

Commented by: ywwg
Date: 2021-12-22T16:45:12Z

---

Oh I think it might be this change:

0c555663035#diff-1778803df6a6852a1b029d65cfee5c4971c163812cea4838859a31fd335f6d0dR2272

#4152

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

[mixxxbot]

Commented by: ywwg
Date: 2021-12-22T16:48:50Z

---

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.

[mixxxbot]

Commented by: ywwg
Date: 2021-12-22T16:51:12Z

---

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

[mixxxbot]

Commented by: uklotzde
Date: 2021-12-22T17:10:42Z

---

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

[mixxxbot]

Commented by: uklotzde
Date: 2021-12-22T17:54:23Z

---

#4578

[mixxxbot]

Issue closed with status Fix Committed.