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: | |
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
[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?