Remove music directory & metadata causes invalid SQL

[mixxxbot]

Reported by: smidge
Date: 2021-07-25T13:50:52Z
Status: Fix Committed
Importance: Critical
Launchpad Issue: lp1937941

---

Affected version(s):

Latest source build from main (commit bee21d8)

Steps to reproduce:

I have been unable to reproduce this since the initial occurrence, but these are the steps I took:

1. Ensure that Mixxx is set up with multiple music directories as part of the library.
2. Open Mixxx, navigate to Preferences.
3. Remove one of the music directories
4. Choose option to delete track metadata.

In my case, I had ~15 different music directories that I needed to remove, and removing some of them caused this bug, whereas some did not. My (uneducated) guess would be that perhaps something about the nature of the tracks in each directory may be the trigger for the bug.

Expected behaviour:

Directory is deleted without fanfare, and without assertions failing.

Actual behaviour:

A critical debug assertion is raised:

```
critical [Main] FwdSqlQuery - Failed to prepare "UPDATE library SET timesplayed=q.timesplayed,last_played_at=q.last_played_at FROM(SELECT PlaylistTracks.track_id as id,COUNT(PlaylistTracks.track_id) as timesplayed,MAX(PlaylistTracks.pl_datetime_added) as last_played_at FROM PlaylistTracks JOIN Playlists ON PlaylistTracks.playlist_id=Playlists.id WHERE Playlists.hidden=2 GROUP BY PlaylistTracks.track_id) q WHERE library.id=q.id AND library.id IN (3391)" : QSqlError("1", "Unable to execute statement", "near \"FROM\": syntax error")
critical [Main] DEBUG ASSERT: "!query.hasError()" in function bool TrackDAO::updatePlayCounterFromPlayedHistory(const QSet<TrackId> &) const at /home/smidge/Development/Audio/Mixxx/mixxx-github/src/library/dao/trackdao.cpp:2248
```

The SQL, when formatted better, looks like this:

UPDATE library
SET timesplayed=q.timesplayed,
    last_played_at=q.last_played_at
FROM (
    SELECT PlaylistTracks.track_id as id,
           COUNT(PlaylistTracks.track_id) as timesplayed,
           MAX(PlaylistTracks.pl_datetime_added) as last_played_at
    FROM PlaylistTracks
    JOIN Playlists ON PlaylistTracks.playlist_id=Playlists.id
    WHERE Playlists.hidden=2
    GROUP BY PlaylistTracks.track_id) q
WHERE library.id=q.id
AND library.id IN (3391)

[mixxxbot]

Commented by: uklotzde
Date: 2021-07-27T12:48:19Z

---

The above query is valid and executed without errors.

• Which schema version is your database?
• Which SQLite version do you use?

[mixxxbot]

Commented by: uklotzde
Date: 2021-07-27T13:01:13Z

---

Tested and works without issues for SQLite 3.34.1.

https://www.sqlite.org/lang_update.html#upfrom
UPDATE-FROM is supported beginning in SQLite version 3.33.0 (2020-08-14).

Oh, that's surprising! If you are able to rewrite and test the SQL command for an older version please share.

[mixxxbot]

Commented by: uklotzde
Date: 2021-07-27T13:27:36Z

---

Temporary mitigation to prevent the debug assertion: #4152

[mixxxbot]

Commented by: smidge
Date: 2021-07-27T13:45:05Z

---

I didn't realise UPDATE FROM was so recent for Sqlite! The version on my workstation is indeed older:

```
$ cat /etc/issue.net 
Linux Mint 20.1 Ulyssa
$ apt show libsqlite3-0 | grep Version
Version: 3.31.1-4ubuntu0.2
$ sqlite3 --version
3.31.1 2020-01-27 19:55:54 3bfa9cc97da10598521b342961df8f5f68c7388fa117345eeb516eaa837balt1
```

And:

```
$ sqlite3 mixxxdb.sqlite "select * from settings where name = 'mixxx.schema.version';"
mixxx.schema.version|38|0|0
```

[mixxxbot]

Commented by: Holzhaus
Date: 2021-07-29T14:49:03Z

---

@Smidge: Could you test #4152 and check if that fixes the issue?

[mixxxbot]

Commented by: smidge
Date: 2021-07-29T15:13:27Z

---

This is on my list of things to do :-). Will try to get round to it in the next few days.

[mixxxbot]

Commented by: smidge
Date: 2021-07-31T21:00:42Z

---

I'm no longer able to replicate the bug (tried 3 different dirs) with the above PR, so LGTM.

[mixxxbot]

Issue closed with status Fix Committed.