MDEV-16250: Spider system tables are MyISAM and needs repair after a crashbb-10.4-MDEV-16250

During a server restart that follows a server crash, there may be error
messages that indicate that certain system tables are marked as crashed and
may be corrupted.  Upon checking the system tables that are marked as crashed,
it may be found that there is no corruption that needs repair.  However, the
error messages that are issued imply that the user needs to nevertheless
manually repair the system tables that were marked as crashed.  These issues
have been moved to a separate bug MDEV-17068.

MDEV-16250 addresses the work to make the Spider system tables crash safe.
This work involves the following changes in 10.4:
- Changes to the install_spider.sql script to change the storage engine for
  the Spider system tables to Aria.  This is implemented in such a way as to
  allow the script to be run repeatedly without any harm or errors.
- Changes to the init_spider.inc script that is run during initialization of
  every test in the Spider test suites.  This script now uses the Aria
  storage engine for the Spider system tables in MariaDB Server 10.4 and
  later releases.
- Added a test to the Spider test suite to display the storage engine of each
  Spider system table, to verify that the correct storage engine is used.

Author:
  Jacob Mathew.

Reviewer:
  Kentoku Shiba.

4 files changed, 164 insertions, 9 deletions

diff --git a/storage/spider/mysql-test/spider/include/init_spider.inc b/storage/spider/mysql-test/spider/include/init_spider.inc
index c4d171d418e..1da1ec970b5 100644
--- a/storage/spider/mysql-test/spider/include/init_spider.inc
+++ b/storage/spider/mysql-test/spider/include/init_spider.inc
@@ -103,10 +103,17 @@ if (!$VERSION_COMPILE_OS_WIN)
   );
 }
 
+let $SERVER_NAME=
+  `SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(version(), '-', 2), '-', -1)`;
+let $SERVER_MAJOR_VERSION=
+  `SELECT SUBSTRING_INDEX(version(), '.', 1)`;
+let $SERVER_MINOR_VERSION=
+  `SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(version(), '.', 2), '.', -1)`;
 let $PLUGIN_VERSION=
   `SELECT SUBSTRING_INDEX(plugin_version, '.', 1)
      FROM information_schema.plugins
     WHERE plugin_name = 'SPIDER'`;
+
 if (`SELECT IF($PLUGIN_VERSION = 1, 1, 0)`)
 {
   DROP TABLE IF EXISTS mysql.spider_xa;
@@ -245,7 +252,16 @@ if (`SELECT IF($PLUGIN_VERSION = 2, 1, 0)`)
 }
 if (`SELECT IF($PLUGIN_VERSION = 3, 1, 0)`)
 {
+  let $ENGINE_NAME=
+    `SELECT IF (STRCMP('$SERVER_NAME', 'MariaDB') = 0,
+                IF ($SERVER_MAJOR_VERSION = 10,
+                    IF ($SERVER_MINOR_VERSION < 4,  'MyISAM',
+                        'Aria transactional=1'),
+                    IF ($SERVER_MAJOR_VERSION < 10, 'MyISAM',
+                        'Aria transactional=1')),
+                'MyISAM')`;
   DROP TABLE IF EXISTS mysql.spider_xa;
+  eval
   CREATE TABLE mysql.spider_xa(
     format_id int not null default 0,
     gtrid_length int not null default 0,
@@ -254,8 +270,9 @@ if (`SELECT IF($PLUGIN_VERSION = 3, 1, 0)`)
     status char(8) not null default '',
     PRIMARY KEY (data, format_id, gtrid_length),
     KEY idx1 (status)
-  ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
+  ) ENGINE=$ENGINE_NAME DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
   DROP TABLE IF EXISTS mysql.spider_xa_member;
+  eval
   CREATE TABLE mysql.spider_xa_member(
     format_id int not null default 0,
     gtrid_length int not null default 0,
@@ -276,8 +293,9 @@ if (`SELECT IF($PLUGIN_VERSION = 3, 1, 0)`)
     default_file text default null,
     default_group char(64) default null,
     KEY idx1 (data, format_id, gtrid_length, host)
-  ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
+  ) ENGINE=$ENGINE_NAME DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
   DROP TABLE IF EXISTS mysql.spider_xa_failed_log;
+  eval
   CREATE TABLE mysql.spider_xa_failed_log(
     format_id int not null default 0,
     gtrid_length int not null default 0,
@@ -301,8 +319,9 @@ if (`SELECT IF($PLUGIN_VERSION = 3, 1, 0)`)
     status char(8) not null default '',
     failed_time timestamp not null default current_timestamp,
     key idx1 (data, format_id, gtrid_length, host)
-  ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
+  ) ENGINE=$ENGINE_NAME DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
   DROP TABLE IF EXISTS mysql.spider_tables;
+  eval
   CREATE TABLE mysql.spider_tables(
     db_name char(64) not null default '',
     table_name char(199) not null default '',
@@ -332,8 +351,9 @@ if (`SELECT IF($PLUGIN_VERSION = 3, 1, 0)`)
     PRIMARY KEY (db_name, table_name, link_id),
     KEY idx1 (priority),
     UNIQUE KEY uidx1 (db_name, table_name, static_link_id)
-  ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
+  ) ENGINE=$ENGINE_NAME DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
   DROP TABLE IF EXISTS mysql.spider_link_mon_servers;
+  eval
   CREATE TABLE mysql.spider_link_mon_servers(
     db_name char(64) not null default '',
     table_name char(199) not null default '',
@@ -355,15 +375,17 @@ if (`SELECT IF($PLUGIN_VERSION = 3, 1, 0)`)
     default_file text default null,
     default_group char(64) default null,
     PRIMARY KEY (db_name, table_name, link_id, sid)
-  ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
+  ) ENGINE=$ENGINE_NAME DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
   DROP TABLE IF EXISTS mysql.spider_link_failed_log;
+  eval
   CREATE TABLE mysql.spider_link_failed_log(
     db_name char(64) not null default '',
     table_name char(199) not null default '',
     link_id char(64) not null default '',
     failed_time timestamp not null default current_timestamp
-  ) ENGINE=MYISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
+  ) ENGINE=$ENGINE_NAME DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
   DROP TABLE IF EXISTS mysql.spider_table_position_for_recovery;
+  eval
   CREATE TABLE mysql.spider_table_position_for_recovery(
     db_name char(64) not null default '',
     table_name char(199) not null default '',
@@ -373,8 +395,9 @@ if (`SELECT IF($PLUGIN_VERSION = 3, 1, 0)`)
     position text,
     gtid text,
     primary key (db_name, table_name, failed_link_id, source_link_id)
-  ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
+  ) ENGINE=$ENGINE_NAME DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
   DROP TABLE IF EXISTS mysql.spider_table_sts;
+  eval
   CREATE TABLE mysql.spider_table_sts(
     db_name char(64) not null default '',
     table_name char(199) not null default '',
@@ -387,15 +410,16 @@ if (`SELECT IF($PLUGIN_VERSION = 3, 1, 0)`)
     create_time datetime not null default '0000-00-00 00:00:00',
     update_time datetime not null default '0000-00-00 00:00:00',
     primary key (db_name, table_name)
-  ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
+  ) ENGINE=$ENGINE_NAME DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
   DROP TABLE IF EXISTS mysql.spider_table_crd;
+  eval
   CREATE TABLE mysql.spider_table_crd(
     db_name char(64) not null default '',
     table_name char(199) not null default '',
     key_seq int unsigned not null default 0,
     cardinality bigint not null default 0,
     primary key (db_name, table_name, key_seq)
-  ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
+  ) ENGINE=$ENGINE_NAME DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
 }
 
 SET spider_internal_sql_log_off= 0;
diff --git a/storage/spider/mysql-test/spider/r/show_system_tables.result b/storage/spider/mysql-test/spider/r/show_system_tables.result
new file mode 100644
index 00000000000..39c3953b73d
--- /dev/null
+++ b/storage/spider/mysql-test/spider/r/show_system_tables.result
@@ -0,0 +1,37 @@
+for master_1
+for child2
+child2_1
+child2_2
+child2_3
+for child3
+child3_1
+child3_2
+child3_3
+
+Show system tables on the Spider node
+connection master_1;
+SELECT table_name, engine FROM information_schema.tables 
+WHERE table_schema = 'mysql' AND table_name like '%spider_%'
+table_name	engine
+spider_xa_member	Aria
+spider_xa_failed_log	Aria
+spider_xa	Aria
+spider_tables	Aria
+spider_table_sts	Aria
+spider_table_position_for_recovery	Aria
+spider_table_crd	Aria
+spider_link_mon_servers	Aria
+spider_link_failed_log	Aria
+
+deinit
+for master_1
+for child2
+child2_1
+child2_2
+child2_3
+for child3
+child3_1
+child3_2
+child3_3
+
+end of test
diff --git a/storage/spider/mysql-test/spider/t/show_system_tables.test b/storage/spider/mysql-test/spider/t/show_system_tables.test
new file mode 100644
index 00000000000..fd8451a2a60
--- /dev/null
+++ b/storage/spider/mysql-test/spider/t/show_system_tables.test
@@ -0,0 +1,29 @@
+--disable_warnings
+--disable_query_log
+--disable_result_log
+--source test_init.inc
+--enable_result_log
+--enable_query_log
+--enable_warnings
+
+--echo
+--echo Show system tables on the Spider node
+--connection master_1
+--disable_query_log
+echo SELECT table_name, engine FROM information_schema.tables 
+    WHERE table_schema = 'mysql' AND table_name like '%spider_%';
+eval SELECT table_name, engine FROM information_schema.tables
+    WHERE table_schema = 'mysql' AND table_name like '%spider_%';
+--enable_query_log
+
+--echo
+--echo deinit
+--disable_warnings
+--disable_query_log
+--disable_result_log
+--source test_deinit.inc
+--enable_result_log
+--enable_query_log
+--enable_warnings
+--echo
+--echo end of test
diff --git a/storage/spider/scripts/install_spider.sql b/storage/spider/scripts/install_spider.sql
index c5a86caa219..9b2e5c480ab 100644
--- a/storage/spider/scripts/install_spider.sql
+++ b/storage/spider/scripts/install_spider.sql
@@ -400,6 +400,71 @@ begin
     alter table mysql.spider_table_crd
     modify table_name char(199) not null default '';
   end if;
+
+  -- Fix for MariaDB 10.4: Crash-Safe system tables
+  select ENGINE INTO @engine_name from INFORMATION_SCHEMA.TABLES
+    where TABLE_SCHEMA = 'mysql'
+      AND TABLE_NAME = 'spider_link_failed_log';
+    if @engine_name != 'Aria' then
+      alter table mysql.spider_link_failed_log
+        engine=Aria transactional=1;
+  end if;
+  select ENGINE INTO @engine_name from INFORMATION_SCHEMA.TABLES
+    where TABLE_SCHEMA = 'mysql'
+      AND TABLE_NAME = 'spider_link_mon_servers';
+    if @engine_name != 'Aria' then
+      alter table mysql.spider_link_mon_servers
+        engine=Aria transactional=1;
+  end if;
+  select ENGINE INTO @engine_name from INFORMATION_SCHEMA.TABLES
+    where TABLE_SCHEMA = 'mysql'
+      AND TABLE_NAME = 'spider_table_crd';
+    if @engine_name != 'Aria' then
+      alter table mysql.spider_table_crd
+        engine=Aria transactional=1;
+  end if;
+  select ENGINE INTO @engine_name from INFORMATION_SCHEMA.TABLES
+    where TABLE_SCHEMA = 'mysql'
+      AND TABLE_NAME = 'spider_table_position_for_recovery';
+    if @engine_name != 'Aria' then
+      alter table mysql.spider_table_position_for_recovery
+        engine=Aria transactional=1;
+  end if;
+  select ENGINE INTO @engine_name from INFORMATION_SCHEMA.TABLES
+    where TABLE_SCHEMA = 'mysql'
+      AND TABLE_NAME = 'spider_table_sts';
+    if @engine_name != 'Aria' then
+      alter table mysql.spider_table_sts
+        engine=Aria transactional=1;
+  end if;
+  select ENGINE INTO @engine_name from INFORMATION_SCHEMA.TABLES
+    where TABLE_SCHEMA = 'mysql'
+      AND TABLE_NAME = 'spider_tables';
+    if @engine_name != 'Aria' then
+      alter table mysql.spider_tables
+        engine=Aria transactional=1;
+  end if;
+  select ENGINE INTO @engine_name from INFORMATION_SCHEMA.TABLES
+    where TABLE_SCHEMA = 'mysql'
+      AND TABLE_NAME = 'spider_xa';
+    if @engine_name != 'Aria' then
+      alter table mysql.spider_xa
+        engine=Aria transactional=1;
+  end if;
+  select ENGINE INTO @engine_name from INFORMATION_SCHEMA.TABLES
+    where TABLE_SCHEMA = 'mysql'
+      AND TABLE_NAME = 'spider_xa_failed_log';
+    if @engine_name != 'Aria' then
+      alter table mysql.spider_xa_failed_log
+        engine=Aria transactional=1;
+  end if;
+  select ENGINE INTO @engine_name from INFORMATION_SCHEMA.TABLES
+    where TABLE_SCHEMA = 'mysql'
+      AND TABLE_NAME = 'spider_xa_member';
+    if @engine_name != 'Aria' then
+      alter table mysql.spider_xa_member
+        engine=Aria transactional=1;
+  end if;
 end;//
 delimiter ;
 call mysql.spider_fix_system_tables;