MDEV-18973 CLIENT_FOUND_ROWS wrong in spider

Get count from last_used_con->info
Contributed by willhan at Tencent Games
Reviewed by Monty

10 files changed, 535 insertions, 0 deletions

diff --git a/storage/spider/mysql-test/spider/bugfix/include/return_found_rows_insert_deinit.inc b/storage/spider/mysql-test/spider/bugfix/include/return_found_rows_insert_deinit.inc
new file mode 100644
index 00000000000..76b7582abfe
--- /dev/null
+++ b/storage/spider/mysql-test/spider/bugfix/include/return_found_rows_insert_deinit.inc
@@ -0,0 +1,11 @@
+--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
+--disable_warnings
+--disable_query_log
+--disable_result_log
+--source ../t/test_deinit.inc
+--enable_result_log
+--enable_query_log
+--enable_warnings
diff --git a/storage/spider/mysql-test/spider/bugfix/include/return_found_rows_insert_init.inc b/storage/spider/mysql-test/spider/bugfix/include/return_found_rows_insert_init.inc
new file mode 100644
index 00000000000..da6778de504
--- /dev/null
+++ b/storage/spider/mysql-test/spider/bugfix/include/return_found_rows_insert_init.inc
@@ -0,0 +1,27 @@
+--disable_warnings
+--disable_query_log
+--disable_result_log
+--source ../t/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 (
+    skey int NOT NULL,
+    dt date NOT NULL,
+    tm time NOT NULL,
+    PRIMARY KEY (skey)
+  ) $CHILD2_1_ENGINE $CHILD2_1_CHARSET;
+--let $CHILD2_1_SELECT_TABLES_BACKUP= $CHILD2_1_SELECT_TABLES
+let $CHILD2_1_SELECT_TABLES=
+  SELECT skey, dt, tm FROM tbl_a ORDER BY skey;
+let $CHILD2_1_SELECT_ARGUMENT1=
+  SELECT argument FROM mysql.general_log WHERE argument LIKE '%insert %' OR argument LIKE '%replace %';
+--let $MASTER_1_SET_COMMAND=set session spider_direct_dup_insert=1 $STR_SEMICOLON
diff --git a/storage/spider/mysql-test/spider/bugfix/include/return_found_rows_update_deinit.inc b/storage/spider/mysql-test/spider/bugfix/include/return_found_rows_update_deinit.inc
new file mode 100644
index 00000000000..76b7582abfe
--- /dev/null
+++ b/storage/spider/mysql-test/spider/bugfix/include/return_found_rows_update_deinit.inc
@@ -0,0 +1,11 @@
+--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
+--disable_warnings
+--disable_query_log
+--disable_result_log
+--source ../t/test_deinit.inc
+--enable_result_log
+--enable_query_log
+--enable_warnings
diff --git a/storage/spider/mysql-test/spider/bugfix/include/return_found_rows_update_init.inc b/storage/spider/mysql-test/spider/bugfix/include/return_found_rows_update_init.inc
new file mode 100644
index 00000000000..884ef74c47e
--- /dev/null
+++ b/storage/spider/mysql-test/spider/bugfix/include/return_found_rows_update_init.inc
@@ -0,0 +1,26 @@
+--disable_warnings
+--disable_query_log
+--disable_result_log
+--source ../t/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 (
+    skey int NOT NULL,
+    dt date NOT NULL,
+    tm time NOT NULL,
+    KEY idx1 (skey,dt)
+  ) $CHILD2_1_ENGINE $CHILD2_1_CHARSET;
+--let $CHILD2_1_SELECT_TABLES_BACKUP= $CHILD2_1_SELECT_TABLES
+let $CHILD2_1_SELECT_TABLES=
+  SELECT skey, dt, tm FROM tbl_a ORDER BY skey;
+let $CHILD2_1_SELECT_ARGUMENT1=
+  SELECT argument FROM mysql.general_log WHERE argument LIKE '%update %';
diff --git a/storage/spider/mysql-test/spider/bugfix/r/return_found_rows_insert.result b/storage/spider/mysql-test/spider/bugfix/r/return_found_rows_insert.result
new file mode 100644
index 00000000000..df88d7a5165
--- /dev/null
+++ b/storage/spider/mysql-test/spider/bugfix/r/return_found_rows_insert.result
@@ -0,0 +1,179 @@
+for master_1
+for child2
+child2_1
+child2_2
+child2_3
+for child3
+
+this test is for MDEV-18973
+
+drop and create databases
+connection master_1;
+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';
+CREATE DATABASE auto_test_remote;
+USE auto_test_remote;
+
+create table and insert
+connection child2_1;
+CHILD2_1_CREATE_TABLES
+TRUNCATE TABLE mysql.general_log;
+connection master_1;
+CREATE TABLE tbl_a (
+skey int NOT NULL,
+dt date NOT NULL,
+tm time NOT NULL,
+PRIMARY KEY (skey)
+) MASTER_1_ENGINE MASTER_1_CHARSET MASTER_1_COMMENT_2_1
+INSERT INTO tbl_a (skey, dt, tm) VALUES (0, '2012-01-01', '12:00:00'),(1, '2012-02-01', '12:00:00'),(2, '2012-03-01', '12:00:00'),(3, '2012-04-01', '12:00:00'),(4, '2012-05-01', '12:00:00'),(5, '2012-06-01', '12:00:00'),(6, '2012-07-01', '12:00:00'),(7, '2012-08-01', '12:00:00'),(8, '2012-09-01', '12:00:00'),(9, '2012-10-01', '12:00:00');
+FLUSH TABLES;
+
+select test 1
+connection child2_1;
+TRUNCATE TABLE mysql.general_log;
+EXE_MYSQL -v -v -u root -h localhost -P MASTER_1_MYPORT -S MASTER_1_MYSOCK -e "MASTER_1_SET_COMMAND INSERT IGNORE INTO tbl_a (skey, dt, tm) VALUES (0, '2013-01-01', '13:00:00'),(2, '2013-02-01', '13:00:00'),(4, '2013-03-01', '13:00:00'),(7, '2013-04-01', '13:00:00'),(8, '2013-05-01', '13:00:00'),(10, '2013-06-01', '13:00:00'),(11, '2013-07-01', '13:00:00'),(12, '2013-08-01', '13:00:00'),(13, '2013-09-01', '13:00:00'),(14, '2013-10-01', '13:00:00')" auto_test_local
+--------------
+set session spider_direct_dup_insert=1
+--------------
+
+Query OK, 0 rows affected
+
+--------------
+INSERT IGNORE INTO tbl_a (skey, dt, tm) VALUES (0, '2013-01-01', '13:00:00'),(2, '2013-02-01', '13:00:00'),(4, '2013-03-01', '13:00:00'),(7, '2013-04-01', '13:00:00'),(8, '2013-05-01', '13:00:00'),(10, '2013-06-01', '13:00:00'),(11, '2013-07-01', '13:00:00'),(12, '2013-08-01', '13:00:00'),(13, '2013-09-01', '13:00:00'),(14, '2013-10-01', '13:00:00')
+--------------
+
+Query OK, 5 rows affected
+Records: 10  Duplicates: 5  Warnings: 0
+
+Bye
+connection child2_1;
+SELECT argument FROM mysql.general_log WHERE argument LIKE '%insert %' OR argument LIKE '%replace %';
+argument
+insert ignore into `auto_test_remote`.`tbl_a`(`skey`,`dt`,`tm`)values(0,_latin1'2013-01-01',_latin1'13:00:00'),(2,_latin1'2013-02-01',_latin1'13:00:00'),(4,_latin1'2013-03-01',_latin1'13:00:00'),(7,_latin1'2013-04-01',_latin1'13:00:00'),(8,_latin1'2013-05-01',_latin1'13:00:00'),(10,_latin1'2013-06-01',_latin1'13:00:00'),(11,_latin1'2013-07-01',_latin1'13:00:00'),(12,_latin1'2013-08-01',_latin1'13:00:00'),(13,_latin1'2013-09-01',_latin1'13:00:00'),(14,_latin1'2013-10-01',_latin1'13:00:00')
+SELECT argument FROM mysql.general_log WHERE argument LIKE '%insert %' OR argument LIKE '%replace %'
+SELECT skey, dt, tm FROM tbl_a ORDER BY skey;
+skey	dt	tm
+0	2012-01-01	12:00:00
+1	2012-02-01	12:00:00
+2	2012-03-01	12:00:00
+3	2012-04-01	12:00:00
+4	2012-05-01	12:00:00
+5	2012-06-01	12:00:00
+6	2012-07-01	12:00:00
+7	2012-08-01	12:00:00
+8	2012-09-01	12:00:00
+9	2012-10-01	12:00:00
+10	2013-06-01	13:00:00
+11	2013-07-01	13:00:00
+12	2013-08-01	13:00:00
+13	2013-09-01	13:00:00
+14	2013-10-01	13:00:00
+TRUNCATE TABLE mysql.general_log;
+EXE_MYSQL -v -v -u root -h localhost -P MASTER_1_MYPORT -S MASTER_1_MYSOCK -e "MASTER_1_SET_COMMAND REPLACE INTO tbl_a (skey, dt, tm) VALUES (1, '2012-02-01', '12:00:00'),(3, '2012-12-01', '11:00:00'),(8, '2012-11-30', '11:00:00'),(9, '2012-11-29', '11:00:00'),(10, '2012-11-28', '11:00:00'),(15, '2012-11-27', '11:00:00'),(16, '2012-11-26', '11:00:00'),(17, '2012-11-25', '11:00:00'),(18, '2012-11-24', '11:00:00'),(19, '2012-11-23', '11:00:00')" auto_test_local
+--------------
+set session spider_direct_dup_insert=1
+--------------
+
+Query OK, 0 rows affected
+
+--------------
+REPLACE INTO tbl_a (skey, dt, tm) VALUES (1, '2012-02-01', '12:00:00'),(3, '2012-12-01', '11:00:00'),(8, '2012-11-30', '11:00:00'),(9, '2012-11-29', '11:00:00'),(10, '2012-11-28', '11:00:00'),(15, '2012-11-27', '11:00:00'),(16, '2012-11-26', '11:00:00'),(17, '2012-11-25', '11:00:00'),(18, '2012-11-24', '11:00:00'),(19, '2012-11-23', '11:00:00')
+--------------
+
+Query OK, 14 rows affected
+Records: 10  Duplicates: 4  Warnings: 0
+
+Bye
+connection child2_1;
+SELECT argument FROM mysql.general_log WHERE argument LIKE '%insert %' OR argument LIKE '%replace %';
+argument
+replace into `auto_test_remote`.`tbl_a`(`skey`,`dt`,`tm`)values(1,_latin1'2012-02-01',_latin1'12:00:00'),(3,_latin1'2012-12-01',_latin1'11:00:00'),(8,_latin1'2012-11-30',_latin1'11:00:00'),(9,_latin1'2012-11-29',_latin1'11:00:00'),(10,_latin1'2012-11-28',_latin1'11:00:00'),(15,_latin1'2012-11-27',_latin1'11:00:00'),(16,_latin1'2012-11-26',_latin1'11:00:00'),(17,_latin1'2012-11-25',_latin1'11:00:00'),(18,_latin1'2012-11-24',_latin1'11:00:00'),(19,_latin1'2012-11-23',_latin1'11:00:00')
+SELECT argument FROM mysql.general_log WHERE argument LIKE '%insert %' OR argument LIKE '%replace %'
+SELECT skey, dt, tm FROM tbl_a ORDER BY skey;
+skey	dt	tm
+0	2012-01-01	12:00:00
+1	2012-02-01	12:00:00
+2	2012-03-01	12:00:00
+3	2012-12-01	11:00:00
+4	2012-05-01	12:00:00
+5	2012-06-01	12:00:00
+6	2012-07-01	12:00:00
+7	2012-08-01	12:00:00
+8	2012-11-30	11:00:00
+9	2012-11-29	11:00:00
+10	2012-11-28	11:00:00
+11	2013-07-01	13:00:00
+12	2013-08-01	13:00:00
+13	2013-09-01	13:00:00
+14	2013-10-01	13:00:00
+15	2012-11-27	11:00:00
+16	2012-11-26	11:00:00
+17	2012-11-25	11:00:00
+18	2012-11-24	11:00:00
+19	2012-11-23	11:00:00
+TRUNCATE TABLE mysql.general_log;
+EXE_MYSQL -v -v -u root -h localhost -P MASTER_1_MYPORT -S MASTER_1_MYSOCK -e "MASTER_1_SET_COMMAND INSERT INTO tbl_a (skey, dt, tm) VALUES (1, '2012-11-01', '11:00:00'),(3, '2012-12-01', '11:00:00'),(11, '2012-11-30', '11:00:00'),(15, '2012-11-29', '11:00:00'),(16, '2012-11-28', '11:00:00'),(20, '2012-11-27', '11:00:00'),(21, '2012-11-26', '11:00:00'),(22, '2012-11-25', '11:00:00'),(23, '2012-11-24', '11:00:00'),(24, '2012-11-23', '11:00:00') ON DUPLICATE KEY UPDATE dt=VALUE(dt), tm=VALUE(tm)" auto_test_local
+--------------
+set session spider_direct_dup_insert=1
+--------------
+
+Query OK, 0 rows affected
+
+--------------
+INSERT INTO tbl_a (skey, dt, tm) VALUES (1, '2012-11-01', '11:00:00'),(3, '2012-12-01', '11:00:00'),(11, '2012-11-30', '11:00:00'),(15, '2012-11-29', '11:00:00'),(16, '2012-11-28', '11:00:00'),(20, '2012-11-27', '11:00:00'),(21, '2012-11-26', '11:00:00'),(22, '2012-11-25', '11:00:00'),(23, '2012-11-24', '11:00:00'),(24, '2012-11-23', '11:00:00') ON DUPLICATE KEY UPDATE dt=VALUE(dt), tm=VALUE(tm)
+--------------
+
+Query OK, 13 rows affected
+Records: 10  Duplicates: 4  Warnings: 0
+
+Bye
+connection child2_1;
+SELECT argument FROM mysql.general_log WHERE argument LIKE '%insert %' OR argument LIKE '%replace %';
+argument
+insert high_priority into `auto_test_remote`.`tbl_a`(`skey`,`dt`,`tm`)values(1,_latin1'2012-11-01',_latin1'11:00:00'),(3,_latin1'2012-12-01',_latin1'11:00:00'),(11,_latin1'2012-11-30',_latin1'11:00:00'),(15,_latin1'2012-11-29',_latin1'11:00:00'),(16,_latin1'2012-11-28',_latin1'11:00:00'),(20,_latin1'2012-11-27',_latin1'11:00:00'),(21,_latin1'2012-11-26',_latin1'11:00:00'),(22,_latin1'2012-11-25',_latin1'11:00:00'),(23,_latin1'2012-11-24',_latin1'11:00:00'),(24,_latin1'2012-11-23',_latin1'11:00:00') on duplicate key update `dt` = values(`dt`),`tm` = values(`tm`)
+SELECT argument FROM mysql.general_log WHERE argument LIKE '%insert %' OR argument LIKE '%replace %'
+SELECT skey, dt, tm FROM tbl_a ORDER BY skey;
+skey	dt	tm
+0	2012-01-01	12:00:00
+1	2012-11-01	11:00:00
+2	2012-03-01	12:00:00
+3	2012-12-01	11:00:00
+4	2012-05-01	12:00:00
+5	2012-06-01	12:00:00
+6	2012-07-01	12:00:00
+7	2012-08-01	12:00:00
+8	2012-11-30	11:00:00
+9	2012-11-29	11:00:00
+10	2012-11-28	11:00:00
+11	2012-11-30	11:00:00
+12	2013-08-01	13:00:00
+13	2013-09-01	13:00:00
+14	2013-10-01	13:00:00
+15	2012-11-29	11:00:00
+16	2012-11-28	11:00:00
+17	2012-11-25	11:00:00
+18	2012-11-24	11:00:00
+19	2012-11-23	11:00:00
+20	2012-11-27	11:00:00
+21	2012-11-26	11:00:00
+22	2012-11-25	11:00:00
+23	2012-11-24	11:00:00
+24	2012-11-23	11:00:00
+
+deinit
+connection master_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;
+for master_1
+for child2
+child2_1
+child2_2
+child2_3
+for child3
+
+end of test
diff --git a/storage/spider/mysql-test/spider/bugfix/r/return_found_rows_update.result b/storage/spider/mysql-test/spider/bugfix/r/return_found_rows_update.result
new file mode 100644
index 00000000000..2ebc2693dc5
--- /dev/null
+++ b/storage/spider/mysql-test/spider/bugfix/r/return_found_rows_update.result
@@ -0,0 +1,99 @@
+for master_1
+for child2
+child2_1
+child2_2
+child2_3
+for child3
+
+this test is for MDEV-18973
+
+drop and create databases
+connection master_1;
+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';
+CREATE DATABASE auto_test_remote;
+USE auto_test_remote;
+
+create table and insert
+connection child2_1;
+CHILD2_1_CREATE_TABLES
+TRUNCATE TABLE mysql.general_log;
+connection master_1;
+CREATE TABLE tbl_a (
+skey int NOT NULL,
+dt date NOT NULL,
+tm time NOT NULL,
+KEY idx1 (skey,dt)
+) MASTER_1_ENGINE MASTER_1_CHARSET MASTER_1_COMMENT_2_1
+INSERT INTO tbl_a (skey, dt, tm) VALUES (0, '2012-01-01', '12:00:00'),(1, '2012-02-01', '12:00:00'),(2, '2012-03-01', '12:00:00'),(3, '2012-04-01', '12:00:00'),(4, '2012-05-01', '12:00:00'),(5, '2012-06-01', '12:00:00'),(6, '2012-07-01', '12:00:00'),(7, '2012-08-01', '12:00:00'),(8, '2012-09-01', '12:00:00'),(9, '2012-10-01', '12:00:00');
+INSERT INTO tbl_a (skey, dt, tm) VALUES (0, '2013-01-01', '13:00:00'),(1, '2013-02-01', '13:00:00'),(2, '2013-03-01', '13:00:00'),(3, '2013-04-01', '13:00:00'),(4, '2013-05-01', '13:00:00'),(5, '2013-06-01', '13:00:00'),(6, '2013-07-01', '13:00:00'),(7, '2013-08-01', '13:00:00'),(8, '2013-09-01', '13:00:00'),(9, '2013-10-01', '13:00:00');
+INSERT INTO tbl_a (skey, dt, tm) VALUES (0, '2012-11-01', '11:00:00'),(1, '2012-12-01', '11:00:00'),(2, '2012-11-30', '11:00:00'),(3, '2012-11-29', '11:00:00'),(4, '2012-11-28', '11:00:00'),(5, '2012-11-27', '11:00:00'),(6, '2012-11-26', '11:00:00'),(7, '2012-11-25', '11:00:00'),(8, '2012-11-24', '11:00:00'),(9, '2012-11-23', '11:00:00');
+FLUSH TABLES;
+
+select test 1
+connection child2_1;
+TRUNCATE TABLE mysql.general_log;
+EXE_MYSQL -v -v -u root -h localhost -P MASTER_1_MYPORT -S MASTER_1_MYSOCK -e "UPDATE tbl_a SET tm = '12:00:00' WHERE skey = 0" auto_test_local
+--------------
+UPDATE tbl_a SET tm = '12:00:00' WHERE skey = 0
+--------------
+
+Query OK, 2 rows affected
+Rows matched: 3  Changed: 2  Warnings: 0
+
+Bye
+connection child2_1;
+SELECT argument FROM mysql.general_log WHERE argument LIKE '%update %';
+argument
+update `auto_test_remote`.`tbl_a` set `tm` = _latin1'12:00:00' where (`skey` = 0)
+SELECT argument FROM mysql.general_log WHERE argument LIKE '%update %'
+SELECT skey, dt, tm FROM tbl_a ORDER BY skey;
+skey	dt	tm
+0	2012-01-01	12:00:00
+0	2013-01-01	12:00:00
+0	2012-11-01	12:00:00
+1	2012-12-01	11:00:00
+1	2013-02-01	13:00:00
+1	2012-02-01	12:00:00
+2	2013-03-01	13:00:00
+2	2012-11-30	11:00:00
+2	2012-03-01	12:00:00
+3	2012-11-29	11:00:00
+3	2013-04-01	13:00:00
+3	2012-04-01	12:00:00
+4	2012-11-28	11:00:00
+4	2012-05-01	12:00:00
+4	2013-05-01	13:00:00
+5	2012-11-27	11:00:00
+5	2012-06-01	12:00:00
+5	2013-06-01	13:00:00
+6	2013-07-01	13:00:00
+6	2012-11-26	11:00:00
+6	2012-07-01	12:00:00
+7	2012-11-25	11:00:00
+7	2012-08-01	12:00:00
+7	2013-08-01	13:00:00
+8	2012-09-01	12:00:00
+8	2013-09-01	13:00:00
+8	2012-11-24	11:00:00
+9	2012-10-01	12:00:00
+9	2013-10-01	13:00:00
+9	2012-11-23	11:00:00
+
+deinit
+connection master_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;
+for master_1
+for child2
+child2_1
+child2_2
+child2_3
+for child3
+
+end of test
diff --git a/storage/spider/mysql-test/spider/bugfix/t/return_found_rows_insert.cnf b/storage/spider/mysql-test/spider/bugfix/t/return_found_rows_insert.cnf
new file mode 100644
index 00000000000..05dfd8a0bce
--- /dev/null
+++ b/storage/spider/mysql-test/spider/bugfix/t/return_found_rows_insert.cnf
@@ -0,0 +1,3 @@
+!include include/default_mysqld.cnf
+!include ../my_1_1.cnf
+!include ../my_2_1.cnf
diff --git a/storage/spider/mysql-test/spider/bugfix/t/return_found_rows_insert.test b/storage/spider/mysql-test/spider/bugfix/t/return_found_rows_insert.test
new file mode 100644
index 00000000000..ea2a2147910
--- /dev/null
+++ b/storage/spider/mysql-test/spider/bugfix/t/return_found_rows_insert.test
@@ -0,0 +1,98 @@
+--source ../include/return_found_rows_insert_init.inc
+--echo
+--echo this test is for MDEV-18973
+--echo
+--echo drop and create databases
+
+--connection master_1
+--disable_warnings
+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';
+CREATE DATABASE auto_test_remote;
+USE auto_test_remote;
+--enable_warnings
+
+--echo
+--echo create table and insert
+
+--connection child2_1
+--disable_query_log
+echo CHILD2_1_CREATE_TABLES;
+eval $CHILD2_1_CREATE_TABLES;
+--enable_query_log
+TRUNCATE TABLE mysql.general_log;
+
+--connection master_1
+--disable_query_log
+echo CREATE TABLE tbl_a (
+    skey int NOT NULL,
+    dt date NOT NULL,
+    tm time NOT NULL,
+    PRIMARY KEY (skey)
+) MASTER_1_ENGINE MASTER_1_CHARSET MASTER_1_COMMENT_2_1;
+eval CREATE TABLE tbl_a (
+    skey int NOT NULL,
+    dt date NOT NULL,
+    tm time NOT NULL,
+    PRIMARY KEY (skey)
+) $MASTER_1_ENGINE $MASTER_1_CHARSET $MASTER_1_COMMENT_2_1;
+--enable_query_log
+INSERT INTO tbl_a (skey, dt, tm) VALUES (0, '2012-01-01', '12:00:00'),(1, '2012-02-01', '12:00:00'),(2, '2012-03-01', '12:00:00'),(3, '2012-04-01', '12:00:00'),(4, '2012-05-01', '12:00:00'),(5, '2012-06-01', '12:00:00'),(6, '2012-07-01', '12:00:00'),(7, '2012-08-01', '12:00:00'),(8, '2012-09-01', '12:00:00'),(9, '2012-10-01', '12:00:00');
+FLUSH TABLES;
+
+--echo
+--echo select test 1
+
+--connection child2_1
+TRUNCATE TABLE mysql.general_log;
+
+--disable_query_log
+echo EXE_MYSQL -v -v -u root -h localhost -P MASTER_1_MYPORT -S MASTER_1_MYSOCK -e "MASTER_1_SET_COMMAND INSERT IGNORE INTO tbl_a (skey, dt, tm) VALUES (0, '2013-01-01', '13:00:00'),(2, '2013-02-01', '13:00:00'),(4, '2013-03-01', '13:00:00'),(7, '2013-04-01', '13:00:00'),(8, '2013-05-01', '13:00:00'),(10, '2013-06-01', '13:00:00'),(11, '2013-07-01', '13:00:00'),(12, '2013-08-01', '13:00:00'),(13, '2013-09-01', '13:00:00'),(14, '2013-10-01', '13:00:00')" auto_test_local;
+exec $EXE_MYSQL -v -v -u root -h localhost -P $MASTER_1_MYPORT -S $MASTER_1_MYSOCK -e "$MASTER_1_SET_COMMAND INSERT IGNORE INTO tbl_a (skey, dt, tm) VALUES (0, '2013-01-01', '13:00:00'),(2, '2013-02-01', '13:00:00'),(4, '2013-03-01', '13:00:00'),(7, '2013-04-01', '13:00:00'),(8, '2013-05-01', '13:00:00'),(10, '2013-06-01', '13:00:00'),(11, '2013-07-01', '13:00:00'),(12, '2013-08-01', '13:00:00'),(13, '2013-09-01', '13:00:00'),(14, '2013-10-01', '13:00:00')" auto_test_local;
+--enable_query_log
+
+--connection child2_1
+eval $CHILD2_1_SELECT_ARGUMENT1;
+eval $CHILD2_1_SELECT_TABLES;
+
+TRUNCATE TABLE mysql.general_log;
+
+--disable_query_log
+echo EXE_MYSQL -v -v -u root -h localhost -P MASTER_1_MYPORT -S MASTER_1_MYSOCK -e "MASTER_1_SET_COMMAND REPLACE INTO tbl_a (skey, dt, tm) VALUES (1, '2012-02-01', '12:00:00'),(3, '2012-12-01', '11:00:00'),(8, '2012-11-30', '11:00:00'),(9, '2012-11-29', '11:00:00'),(10, '2012-11-28', '11:00:00'),(15, '2012-11-27', '11:00:00'),(16, '2012-11-26', '11:00:00'),(17, '2012-11-25', '11:00:00'),(18, '2012-11-24', '11:00:00'),(19, '2012-11-23', '11:00:00')" auto_test_local;
+exec $EXE_MYSQL -v -v -u root -h localhost -P $MASTER_1_MYPORT -S $MASTER_1_MYSOCK -e "$MASTER_1_SET_COMMAND REPLACE INTO tbl_a (skey, dt, tm) VALUES (1, '2012-02-01', '12:00:00'),(3, '2012-12-01', '11:00:00'),(8, '2012-11-30', '11:00:00'),(9, '2012-11-29', '11:00:00'),(10, '2012-11-28', '11:00:00'),(15, '2012-11-27', '11:00:00'),(16, '2012-11-26', '11:00:00'),(17, '2012-11-25', '11:00:00'),(18, '2012-11-24', '11:00:00'),(19, '2012-11-23', '11:00:00')" auto_test_local;
+--enable_query_log
+
+--connection child2_1
+eval $CHILD2_1_SELECT_ARGUMENT1;
+eval $CHILD2_1_SELECT_TABLES;
+
+TRUNCATE TABLE mysql.general_log;
+
+--disable_query_log
+echo EXE_MYSQL -v -v -u root -h localhost -P MASTER_1_MYPORT -S MASTER_1_MYSOCK -e "MASTER_1_SET_COMMAND INSERT INTO tbl_a (skey, dt, tm) VALUES (1, '2012-11-01', '11:00:00'),(3, '2012-12-01', '11:00:00'),(11, '2012-11-30', '11:00:00'),(15, '2012-11-29', '11:00:00'),(16, '2012-11-28', '11:00:00'),(20, '2012-11-27', '11:00:00'),(21, '2012-11-26', '11:00:00'),(22, '2012-11-25', '11:00:00'),(23, '2012-11-24', '11:00:00'),(24, '2012-11-23', '11:00:00') ON DUPLICATE KEY UPDATE dt=VALUE(dt), tm=VALUE(tm)" auto_test_local;
+exec $EXE_MYSQL -v -v -u root -h localhost -P $MASTER_1_MYPORT -S $MASTER_1_MYSOCK -e "$MASTER_1_SET_COMMAND INSERT INTO tbl_a (skey, dt, tm) VALUES (1, '2012-11-01', '11:00:00'),(3, '2012-12-01', '11:00:00'),(11, '2012-11-30', '11:00:00'),(15, '2012-11-29', '11:00:00'),(16, '2012-11-28', '11:00:00'),(20, '2012-11-27', '11:00:00'),(21, '2012-11-26', '11:00:00'),(22, '2012-11-25', '11:00:00'),(23, '2012-11-24', '11:00:00'),(24, '2012-11-23', '11:00:00') ON DUPLICATE KEY UPDATE dt=VALUE(dt), tm=VALUE(tm)" auto_test_local;
+--enable_query_log
+
+--connection child2_1
+eval $CHILD2_1_SELECT_ARGUMENT1;
+eval $CHILD2_1_SELECT_TABLES;
+
+--echo
+--echo deinit
+--disable_warnings
+
+--connection master_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;
+
+--enable_warnings
+--source ../include/return_found_rows_insert_deinit.inc
+--echo
+--echo end of test
diff --git a/storage/spider/mysql-test/spider/bugfix/t/return_found_rows_update.cnf b/storage/spider/mysql-test/spider/bugfix/t/return_found_rows_update.cnf
new file mode 100644
index 00000000000..05dfd8a0bce
--- /dev/null
+++ b/storage/spider/mysql-test/spider/bugfix/t/return_found_rows_update.cnf
@@ -0,0 +1,3 @@
+!include include/default_mysqld.cnf
+!include ../my_1_1.cnf
+!include ../my_2_1.cnf
diff --git a/storage/spider/mysql-test/spider/bugfix/t/return_found_rows_update.test b/storage/spider/mysql-test/spider/bugfix/t/return_found_rows_update.test
new file mode 100644
index 00000000000..31f1d9c4c6c
--- /dev/null
+++ b/storage/spider/mysql-test/spider/bugfix/t/return_found_rows_update.test
@@ -0,0 +1,78 @@
+--source ../include/return_found_rows_update_init.inc
+--echo
+--echo this test is for MDEV-18973
+--echo
+--echo drop and create databases
+
+--connection master_1
+--disable_warnings
+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';
+CREATE DATABASE auto_test_remote;
+USE auto_test_remote;
+--enable_warnings
+
+--echo
+--echo create table and insert
+
+--connection child2_1
+--disable_query_log
+echo CHILD2_1_CREATE_TABLES;
+eval $CHILD2_1_CREATE_TABLES;
+--enable_query_log
+TRUNCATE TABLE mysql.general_log;
+
+--connection master_1
+--disable_query_log
+echo CREATE TABLE tbl_a (
+    skey int NOT NULL,
+    dt date NOT NULL,
+    tm time NOT NULL,
+    KEY idx1 (skey,dt)
+) MASTER_1_ENGINE MASTER_1_CHARSET MASTER_1_COMMENT_2_1;
+eval CREATE TABLE tbl_a (
+    skey int NOT NULL,
+    dt date NOT NULL,
+    tm time NOT NULL,
+    KEY idx1 (skey,dt)
+) $MASTER_1_ENGINE $MASTER_1_CHARSET $MASTER_1_COMMENT_2_1;
+--enable_query_log
+INSERT INTO tbl_a (skey, dt, tm) VALUES (0, '2012-01-01', '12:00:00'),(1, '2012-02-01', '12:00:00'),(2, '2012-03-01', '12:00:00'),(3, '2012-04-01', '12:00:00'),(4, '2012-05-01', '12:00:00'),(5, '2012-06-01', '12:00:00'),(6, '2012-07-01', '12:00:00'),(7, '2012-08-01', '12:00:00'),(8, '2012-09-01', '12:00:00'),(9, '2012-10-01', '12:00:00');
+INSERT INTO tbl_a (skey, dt, tm) VALUES (0, '2013-01-01', '13:00:00'),(1, '2013-02-01', '13:00:00'),(2, '2013-03-01', '13:00:00'),(3, '2013-04-01', '13:00:00'),(4, '2013-05-01', '13:00:00'),(5, '2013-06-01', '13:00:00'),(6, '2013-07-01', '13:00:00'),(7, '2013-08-01', '13:00:00'),(8, '2013-09-01', '13:00:00'),(9, '2013-10-01', '13:00:00');
+INSERT INTO tbl_a (skey, dt, tm) VALUES (0, '2012-11-01', '11:00:00'),(1, '2012-12-01', '11:00:00'),(2, '2012-11-30', '11:00:00'),(3, '2012-11-29', '11:00:00'),(4, '2012-11-28', '11:00:00'),(5, '2012-11-27', '11:00:00'),(6, '2012-11-26', '11:00:00'),(7, '2012-11-25', '11:00:00'),(8, '2012-11-24', '11:00:00'),(9, '2012-11-23', '11:00:00');
+FLUSH TABLES;
+
+--echo
+--echo select test 1
+
+--connection child2_1
+TRUNCATE TABLE mysql.general_log;
+
+--disable_query_log
+echo EXE_MYSQL -v -v -u root -h localhost -P MASTER_1_MYPORT -S MASTER_1_MYSOCK -e "UPDATE tbl_a SET tm = '12:00:00' WHERE skey = 0" auto_test_local;
+exec $EXE_MYSQL -v -v -u root -h localhost -P $MASTER_1_MYPORT -S $MASTER_1_MYSOCK -e "UPDATE tbl_a SET tm = '12:00:00' WHERE skey = 0" auto_test_local;
+--enable_query_log
+
+--connection child2_1
+eval $CHILD2_1_SELECT_ARGUMENT1;
+eval $CHILD2_1_SELECT_TABLES;
+
+--echo
+--echo deinit
+--disable_warnings
+
+--connection master_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;
+
+--enable_warnings
+--source ../include/return_found_rows_update_deinit.inc
+--echo
+--echo end of test