Bug #70333 InnoDB Fulltext search doesn't find records when savepoints are involved
Submitted: 13 Sep 2013 14:15 Modified: 11 Oct 2013 16:15
Reporter: Saverio Miroddi Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: FULLTEXT search Severity:S3 (Non-critical)
Version:5.6.13 OS:Any
Assigned to: CPU Architecture:Any

[13 Sep 2013 14:15] Saverio Miroddi 
Description:
I've found a case where the InnoDB FULLLTEXT search doesn't found the records in an FT search.

Test case provided.

Notes about the test case:
- Removing the SAVEPOINT statements solves the problem
- Any record added after the RELEASE SAVEPOINT is not affected by the problem
- Note that adding another filling records doesn't solve the problem.

How to repeat:
DROP TABLE IF EXISTS test_table;

CREATE TABLE test_table (
  id int(11) NOT NULL,
  keywords varchar(2048) NOT NULL,
  PRIMARY KEY (id),
  FULLTEXT KEY index_test_table_on_keywords (keywords)
) ENGINE=InnoDB;

BEGIN;

INSERT INTO test_table( id, keywords ) VALUES( 40, '' );

SAVEPOINT active_record_1;

INSERT INTO test_table( id, keywords ) VALUES( 38, 'has_amount_due' );

RELEASE SAVEPOINT active_record_1;

COMMIT;

################ TESTING CODE ####################

SELECT '>>>>> Table contents:' ``;

SELECT * FROM test_table;

SELECT '>>>>> Not found!:' ``;

SELECT * FROM test_table WHERE MATCH(keywords) AGAINST('has_amount_due');

SELECT '>>>>> Optimizing table - still not found!:' ``;

OPTIMIZE TABLE test_table;
SELECT * FROM test_table WHERE MATCH(keywords) AGAINST('has_amount_due');

SELECT '>>>>> Copied table - record found!:' ``;

DROP TABLE IF EXISTS test_table_copy;
CREATE TABLE test_table_copy LIKE test_table;
INSERT INTO test_table_copy SELECT * FROM test_table;
SELECT * FROM test_table_copy WHERE MATCH(keywords) AGAINST('has_amount_due');
[14 Sep 2013 8:04] MySQL Verification Team 
Hello Saverio,

Thank you for the bug report and test case.
Verified as described.

Thanks,
Umesh
[11 Oct 2013 16:15] Paul DuBois 
Noted in 5.6.15, 5.7.3 changelogs.

InnoDB full-text searches failed to find records within transactions
that included savepoints.
[4 Dec 2013 11:53] Laurynas Biveinis 
5.6$ bzr log -r 5506
------------------------------------------------------------
revno: 5506
committer: Shaohua Wang <shaohua.wang@oracle.com>
branch nick: mysql-5.6-bugfix2
timestamp: Wed 2013-10-09 19:00:56 +0800
message:
  BUG#17458835 - INNODB FULLTEXT SEARCH DOESN'T FIND RECORDS WHEN SAVEPOINTS ARE INVOLVED
  
  Analysis & Solution:
  we simply free a savepoint when releasing a savepoint in which holds all rows
  including newly inserted rows, so we get rows lost.
  
  The solution is putting the last savepoint's rows to top savepoint.
  
  rb://3529 approved by Jimmy.Yang