FTS partially broken after Album Rescan

[mikeysas]

It has taken me a while to figure out what was happening and confirm my suspicion that it is also related to Album Rescan. I was having intermittent issue with Search and I am not sure I captured all the scenarios, but I think enough to reproduce and investigate.

Describe the bug
After an Album Rescan, full text search on words from Artist Name (see note below), Album Name, and Track Title fail with DBD::SQLite::db do failed: UNIQUE constraint failed:. In the attached log, I included searches using both Material and Default as they generate slight different error messages. It starts with a Album Rescan of a Ray Brown, Liver at Scullers followed later by a Rescan of U2, The Unforgettable Fire. The results of search vary, but consistently if the word exists in Artist Name, Album Name, or Track Title, then that section of the search provides no results.

Running a New & Change scan (even if there is nothing to scan) corrects the problem I am assuming because the FTS index is rebuilt.

*Artist Name specifics
Live at Scullers is tagged as follows:
ALBUM: Live at Scullers
ALBUMARTIST: [ Ray Brown Trio, Ray Brown ]
ARTIST: [ Ray Brown, Benny Green, Gregory Hutchinson ]

Searches on "Ray" or "Brown" result no Artists and the errors, but search for Trio, Benny, Green, Gregory, and Hutchinson all work as expected.

I tested with the U2 album which is simply tagged U2 for both ARTIST and ALBUMARTIST to differentiate if the issue was just occuring with the second more than one Album Artist or just the first Artist name which I guess is what is happening???

To Reproduce
Steps to reproduce the behavior:

1. Rescan an Album
2. Search on words from first Artist, Album name and Track Title.
3. See error

Expected behavior
Search results that include all sections as expected

System Information (please complete the following information):

• Ubuntu x86_64
• Both Material and Default
• Chrome
• Version: 9.1.0 - 1769167449 @ Sat Jan 24 03:15:14 UTC 2026
• The test in the log results includes @darrell-k patched schema.pm but I saw this problem and error messages also before adding that patch, but had not nailed down the details yet.

server.log

[darrell-k]

I can see why this is happening. Rescanning the album is leading to multiple fulltext rows with the same key. There is an attempt in the code to avoid this by using "INSERT OR REPLACE" in the sql, but this is having no effect, as "OR REPLACE" depends on a unique index contraint existing, and fulltext is a virtual table and so cannot have an index.

In theory, this is easy enough to fix, but when I tried it exposed other problems which I am looking into. I'm trying to avoid having to rebuilt the entire fulltext table for a single album rescan.

[michaelherger]

@darrell-k - what was the approach you tried (and which failed)?

[darrell-k]

@darrell-k - what was the approach you tried (and which failed)?

The problem was with works scanning, which wasn't working when the scan was not running in the SCANNER process, but I've got a fix for that now.

As to the rest of the fix, this works:

@@ -233,8 +237,11 @@ sub checkSingleTrack {
 
        my $dbh = Slim::Schema->dbh;
 
+       $dbh->do("DELETE FROM fulltext WHERE id='" . $trackObj->urlmd5 . "'||'" . $trackObj->id . "'");
        $dbh->do( sprintf(SQL_CREATE_TRACK_ITEM,       'OR REPLACE', 'WHERE tracks.id=?'),       undef, $trackObj->id );
+       $dbh->do("DELETE FROM fulltext WHERE id='YXLALBUMSYYYYYYYYYYYYYYYYYYYYYYY'||'" . $trackObj->albumid . "'") if $trackObj->albumid;
        $dbh->do( sprintf(SQL_CREATE_ALBUM_ITEM,       'OR REPLACE', 'WHERE albums.id=?'),       undef, $trackObj->albumid )  if $trackObj->albumid;
+       $dbh->do("DELETE FROM fulltext WHERE id='YXLCONTRIBUTORSYYYYYYYYYYYYYYYYY'||'" . $trackObj->artistid . "'") if $trackObj->artistid;
        $dbh->do( sprintf(SQL_CREATE_CONTRIBUTOR_ITEM, 'OR REPLACE', 'WHERE contributors.id=?'), undef, $trackObj->artistid ) if $trackObj->artistid;
 }

but it's pretty inefficient running the Album and Contributor statements for every track. I'm working on moving those to a Slim::Utils::Scanner::API->onFinished handler for the fulltext scan.

As I said, the alternative would be to call Slim::Plugin::FullTextSearch::_rebuildIndex after an album rescan, but that seems overkill for changes to a single album and would probably be even slower than the checkSingleTrack change above.

[michaelherger]

What does "pretty inefficient" mean? It's not something you'd do every day, dozens of times. Most albums would only have a dozen or less tracks. Could the three deletions be combined into one, using a few ORs between the id conditions?

[darrell-k]

You're probably right, implementing an onFinished handler is proving not straightforward.

Let's try as you suggest, although I'm going to need to delete/add YXLWORKS.... entries in fullscan too (missed that when we introduced Works).

[michaelherger]

Created PR #1506 to further discuss the approach to fixing this.

[darrell-k]

Hmm, we're coding the same thing at the same time here!

Do you want me to create a PR for your branch with the Works addition? It would be easier for me to just carry on working on my own branch?

[michaelherger]

I wanted to have something we can discuss. It's easier IMHO when there's a PR. Feel free to create another one with your work.

[darrell-k]

See PR #1507