Bug #110972 Innodb Fulltext search returns incorrect results when kill and restart mysqld
Submitted: 10 May 2023 7:14 Modified: 11 May 2023 12:53
Reporter: fei yang Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: FULLTEXT search Severity:S2 (Serious)
Version:8.0.32 OS:Linux
Assigned to: CPU Architecture:Any

[10 May 2023 7:14] fei yang
Description:
Hi,we insert some records into a table with fulltext and delete these records,
if we kill and restart the mysql server, and then insert some records, the newly
inserted records can't be found by fulltext search.

How to repeat:
#step1
drop table if exists t1;
create table t1(name text(10), fulltext index idx(name))engine=innodb;
insert into t1 values('aaaaa');
insert into t1 values('bbbbb'); 
delete from t1;

#step2 excute kill -9 ${mysqld} and restart mysql server, where {mysqld} is the pid of mysql server

#step3
insert into t1 values('ccccc');
insert into t1 values('ddddd');
select * from t1 where name like '%ccccc%';
+-------+
| name  |
+-------+
| ccccc |
+-------+

select * from t1 where match(name) against('ccccc');
Empty set (0.03 sec)

select * from t1 where name like '%ddddd%';
+-------+
| name  |
+-------+
| ddddd |
+-------+

select * from t1 where match(name) against('ddddd');
Empty set (0.00 sec)

As we can see, the fulltext search can't 'ccccc' and 'ddddd' though these word have been inserted into tables.
In fact, these word have been tokenized into fts cache, while have been marked as deleted, this is unexpected.
These can be verify through following statements:

set global innodb_ft_aux_table ='test/t1';
select * from information_schema.INNODB_FT_INDEX_CACHE;
+-------+--------------+-------------+-----------+--------+----------+
| WORD  | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION |
+-------+--------------+-------------+-----------+--------+----------+
| ccccc |            2 |           2 |         1 |      2 |        0 |
| ddddd |            3 |           3 |         1 |      3 |        0 |
+-------+--------------+-------------+-----------+--------+----------+

select * from information_schema.INNODB_FT_DELETED;
+--------+
| DOC_ID |
+--------+
|      2 |
|      3 |
+--------+

And if we recreate the table, fulltext search can return correct results:

alter table t1 engine=innodb;
select * from t1 where match(name) against('ddddd');
+-------+
| name  |
+-------+
| ddddd |
+-------+
[10 May 2023 12:40] MySQL Verification Team
Hi Mr. vang,

Thank you for your bug report.

We have tried to repeat your test case, without success. The main difference between your setup and ours is that ours is full ACID. Hence, there are no losses after we kill mysqld process. In short, we are always using full syncing and flushing, so that we have almost full ACID security. As described in our Reference Manual.

This is the output from our queries, which finish with two FTS searches:

+-------+
| name  |
+-------+
| ccccc |
+-------+
+-------+
| name  |
+-------+
| ccccc |
| ccccc |
+-------+
+-------+
| name  |
+-------+
| ccccc |
| ccccc |
+-------+
+-------+
| name  |
+-------+
| ddddd |
| ddddd |
+-------+
+-------+
| name  |
+-------+
| ddddd |
| ddddd |
+-------+

As you can see it is all working properly.

Can't repeat.
[11 May 2023 12:53] fei yang
Hi, theoretically, this problem occurs certainly,
and 'delet from t1' for step1 and 'kill -9' for step2 are the key point. I have
provided a mysql-test case as following, you can have a try:

cat t/mytest1.test:
drop table if exists t1;
create table t1(name text(10), fulltext index idx(name))engine=innodb;
insert into t1 values('aaaaa');
insert into t1 values('bbbbb'); 
delete from t1;

--source include/kill_and_restart_mysqld.inc

insert into t1 values('ccccc');
insert into t1 values('ddddd');
select * from t1 where name like '%ccccc%';
select * from t1 where match(name) against('ccccc');
select * from t1 where name like '%ddddd%';

The output can be seen as follows:

drop table if exists t1;
Warnings:
Note	1051	Unknown table 'test.t1'
create table t1(name text(10), fulltext index idx(name))engine=innodb;
insert into t1 values('aaaaa');
insert into t1 values('bbbbb');
delete from t1;
# Kill and restart
insert into t1 values('ccccc');
insert into t1 values('ddddd');
select * from t1 where name like '%ccccc%';
name
ccccc
select * from t1 where match(name) against('ccccc');
name
select * from t1 where name like '%ddddd%';
name
ddddd
select * from t1 where match(name) against('ddddd');
name
[11 May 2023 12:55] MySQL Verification Team
Hi,

We have to repeat what we wrote in our previous comment.

The key event in the test case is a kill command. When you are 100 % ACID compliant (like in our installation), then no problem can occur when you kill -9 a daemon. 

Can't repeat.
[12 May 2023 2:59] biao li
The same problem has ever happened in my release environment.

I ran the testcase in MySQL 8.0.33. MySQL indeed lost data if we use the full-text index.

[lb@mysql-develop-0001 mysql-test]$ ./mtr main.mytest1
Logging: ./mtr  main.mytest1
MySQL Version 8.0.33
Checking supported features
Using 'all' suites
Collecting tests
Checking leftover processes
Removing old var directory
Creating var directory '/u04/lb/mysql-server/install/mysql-test/var'
Installing system database
Using parallel: 1

==============================================================================
                  TEST NAME                       RESULT  TIME (ms) COMMENT
------------------------------------------------------------------------------
[ 50%] main.mytest1                              [ fail ]
        Test ended at 2023-05-12 10:41:26

Result file '/u04/lb/mysql-server/install/mysql-test/r/mytest1.result' doesn't exist.
Either create a result file or disable check-testcases and run the test case. Use --nocheck-testcases option to disable check-testcases.

Mysqltest client output from logfile
----------- MYSQLTEST OUTPUT START -----------

drop table if exists t1;
Warnings:
Note    1051    Unknown table 'test.t1'
create table t1(name text(10), fulltext index idx(name))engine=innodb;
insert into t1 values('aaaaa');
insert into t1 values('bbbbb');
delete from t1;
# Kill and restart
insert into t1 values('ccccc');
insert into t1 values('ddddd');
select * from t1 where name like '%ccccc%';                  --- important
name
ccccc
select * from t1 where match(name) against('ccccc');         --- important
name
select * from t1 where name like '%ddddd%';
name
ddddd

So, what do you mean by "100 % ACID compliant (like in our installation)". Now, I ran the mtr testcase with the default configuration, the result is MySQL lost data.

Do I need some extra configuration if I want 100% ACID compliant. Or, can you give me your extra configuration, if you can not repeat the mtr testcase?