MDEV-27233 Server hangs when using --init-file which loads Spider and creates a Spider tablebb-10.8-MDEV-27233

Spider waits until the server initialization has been completed
(i.e., mysqld_server_started has been set to 1) before creating the
Spider system tables. Spider also wait until the system tables have
been created before instantiating ha_spider.

These waits leads to dead lock when a non-system Spider table is
created by --in-file. This is because queries passed by --in-file
are executed during the server initialization and creating the
non-system Spider causes an instantiation of ha_spider.

In the first place, there seems to be no need for Spider to do such
a synchronization. Thus, we simply remove the synchronization.

11 files changed, 74 insertions, 253 deletions

diff --git a/storage/spider/mysql-test/spider/bugfix/r/index.result b/storage/spider/mysql-test/spider/bugfix/r/index.result
index 2aa0b56ef06..5e58efbd858 100644
--- a/storage/spider/mysql-test/spider/bugfix/r/index.result
+++ b/storage/spider/mysql-test/spider/bugfix/r/index.result
@@ -1,21 +1,4 @@
 for master_1
-connect  master_1, localhost, root, , , $MASTER_1_MYPORT, $MASTER_1_MYSOCK;
-connection master_1;
-CALL mtr.add_suppression("unknown variable");
-SET SESSION sql_log_bin= 0;
-INSTALL PLUGIN spider SONAME 'ha_spider';
-CREATE SERVER s_1 FOREIGN DATA WRAPPER mysql OPTIONS (
-HOST 'localhost',
-DATABASE 'auto_test_local',
-USER 'root',
-PASSWORD '',
-SOCKET '$MASTER_1_MYSOCK'
-    );
-SET spider_internal_sql_log_off= 0;
-SET spider_direct_order_limit= 10000;
-SET spider_init_sql_alloc_size= 1;
-Warnings:
-Warning	1287	'@@spider_init_sql_alloc_size' is deprecated and will be removed in a future release
 for child2
 for child3
 create database auto_test_local;
@@ -79,27 +62,5 @@ a	b
 drop table ts, auto_test_local.t;
 drop database auto_test_local;
 for master_1
-connection master_1;
-DROP FUNCTION spider_flush_table_mon_cache;
-DROP FUNCTION spider_copy_tables;
-DROP FUNCTION spider_ping_table;
-DROP FUNCTION spider_bg_direct_sql;
-DROP FUNCTION spider_direct_sql;
-UNINSTALL PLUGIN spider_wrapper_protocols;
-UNINSTALL PLUGIN spider_alloc_mem;
-UNINSTALL PLUGIN spider;
-Warnings:
-Warning	1620	Plugin is busy and will be uninstalled on shutdown
-DROP TABLE IF EXISTS mysql.spider_xa;
-DROP TABLE IF EXISTS mysql.spider_xa_member;
-DROP TABLE IF EXISTS mysql.spider_xa_failed_log;
-DROP TABLE IF EXISTS mysql.spider_tables;
-DROP TABLE IF EXISTS mysql.spider_link_mon_servers;
-DROP TABLE IF EXISTS mysql.spider_link_failed_log;
-DROP TABLE IF EXISTS mysql.spider_table_position_for_recovery;
-DROP TABLE IF EXISTS mysql.spider_table_sts;
-DROP TABLE IF EXISTS mysql.spider_table_crd;
-DROP SERVER s_1;
-disconnect master_1;
 for child2
 for child3
diff --git a/storage/spider/mysql-test/spider/bugfix/r/mdev_27233.result b/storage/spider/mysql-test/spider/bugfix/r/mdev_27233.result
new file mode 100644
index 00000000000..53622bb6a23
--- /dev/null
+++ b/storage/spider/mysql-test/spider/bugfix/r/mdev_27233.result
@@ -0,0 +1,3 @@
+#
+# MDEV-27233 Server hangs when using --init-file which loads Spider and creates a Spider table
+#
diff --git a/storage/spider/mysql-test/spider/bugfix/t/index.test b/storage/spider/mysql-test/spider/bugfix/t/index.test
index 5167e40e8c4..384f0f3c890 100644
--- a/storage/spider/mysql-test/spider/bugfix/t/index.test
+++ b/storage/spider/mysql-test/spider/bugfix/t/index.test
@@ -1,4 +1,9 @@
+--disable_query_log
+--disable_result_log
 --source ../t/test_init.inc
+--enable_query_log
+--enable_result_log
+
 create database auto_test_local;
 set spider_same_server_link= on;
 
@@ -45,4 +50,9 @@ select a, b from ts where a > 0 and b = 'g' order by a;
 drop table ts, auto_test_local.t;
 
 drop database auto_test_local;
+
+--disable_query_log
+--disable_result_log
 --source ../t/test_deinit.inc
+--enable_query_log
+--enable_result_log
diff --git a/storage/spider/mysql-test/spider/bugfix/t/mdev_27233.opt b/storage/spider/mysql-test/spider/bugfix/t/mdev_27233.opt
new file mode 100644
index 00000000000..7bc1c2127a6
--- /dev/null
+++ b/storage/spider/mysql-test/spider/bugfix/t/mdev_27233.opt
@@ -0,0 +1 @@
+--init-file=$MYSQL_TEST_DIR/../storage/spider/mysql-test/spider/bugfix/t/mdev_27233.sql
diff --git a/storage/spider/mysql-test/spider/bugfix/t/mdev_27233.sql b/storage/spider/mysql-test/spider/bugfix/t/mdev_27233.sql
new file mode 100644
index 00000000000..2be9ec1fad6
--- /dev/null
+++ b/storage/spider/mysql-test/spider/bugfix/t/mdev_27233.sql
@@ -0,0 +1,3 @@
+INSTALL SONAME 'ha_spider.so';
+USE test;
+CREATE TABLE t (c INT) ENGINE=SPIDER;
diff --git a/storage/spider/mysql-test/spider/bugfix/t/mdev_27233.test b/storage/spider/mysql-test/spider/bugfix/t/mdev_27233.test
new file mode 100644
index 00000000000..79603439d35
--- /dev/null
+++ b/storage/spider/mysql-test/spider/bugfix/t/mdev_27233.test
@@ -0,0 +1,3 @@
+--echo #
+--echo # MDEV-27233 Server hangs when using --init-file which loads Spider and creates a Spider table
+--echo #
diff --git a/storage/spider/mysql-test/spider/include/deinit_spider.inc b/storage/spider/mysql-test/spider/include/deinit_spider.inc
index dd474c59bc7..cfca8dc2d50 100644
--- a/storage/spider/mysql-test/spider/include/deinit_spider.inc
+++ b/storage/spider/mysql-test/spider/include/deinit_spider.inc
@@ -20,7 +20,6 @@ if (`SELECT IF($PLUGIN_VERSION = 3, 1, 0)`)
   if ($HAS_REWRITE)
   {
     DROP FUNCTION spider_flush_rewrite_cache;
-    UNINSTALL PLUGIN spider_rewrite;
     DROP TABLE IF EXISTS mysql.spider_rewrite_tables;
     DROP TABLE IF EXISTS mysql.spider_rewrite_table_tables;
     DROP TABLE IF EXISTS mysql.spider_rewrite_table_partitions;
@@ -33,9 +32,7 @@ DROP FUNCTION spider_copy_tables;
 DROP FUNCTION spider_ping_table;
 DROP FUNCTION spider_bg_direct_sql;
 DROP FUNCTION spider_direct_sql;
-UNINSTALL PLUGIN spider_wrapper_protocols;
-UNINSTALL PLUGIN spider_alloc_mem;
-UNINSTALL PLUGIN spider;
+UNINSTALL SONAME "ha_spider";
 DROP TABLE IF EXISTS mysql.spider_xa;
 DROP TABLE IF EXISTS mysql.spider_xa_member;
 DROP TABLE IF EXISTS mysql.spider_xa_failed_log;
diff --git a/storage/spider/mysql-test/spider/include/init_spider.inc b/storage/spider/mysql-test/spider/include/init_spider.inc
index b4ce7c8f035..ccf9e305cf7 100644
--- a/storage/spider/mysql-test/spider/include/init_spider.inc
+++ b/storage/spider/mysql-test/spider/include/init_spider.inc
@@ -2,7 +2,7 @@ let $VERSION_COMPILE_OS_WIN=
   `SELECT IF(@@version_compile_os like 'Win%', 1, 0)`;
 if ($VERSION_COMPILE_OS_WIN)
 {
-  INSTALL PLUGIN spider SONAME 'ha_spider';
+  INSTALL SONAME 'ha_spider';
   if ($MASTER_1_MYPORT)
   {
     evalp CREATE SERVER s_1 FOREIGN DATA WRAPPER mysql OPTIONS (
@@ -76,7 +76,7 @@ if ($VERSION_COMPILE_OS_WIN)
 }
 if (!$VERSION_COMPILE_OS_WIN)
 {
-  INSTALL PLUGIN spider SONAME 'ha_spider';
+  INSTALL SONAME 'ha_spider';
   if ($MASTER_1_MYSOCK)
   {
     evalp CREATE SERVER s_1 FOREIGN DATA WRAPPER mysql OPTIONS (
diff --git a/storage/spider/spd_include.h b/storage/spider/spd_include.h
index dde20098a4a..b6d54e25ce6 100644
--- a/storage/spider/spd_include.h
+++ b/storage/spider/spd_include.h
@@ -324,7 +324,6 @@ typedef struct st_spider_thread
   volatile bool         killed;
   volatile bool         thd_wait;
   volatile bool         first_free_wait;
-  volatile bool         init_command;
   volatile int          error;
   pthread_t             thread;
   pthread_cond_t        cond;
diff --git a/storage/spider/spd_init_query.h b/storage/spider/spd_init_query.h
index 1cf987134f6..03eea1c5036 100644
--- a/storage/spider/spd_init_query.h
+++ b/storage/spider/spd_init_query.h
@@ -662,82 +662,6 @@ static LEX_STRING spider_init_queries[] = {
     "create procedure mysql.spider_plugin_installer()"
     "begin"
     "  set @win_plugin := IF(@@version_compile_os like 'Win%', 1, 0);"
-/*
-  Install spider plugin
-*/
-/*
-    "  set @have_spider_i_s_plugin := 0;"
-    "  select @have_spider_i_s_plugin := 1 from INFORMATION_SCHEMA.plugins"
-    "    where PLUGIN_NAME = 'SPIDER';"
-    "  set @have_spider_plugin := 0;"
-    "  select @have_spider_plugin := 1 from mysql.plugin"
-    "    where name = 'spider';"
-    "  if @have_spider_i_s_plugin = 0 then"
-    "    if @have_spider_plugin = 1 then"
-    "      / *"
-    "        spider plugin is present in mysql.plugin but not in"
-    "        information_schema.plugins.  Remove spider plugin entry"
-    "        in mysql.plugin first."
-    "      * /"
-    "      delete from mysql.plugin where name = 'spider';"
-    "    end if;"
-    "    if @win_plugin = 0 then "
-    "      install plugin spider soname 'ha_spider.so';"
-    "    else"
-    "      install plugin spider soname 'ha_spider.dll';"
-    "    end if;"
-    "  end if;"
-*/
-/*
-  Install spider_alloc_mem plugin
-*/
-    "  set @have_spider_i_s_alloc_mem_plugin := 0;"
-    "  select @have_spider_i_s_alloc_mem_plugin := 1"
-    "    from INFORMATION_SCHEMA.plugins"
-    "    where PLUGIN_NAME = 'SPIDER_ALLOC_MEM';"
-    "  set @have_spider_alloc_mem_plugin := 0;"
-    "  select @have_spider_alloc_mem_plugin := 1 from mysql.plugin"
-    "    where name = 'spider_alloc_mem';"
-    "  if @have_spider_i_s_alloc_mem_plugin = 0 then"
-    "    if @have_spider_alloc_mem_plugin = 1 then"
-    "      /*"
-    "        spider_alloc_mem plugin is present in mysql.plugin but not in"
-    "        information_schema.plugins. Remove spider_alloc_mem plugin entry"
-    "        in mysql.plugin first."
-    "      */"
-    "      delete from mysql.plugin where name = 'spider_alloc_mem';"
-    "    end if;"
-    "    if @win_plugin = 0 then "
-    "      install plugin spider_alloc_mem soname 'ha_spider.so';"
-    "    else"
-    "      install plugin spider_alloc_mem soname 'ha_spider.dll';"
-    "    end if;"
-    "  end if;"
-/*
-  Install spider_wrapper_protocols plugin
-*/
-    "  set @have_spider_i_s_wrapper_protocols_plugin := 0;"
-    "  select @have_spider_i_s_wrapper_protocols_plugin := 1"
-    "    from INFORMATION_SCHEMA.plugins"
-    "    where PLUGIN_NAME = 'SPIDER_WRAPPER_PROTOCOLS';"
-    "  set @have_spider_wrapper_protocols_plugin := 0;"
-    "  select @have_spider_wrapper_protocols_plugin := 1 from mysql.plugin"
-    "    where name = 'spider_wrapper_protocols';"
-    "  if @have_spider_i_s_wrapper_protocols_plugin = 0 then"
-    "    if @have_spider_wrapper_protocols_plugin = 1 then"
-    "      /*"
-    "        spider_wrapper_protocols plugin is present in mysql.plugin but not in"
-    "        information_schema.plugins. Remove spider_wrapper_protocols plugin entry"
-    "        in mysql.plugin first."
-    "      */"
-    "      delete from mysql.plugin where name = 'spider_wrapper_protocols';"
-    "    end if;"
-    "    if @win_plugin = 0 then "
-    "      install plugin spider_wrapper_protocols soname 'ha_spider.so';"
-    "    else"
-    "      install plugin spider_wrapper_protocols soname 'ha_spider.dll';"
-    "    end if;"
-    "  end if;"
     "  set @have_spider_direct_sql_udf := 0;"
     "  select @have_spider_direct_sql_udf := 1 from mysql.func"
     "    where name = 'spider_direct_sql';"
@@ -798,53 +722,6 @@ static LEX_STRING spider_init_queries[] = {
     "        soname 'ha_spider.dll';"
     "    end if;"
     "  end if;"
-    "  if @server_name = 'MariaDB' and"
-    "    ("
-    "      @server_major_version > 10 or"
-    "      ("
-    "        @server_major_version = 10 and"
-    "        @server_minor_version >= 999"
-    "      )"
-    "    )"
-    "  then"
-/*
-  Install spider_rewrite plugin
-*/
-    "    set @have_spider_i_s_rewrite_plugin := 0;"
-    "    select @have_spider_i_s_rewrite_plugin := 1"
-    "      from INFORMATION_SCHEMA.plugins"
-    "      where PLUGIN_NAME = 'SPIDER_REWRITE';"
-    "    set @have_spider_rewrite_plugin := 0;"
-    "    select @have_spider_rewrite_plugin := 1 from mysql.plugin"
-    "      where name = 'spider_rewrite';"
-    "    if @have_spider_i_s_rewrite_plugin = 0 then"
-    "      if @have_spider_rewrite_plugin = 1 then"
-    "        /*"
-    "          spider_rewrite plugin is present in mysql.plugin but not in"
-    "          information_schema.plugins. Remove spider_rewrite plugin entry"
-    "          in mysql.plugin first."
-    "        */"
-    "        delete from mysql.plugin where name = 'spider_rewrite';"
-    "      end if;"
-    "      if @win_plugin = 0 then "
-    "        install plugin spider_rewrite soname 'ha_spider.so';"
-    "      else"
-    "        install plugin spider_rewrite soname 'ha_spider.dll';"
-    "      end if;"
-    "    end if;"
-    "    set @have_spider_flush_rewrite_cache_udf := 0;"
-    "    select @have_spider_flush_rewrite_cache_udf := 1 from mysql.func"
-    "      where name = 'spider_flush_rewrite_cache';"
-    "    if @have_spider_flush_rewrite_cache_udf = 0 then"
-    "      if @win_plugin = 0 then "
-    "        create function spider_flush_rewrite_cache returns int"
-    "          soname 'ha_spider.so';"
-    "      else"
-    "        create function spider_flush_rewrite_cache returns int"
-    "          soname 'ha_spider.dll';"
-    "      end if;"
-    "    end if;"
-    "  end if;"
     "end;"
   )},
   {C_STRING_WITH_LEN(
@@ -852,6 +729,5 @@ static LEX_STRING spider_init_queries[] = {
   )},
   {C_STRING_WITH_LEN(
     "drop procedure mysql.spider_plugin_installer"
-  )},
-  {C_STRING_WITH_LEN("")}
+  )}
 };
diff --git a/storage/spider/spd_table.cc b/storage/spider/spd_table.cc
index e3f8ba22b7f..3505ea176f8 100644
--- a/storage/spider/spd_table.cc
+++ b/storage/spider/spd_table.cc
@@ -130,9 +130,6 @@ const char **spd_mysqld_unix_port;
 uint *spd_mysqld_port;
 bool volatile *spd_abort_loop;
 Time_zone *spd_tz_system;
-static int *spd_mysqld_server_started;
-static pthread_mutex_t *spd_LOCK_server_started;
-static pthread_cond_t *spd_COND_server_started;
 extern long spider_conn_mutex_id;
 handlerton *spider_hton_ptr;
 SPIDER_DBTON spider_dbton[SPIDER_DBTON_SIZE];
@@ -6620,30 +6617,6 @@ handler* spider_create_handler(
   MEM_ROOT *mem_root
 ) {
   DBUG_ENTER("spider_create_handler");
-#ifndef WITHOUT_SPIDER_BG_SEARCH
-  SPIDER_THREAD *thread = &spider_table_sts_threads[0];
-  if (unlikely(thread->init_command))
-  {
-    THD *thd = current_thd;
-    pthread_cond_t *cond = thd->mysys_var->current_cond;
-    pthread_mutex_t *mutex = thd->mysys_var->current_mutex;
-    /* wait for finishing init_command */
-    pthread_mutex_lock(&thread->mutex);
-    if (unlikely(thread->init_command))
-    {
-      thd->mysys_var->current_cond = &thread->sync_cond;
-      thd->mysys_var->current_mutex = &thread->mutex;
-      pthread_cond_wait(&thread->sync_cond, &thread->mutex);
-    }
-    pthread_mutex_unlock(&thread->mutex);
-    thd->mysys_var->current_cond = cond;
-    thd->mysys_var->current_mutex = mutex;
-    if (thd->killed)
-    {
-      DBUG_RETURN(NULL);
-    }
-  }
-#endif
   DBUG_RETURN(new (mem_root) ha_spider(hton, table));
 }
 
@@ -6922,6 +6895,50 @@ int spider_panic(
   DBUG_RETURN(0);
 }
 
+/*
+  Create or fix the system tables. See spd_init_query.h for the details.
+*/
+bool spider_init_system_tables()
+{
+  DBUG_ENTER("spider_init_system_tables");
+
+  MYSQL *mysql= mysql_init(NULL);
+  if (!mysql)
+  {
+    DBUG_RETURN(TRUE);
+  }
+
+  if (!mysql_real_connect_local(mysql))
+  {
+    mysql_close(mysql);
+    DBUG_RETURN(TRUE);
+  }
+
+  int size= sizeof(spider_init_queries) / sizeof(spider_init_queries[0]);
+  for (int i= 0; i < size; i++)
+  {
+    if (mysql_real_query(mysql, spider_init_queries[i].str,
+                         spider_init_queries[i].length))
+    {
+      fprintf(stderr,
+              "[ERROR] SPIDER plugin initialization failed at '%s' by '%s'\n",
+              spider_init_queries[i].str, mysql_error(mysql));
+
+      mysql_close(mysql);
+      DBUG_RETURN(TRUE);
+    }
+
+    if (MYSQL_RES *res= mysql_store_result(mysql))
+    {
+      mysql_free_result(res);
+    }
+  }
+
+  mysql_close(mysql);
+
+  DBUG_RETURN(FALSE);
+}
+
 int spider_db_init(
   void *p
 ) {
@@ -7010,9 +7027,6 @@ int spider_db_init(
   spd_mysqld_port = &mysqld_port;
   spd_abort_loop = &abort_loop;
   spd_tz_system = my_tz_SYSTEM;
-  spd_mysqld_server_started = &mysqld_server_started;
-  spd_LOCK_server_started = &LOCK_server_started;
-  spd_COND_server_started = &COND_server_started;
 
 #ifdef HAVE_PSI_INTERFACE
   init_spider_psi_keys();
@@ -7250,6 +7264,11 @@ int spider_db_init(
       spider_udf_table_mon_list_hash[roop_count].array.size_of_element);
   }
 
+  if (spider_init_system_tables())
+  {
+    goto error_system_table_creation;
+  }
+
 #ifndef WITHOUT_SPIDER_BG_SEARCH
   if (!(spider_table_sts_threads = (SPIDER_THREAD *)
     spider_bulk_malloc(NULL, 256, MYF(MY_WME | MY_ZEROFILL),
@@ -7260,7 +7279,6 @@ int spider_db_init(
       NullS))
   )
     goto error_alloc_mon_mutxes;
-  spider_table_sts_threads[0].init_command = TRUE;
 
   for (roop_count = 0;
     roop_count < (int) spider_param_table_sts_thread_count();
@@ -7345,6 +7363,7 @@ error_init_udf_table_mon_list_hash:
 error_init_udf_table_mon_cond:
   for (; roop_count >= 0; roop_count--)
     pthread_cond_destroy(&spider_udf_table_mon_conds[roop_count]);
+error_system_table_creation:
   roop_count = spider_param_udf_table_mon_mutex_count() - 1;
 error_init_udf_table_mon_mutex:
   for (; roop_count >= 0; roop_count--)
@@ -9779,57 +9798,6 @@ void *spider_table_bg_sts_action(
   trx->thd = thd;
   /* init end */
 
-  if (thread->init_command)
-  {
-    uint i = 0;
-    tmp_disable_binlog(thd);
-    thd->security_ctx->skip_grants();
-    thd->client_capabilities |= CLIENT_MULTI_RESULTS;
-    if (!(*spd_mysqld_server_started) && !thd->killed)
-    {
-      pthread_mutex_lock(spd_LOCK_server_started);
-      thd->mysys_var->current_cond = spd_COND_server_started;
-      thd->mysys_var->current_mutex = spd_LOCK_server_started;
-      if (!(*spd_mysqld_server_started) && !thd->killed)
-      {
-        do
-        {
-          struct timespec abstime;
-          set_timespec_nsec(abstime, 1000);
-          error_num = pthread_cond_timedwait(spd_COND_server_started,
-            spd_LOCK_server_started, &abstime);
-        } while (
-          (error_num == ETIMEDOUT || error_num == ETIME) &&
-          !(*spd_mysqld_server_started) && !thd->killed && !thread->killed
-        );
-      }
-      pthread_mutex_unlock(spd_LOCK_server_started);
-      thd->mysys_var->current_cond = &thread->cond;
-      thd->mysys_var->current_mutex = &thread->mutex;
-    }
-    while (spider_init_queries[i].length && !thd->killed && !thread->killed)
-    {
-      dispatch_command(COM_QUERY, thd, spider_init_queries[i].str,
-        (uint) spider_init_queries[i].length);
-      if (unlikely(thd->is_error()))
-      {
-        fprintf(stderr, "[ERROR] %s\n", spider_stmt_da_message(thd));
-        thd->clear_error();
-        break;
-      }
-      ++i;
-    }
-    thd->mysys_var->current_cond = &thread->cond;
-    thd->mysys_var->current_mutex = &thread->mutex;
-    thd->client_capabilities -= CLIENT_MULTI_RESULTS;
-    reenable_binlog(thd);
-    thread->init_command = FALSE;
-    pthread_cond_broadcast(&thread->sync_cond);
-  }
-  if (thd->killed)
-  {
-    thread->killed = TRUE;
-  }
   if (thd->killed)
   {
     thread->killed = TRUE;