MDEV-27582 Fulltext DDL decrements the FTS_DOC_ID valuebb-10.2-MDEV-27582

- InnoDB FTS DDL decrements the FTS_DOC_ID when there is a
deleted marked record involved. FTS_DOC_ID must never be
reused. The purpose of FTS_DOC_ID is to be a unique row
identifier that will be changed whenever a fulltext indexed
column is updated.

4 files changed, 59 insertions, 1 deletions

diff --git a/mysql-test/suite/innodb_fts/r/innodb-fts-ddl.result b/mysql-test/suite/innodb_fts/r/innodb-fts-ddl.result
index 4efab17d441..bc3370b8daa 100644
--- a/mysql-test/suite/innodb_fts/r/innodb-fts-ddl.result
+++ b/mysql-test/suite/innodb_fts/r/innodb-fts-ddl.result
@@ -278,3 +278,32 @@ CREATE TABLE t1(a TEXT, FTS_DOC_ID BIGINT UNSIGNED NOT NULL UNIQUE) ENGINE=InnoD
 ALTER TABLE t1 ADD FULLTEXT(a), ALGORITHM=INPLACE;
 ERROR HY000: Got error 11 "Resource temporarily unavailable" from storage engine InnoDB
 DROP TABLE t1;
+#
+#  MDEV-27582 Fulltext DDL decrements the FTS_DOC_ID value
+#
+CREATE TABLE t1 (
+f1 INT NOT NULL PRIMARY KEY,
+f2 VARCHAR(64), FULLTEXT ft(f2)) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
+connect con1,localhost,root,,,;
+START TRANSACTION WITH CONSISTENT SNAPSHOT;
+connection default;
+DELETE FROM t1 WHERE f1 = 2;
+ALTER TABLE t1 DROP INDEX ft;
+ALTER TABLE t1 ADD FULLTEXT INDEX ft (f2);
+INSERT INTO t1 VALUES (3, 'innodb fts search');
+SET GLOBAL innodb_optimize_fulltext_only=ON;
+OPTIMIZE TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	optimize	status	OK
+SET GLOBAL innodb_ft_aux_table = 'test/t1';
+SELECT max(DOC_ID) FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE;
+max(DOC_ID)
+3
+SELECT * FROM t1 WHERE MATCH(f2) AGAINST("+innodb +search" IN BOOLEAN MODE);
+f1	f2
+3	innodb fts search
+DROP TABLE t1;
+disconnect con1;
+SET GLOBAL innodb_optimize_fulltext_only=OFF;
+SET GLOBAL innodb_ft_aux_table = default;
diff --git a/mysql-test/suite/innodb_fts/t/innodb-fts-ddl.opt b/mysql-test/suite/innodb_fts/t/innodb-fts-ddl.opt
index e6ae8d0fe0a..5d5cca1c6f2 100644
--- a/mysql-test/suite/innodb_fts/t/innodb-fts-ddl.opt
+++ b/mysql-test/suite/innodb_fts/t/innodb-fts-ddl.opt
@@ -1 +1,2 @@
 --enable-plugin-innodb-sys-tables
+--innodb_ft_index_table
diff --git a/mysql-test/suite/innodb_fts/t/innodb-fts-ddl.test b/mysql-test/suite/innodb_fts/t/innodb-fts-ddl.test
index 824e719b7a8..235f10e1f1d 100644
--- a/mysql-test/suite/innodb_fts/t/innodb-fts-ddl.test
+++ b/mysql-test/suite/innodb_fts/t/innodb-fts-ddl.test
@@ -339,3 +339,28 @@ EOF
 ALTER TABLE t1 ADD FULLTEXT(a), ALGORITHM=INPLACE;
 DROP TABLE t1;
 remove_file $MYSQLD_DATADIR/test/$fts_aux_file;
+
+--echo #
+--echo #  MDEV-27582 Fulltext DDL decrements the FTS_DOC_ID value
+--echo #
+CREATE TABLE t1 (
+	f1 INT NOT NULL PRIMARY KEY,
+        f2 VARCHAR(64), FULLTEXT ft(f2)) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
+connect(con1,localhost,root,,,);
+START TRANSACTION WITH CONSISTENT SNAPSHOT;
+
+connection default;
+DELETE FROM t1 WHERE f1 = 2;
+ALTER TABLE t1 DROP INDEX ft;
+ALTER TABLE t1 ADD FULLTEXT INDEX ft (f2);
+INSERT INTO t1 VALUES (3, 'innodb fts search');
+SET GLOBAL innodb_optimize_fulltext_only=ON;
+OPTIMIZE TABLE t1;
+SET GLOBAL innodb_ft_aux_table = 'test/t1';
+SELECT max(DOC_ID) FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE;
+SELECT * FROM t1 WHERE MATCH(f2) AGAINST("+innodb +search" IN BOOLEAN MODE);
+DROP TABLE t1;
+disconnect con1;
+SET GLOBAL innodb_optimize_fulltext_only=OFF;
+SET GLOBAL innodb_ft_aux_table = default;
diff --git a/storage/innobase/fts/fts0fts.cc b/storage/innobase/fts/fts0fts.cc
index 3cb15d64e91..e209f1adb14 100644
--- a/storage/innobase/fts/fts0fts.cc
+++ b/storage/innobase/fts/fts0fts.cc
@@ -2580,7 +2580,10 @@ fts_update_next_doc_id(
 	doc_id_t		doc_id)		/*!< in: DOC ID to set */
 {
 	table->fts->cache->synced_doc_id = doc_id;
-	table->fts->cache->next_doc_id = doc_id + 1;
+	/* Update the max value as next FTS_DOC_ID */
+	if (doc_id + 1 > table->fts->cache->next_doc_id) {
+		table->fts->cache->next_doc_id = doc_id + 1;
+	}
 
 	table->fts->cache->first_doc_id = table->fts->cache->next_doc_id;