Removing a track from history list set all play counters to 0

[MarcPlace]

Bug Description

Yesterday I fell over a nasty bug in Mixxx 2.4.0 using e3e0f6e, but it also happened a few weeks ago. Unfortunately I didn't know what was the cause for it so I did some investigations today.

Track list looks like this ("Played" counter is set to 1 or more):

Then I go to the "History" crate and remove a track from the list:

After restarting Mixxx, all "Played" counters are now set to 0 (funny: the track I removed from the list seems to be OK and the counter has still 3 plays):

Luckily I had a backup of "mixxxdb.sqlite" file with me, so I could quickly restore all the settings again (keep this in mind if you try to reproduce this issue :-) )

Hope this helps for digging into it to confirm this issue.

Kind regards,
Marco

Version

2.4.0

OS

Gentoo Linux

[uklotzde]

Good job and excellent bug report! You were able to reproduce #12046 which was reported earlier.

@Swiftb0y Mark #12046 as a duplicate and continue here?

[MarcPlace]

Sorry, I've crawled the bug reports before of course, but haven't found anything similar to my issue. But #12046 looks indeed like something related to mine. Glad that I could help you.

[uklotzde]

The query updateNotPlayed in TrackDAO::updatePlayCounterFromPlayedHistory() seems to be the cuplrit.

[uklotzde]

I'll fix it. Looks like a closing brace in the SQL statement is misplaced.

[uklotzde]

The bug only affects the code path for SQLite >= 3.33.0 that makes use of UPDATE-FROM.

[Swiftb0y]

The bug only affects the code path for SQLite >= 3.33.0 that makes use of UPDATE-FROM.

That would likely explain why the devs on ubuntu 20.04. did not experience this issue.

I'll fix it. Looks like a closing brace in the SQL statement is misplaced.

Thank hugely you for identifying and fixing the problem. Please ping me in your fix, I will review it ASAP.

[uklotzde]

As a bonus I have added the SQL statement that restores the lost data: 0083a21

Could also be executed as part of a database migration or triggered on demand.

[MarcPlace]

Did a quick test and can confirm, that 0083a21 has fixed this issue (at least for me). 👍

The SQLite version that Gentoo 17.1 offers looks quite recent:
marco@timmy ~ $ sqlite3 --version
3.43.2 2023-10-10 12:14:04 4310099cce5a487035fa535dd3002c59ac7f1d1bec68d7cf317fd3e76948alt1 (64-bit)

I think all most recent distributions are using SQLite >3.33.0 for now (except Ubuntu Focal as mentioned above).
Anyway guys, thanks for tracking this issue down that fast. 🎉

[Swiftb0y]

fixed in 2.4. Thank you uklotzde for the fix and thank you marc for the detailed bug report.