| Bug #116212 | fts_delete is not crash-safe if binlog is disabled | ||
|---|---|---|---|
| Submitted: | 24 Sep 2024 9:29 | Modified: | 3 Dec 2024 10:06 |
| Reporter: | Yin Peng (OCA) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: FULLTEXT search | Severity: | S3 (Non-critical) |
| Version: | 8.4.2 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | Contribution | ||
[24 Sep 2024 9:29]
Yin Peng
Use the original trx object if it's active. (*) 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), 1.74 KiB.
[24 Sep 2024 11:53]
MySQL Verification Team
Hello yin peng, Thank you for the report and contribution. regards, Umesh
[3 Dec 2024 10:06]
Edward Gilmore
Posted by developer: Added the following note to the MySQL Server 9.2.0 release notes: If binary logging was disabled, it was possible for full-text search queries to be incorrect after a server crash. Our thanks to Yin Peng and the Tencent team for the contribution.
Description: In function 'fts_commit_table' a new trx object is allocated to handle the insertion of fts_*_deleted. When binlog is disabled, an inconsistency may occur between fulltext index and clustered index. How to repeat: A debug point should be added in function 'fts_commit_table': diff --git a/storage/innobase/fts/fts0fts.cc b/storage/innobase/fts/fts0fts.cc index c0e16dfd93d..ba48a628c63 100644 --- a/storage/innobase/fts/fts0fts.cc +++ b/storage/innobase/fts/fts0fts.cc @@ -3239,6 +3239,10 @@ dberr_t fts_create_doc_id(dict_table_t *table, dtuple_t *row, trx_free_for_background(trx); + DBUG_EXECUTE_IF("fts_sync_cache_and_crash_after_commit_table", { + DBUG_SUICIDE(); + }); + return (error); } Run test script(option '--mysqld=--skip-log-bin' should be specified): create table opening_lines ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, opening_line TEXT(500), author VARCHAR(200), title VARCHAR(200), FULLTEXT idx (opening_line) ) ENGINE=InnoDB; insert into opening_lines(opening_line,author,title) VALUES ('A screaming comes across the sky.','Thomas Pynchon','Gravity\'s Rainbow'); select * from opening_lines WHERE MATCH(opening_line) AGAINST('screaming'); --exec echo "wait" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect set debug='+d,fts_sync_cache_and_crash_after_commit_table'; --error 2013 delete from opening_lines; --source include/wait_until_disconnected.inc --echo # Restart the master server --exec echo "restart" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect --source include/wait_until_connected_again.inc select * from opening_lines; select * from opening_lines WHERE MATCH(opening_line) AGAINST('screaming'); No row is returned in the result of the full-text search, although there should be one. Suggested fix: Use the original trx object if it's active.