MDEV-22811 DDL fails to drop and re-create FTS index

Problem:
========
- InnoDB clears the fts resource when last FTS index is being dropped
if the table has user defined FTS_DOC_ID. While creating the new fts
index, InnoDB expects to have FTS resources.

Fix:
===
fts_drop_index(): Removed the fts resource clear.

fts_clear_all(): Clear the fts resource when there are no new fts
index to be added.

commit_cache_norebuild(), row_merge_drop_indexes():
Tries to call fts resource after removing associated fts index
from table object

6 files changed, 51 insertions, 24 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 983f254cf90..f23813aed48 100644
--- a/mysql-test/suite/innodb_fts/r/innodb-fts-ddl.result
+++ b/mysql-test/suite/innodb_fts/r/innodb-fts-ddl.result
@@ -227,3 +227,10 @@ id	title	body
 1	MySQL Tutorial	DBMS stands for DataBase ...
 3	Optimizing MySQL	In this tutorial we will show ...
 DROP TABLE articles;
+#
+# MDEV-22811 DDL fails to drop and re-create FTS index
+#
+CREATE TABLE t1 (FTS_DOC_ID BIGINT UNSIGNED PRIMARY KEY,
+f1 VARCHAR(200),FULLTEXT fidx(f1))engine=innodb;
+ALTER TABLE t1 DROP index fidx, ADD FULLTEXT INDEX(f1);
+DROP TABLE t1;
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 10dc1462c98..cd292803fb3 100644
--- a/mysql-test/suite/innodb_fts/t/innodb-fts-ddl.test
+++ b/mysql-test/suite/innodb_fts/t/innodb-fts-ddl.test
@@ -270,3 +270,11 @@ SELECT * FROM articles WHERE MATCH (title, body)
 	AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
 
 DROP TABLE articles;
+
+--echo #
+--echo # MDEV-22811 DDL fails to drop and re-create FTS index
+--echo #
+CREATE TABLE t1 (FTS_DOC_ID BIGINT UNSIGNED PRIMARY KEY,
+		 f1 VARCHAR(200),FULLTEXT fidx(f1))engine=innodb;
+ALTER TABLE t1 DROP index fidx, ADD FULLTEXT INDEX(f1);
+DROP TABLE t1;
diff --git a/storage/innobase/fts/fts0fts.cc b/storage/innobase/fts/fts0fts.cc
index dd4938dd019..739b402314c 100644
--- a/storage/innobase/fts/fts0fts.cc
+++ b/storage/innobase/fts/fts0fts.cc
@@ -805,6 +805,29 @@ fts_check_cached_index(
 	return(TRUE);
 }
 
+/** Clear all fts resources when there is no internal DOC_ID
+and there are no new fts index to add.
+@param[in,out]	table	table  where fts is to be freed
+@param[in]	trx	transaction to drop all fts tables */
+void fts_clear_all(dict_table_t *table, trx_t *trx)
+{
+  if (DICT_TF2_FLAG_IS_SET(table, DICT_TF2_FTS_HAS_DOC_ID) ||
+      !table->fts ||
+      !ib_vector_is_empty(table->fts->indexes))
+    return;
+
+  for (const dict_index_t *index= dict_table_get_first_index(table);
+       index; index= dict_table_get_next_index(index))
+    if (index->type & DICT_FTS)
+      return;
+
+  fts_optimize_remove_table(table);
+
+  fts_drop_tables(trx, table);
+  fts_free(table);
+  DICT_TF2_FLAG_UNSET(table, DICT_TF2_FTS);
+}
+
 /*******************************************************************//**
 Drop auxiliary tables related to an FTS index
 @return DB_SUCCESS or error number */
@@ -821,9 +844,10 @@ fts_drop_index(
 	ut_a(indexes);
 
 	if ((ib_vector_size(indexes) == 1
-	    && (index == static_cast<dict_index_t*>(
-			ib_vector_getp(table->fts->indexes, 0))))
-	   || ib_vector_is_empty(indexes)) {
+	     && (index == static_cast<dict_index_t*>(
+			ib_vector_getp(table->fts->indexes, 0)))
+	     && DICT_TF2_FLAG_IS_SET(table, DICT_TF2_FTS_HAS_DOC_ID))
+	    || ib_vector_is_empty(indexes)) {
 		doc_id_t	current_doc_id;
 		doc_id_t	first_doc_id;
 
@@ -833,27 +857,6 @@ fts_drop_index(
 
 		DICT_TF2_FLAG_UNSET(table, DICT_TF2_FTS);
 
-		/* If Doc ID column is not added internally by FTS index,
-		we can drop all FTS auxiliary tables. Otherwise, we will
-		need to keep some common table such as CONFIG table, so
-		as to keep track of incrementing Doc IDs */
-		if (!DICT_TF2_FLAG_IS_SET(
-			table, DICT_TF2_FTS_HAS_DOC_ID)) {
-
-			err = fts_drop_tables(trx, table);
-
-			err = fts_drop_index_tables(trx, index);
-
-			while (index->index_fts_syncing
-				&& !trx_is_interrupted(trx)) {
-				DICT_BG_YIELD(trx);
-			}
-
-			fts_free(table);
-
-			return(err);
-		}
-
 		while (index->index_fts_syncing
 		       && !trx_is_interrupted(trx)) {
 			DICT_BG_YIELD(trx);
diff --git a/storage/innobase/handler/handler0alter.cc b/storage/innobase/handler/handler0alter.cc
index d057afef9b5..db161ba50d8 100644
--- a/storage/innobase/handler/handler0alter.cc
+++ b/storage/innobase/handler/handler0alter.cc
@@ -7922,6 +7922,7 @@ commit_cache_norebuild(
 			dict_index_remove_from_cache(index->table, index);
 		}
 
+		fts_clear_all(ctx->old_table, trx);
 		trx_commit_for_mysql(trx);
 	}
 
diff --git a/storage/innobase/include/fts0fts.h b/storage/innobase/include/fts0fts.h
index c2a89c25d16..b7d3451e887 100644
--- a/storage/innobase/include/fts0fts.h
+++ b/storage/innobase/include/fts0fts.h
@@ -983,4 +983,10 @@ fts_trx_t*
 fts_trx_create(
 	trx_t*  trx);
 
+/** Clear all fts resources when there is no internal DOC_ID
+and there are no new fts index to add.
+@param[in,out]  table   table  where fts is to be freed
+@param[in]      trx     transaction to drop all fts tables */
+void fts_clear_all(dict_table_t *table, trx_t *trx);
+
 #endif /*!< fts0fts.h */
diff --git a/storage/innobase/row/row0merge.cc b/storage/innobase/row/row0merge.cc
index e44b9aae626..5cbfe0e3ddb 100644
--- a/storage/innobase/row/row0merge.cc
+++ b/storage/innobase/row/row0merge.cc
@@ -3815,6 +3815,7 @@ row_merge_drop_indexes(
 			ut_error;
 		}
 
+		fts_clear_all(table, trx);
 		return;
 	}
 
@@ -3867,6 +3868,7 @@ row_merge_drop_indexes(
 		}
 	}
 
+	fts_clear_all(table, trx);
 	table->drop_aborted = FALSE;
 	ut_d(dict_table_check_for_dup_indexes(table, CHECK_ALL_COMPLETE));
 }