MDEV-19073 FTS row mismatch after crash recovery

InnoDB stores synced_doc_id + 1 value in FTS_CONFIG table. But
while reading the synced doc id from FTS_CONFIG table after restart,
InnoDB should read synced_doc_id - 1 to get the actual synced
doc id value.

1 files changed, 55 insertions, 0 deletions

diff --git a/mysql-test/suite/innodb_fts/t/crash_recovery.test b/mysql-test/suite/innodb_fts/t/crash_recovery.test
index ec055930672..861dfdfd658 100644
--- a/mysql-test/suite/innodb_fts/t/crash_recovery.test
+++ b/mysql-test/suite/innodb_fts/t/crash_recovery.test
@@ -6,6 +6,7 @@
 --source include/have_innodb.inc
 # The embedded server tests do not support restarting.
 --source include/not_embedded.inc
+--source include/maybe_debug.inc
 
 # Following are test for crash recovery on FTS index, the first scenario
 # is for bug Bug #14586855 INNODB: FAILING ASSERTION: (DICT_INDEX_GET_N_UNIQUE(
@@ -62,13 +63,17 @@ INSERT INTO articles (title,body) VALUES
         ('MySQL vs. YourSQL','In the following database comparison ...'),
         ('MySQL Security','When configured properly, MySQL ...');
 
+connect(dml, localhost, root,,);
 BEGIN;
 
 INSERT INTO articles (title,body) VALUES
         ('MySQL Tutorial','DBMS stands for DataBase ...');
+connection default;
 
 --source include/kill_and_restart_mysqld.inc
 
+disconnect dml;
+
 # This insert will re-initialize the Doc ID counter, it should not crash
 INSERT INTO articles (title,body) VALUES
         ('MySQL Tutorial','DBMS stands for DataBase ...');
@@ -101,12 +106,55 @@ INSERT INTO articles VALUES
         (5, 6, 'MySQL vs. YourSQL','In the following database comparison ...'),
         (7, 4, 'MySQL Security','When configured properly, MySQL ...');
 
+connect(dml, localhost, root,,);
 BEGIN;
 
 INSERT INTO articles VALUES
         (100, 200, 'MySQL Tutorial','DBMS stands for DataBase ...');
 
+connect(dml2, localhost, root,,);
+
+--echo #
+--echo # MDEV-19073 FTS row mismatch after crash recovery
+--echo #
+
+CREATE TABLE mdev19073(id SERIAL, title VARCHAR(200), body TEXT,
+                       FULLTEXT(title,body)) ENGINE=InnoDB;
+INSERT INTO mdev19073 (title, body) VALUES
+	('MySQL Tutorial', 'DBMS stands for Database...');
+CREATE FULLTEXT INDEX idx ON mdev19073(title, body);
+CREATE TABLE mdev19073_2 LIKE mdev19073;
+if ($have_debug)
+{
+--disable_query_log
+SET @saved_dbug = @@debug_dbug;
+SET DEBUG_DBUG = '+d,fts_instrument_sync_debug';
+--enable_query_log
+}
+INSERT INTO mdev19073_2 (title, body) VALUES
+	('MySQL Tutorial', 'DBMS stands for Database...');
+if ($have_debug)
+{
+--disable_query_log
+SET DEBUG_DBUG = @saved_dbug;
+--enable_query_log
+}
+
+INSERT INTO mdev19073 (title, body) VALUES
+	('MariaDB Tutorial', 'DB means Database ...');
+INSERT INTO mdev19073_2 (title, body) VALUES
+	('MariaDB Tutorial', 'DB means Database ...');
+
+# Should return 2 rows
+SELECT * FROM mdev19073 WHERE MATCH (title, body)
+AGAINST ('Database' IN NATURAL LANGUAGE MODE);
+SELECT * FROM mdev19073_2 WHERE MATCH (title, body)
+AGAINST ('Database' IN NATURAL LANGUAGE MODE);
+
+connection default;
 --source include/kill_and_restart_mysqld.inc
+disconnect dml;
+disconnect dml2;
 
 # This would re-initialize the FTS index and do the re-tokenization
 # of above records
@@ -116,3 +164,10 @@ SELECT * FROM articles WHERE MATCH (title, body)
         AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
 
 DROP TABLE articles;
+
+# Should return 2 rows
+SELECT * FROM mdev19073 WHERE MATCH (title, body)
+AGAINST ('Database' IN NATURAL LANGUAGE MODE);
+SELECT * FROM mdev19073_2 WHERE MATCH (title, body)
+AGAINST ('Database' IN NATURAL LANGUAGE MODE);
+DROP TABLE mdev19073, mdev19073_2;