MDEV-16787 optimistic parallel replication fails on spider

Add a system variable spider_slave_trx_isolation.
- spider_slave_trx_isolation
  The transaction isolation level when Spider table is used by slave SQL thread.
  -1 : OFF
   0 : READ UNCOMMITTED
   1 : READ COMMITTED
   2 : REPEATABLE READ
   3 : SERIALIZABLE
  The default value is -1

Miscellaneous Spider typos

10 files changed, 378 insertions, 15 deletions

diff --git a/storage/spider/mysql-test/spider/include/slave_trx_isolation_deinit.inc b/storage/spider/mysql-test/spider/include/slave_trx_isolation_deinit.inc
new file mode 100644
index 00000000000..e5f585e5cca
--- /dev/null
+++ b/storage/spider/mysql-test/spider/include/slave_trx_isolation_deinit.inc
@@ -0,0 +1,17 @@
+--let $MASTER_1_COMMENT_2_1= $MASTER_1_COMMENT_2_1_BACKUP
+--let $CHILD2_1_DROP_TABLES= $CHILD2_1_DROP_TABLES_BACKUP
+--let $CHILD2_1_CREATE_TABLES= $CHILD2_1_CREATE_TABLES_BACKUP
+--let $CHILD2_1_SELECT_TABLES= $CHILD2_1_SELECT_TABLES_BACKUP
+--let $OUTPUT_CHILD_GROUP2= $OUTPUT_CHILD_GROUP2_BACKUP
+--let $USE_GENERAL_LOG= $USE_GENERAL_LOG_BACKUP
+--connection slave1_1
+set global spider_slave_trx_isolation= @old_spider_slave_trx_isolation;
+--disable_warnings
+--disable_query_log
+--disable_result_log
+--source ../include/deinit_spider.inc
+--source ../t/slave_test_deinit.inc
+--source ../t/test_deinit.inc
+--enable_result_log
+--enable_query_log
+--enable_warnings
diff --git a/storage/spider/mysql-test/spider/include/slave_trx_isolation_init.inc b/storage/spider/mysql-test/spider/include/slave_trx_isolation_init.inc
new file mode 100644
index 00000000000..94ccf1d3295
--- /dev/null
+++ b/storage/spider/mysql-test/spider/include/slave_trx_isolation_init.inc
@@ -0,0 +1,39 @@
+--disable_warnings
+--disable_query_log
+--disable_result_log
+--source ../t/test_init.inc
+--source ../t/slave_test_init.inc
+--enable_result_log
+--enable_query_log
+--enable_warnings
+--let $MASTER_1_COMMENT_2_1_BACKUP= $MASTER_1_COMMENT_2_1
+let $MASTER_1_COMMENT_2_1=
+  COMMENT='table "tbl_a", srv "s_2_1"';
+--let $CHILD2_1_DROP_TABLES_BACKUP= $CHILD2_1_DROP_TABLES
+let $CHILD2_1_DROP_TABLES=
+  DROP TABLE IF EXISTS tbl_a;
+--let $CHILD2_1_CREATE_TABLES_BACKUP= $CHILD2_1_CREATE_TABLES
+let $CHILD2_1_CREATE_TABLES=
+  CREATE TABLE tbl_a (
+    pkey int NOT NULL,
+    PRIMARY KEY (pkey)
+  ) $CHILD2_1_ENGINE $CHILD2_1_CHARSET;
+--let $CHILD2_1_SELECT_TABLES_BACKUP= $CHILD2_1_SELECT_TABLES
+let $CHILD2_1_SELECT_TABLES=
+  SELECT pkey FROM tbl_a ORDER BY pkey;
+let $CHILD2_1_SELECT_ARGUMENT1=
+  SELECT argument FROM mysql.general_log WHERE argument LIKE '%set %';
+--let $OUTPUT_CHILD_GROUP2_BACKUP= $OUTPUT_CHILD_GROUP2
+--let $OUTPUT_CHILD_GROUP2= 1
+--let $USE_GENERAL_LOG_BACKUP= $USE_GENERAL_LOG
+--let $USE_GENERAL_LOG= 1
+--connection slave1_1
+--disable_warnings
+--disable_query_log
+--disable_result_log
+--source ../include/init_spider.inc
+--enable_result_log
+--enable_query_log
+--enable_warnings
+set @old_spider_slave_trx_isolation= @@spider_slave_trx_isolation;
+set global spider_slave_trx_isolation= 1;
diff --git a/storage/spider/mysql-test/spider/r/slave_trx_isolation.result b/storage/spider/mysql-test/spider/r/slave_trx_isolation.result
new file mode 100644
index 00000000000..167739beaad
--- /dev/null
+++ b/storage/spider/mysql-test/spider/r/slave_trx_isolation.result
@@ -0,0 +1,110 @@
+for master_1
+for child2
+child2_1
+child2_2
+child2_3
+for child3
+child3_1
+child3_2
+child3_3
+for slave1_1
+connection slave1_1;
+set @old_spider_slave_trx_isolation= @@spider_slave_trx_isolation;
+set global spider_slave_trx_isolation= 1;
+
+drop and create databases
+connection master_1;
+DROP DATABASE IF EXISTS auto_test_local;
+CREATE DATABASE auto_test_local;
+USE auto_test_local;
+connection slave1_1;
+DROP DATABASE IF EXISTS auto_test_local;
+CREATE DATABASE auto_test_local;
+USE auto_test_local;
+connection child2_1;
+SET @old_log_output = @@global.log_output;
+SET GLOBAL log_output = 'TABLE,FILE';
+DROP DATABASE IF EXISTS auto_test_remote;
+CREATE DATABASE auto_test_remote;
+USE auto_test_remote;
+
+create table and insert
+connection child2_1;
+CHILD2_1_DROP_TABLES
+CHILD2_1_CREATE_TABLES
+TRUNCATE TABLE mysql.general_log;
+connection master_1;
+connection slave1_1;
+connection master_1;
+DROP TABLE IF EXISTS tbl_a;
+CREATE TABLE tbl_a (
+pkey int NOT NULL,
+PRIMARY KEY (pkey)
+) MASTER_1_ENGINE2 MASTER_1_CHARSET MASTER_1_COMMENT_2_1
+connection master_1;
+SET SESSION sql_log_bin= 1;
+connection slave1_1;
+DROP TABLE IF EXISTS tbl_a;
+CREATE TABLE tbl_a (
+pkey int NOT NULL,
+PRIMARY KEY (pkey)
+) MASTER_1_ENGINE MASTER_1_CHARSET MASTER_1_COMMENT_2_1
+connection master_1;
+INSERT INTO tbl_a (pkey) VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+connection slave1_1;
+connection master_1;
+connection child2_1;
+SELECT argument FROM mysql.general_log WHERE argument LIKE '%set %';
+argument
+set session time_zone = '+00:00'
+SET NAMES utf8
+set session transaction isolation level read committed;set session autocommit = 1;start transaction
+SELECT argument FROM mysql.general_log WHERE argument LIKE '%set %'
+SELECT pkey FROM tbl_a ORDER BY pkey;
+pkey
+0
+1
+2
+3
+4
+5
+6
+7
+8
+9
+connection slave1_1;
+SELECT pkey FROM tbl_a ORDER BY pkey;
+pkey
+0
+1
+2
+3
+4
+5
+6
+7
+8
+9
+
+deinit
+connection master_1;
+DROP DATABASE IF EXISTS auto_test_local;
+connection slave1_1;
+DROP DATABASE IF EXISTS auto_test_local;
+connection child2_1;
+DROP DATABASE IF EXISTS auto_test_remote;
+SET GLOBAL log_output = @old_log_output;
+connection slave1_1;
+set global spider_slave_trx_isolation= @old_spider_slave_trx_isolation;
+for slave1_1
+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/slave_trx_isolation.test b/storage/spider/mysql-test/spider/t/slave_trx_isolation.test
new file mode 100644
index 00000000000..d1dea8546b9
--- /dev/null
+++ b/storage/spider/mysql-test/spider/t/slave_trx_isolation.test
@@ -0,0 +1,166 @@
+--source ../include/slave_trx_isolation_init.inc
+--echo
+--echo drop and create databases
+--connection master_1
+--disable_warnings
+DROP DATABASE IF EXISTS auto_test_local;
+CREATE DATABASE auto_test_local;
+USE auto_test_local;
+if ($USE_REPLICATION)
+{
+  --connection slave1_1
+  DROP DATABASE IF EXISTS auto_test_local;
+  CREATE DATABASE auto_test_local;
+  USE auto_test_local;
+}
+if ($USE_CHILD_GROUP2)
+{
+  --connection child2_1
+  if ($USE_GENERAL_LOG)
+  {
+    SET @old_log_output = @@global.log_output;
+    SET GLOBAL log_output = 'TABLE,FILE';
+  }
+  DROP DATABASE IF EXISTS auto_test_remote;
+  CREATE DATABASE auto_test_remote;
+  USE auto_test_remote;
+}
+--enable_warnings
+
+--echo
+--echo create table and insert
+if ($USE_CHILD_GROUP2)
+{
+  if (!$OUTPUT_CHILD_GROUP2)
+  {
+    --disable_query_log
+    --disable_result_log
+  }
+  --connection child2_1
+  if ($OUTPUT_CHILD_GROUP2)
+  {
+    --disable_query_log
+    echo CHILD2_1_DROP_TABLES;
+    echo CHILD2_1_CREATE_TABLES;
+  }
+  --disable_warnings
+  eval $CHILD2_1_DROP_TABLES;
+  --enable_warnings
+  eval $CHILD2_1_CREATE_TABLES;
+  if ($OUTPUT_CHILD_GROUP2)
+  {
+    --enable_query_log
+  }
+  if ($USE_GENERAL_LOG)
+  {
+    TRUNCATE TABLE mysql.general_log;
+  }
+  if (!$OUTPUT_CHILD_GROUP2)
+  {
+    --enable_query_log
+    --enable_result_log
+  }
+}
+--connection master_1
+if ($USE_REPLICATION)
+{
+  save_master_pos;
+  --connection slave1_1
+  sync_with_master;
+  --connection master_1
+  --disable_query_log
+  SET SESSION sql_log_bin= 0;
+  --enable_query_log
+}
+--disable_warnings
+DROP TABLE IF EXISTS tbl_a;
+--enable_warnings
+--disable_query_log
+echo CREATE TABLE tbl_a (
+    pkey int NOT NULL,
+    PRIMARY KEY (pkey)
+) MASTER_1_ENGINE2 MASTER_1_CHARSET MASTER_1_COMMENT_2_1;
+eval CREATE TABLE tbl_a (
+    pkey int NOT NULL,
+    PRIMARY KEY (pkey)
+) $MASTER_1_ENGINE2 $MASTER_1_CHARSET $MASTER_1_COMMENT_2_1;
+--enable_query_log
+--connection master_1
+if ($USE_REPLICATION)
+{
+  SET SESSION sql_log_bin= 1;
+  --connection slave1_1
+  --disable_warnings
+  DROP TABLE IF EXISTS tbl_a;
+  --enable_warnings
+  --disable_query_log
+  echo CREATE TABLE tbl_a (
+      pkey int NOT NULL,
+      PRIMARY KEY (pkey)
+  ) MASTER_1_ENGINE MASTER_1_CHARSET MASTER_1_COMMENT_2_1;
+  eval CREATE TABLE tbl_a (
+      pkey int NOT NULL,
+      PRIMARY KEY (pkey)
+  ) $MASTER_1_ENGINE $MASTER_1_CHARSET $MASTER_1_COMMENT_2_1;
+  --enable_query_log
+  --connection master_1
+}
+INSERT INTO tbl_a (pkey) VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+if ($USE_REPLICATION)
+{
+  save_master_pos;
+  --connection slave1_1
+  sync_with_master;
+  --connection master_1
+  --disable_query_log
+  SET SESSION sql_log_bin= 0;
+  --enable_query_log
+}
+if ($USE_CHILD_GROUP2)
+{
+  if (!$OUTPUT_CHILD_GROUP2)
+  {
+    --disable_query_log
+    --disable_result_log
+  }
+  --connection child2_1
+  if ($USE_GENERAL_LOG)
+  {
+    eval $CHILD2_1_SELECT_ARGUMENT1;
+  }
+  eval $CHILD2_1_SELECT_TABLES;
+  if (!$OUTPUT_CHILD_GROUP2)
+  {
+    --enable_query_log
+    --enable_result_log
+  }
+}
+if ($USE_REPLICATION)
+{
+  --connection slave1_1
+  SELECT pkey FROM tbl_a ORDER BY pkey;
+}
+
+--echo
+--echo deinit
+--disable_warnings
+--connection master_1
+DROP DATABASE IF EXISTS auto_test_local;
+if ($USE_REPLICATION)
+{
+  --connection slave1_1
+  DROP DATABASE IF EXISTS auto_test_local;
+}
+if ($USE_CHILD_GROUP2)
+{
+  --connection child2_1
+  DROP DATABASE IF EXISTS auto_test_remote;
+  if ($USE_GENERAL_LOG)
+  {
+    SET GLOBAL log_output = @old_log_output;
+  }
+}
+--enable_warnings
+--source ../include/slave_trx_isolation_deinit.inc
+--echo
+--echo end of test
diff --git a/storage/spider/spd_db_mysql.cc b/storage/spider/spd_db_mysql.cc
index 39f448e44c0..ceb38f886aa 100644
--- a/storage/spider/spd_db_mysql.cc
+++ b/storage/spider/spd_db_mysql.cc
@@ -5895,7 +5895,7 @@ int spider_mysql_share::convert_key_hint_str()
   DBUG_ENTER("spider_mysql_share::convert_key_hint_str");
   if (spider_share->access_charset->cset != system_charset_info->cset)
   {
-    /* need convertion */
+    /* need conversion */
     for (roop_count = 0, tmp_key_hint = key_hint;
       roop_count < (int) table_share->keys; roop_count++, tmp_key_hint++)
     {
diff --git a/storage/spider/spd_db_oracle.cc b/storage/spider/spd_db_oracle.cc
index e5a68f1f8c1..82a1e9a0fbc 100644
--- a/storage/spider/spd_db_oracle.cc
+++ b/storage/spider/spd_db_oracle.cc
@@ -4816,7 +4816,7 @@ int spider_oracle_share::convert_key_hint_str()
   DBUG_ENTER("spider_oracle_share::convert_key_hint_str");
   if (spider_share->access_charset->cset != system_charset_info->cset)
   {
-    /* need convertion */
+    /* need conversion */
     for (roop_count = 0, tmp_key_hint = key_hint;
       roop_count < (int) table_share->keys; roop_count++, tmp_key_hint++)
     {
diff --git a/storage/spider/spd_group_by_handler.cc b/storage/spider/spd_group_by_handler.cc
index 86a9f8cba68..3b57092c4ce 100644
--- a/storage/spider/spd_group_by_handler.cc
+++ b/storage/spider/spd_group_by_handler.cc
@@ -822,7 +822,7 @@ void spider_fields::choose_a_conn(
     current_conn_holder = first_conn_holder;
   }
 
-  DBUG_PRINT("info",("spider choosed connection is %p",
+  DBUG_PRINT("info",("spider chosen connection is %p",
     current_conn_holder->conn));
   last_conn_holder = current_conn_holder;
   current_conn_holder = current_conn_holder->next;
@@ -2065,7 +2065,7 @@ group_by_handler *spider_create_group_by_handler(
   fields->check_support_dbton(dbton_bitmap);
   if (!fields->has_conn_holder())
   {
-    DBUG_PRINT("info",("spider all choosed connections can't match dbton_id"));
+    DBUG_PRINT("info",("spider all chosen connections can't match dbton_id"));
     delete fields;
     DBUG_RETURN(NULL);
   }
diff --git a/storage/spider/spd_param.cc b/storage/spider/spd_param.cc
index c10ed3aad80..6b237bbfff8 100644
--- a/storage/spider/spd_param.cc
+++ b/storage/spider/spd_param.cc
@@ -3328,6 +3328,33 @@ uint spider_param_table_crd_thread_count()
 }
 #endif
 
+static int spider_slave_trx_isolation;
+/*
+ -1 :off
+  0 :read uncommitted
+  1 :read committed
+  2 :repeatable read
+  3 :serializable
+ */
+static MYSQL_SYSVAR_INT(
+  slave_trx_isolation,
+  spider_slave_trx_isolation,
+  PLUGIN_VAR_RQCMDARG,
+  "Transaction isolation level when Spider table is used by slave SQL thread",
+  NULL, /* check */
+  NULL, /* update */
+  -1, /* def */
+  -1, /* min */
+  3, /* max */
+  0 /* blk */
+);
+
+int spider_param_slave_trx_isolation()
+{
+  DBUG_ENTER("spider_param_slave_trx_isolation");
+  DBUG_RETURN(spider_slave_trx_isolation);
+}
+
 static struct st_mysql_storage_engine spider_storage_engine =
 { MYSQL_HANDLERTON_INTERFACE_VERSION };
 
@@ -3477,6 +3504,7 @@ static struct st_mysql_sys_var* spider_system_variables[] = {
   MYSQL_SYSVAR(table_sts_thread_count),
   MYSQL_SYSVAR(table_crd_thread_count),
 #endif
+  MYSQL_SYSVAR(slave_trx_isolation),
   NULL
 };
 
diff --git a/storage/spider/spd_param.h b/storage/spider/spd_param.h
index 7919b0c4837..8fdf2e452b2 100644
--- a/storage/spider/spd_param.h
+++ b/storage/spider/spd_param.h
@@ -420,3 +420,4 @@ int spider_param_load_crd_at_startup(
 uint spider_param_table_sts_thread_count();
 uint spider_param_table_crd_thread_count();
 #endif
+int spider_param_slave_trx_isolation();
diff --git a/storage/spider/spd_trx.cc b/storage/spider/spd_trx.cc
index 565665dc6f7..6204ca9852c 100644
--- a/storage/spider/spd_trx.cc
+++ b/storage/spider/spd_trx.cc
@@ -1586,21 +1586,23 @@ int spider_check_and_set_trx_isolation(
   SPIDER_CONN *conn,
   int *need_mon
 ) {
+  THD *thd = conn->thd;
   int trx_isolation;
   DBUG_ENTER("spider_check_and_set_trx_isolation");
-
-  trx_isolation = thd_tx_isolation(conn->thd);
-  DBUG_PRINT("info",("spider local trx_isolation=%d", trx_isolation));
-/*
-  DBUG_PRINT("info",("spider conn->trx_isolation=%d", conn->trx_isolation));
-  if (conn->trx_isolation != trx_isolation)
+  if (thd->system_thread == SYSTEM_THREAD_SLAVE_SQL)
   {
-*/
-    spider_conn_queue_trx_isolation(conn, trx_isolation);
-/*
-    conn->trx_isolation = trx_isolation;
+    if ((trx_isolation = spider_param_slave_trx_isolation()) == -1)
+    {
+      trx_isolation = thd_tx_isolation(thd);
+      DBUG_PRINT("info",("spider local trx_isolation=%d", trx_isolation));
+    } else {
+      DBUG_PRINT("info",("spider slave trx_isolation=%d", trx_isolation));
+    }
+  } else {
+    trx_isolation = thd_tx_isolation(thd);
+    DBUG_PRINT("info",("spider local trx_isolation=%d", trx_isolation));
   }
-*/
+  spider_conn_queue_trx_isolation(conn, trx_isolation);
   DBUG_RETURN(0);
 }