MySQL Bugs: #108796: Contribution by Tencent: fts query may read unconsistent data

Bug #108796	Contribution by Tencent: fts query may read unconsistent data
Submitted:	17 Oct 2022 7:42	Modified:	17 Oct 2022 8:16
Reporter:	qingxin sun (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: FULLTEXT search	Severity:	S2 (Serious)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	Contribution, fulltext search, innodb

Description:
FTS query may read unconsistent data when background optimize thread do fts syncing.

Details: fts_sync_commit do these operations
1. clean fts cache clean   // fts_cache_clear
2. release ache lock       // rw_lock_x_unlock(&cache->lock);
3. commit the trx which write the fts cache data to auxiliary index table // fts_sql_commit

so if fts query hapends between operation 2 and 3, the fts query could not read
the data uncommitted.

How to repeat:
1. Insert some data adn commit
2. delay optimize thread before it commit the write trx(flush fts cache)
3. do fts query on the data just committed by operation 1，data not seen correctly

see following attachment patch file(based on 8.0.22 version).

Suggested fix:
fts sync commit should release cache lock after commit the trx.

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

Contribution: 0001-test-case-for-opt-thread-fts-sync.patch (application/octet-stream, text), 3.93 KiB.

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

Contribution: 0001-fts-sync-commit-bug-fix.patch (application/octet-stream, text), 1.28 KiB.

Hello qingxin sun,

Thank you for the report and contribution.

regards,
Umesh