MDEV-22979 re-routing --plugin-load-add=ha_spider to an "internal" init filebb-11.0-mdev-22979

- Move spider init query execution from a background thread to
  spider_db_init() so that it is fully synchronous
- if spider is initialised too early from init_server_components then
  "move" the init to an temporary init file

21 files changed, 176 insertions, 84 deletions

diff --git a/sql/mysqld.cc b/sql/mysqld.cc
index 12dd3639365..f18ada71ea3 100644
--- a/sql/mysqld.cc
+++ b/sql/mysqld.cc
@@ -580,6 +580,7 @@ char mysql_real_data_home[FN_REFLEN],
      lc_messages_dir[FN_REFLEN], reg_ext[FN_EXTLEN],
      mysql_charsets_dir[FN_REFLEN],
      *opt_init_file, *opt_tc_log_file, *opt_ddl_recovery_file;
+char temp_init_file[FN_REFLEN] = "";
 char *lc_messages_dir_ptr= lc_messages_dir, *log_error_file_ptr;
 char mysql_unpacked_real_data_home[FN_REFLEN];
 size_t mysql_unpacked_real_data_home_len;
@@ -5965,6 +5966,9 @@ int mysqld_main(int argc, char **argv)
     unireg_abort(1);
   }
 
+  if (temp_init_file[0] && !read_init_file(temp_init_file))
+    my_delete(temp_init_file, MYF(0));
+
   if (opt_init_file && *opt_init_file)
   {
     if (read_init_file(opt_init_file))
diff --git a/sql/mysqld.h b/sql/mysqld.h
index 54cafdcde15..8036b037511 100644
--- a/sql/mysqld.h
+++ b/sql/mysqld.h
@@ -274,6 +274,7 @@ extern const char *myisam_recover_options_str;
 extern const LEX_CSTRING in_left_expr_name, in_additional_cond, in_having_cond;
 extern const LEX_CSTRING NULL_clex_str;
 extern const LEX_CSTRING error_clex_str;
+extern char temp_init_file[];
 extern SHOW_VAR status_vars[];
 extern struct system_variables max_system_variables;
 extern struct system_status_var global_status_var;
diff --git a/sql/sql_plugin.cc b/sql/sql_plugin.cc
index 094ff52a4ea..a400448cba2 100644
--- a/sql/sql_plugin.cc
+++ b/sql/sql_plugin.cc
@@ -1583,6 +1583,40 @@ static void init_plugin_psi_keys(void)
 static void init_plugin_psi_keys(void) {}
 #endif /* HAVE_PSI_INTERFACE */
 
+/** Defers the initialisation of a dynamic plugin to a temporary init file */
+void plugin_defer_init(st_plugin_int &plugin)
+{
+  int fd;
+  const char *name= plugin.name.str;
+  const char *dl= plugin.plugin_dl->dl.str;
+  DBUG_ENTER("plugin_defer_init");
+  if (!plugin.plugin_dl)
+    DBUG_VOID_RETURN;
+  if (!temp_init_file[0])
+  {
+    if ((fd= create_temp_file(temp_init_file, mysql_tmpdir,
+                              "plugin_init_", 0, MYF(MY_WME))) >= 0)
+      my_close(fd, MYF(0));
+    else
+    {
+      sql_print_error("Loading of %s from %s failed as a temporary "
+                      "file cannot be generated.",
+                      name, dl);
+      DBUG_VOID_RETURN;
+    }
+  }
+  FILE *ofile= fopen(temp_init_file, "a");
+  fprintf(ofile, "install plugin %s soname '%s';\n", name, dl);
+  fprintf(ofile, "delete from mysql.plugin where name=\"%s\" and dl=\"%s\";\n",
+          name, dl);
+  fclose(ofile);
+  sql_print_information("Loading of %s from %s is deferred to a temporary "
+                        "init file",
+                        name, dl);
+  plugin_del(&plugin, 0);
+  DBUG_VOID_RETURN;
+}
+
 /*
   The logic is that we first load and initialize all compiled in plugins.
   From there we load up the dynamic types (assuming we have not been told to
@@ -1750,6 +1784,15 @@ int plugin_init(int *argc, char **argv, int flags)
         plugin_ptr= (struct st_plugin_int *) my_hash_element(hash, idx);
         if (plugin_ptr->state == PLUGIN_IS_UNINITIALIZED)
         {
+          if (plugin_ptr->plugin_dl &&
+              (strncmp(plugin_ptr->plugin_dl->dl.str, "ha_spider.so",
+                       plugin_ptr->plugin_dl->dl.length) ||
+               strncmp(plugin_ptr->plugin_dl->dl.str, "ha_spider.dll",
+                       plugin_ptr->plugin_dl->dl.length)))
+          {
+            plugin_defer_init(*plugin_ptr);
+            continue;
+          }
           bool plugin_table_engine= lex_string_eq(&plugin_table_engine_name,
                                                   &plugin_ptr->name);
           bool opts_only= flags & PLUGIN_INIT_SKIP_INITIALIZATION &&
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/r/mdev_29904.result b/storage/spider/mysql-test/spider/bugfix/r/mdev_29904.result
index c89309a514d..ae245a56b48 100644
--- a/storage/spider/mysql-test/spider/bugfix/r/mdev_29904.result
+++ b/storage/spider/mysql-test/spider/bugfix/r/mdev_29904.result
@@ -2,3 +2,4 @@
 # MDEV-29904 SPIDER plugin initialization fails upon startup
 #
 # restart: --plugin-load-add=ha_spider
+FOUND 3 /\[Note\] Loading of SPIDER[a-zA-Z0-9_]* from ha_spider(|.so|.dll) is deferred to a temporary init file/ in mysqld.1.1.err
diff --git a/storage/spider/mysql-test/spider/bugfix/r/mdev_30370.result b/storage/spider/mysql-test/spider/bugfix/r/mdev_30370.result
index df0f6949280..25fc5ac9826 100644
--- a/storage/spider/mysql-test/spider/bugfix/r/mdev_30370.result
+++ b/storage/spider/mysql-test/spider/bugfix/r/mdev_30370.result
@@ -1,3 +1,5 @@
 #
 # MDEV-30370 mariadbd hangs when running with --wsrep-recover and --plugin-load-add=ha_spider.so
 #
+# Kill the server
+# restart
diff --git a/storage/spider/mysql-test/spider/bugfix/r/plugin_load_add_all.result b/storage/spider/mysql-test/spider/bugfix/r/plugin_load_add_all.result
new file mode 100644
index 00000000000..917b95f035a
--- /dev/null
+++ b/storage/spider/mysql-test/spider/bugfix/r/plugin_load_add_all.result
@@ -0,0 +1,8 @@
+#
+# plugin-load-add=ha_spider
+#
+FOUND 3 /\[Note\] Loading of SPIDER[a-zA-Z0-9_]* from ha_spider(|.so|.dll) is deferred to a temporary init file/ in mysqld.1.1.err
+select * from mysql.plugin;
+name	dl
+create table t (c int) Engine=SPIDER;
+drop table t;
diff --git a/storage/spider/mysql-test/spider/bugfix/r/plugin_load_add_spider.result b/storage/spider/mysql-test/spider/bugfix/r/plugin_load_add_spider.result
new file mode 100644
index 00000000000..432d4282562
--- /dev/null
+++ b/storage/spider/mysql-test/spider/bugfix/r/plugin_load_add_spider.result
@@ -0,0 +1,8 @@
+#
+# plugin-load-add=ha_spider
+#
+FOUND 1 /\[Note\] Loading of SPIDER[a-zA-Z0-9_]* from ha_spider(|.so|.dll) is deferred to a temporary init file/ in mysqld.1.1.err
+select * from mysql.plugin;
+name	dl
+create table t (c int) Engine=SPIDER;
+drop table t;
diff --git a/storage/spider/mysql-test/spider/bugfix/t/mdev_22979.test b/storage/spider/mysql-test/spider/bugfix/t/mdev_22979.test
new file mode 100644
index 00000000000..441647e1edd
--- /dev/null
+++ b/storage/spider/mysql-test/spider/bugfix/t/mdev_22979.test
@@ -0,0 +1,16 @@
+--echo MDEV-22979 "mysqld --bootstrap" / mysql_install_db hangs when Spider is installed
+
+# This test is not the most faithful, as it does not have any
+# dependency problems on the existence of the `mysql` database. To
+# test MDEV-22979 faithfully, a mysql_install_db invocation with
+# --plugin-load-add=ha_spider should be run. We cannot run it in mtr
+# because we do not have access to --srcdir.
+
+let $MYSQLD_DATADIR= `select @@datadir`;
+let $PLUGIN_DIR=`select @@plugin_dir`;
+--source include/kill_mysqld.inc
+--write_file $MYSQLTEST_VARDIR/tmp/mdev_22979.sql
+drop table if exists foo.bar;
+EOF
+--exec $MYSQLD_CMD --datadir=$MYSQLD_DATADIR --bootstrap --plugin-dir=$PLUGIN_DIR --plugin-load-add=ha_spider < $MYSQLTEST_VARDIR/tmp/mdev_22979.sql
+--source include/start_mysqld.inc
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/bugfix/t/mdev_29904.test b/storage/spider/mysql-test/spider/bugfix/t/mdev_29904.test
index d3dcb363890..eea172bc483 100644
--- a/storage/spider/mysql-test/spider/bugfix/t/mdev_29904.test
+++ b/storage/spider/mysql-test/spider/bugfix/t/mdev_29904.test
@@ -4,3 +4,7 @@
 
 --let $restart_parameters=--plugin-load-add=ha_spider
 --source include/restart_mysqld.inc
+--let SEARCH_PATTERN=\\[Note\\] Loading of SPIDER[a-zA-Z0-9_]* from ha_spider(|.so|.dll) is deferred to a temporary init file
+--let SEARCH_FILE=`select regexp_replace(@@tmpdir, '^.*/','')`
+--let SEARCH_FILE=$MYSQLTEST_VARDIR/log/$SEARCH_FILE.err
+--source include/search_pattern_in_file.inc
diff --git a/storage/spider/mysql-test/spider/bugfix/t/mdev_30370.test b/storage/spider/mysql-test/spider/bugfix/t/mdev_30370.test
index 788ea2323f7..d4d5296af55 100644
--- a/storage/spider/mysql-test/spider/bugfix/t/mdev_30370.test
+++ b/storage/spider/mysql-test/spider/bugfix/t/mdev_30370.test
@@ -2,4 +2,8 @@
 --echo # MDEV-30370 mariadbd hangs when running with --wsrep-recover and --plugin-load-add=ha_spider.so
 --echo #
 
---exec $MYSQLD_BOOTSTRAP_CMD --wsrep-recover --plugin-load-add=ha_spider.so
+let $MYSQLD_DATADIR= `select @@datadir`;
+let $PLUGIN_DIR=`select @@plugin_dir`;
+--source include/kill_mysqld.inc
+--exec $MYSQLD_CMD --datadir=$MYSQLD_DATADIR --wsrep-recover --plugin-dir=$PLUGIN_DIR --plugin-load-add=ha_spider
+--source include/start_mysqld.inc
diff --git a/storage/spider/mysql-test/spider/bugfix/t/plugin_load_add_all.opt b/storage/spider/mysql-test/spider/bugfix/t/plugin_load_add_all.opt
new file mode 100644
index 00000000000..52144383000
--- /dev/null
+++ b/storage/spider/mysql-test/spider/bugfix/t/plugin_load_add_all.opt
@@ -0,0 +1 @@
+--plugin-load-add=ha_spider
\ No newline at end of file
diff --git a/storage/spider/mysql-test/spider/bugfix/t/plugin_load_add_all.test b/storage/spider/mysql-test/spider/bugfix/t/plugin_load_add_all.test
new file mode 100644
index 00000000000..bc32a6b3147
--- /dev/null
+++ b/storage/spider/mysql-test/spider/bugfix/t/plugin_load_add_all.test
@@ -0,0 +1,11 @@
+--echo #
+--echo # plugin-load-add=ha_spider
+--echo #
+
+--let SEARCH_PATTERN=\\[Note\\] Loading of SPIDER[a-zA-Z0-9_]* from ha_spider(|.so|.dll) is deferred to a temporary init file
+--let SEARCH_FILE=`select regexp_replace(@@tmpdir, '^.*/','')`
+--let SEARCH_FILE=$MYSQLTEST_VARDIR/log/$SEARCH_FILE.err
+--source include/search_pattern_in_file.inc
+select * from mysql.plugin;
+create table t (c int) Engine=SPIDER;
+drop table t;
diff --git a/storage/spider/mysql-test/spider/bugfix/t/plugin_load_add_spider.opt b/storage/spider/mysql-test/spider/bugfix/t/plugin_load_add_spider.opt
new file mode 100644
index 00000000000..154653920e6
--- /dev/null
+++ b/storage/spider/mysql-test/spider/bugfix/t/plugin_load_add_spider.opt
@@ -0,0 +1 @@
+--plugin-load-add=SPIDER=ha_spider
\ No newline at end of file
diff --git a/storage/spider/mysql-test/spider/bugfix/t/plugin_load_add_spider.test b/storage/spider/mysql-test/spider/bugfix/t/plugin_load_add_spider.test
new file mode 100644
index 00000000000..bc32a6b3147
--- /dev/null
+++ b/storage/spider/mysql-test/spider/bugfix/t/plugin_load_add_spider.test
@@ -0,0 +1,11 @@
+--echo #
+--echo # plugin-load-add=ha_spider
+--echo #
+
+--let SEARCH_PATTERN=\\[Note\\] Loading of SPIDER[a-zA-Z0-9_]* from ha_spider(|.so|.dll) is deferred to a temporary init file
+--let SEARCH_FILE=`select regexp_replace(@@tmpdir, '^.*/','')`
+--let SEARCH_FILE=$MYSQLTEST_VARDIR/log/$SEARCH_FILE.err
+--source include/search_pattern_in_file.inc
+select * from mysql.plugin;
+create table t (c int) Engine=SPIDER;
+drop table t;
diff --git a/storage/spider/spd_include.h b/storage/spider/spd_include.h
index 3c5c3754f4e..ce1107838c2 100644
--- a/storage/spider/spd_include.h
+++ b/storage/spider/spd_include.h
@@ -199,7 +199,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 29e27d00610..49206c0cb88 100644
--- a/storage/spider/spd_init_query.h
+++ b/storage/spider/spd_init_query.h
@@ -670,6 +670,5 @@ static LEX_STRING spider_init_queries[] = {
   {C_STRING_WITH_LEN(
     "create function if not exists spider_flush_table_mon_cache returns int"
     "  soname 'ha_spider.so';"
-  )},
-  {C_STRING_WITH_LEN("")}
+  )}
 };
diff --git a/storage/spider/spd_table.cc b/storage/spider/spd_table.cc
index 82a6ac41674..793916e0552 100644
--- a/storage/spider/spd_table.cc
+++ b/storage/spider/spd_table.cc
@@ -117,9 +117,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;
 /** All `SPIDER_DBTON`s */
@@ -5912,28 +5909,6 @@ handler* spider_create_handler(
   MEM_ROOT *mem_root
 ) {
   DBUG_ENTER("spider_create_handler");
-  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);
-    }
-  }
   DBUG_RETURN(new (mem_root) ha_spider(hton, table));
 }
 
@@ -6205,6 +6180,49 @@ static void spider_update_optimizer_costs(OPTIMIZER_COSTS *costs)
   costs->row_copy_cost=      0.00006087;
 }
 
+/*
+  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
@@ -6280,9 +6298,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();
@@ -6460,6 +6475,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;
+  }
+
   if (!(spider_table_sts_threads = (SPIDER_THREAD *)
     spider_bulk_malloc(NULL, 256, MYF(MY_WME | MY_ZEROFILL),
       &spider_table_sts_threads, (uint) (sizeof(SPIDER_THREAD) *
@@ -6469,7 +6489,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();
@@ -6541,6 +6560,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_udf_table_mon_mutex_count - 1;
 error_init_udf_table_mon_mutex:
   for (; roop_count >= 0; roop_count--)
@@ -8605,7 +8625,6 @@ void spider_free_sts_threads(
 ) {
   bool thread_killed;
   DBUG_ENTER("spider_free_sts_threads");
-  spider_thread->init_command = FALSE;
   pthread_mutex_lock(&spider_thread->mutex);
   thread_killed = spider_thread->killed;
   spider_thread->killed = TRUE;
@@ -8737,56 +8756,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 && !thread->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 && !thread->killed &&
-        thread->init_command)
-      {
-        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 &&
-          thread->init_command
-        );
-      }
-      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 &&
-      thread->init_command)
-    {
-      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;