MDEV-27233-22979-30370 Add and update tests for spider init bugs

For development only - will squash this into another commit later - do
not push.

mdev_27233_v2: a variant of mdev-27233 where the spider plugin is
loaded in during server startup.

mdev_30370: we update this test to use mysqld_cmd instead of the
noisy mysqld_bootstrap_cmd. We have to kill the server first to avoid
"Can't lock aria control file"

With the change in commit c160a115b8b that was pushed to fix
mdev-27233 but later reverted because of mdev-29904, mdev_27233 and
the updated mdev_30370 would pass, but not mdev_27233_v2 ("[ERROR]
SPIDER plugin initialization failed at 'drop procedure if exists
mysql.spider_fix_one_table' by 'Unknown storage engine 'Aria''") or
mdev_30370 if it uses mysqld_bootstrap_cmd (post-test check will fail
when running this case with some other case, e.g. mtr mdev_30370
mdev_29562).

18 files changed, 43 insertions, 1 deletions

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_27233_v2.result b/storage/spider/mysql-test/spider/bugfix/r/mdev_27233_v2.result
new file mode 100644
index 00000000000..53622bb6a23
--- /dev/null
+++ b/storage/spider/mysql-test/spider/bugfix/r/mdev_27233_v2.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_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/t/mdev_22979.opt b/storage/spider/mysql-test/spider/bugfix/t/mdev_22979.opt
new file mode 100644
index 00000000000..ef47bac21ce
--- /dev/null
+++ b/storage/spider/mysql-test/spider/bugfix/t/mdev_22979.opt
@@ -0,0 +1,2 @@
+--plugin-load-add=ha_spider
+--init-file=$MYSQL_TEST_DIR/../storage/spider/mysql-test/spider/bugfix/t/mdev_22979.sql
diff --git a/storage/spider/mysql-test/spider/bugfix/t/mdev_22979.sql b/storage/spider/mysql-test/spider/bugfix/t/mdev_22979.sql
new file mode 100644
index 00000000000..81443e258e2
--- /dev/null
+++ b/storage/spider/mysql-test/spider/bugfix/t/mdev_22979.sql
@@ -0,0 +1 @@
+drop table if exists foo.bar;
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..db58516f027
--- /dev/null
+++ b/storage/spider/mysql-test/spider/bugfix/t/mdev_22979.test
@@ -0,0 +1 @@
+--echo MDEV-22979 with --plugin-load-add in opt
diff --git a/storage/spider/mysql-test/spider/bugfix/t/mdev_22979_2.opt b/storage/spider/mysql-test/spider/bugfix/t/mdev_22979_2.opt
new file mode 100644
index 00000000000..64cb7228cac
--- /dev/null
+++ b/storage/spider/mysql-test/spider/bugfix/t/mdev_22979_2.opt
@@ -0,0 +1 @@
+--init-file=$MYSQL_TEST_DIR/../storage/spider/mysql-test/spider/bugfix/t/mdev_22979.sql
diff --git a/storage/spider/mysql-test/spider/bugfix/t/mdev_22979_2.sql b/storage/spider/mysql-test/spider/bugfix/t/mdev_22979_2.sql
new file mode 100644
index 00000000000..96e96385356
--- /dev/null
+++ b/storage/spider/mysql-test/spider/bugfix/t/mdev_22979_2.sql
@@ -0,0 +1,2 @@
+install soname ha_spider;
+drop table if exists foo.bar;
diff --git a/storage/spider/mysql-test/spider/bugfix/t/mdev_22979_2.test b/storage/spider/mysql-test/spider/bugfix/t/mdev_22979_2.test
new file mode 100644
index 00000000000..b76604cbefd
--- /dev/null
+++ b/storage/spider/mysql-test/spider/bugfix/t/mdev_22979_2.test
@@ -0,0 +1 @@
+--echo MDEV-22979 with install soname ha_spider in sql
diff --git a/storage/spider/mysql-test/spider/bugfix/t/mdev_22979_bootstrap.sql b/storage/spider/mysql-test/spider/bugfix/t/mdev_22979_bootstrap.sql
new file mode 100644
index 00000000000..81443e258e2
--- /dev/null
+++ b/storage/spider/mysql-test/spider/bugfix/t/mdev_22979_bootstrap.sql
@@ -0,0 +1 @@
+drop table if exists foo.bar;
diff --git a/storage/spider/mysql-test/spider/bugfix/t/mdev_22979_bootstrap.test b/storage/spider/mysql-test/spider/bugfix/t/mdev_22979_bootstrap.test
new file mode 100644
index 00000000000..cbfc3b450ae
--- /dev/null
+++ b/storage/spider/mysql-test/spider/bugfix/t/mdev_22979_bootstrap.test
@@ -0,0 +1,7 @@
+--echo MDEV-22979 with --bootstrap
+
+let $MYSQLD_DATADIR= `select @@datadir`;
+let $PLUGIN_DIR=`select @@plugin_dir`;
+--source include/kill_mysqld.inc
+--exec $MYSQLD_CMD --datadir=$MYSQLD_DATADIR --bootstrap --plugin-dir=$PLUGIN_DIR --plugin-load-add=ha_spider < $MYSQL_TEST_DIR/../storage/spider/mysql-test/spider/bugfix/t/mdev_22979_bootstrap.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_27233_v2.opt b/storage/spider/mysql-test/spider/bugfix/t/mdev_27233_v2.opt
new file mode 100644
index 00000000000..6f00a085364
--- /dev/null
+++ b/storage/spider/mysql-test/spider/bugfix/t/mdev_27233_v2.opt
@@ -0,0 +1,2 @@
+--plugin-load-add=ha_spider
+--init-file=$MYSQL_TEST_DIR/../storage/spider/mysql-test/spider/bugfix/t/mdev_27233_v2.sql
diff --git a/storage/spider/mysql-test/spider/bugfix/t/mdev_27233_v2.sql b/storage/spider/mysql-test/spider/bugfix/t/mdev_27233_v2.sql
new file mode 100644
index 00000000000..891953343e8
--- /dev/null
+++ b/storage/spider/mysql-test/spider/bugfix/t/mdev_27233_v2.sql
@@ -0,0 +1,2 @@
+USE test;
+CREATE TABLE t (c INT) ENGINE=SPIDER;
diff --git a/storage/spider/mysql-test/spider/bugfix/t/mdev_27233_v2.test b/storage/spider/mysql-test/spider/bugfix/t/mdev_27233_v2.test
new file mode 100644
index 00000000000..79603439d35
--- /dev/null
+++ b/storage/spider/mysql-test/spider/bugfix/t/mdev_27233_v2.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_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