Bug #27304661 MYSQL CRASH DOING SYNC INDEX ] [FATAL] INNODB: SEMAPHORE WAIT HAS LASTED > 600

PROBLEM
-------

Whenever an fts table is created it registers itself in a queue which
is operated by a background thread whose job is to optimize the
fts tables in background. Additionally we place these fts tables in
non-LRU list so that they cannot be evicted from cache. But in the
scenario when a node is brought up which is already having fts
tables ,we first try to load the fts tables in dictionary ,but we skip
the part where it is added in background queue and in non-LRU list because
the background thread is not yet created,so these tables are loaded
but they can be evicted from the cache. Now coming to the deadlock scenario

1. A Server background thread is trying to evict a table from the cache
    because the cache is full,so it scans the LRU list for the tables it can
    evict.It finds the fts table (because of the reason explained above)
    can be evicted and it takes the dict_sys->mutex (this is a system wide mutex)
    submits a request to  the background thread to remove this table from queue
    and waits it to be completed.

2.  In the mean time fts_optimize_thread() is processing another job
    in the queue and needs dict_sys->mutex for a small amount of time,
    but it cannot get it because it is blocked by the first background thread.

So Thread 1 is waiting for its job to be completed by Thread 2,whereas Thread 2
is waiting for dict_sys->mutex held by thread 1 ,causing the deadlock.

FIX

4 files changed, 132 insertions, 21 deletions

diff --git a/mysql-test/suite/innodb_fts/r/fulltext_table_evict.result b/mysql-test/suite/innodb_fts/r/fulltext_table_evict.result
new file mode 100644
index 00000000000..410bf2a34e1
--- /dev/null
+++ b/mysql-test/suite/innodb_fts/r/fulltext_table_evict.result
@@ -0,0 +1,19 @@
+#
+# Bug Bug #27304661 MYSQL CRASH DOING SYNC INDEX ]
+# [FATAL] INNODB: SEMAPHORE WAIT HAS LASTED > 600
+#
+CREATE TABLE t1 (
+id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
+f1 TEXT(500),
+FULLTEXT idx (f1)
+) ENGINE=InnoDB;
+insert into t1 (f1) values ('fjdhfsjhf'),('dhjfhjshfj'),('dhjafjhfj');
+# restart
+set @save_table_definition_cache=@@global.table_definition_cache;
+set @save_table_open_cache=@@global.table_open_cache;
+set global table_definition_cache=400;
+set global table_open_cache= 1024;
+SET GLOBAL DEBUG="+d,crash_if_fts_table_is_evicted";
+set @@global.table_definition_cache=@save_table_definition_cache;
+set @@global.table_open_cache=@save_table_open_cache;
+drop table t1;
diff --git a/mysql-test/suite/innodb_fts/t/fulltext_table_evict.test b/mysql-test/suite/innodb_fts/t/fulltext_table_evict.test
new file mode 100644
index 00000000000..245f00bc336
--- /dev/null
+++ b/mysql-test/suite/innodb_fts/t/fulltext_table_evict.test
@@ -0,0 +1,47 @@
+--echo #
+--echo # Bug Bug #27304661 MYSQL CRASH DOING SYNC INDEX ]
+--echo # [FATAL] INNODB: SEMAPHORE WAIT HAS LASTED > 600
+--echo #
+
+--source include/have_innodb.inc
+--source include/have_debug.inc
+
+CREATE TABLE t1 (
+       id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
+       f1 TEXT(500),
+       FULLTEXT idx (f1)
+       ) ENGINE=InnoDB;
+insert into t1 (f1) values ('fjdhfsjhf'),('dhjfhjshfj'),('dhjafjhfj');
+
+--source include/restart_mysqld.inc
+
+set @save_table_definition_cache=@@global.table_definition_cache;
+set @save_table_open_cache=@@global.table_open_cache;
+
+set global table_definition_cache=400;
+set global table_open_cache= 1024;
+
+SET GLOBAL DEBUG="+d,crash_if_fts_table_is_evicted";
+#Create 1000 tables, try the best to evict t1 .
+
+--disable_query_log
+let $loop=1000;
+while($loop)
+{
+  eval create table t_$loop(id int, name text(100), fulltext idxt_$loop(name) )engine=innodb;
+  dec $loop;
+}
+
+let $loop=1000;
+while($loop)
+{
+  eval drop table t_$loop;
+  dec $loop;
+}
+
+SET GLOBAL DEBUG="-d,crash_if_fts_table_is_evicted";
+--enable_query_log
+set @@global.table_definition_cache=@save_table_definition_cache;
+set @@global.table_open_cache=@save_table_open_cache;
+drop table t1;
+
diff --git a/storage/innobase/dict/dict0dict.cc b/storage/innobase/dict/dict0dict.cc
index ac5357ded3a..ff8b3d75010 100644
--- a/storage/innobase/dict/dict0dict.cc
+++ b/storage/innobase/dict/dict0dict.cc
@@ -1,6 +1,6 @@
 /*****************************************************************************
 
-Copyright (c) 1996, 2016, Oracle and/or its affiliates. All Rights Reserved.
+Copyright (c) 1996, 2018, Oracle and/or its affiliates. All Rights Reserved.
 Copyright (c) 2012, Facebook Inc.
 Copyright (c) 2013, 2018, MariaDB Corporation.
 
@@ -1453,6 +1453,13 @@ dict_make_room_in_cache(
 
 		if (dict_table_can_be_evicted(table)) {
 
+			DBUG_EXECUTE_IF("crash_if_fts_table_is_evicted",
+			{
+				  if (table->fts &&
+				      dict_table_has_fts_index(table)) {
+					ut_ad(0);
+				  }
+			};);
 			dict_table_remove_from_cache_low(table, TRUE);
 
 			++n_evicted;
diff --git a/storage/innobase/fts/fts0opt.cc b/storage/innobase/fts/fts0opt.cc
index 910721d8b32..cb1df7082f7 100644
--- a/storage/innobase/fts/fts0opt.cc
+++ b/storage/innobase/fts/fts0opt.cc
@@ -1,6 +1,6 @@
 /*****************************************************************************
 
-Copyright (c) 2007, 2017, Oracle and/or its affiliates. All Rights Reserved.
+Copyright (c) 2007, 2018, Oracle and/or its affiliates. All Rights Reserved.
 Copyright (c) 2016, 2018, MariaDB Corporation.
 
 This program is free software; you can redistribute it and/or modify it under
@@ -41,6 +41,9 @@ Completed 2011/7/10 Sunny and Jimmy Yang
 /** The FTS optimize thread's work queue. */
 static ib_wqueue_t* fts_optimize_wq;
 
+/** The FTS vector to store fts_slot_t */
+static ib_vector_t*  fts_slots;
+
 /** Time to wait for a message. */
 static const ulint FTS_QUEUE_WAIT_IN_USECS = 5000000;
 
@@ -2976,9 +2979,6 @@ fts_optimize_thread(
 /*================*/
 	void*		arg)			/*!< in: work queue*/
 {
-	mem_heap_t*	heap;
-	ib_vector_t*	tables;
-	ib_alloc_t*	heap_alloc;
 	ulint		current = 0;
 	ibool		done = FALSE;
 	ulint		n_tables = 0;
@@ -2988,10 +2988,10 @@ fts_optimize_thread(
 	ut_ad(!srv_read_only_mode);
 	my_thread_init();
 
-	heap = mem_heap_create(sizeof(dict_table_t*) * 64);
-	heap_alloc = ib_heap_allocator_create(heap);
+	ut_ad(fts_slots);
 
-	tables = ib_vector_create(heap_alloc, sizeof(fts_slot_t), 4);
+	/* Assign number of tables added in fts_slots_t to n_tables */
+	n_tables = ib_vector_size(fts_slots);
 
 	while (!done && srv_shutdown_state == SRV_SHUTDOWN_NONE) {
 
@@ -3005,10 +3005,10 @@ fts_optimize_thread(
 
 			fts_slot_t*	slot;
 
-			ut_a(ib_vector_size(tables) > 0);
+			ut_a(ib_vector_size(fts_slots) > 0);
 
 			slot = static_cast<fts_slot_t*>(
-				ib_vector_get(tables, current));
+				ib_vector_get(fts_slots, current));
 
 			/* Handle the case of empty slots. */
 			if (slot->state != FTS_STATE_EMPTY) {
@@ -3021,8 +3021,8 @@ fts_optimize_thread(
 			++current;
 
 			/* Wrap around the counter. */
-			if (current >= ib_vector_size(tables)) {
-				n_optimize = fts_optimize_how_many(tables);
+			if (current >= ib_vector_size(fts_slots)) {
+				n_optimize = fts_optimize_how_many(fts_slots);
 
 				current = 0;
 			}
@@ -3036,7 +3036,7 @@ fts_optimize_thread(
 
 			/* Timeout ? */
 			if (msg == NULL) {
-				if (fts_is_sync_needed(tables)) {
+				if (fts_is_sync_needed(fts_slots)) {
 					fts_need_sync = true;
 				}
 
@@ -3057,7 +3057,7 @@ fts_optimize_thread(
 			case FTS_MSG_ADD_TABLE:
 				ut_a(!done);
 				if (fts_optimize_new_table(
-					tables,
+					fts_slots,
 					static_cast<dict_table_t*>(
 					msg->ptr))) {
 					++n_tables;
@@ -3067,7 +3067,7 @@ fts_optimize_thread(
 			case FTS_MSG_OPTIMIZE_TABLE:
 				if (!done) {
 					fts_optimize_start_table(
-						tables,
+						fts_slots,
 						static_cast<dict_table_t*>(
 						msg->ptr));
 				}
@@ -3075,7 +3075,7 @@ fts_optimize_thread(
 
 			case FTS_MSG_DEL_TABLE:
 				if (fts_optimize_del_table(
-					tables, static_cast<fts_msg_del_t*>(
+					fts_slots, static_cast<fts_msg_del_t*>(
 						msg->ptr))) {
 					--n_tables;
 				}
@@ -3098,7 +3098,7 @@ fts_optimize_thread(
 			mem_heap_free(msg->heap);
 
 			if (!done) {
-				n_optimize = fts_optimize_how_many(tables);
+				n_optimize = fts_optimize_how_many(fts_slots);
 			} else {
 				n_optimize = 0;
 			}
@@ -3110,11 +3110,11 @@ fts_optimize_thread(
 	if (n_tables > 0) {
 		ulint	i;
 
-		for (i = 0; i < ib_vector_size(tables); i++) {
+		for (i = 0; i < ib_vector_size(fts_slots); i++) {
 			fts_slot_t*	slot;
 
 			slot = static_cast<fts_slot_t*>(
-				ib_vector_get(tables, i));
+				ib_vector_get(fts_slots, i));
 
 			if (slot->state != FTS_STATE_EMPTY) {
 				fts_optimize_sync_table(slot->table_id);
@@ -3122,7 +3122,7 @@ fts_optimize_thread(
 		}
 	}
 
-	ib_vector_free(tables);
+	ib_vector_free(fts_slots);
 
 	ib::info() << "FTS optimize thread exiting.";
 
@@ -3142,14 +3142,52 @@ void
 fts_optimize_init(void)
 /*===================*/
 {
+	mem_heap_t*	heap;
+	ib_alloc_t*     heap_alloc;
+	dict_table_t*   table;
+
 	ut_ad(!srv_read_only_mode);
 
 	/* For now we only support one optimize thread. */
 	ut_a(fts_optimize_wq == NULL);
 
+	/* Create FTS optimize work queue */
 	fts_optimize_wq = ib_wqueue_create();
-	fts_opt_shutdown_event = os_event_create(0);
 	ut_a(fts_optimize_wq != NULL);
+
+	/* Create FTS vector to store fts_slot_t */
+	heap = mem_heap_create(sizeof(dict_table_t*) * 64);
+	heap_alloc = ib_heap_allocator_create(heap);
+	fts_slots = ib_vector_create(heap_alloc, sizeof(fts_slot_t), 4);
+
+	/* Add fts tables to the fts_slots vector which were skipped during restart */
+	std::vector<dict_table_t*> table_vector;
+	std::vector<dict_table_t*>::iterator it;
+
+	mutex_enter(&dict_sys->mutex);
+	for (table = UT_LIST_GET_FIRST(dict_sys->table_LRU);
+             table != NULL;
+             table = UT_LIST_GET_NEXT(table_LRU, table)) {
+                if (table->fts &&
+                    dict_table_has_fts_index(table)) {
+			if (fts_optimize_new_table(fts_slots,
+						   table)){
+				table_vector.push_back(table);
+			}
+		}
+	}
+
+	/* It is better to call dict_table_prevent_eviction()
+	outside the above loop because it operates on
+	dict_sys->table_LRU list.*/
+	for (it=table_vector.begin();it!=table_vector.end();++it) {
+		dict_table_prevent_eviction(*it);
+	}
+
+	mutex_exit(&dict_sys->mutex);
+	table_vector.clear();
+
+	fts_opt_shutdown_event = os_event_create(0);
 	last_check_sync_time = ut_time();
 
 	os_thread_create(fts_optimize_thread, fts_optimize_wq, NULL);