I have a PHP app in front of a Manticore instance and I’m using PDO to create queries. Generally this works. However, I (or rather: my user) has found a query that sometimes returns zero results, and sometimes multiple results.
Looking in query.log I can see this as an example of when the results are returned:
/* Fri Jul 14 13:18:39.065 2023 conn 2073 real 0.001 wall 0.002 found 0 */ SELECT user_id AS id, name, path, album, art,artist, band, composer, duration, genre, title, disc, track, year, format, bitrate, filesize, created, edited, id AS db_id, tag FROM library WHERE MATCH('@(album) (*"Arcadia Q"*)') AND user_id=24850 GROUP BY album, band;
/* Fri Jul 14 13:18:39.067 2023 conn 2073 real 0.001 wall 0.002 found 1 */ SELECT user_id AS id, name, path, album, art,artist, band, composer, duration, genre, title, disc, track, year, format, bitrate, filesize, created, edited, id AS db_id, tag FROM library WHERE MATCH('@(artist) (*"Arcadia Q"*)') AND user_id=24850 GROUP BY artist;
/* Fri Jul 14 13:18:39.069 2023 conn 2073 real 0.001 wall 0.001 found 1 */ SELECT user_id AS id, name, path, album, art,artist, band, composer, duration, genre, title, disc, track, year, format, bitrate, filesize, created, edited, id AS db_id, tag FROM library WHERE MATCH('@(band) (*"Arcadia Q"*)') AND user_id=24850 GROUP BY band;
/* Fri Jul 14 13:18:39.070 2023 conn 2073 real 0.000 wall 0.000 found 0 */ SELECT user_id AS id, name, path, album, art,artist, band, composer, duration, genre, title, disc, track, year, format, bitrate, filesize, created, edited, id AS db_id, tag FROM library WHERE MATCH('@(genre) (*"Arcadia Q"*)') AND user_id=24850 GROUP BY genre;
/* Fri Jul 14 13:18:39.072 2023 conn 2073 real 0.001 wall 0.002 found 25 */ SELECT user_id AS id, name, path, album, art, artist, band, composer, duration, genre, title, disc, track, year, format, bitrate, filesize, created, edited, id AS db_id, tag, WEIGHT() AS score FROM library WHERE MATCH('(*"Arcadia Q"*)') AND user_id=24850 ORDER BY score DESC, album ASC,disc ASC, track ASC, title ASC LIMIT 0,160 OPTION field_weights=(title=2, user_id=0); /*warning=index library: Fields specified in field_weights option not found: [user_id] */
However, here’s an example with the same set of queries that return zero results:
/* Fri Jul 14 13:19:06.808 2023 conn 7279484 real 0.001 wall 0.002 found 0 */ SELECT user_id AS id, name, path, album, art, artist, band, composer, duration, genre, title, disc, track, year, format, bitrate, filesize, created, edited, id AS db_id, tag FROM library WHERE MATCH('@(album) (*"Arcadia Q"*)') AND user_id=24850 GROUP BY album, band;
/* Fri Jul 14 13:19:06.810 2023 conn 7279484 real 0.001 wall 0.002 found 0 */ SELECT user_id AS id, name, path, album, art, artist, band, composer, duration, genre, title, disc, track, year, format, bitrate, filesize, created, edited, id AS db_id, tag FROM library WHERE MATCH('@(artist) (*"Arcadia Q"*)') AND user_id=24850 GROUP BY artist;
/* Fri Jul 14 13:19:06.811 2023 conn 7279484 real 0.001 wall 0.001 found 0 */ SELECT user_id AS id, name, path, album, art, artist, band, composer, duration, genre, title, disc, track, year, format, bitrate, filesize, created, edited, id AS db_id, tag FROM library WHERE MATCH('@(band) (*"Arcadia Q"*)') AND user_id=24850 GROUP BY band;
/* Fri Jul 14 13:19:06.813 2023 conn 7279484 real 0.000 wall 0.000 found 0 */ SELECT user_id AS id, name, path, album, art, artist, band, composer, duration, genre, title, disc, track, year, format, bitrate, filesize, created, edited, id AS db_id, tag FROM library WHERE MATCH('@(genre) (*"Arcadia Q"*)') AND user_id=24850 GROUP BY genre;
/* Fri Jul 14 13:19:06.815 2023 conn 7279484 real 0.002 wall 0.003 found 0 */ SELECT user_id AS id, name, path, album, art, artist, band, composer, duration, genre, title, disc, track, year, format, bitrate, filesize, created, edited, id AS db_id, tag, WEIGHT() AS score FROM library WHERE MATCH('(*"Arcadia Q"*)') AND user_id=24850 ORDER BY score DESC, album ASC, disc ASC, track ASC, title ASC LIMIT 0,160 OPTION field_weights=(title=2, user_id=0); /*warning=index library: Fields specified in field_weights option not found: [user_id] */
Looking through query.log the only consistent thing I can find for those batches that return zero results is that the conn number is always really high - seven characters. The ones that work always are four characters. I’m guessing this is just an id for the DB connection, so is a co-incidence and shouldn’t have any effect.
If I run the queries using the MySQL cli then the queries always return the results.
If I remove the quote marks (and the “Q”, because otherwise I get a Query word length is less than min infix length. word: 'q*' error) it seems to work more in a more stable fashion, always returning results.
Am I just forming queries incorrectly?