MySQL Bugs: #117030: the number of rows returned by count(*) query using fulltext index exceeds the number of rows in the table

Bug #117030	the number of rows returned by count(*) query using fulltext index exceeds the number of rows in the table
Submitted:	20 Dec 7:46	Modified:	20 Dec 11:11
Reporter:	Zhenye Lin	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: FULLTEXT search	Severity:	S3 (Non-critical)
Version:	8.0.40	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When the update operation is in progress, kill mysqld and then restarted. Repeat these operations several times. 
After that, we stop kill mysqld, but keep update operation concurrency. Then we  query through the fulltext index "select count(*) from test.t where match(content) against ('delete')", the number of rows sometimes returned exceeds the number of rows in the table.

How to repeat:
CREATE TABLE `t` (   `id` int NOT NULL AUTO_INCREMENT,   `content` text NOT NULL,   PRIMARY KEY (`id`),   FULLTEXT KEY `content_fulltext` (`content`) /*!50100 WITH PARSER `ngram` */ ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

generate 10000 row, then update and kill mysqld concurrency several times

restart mysqld, update and query through fulltext concurrency
select count(*) from test.t where match(content) against ('delete');

sometimes you can see 
mysql> select count(*) from test.t where match(content) against ('delete');
+----------+
| count(*) |
+----------+
|    10124 |
+----------+
however
mysql> select count(*) from test.t;
+----------+
| count(*) |
+----------+
|    10000 |
+----------+

Hi Mr. Lin,

Thank you for your bug report.

However, we were unable to repeat the problem that you are observing.

Namely, we have created the table, exactly as you have defined it and filled it up with 10000 rows, by using combination of the scripts in our test suite. You can find them here:

/mysql-test/suite/innodb_fts//t/

Namely, we accept only test cases written in pure SQL. We also accept test cases written in our test language, that you can find in mysql-test/ subdirectories and we accept scripts that can be run with sysbench. We also accept test cases written in our C API>

We do not accept test cases written in some strange programming language, like the one that you used.

We have filled the table up and then we ran the queries, with the following results:

mysql> select count(*) from test.t where match(content) against ('delete');
+----------+
| count(*) |
+----------+
| 10000 |
+----------+

mysql> select count(*) from test.t;
+----------+
| count(*) |
+----------+
| 10000 |
+----------+

We know exactly why we could not repeat your problem. Namely, we configure our InnoDB storage engine to be 100 % ACID compliant. The art of doing that is pretty complex and we are very skilled in doing it. You can also make your InnoDB fully ACID compliant, by following the instructions in our Reference Manual. You also have to chose the correct hardware to make it ACID compliant in any situation, so that all data pages, undo logs and redo logs are fully written and totally flushed. That makes the MySQL installation fully ACID compliant.

We have never ever run into situation where killing MySQL server (once or 100 times) would lead to any data corruption.

What you are reporting here is that your MySQL server is not configured for 100 % ACID security.

Hence, what you are reporting is not MySQL bug.

Can't repeat.