MySQL Full-Text Search index on InnoDB table and missing results

Question

A customer notifies me that the search of an application we have developed for him is not working properly. The search is based on queries on Full-Text Search indexes on InnoDB tables in MySQL 5.6 (the first version that supported it).

When performing the query manually I check that it only returns 1 result when it should return 2. I tried with other search terms and the same table row is always omitted. The same queries using LIKE works well.

When trying to replicate the problem in a virtualized environment with the same OS and MySQL server versions I am unable to reproduce it, as the query works correctly after restoring a database dump.

After trying several options what has worked is to run OPTIMIZE TABLE. The search query works well after optimizing the table, showing the missing record.

Why does this happen? What is the explanation for this problem and how can it be detected or prevented?

The query is something like this: SELECT * FROM workers WHERE MATCH (name, surname) AGAINST('+rosario' IN BOOLEAN MODE). The missing row has nothing special, but was updated recently (has timestamp columns for create/update) and the others dont. This is why a locked row seems possible... — David Martínez
– David Martínez, Commented Jun 23, 2021 at 6:31
To file a bug I must be able to reproduce the problem, and I can't. This is why I'm posting it here, to know if anyone else had this problem and knows how to reproduce/resolve it... — David Martínez
– David Martínez, Commented Jun 24, 2021 at 8:33

Raphael PICCOLO · Accepted Answer · 2021-06-22 08:17:05Z

0

i dont know much about your code, your database, your queries, but i see only 2 situations :

a row level locking which is never released : (may be a bug in mysql) https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html
table corruption : i'm not sure if OPTIMIZE TABLE is repairing anything or only reducing fragmentation.

answered Jun 22, 2021 at 8:17

Raphael PICCOLO

2,1881 gold badge16 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

David Martínez Over a year ago

Thank you Raphael. The first option makes more sense, as I assume that a corrupted table would prevent any records from being read (it has already happened to me). If it happens again I will try to see if there are locked records, but at the moment I don't know how to reproduce the problem....

Collectives™ on Stack Overflow

MySQL Full-Text Search index on InnoDB table and missing results

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related