MySQL Bugs: #114156: Contribution by Tencent: fts_sync

Bug #114156	Contribution by Tencent: fts_sync_commit is not crash safe
Submitted:	28 Feb 2024 11:52	Modified:	1 Apr 2024 20:34
Reporter:	Yin Peng (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: FULLTEXT search	Severity:	S3 (Non-critical)
Version:	8.0.36	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	Contribution

Description:
Function 'fts_cmp_set_sync_doc_id' and 'fts_sql_commit' use different trx_t objects in function 'fts_sync_commit', fts_sync_commit is not crash safe.

How to repeat:
How to repeat:
A debug point should be added in function 'fts_sync_commit' before calling 'fts_sql_commit':

diff --git a/storage/innobase/fts/fts0fts.cc b/storage/innobase/fts/fts0fts.cc
index 2e36facb6ce..39c25e99230 100644
--- a/storage/innobase/fts/fts0fts.cc
+++ b/storage/innobase/fts/fts0fts.cc
@@ -4266,6 +4266,7 @@ static void fts_sync_index_reset(fts_index_cache_t *index_cache) {
   rw_lock_x_unlock(&cache->lock);
 
   if (error == DB_SUCCESS) {
+    DBUG_EXECUTE_IF("fts_crash_before_commit_sync", { DBUG_SUICIDE(); });
     fts_sql_commit(trx);
 
   } else if (error != DB_SUCCESS) {

Test script:

source include/have_debug.inc;
source include/not_valgrind.inc;

CREATE TABLE opening_lines (
       id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
       opening_line TEXT(500),
       author VARCHAR(200),
       title VARCHAR(200)
       ) ENGINE=InnoDB;

CREATE FULLTEXT INDEX idx ON opening_lines(opening_line);
CREATE FULLTEXT INDEX ft_idx1 ON opening_lines(title);

INSERT INTO opening_lines(opening_line,author,title) VALUES
       ('Call me Ishmael.','Herman Melville','Moby-Dick'),
       ('A screaming comes across the sky.','Thomas Pynchon','Gravity\'s Rainbow'),
       ('I am an invisible man.','Ralph Ellison','Invisible Man'),
       ('Where now? Who now? When now?','Samuel Beckett','The Unnamable'),
       ('It was love at first sight.','Joseph Heller','Catch-22'),
       ('All this happened, more or less.','Kurt Vonnegut','Slaughterhouse-Five'),
       ('Mrs. Dalloway said she would buy the flowers herself.','Virginia Woolf','Mrs. Dalloway'),
       ('It was a pleasure to burn.','Ray Bradbury','Fahrenheit 451');

set global innodb_ft_aux_table='test/opening_lines';
select * from information_schema.innodb_ft_config;

SELECT * FROM opening_lines WHERE MATCH(opening_line) AGAINST('Ishmael');
SELECT * FROM opening_lines WHERE MATCH(opening_line) AGAINST('invisible');
select * from opening_lines;

set global innodb_optimize_fulltext_only=ON;
set global debug='+d,fts_crash_before_commit_sync';
--exec echo "restart" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
--error 2013
optimize table opening_lines;

--enable_reconnect
--source include/wait_until_connected_again.inc

SELECT * FROM opening_lines WHERE MATCH(opening_line) AGAINST('Ishmael');
SELECT * FROM opening_lines WHERE MATCH(opening_line) AGAINST('invisible');
select * from opening_lines;

After restart, you can see the results of two queries are incorrect.

Suggested fix:
Use the same trx_t object for 'fts_sql_commit' and 'fts_cmp_set_sync_doc_id' in function 'in function 'fts_sync_commit'.

Use the same trx_t object for 'fts_sql_commit' and 'fts_cmp_set_sync_doc_id' in function 'in function 'fts_sync_commit'.

(*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: patch.txt (text/plain), 3.04 KiB.

Hello yin peng,

Thank you for the report and contribution.

regards,
Umesh

Posted by developer:
 
Fixed as of the upcoming MySQL Server 8.0.37 and 8.4.0 releases, and here's the proposed changelog entry from the documentation team:

Some FTS operations on tables with FTS indexes could have caused
inconsistent results. For example, if the server terminated while
synchronizing the FTS cache or when synchronization occurred concurrently
with another FTS operation.

Our thanks to Yin Peng and the Tencent team for the contribution.

Thank you for the bug report.

Posted by developer:
 
Bug reopened by merge info, no change to release note.