MDEV-16248 Row based replication to spider with float column fails on delete/update (#1354)

13 files changed, 361 insertions, 0 deletions

diff --git a/storage/spider/mysql-test/spider/bugfix/include/delete_with_float_column_deinit.inc b/storage/spider/mysql-test/spider/bugfix/include/delete_with_float_column_deinit.inc
new file mode 100644
index 00000000000..4ea789feae8
--- /dev/null
+++ b/storage/spider/mysql-test/spider/bugfix/include/delete_with_float_column_deinit.inc
@@ -0,0 +1,13 @@
+--let $CHILD2_1_SELECT_TABLES= $CHILD2_1_SELECT_TABLES_BACKUP
+--connection master_1
+set session binlog_format= @old_binlog_format;
+--connection slave1_1
+--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/bugfix/include/delete_with_float_column_init.inc b/storage/spider/mysql-test/spider/bugfix/include/delete_with_float_column_init.inc
new file mode 100644
index 00000000000..f2f8635f9e3
--- /dev/null
+++ b/storage/spider/mysql-test/spider/bugfix/include/delete_with_float_column_init.inc
@@ -0,0 +1,24 @@
+--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 $CHILD2_1_SELECT_TABLES_BACKUP= $CHILD2_1_SELECT_TABLES
+let $CHILD2_1_SELECT_TABLES=
+  SELECT pkey, f FROM tbl_a ORDER BY pkey;
+let $CHILD2_1_SELECT_ARGUMENT1=
+  SELECT argument FROM mysql.general_log WHERE argument LIKE '%delete %';
+--connection slave1_1
+--disable_warnings
+--disable_query_log
+--disable_result_log
+--source ../include/init_spider.inc
+--enable_result_log
+--enable_query_log
+--enable_warnings
+--connection master_1
+set @old_binlog_format= @@binlog_format;
+set session binlog_format= 'ROW';
diff --git a/storage/spider/mysql-test/spider/bugfix/include/delete_with_float_column_mariadb_deinit.inc b/storage/spider/mysql-test/spider/bugfix/include/delete_with_float_column_mariadb_deinit.inc
new file mode 100644
index 00000000000..4fd217a5676
--- /dev/null
+++ b/storage/spider/mysql-test/spider/bugfix/include/delete_with_float_column_mariadb_deinit.inc
@@ -0,0 +1,4 @@
+--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
+--source delete_with_float_column_deinit.inc
diff --git a/storage/spider/mysql-test/spider/bugfix/include/delete_with_float_column_mariadb_init.inc b/storage/spider/mysql-test/spider/bugfix/include/delete_with_float_column_mariadb_init.inc
new file mode 100644
index 00000000000..9d3961491e6
--- /dev/null
+++ b/storage/spider/mysql-test/spider/bugfix/include/delete_with_float_column_mariadb_init.inc
@@ -0,0 +1,14 @@
+--source delete_with_float_column_init.inc
+--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", wrapper "mariadb"';
+--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,
+    f float DEFAULT NULL,
+    PRIMARY KEY (pkey)
+  ) $CHILD2_1_ENGINE $CHILD2_1_CHARSET;
diff --git a/storage/spider/mysql-test/spider/bugfix/include/delete_with_float_column_mysql_deinit.inc b/storage/spider/mysql-test/spider/bugfix/include/delete_with_float_column_mysql_deinit.inc
new file mode 100644
index 00000000000..4fd217a5676
--- /dev/null
+++ b/storage/spider/mysql-test/spider/bugfix/include/delete_with_float_column_mysql_deinit.inc
@@ -0,0 +1,4 @@
+--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
+--source delete_with_float_column_deinit.inc
diff --git a/storage/spider/mysql-test/spider/bugfix/include/delete_with_float_column_mysql_init.inc b/storage/spider/mysql-test/spider/bugfix/include/delete_with_float_column_mysql_init.inc
new file mode 100644
index 00000000000..d98f3c12397
--- /dev/null
+++ b/storage/spider/mysql-test/spider/bugfix/include/delete_with_float_column_mysql_init.inc
@@ -0,0 +1,16 @@
+--source delete_with_float_column_init.inc
+--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", wrapper "mysql"';
+--let $CHILD2_1_DROP_TABLES_BACKUP= $CHILD2_1_DROP_TABLES
+let $CHILD2_1_DROP_TABLES=
+  DROP TABLE IF EXISTS tbl_a $STR_SEMICOLON
+  DROP FUNCTION to_float;
+--let $CHILD2_1_CREATE_TABLES_BACKUP= $CHILD2_1_CREATE_TABLES
+let $CHILD2_1_CREATE_TABLES=
+  CREATE TABLE tbl_a (
+    pkey int NOT NULL,
+    f float DEFAULT NULL,
+    PRIMARY KEY (pkey)
+  ) $CHILD2_1_ENGINE $CHILD2_1_CHARSET $STR_SEMICOLON
+  CREATE FUNCTION to_float(a decimal(20,6)) RETURNS float RETURN cast(a as double);
diff --git a/storage/spider/mysql-test/spider/bugfix/r/delete_with_float_column_mariadb.result b/storage/spider/mysql-test/spider/bugfix/r/delete_with_float_column_mariadb.result
new file mode 100644
index 00000000000..d9fb00a09fc
--- /dev/null
+++ b/storage/spider/mysql-test/spider/bugfix/r/delete_with_float_column_mariadb.result
@@ -0,0 +1,85 @@
+for master_1
+for child2
+child2_1
+child2_2
+child2_3
+for child3
+for slave1_1
+connection slave1_1;
+connection master_1;
+set @old_binlog_format= @@binlog_format;
+set session binlog_format= 'ROW';
+
+drop and create databases
+connection master_1;
+CREATE DATABASE auto_test_local;
+USE auto_test_local;
+connection slave1_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;
+connection slave1_1;
+connection master_1;
+SET SESSION sql_log_bin= 0;
+CREATE TABLE tbl_a (
+pkey int NOT NULL,
+f float DEFAULT NULL,
+PRIMARY KEY (pkey)
+) MASTER_1_ENGINE2 MASTER_1_CHARSET MASTER_1_COMMENT_2_1
+SET SESSION sql_log_bin= 1;
+connection slave1_1;
+CREATE TABLE tbl_a (
+pkey int NOT NULL,
+f float DEFAULT NULL,
+PRIMARY KEY (pkey)
+) MASTER_1_ENGINE MASTER_1_CHARSET MASTER_1_COMMENT_2_1
+connection master_1;
+INSERT INTO tbl_a (pkey, f) VALUES (0,NULL);
+INSERT INTO tbl_a (pkey, f) VALUES (1,0.671437);
+DELETE FROM tbl_a WHERE pkey = 0;
+DELETE FROM tbl_a WHERE pkey = 1;
+connection slave1_1;
+connection master_1;
+SET SESSION sql_log_bin= 0;
+connection child2_1;
+SELECT argument FROM mysql.general_log WHERE argument LIKE '%delete %';
+argument
+delete from `auto_test_remote`.`tbl_a` where `pkey` = 0 and `f` is null limit 1
+delete from `auto_test_remote`.`tbl_a` where `pkey` = 1 and `f` = cast(0.671437 as float) limit 1
+SELECT argument FROM mysql.general_log WHERE argument LIKE '%delete %'
+SELECT pkey, f FROM tbl_a ORDER BY pkey;
+pkey	f
+connection slave1_1;
+SELECT pkey, f FROM tbl_a ORDER BY pkey;
+pkey	f
+
+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 master_1;
+set session binlog_format= @old_binlog_format;
+connection slave1_1;
+for slave1_1
+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/delete_with_float_column_mysql.result b/storage/spider/mysql-test/spider/bugfix/r/delete_with_float_column_mysql.result
new file mode 100644
index 00000000000..c41cfe8156a
--- /dev/null
+++ b/storage/spider/mysql-test/spider/bugfix/r/delete_with_float_column_mysql.result
@@ -0,0 +1,85 @@
+for master_1
+for child2
+child2_1
+child2_2
+child2_3
+for child3
+for slave1_1
+connection slave1_1;
+connection master_1;
+set @old_binlog_format= @@binlog_format;
+set session binlog_format= 'ROW';
+
+drop and create databases
+connection master_1;
+CREATE DATABASE auto_test_local;
+USE auto_test_local;
+connection slave1_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;
+connection slave1_1;
+connection master_1;
+SET SESSION sql_log_bin= 0;
+CREATE TABLE tbl_a (
+pkey int NOT NULL,
+f float DEFAULT NULL,
+PRIMARY KEY (pkey)
+) MASTER_1_ENGINE2 MASTER_1_CHARSET MASTER_1_COMMENT_2_1
+SET SESSION sql_log_bin= 1;
+connection slave1_1;
+CREATE TABLE tbl_a (
+pkey int NOT NULL,
+f float DEFAULT NULL,
+PRIMARY KEY (pkey)
+) MASTER_1_ENGINE MASTER_1_CHARSET MASTER_1_COMMENT_2_1
+connection master_1;
+INSERT INTO tbl_a (pkey, f) VALUES (0,NULL);
+INSERT INTO tbl_a (pkey, f) VALUES (1,0.671437);
+DELETE FROM tbl_a WHERE pkey = 0;
+DELETE FROM tbl_a WHERE pkey = 1;
+connection slave1_1;
+connection master_1;
+SET SESSION sql_log_bin= 0;
+connection child2_1;
+SELECT argument FROM mysql.general_log WHERE argument LIKE '%delete %';
+argument
+delete from `auto_test_remote`.`tbl_a` where `pkey` = 0 and `f` is null limit 1
+delete from `auto_test_remote`.`tbl_a` where `pkey` = 1 and `f` = /* create function to_float(a decimal(20,6)) returns float return cast(a as double) */ to_float(0.671437) limit 1
+SELECT argument FROM mysql.general_log WHERE argument LIKE '%delete %'
+SELECT pkey, f FROM tbl_a ORDER BY pkey;
+pkey	f
+connection slave1_1;
+SELECT pkey, f FROM tbl_a ORDER BY pkey;
+pkey	f
+
+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 master_1;
+set session binlog_format= @old_binlog_format;
+connection slave1_1;
+for slave1_1
+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/delete_with_float_column.inc b/storage/spider/mysql-test/spider/bugfix/t/delete_with_float_column.inc
new file mode 100644
index 00000000000..794ebedf355
--- /dev/null
+++ b/storage/spider/mysql-test/spider/bugfix/t/delete_with_float_column.inc
@@ -0,0 +1,98 @@
+--echo
+--echo drop and create databases
+--connection master_1
+--disable_warnings
+CREATE DATABASE auto_test_local;
+USE auto_test_local;
+
+--connection slave1_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;
+--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
+save_master_pos;
+
+--connection slave1_1
+sync_with_master;
+
+--connection master_1
+SET SESSION sql_log_bin= 0;
+--disable_query_log
+echo CREATE TABLE tbl_a (
+    pkey int NOT NULL,
+    f float DEFAULT NULL,
+    PRIMARY KEY (pkey)
+) MASTER_1_ENGINE2 MASTER_1_CHARSET MASTER_1_COMMENT_2_1;
+eval CREATE TABLE tbl_a (
+    pkey int NOT NULL,
+    f float DEFAULT NULL,
+    PRIMARY KEY (pkey)
+) $MASTER_1_ENGINE2 $MASTER_1_CHARSET $MASTER_1_COMMENT_2_1;
+--enable_query_log
+SET SESSION sql_log_bin= 1;
+
+--connection slave1_1
+--disable_query_log
+echo CREATE TABLE tbl_a (
+    pkey int NOT NULL,
+    f float DEFAULT NULL,
+    PRIMARY KEY (pkey)
+) MASTER_1_ENGINE MASTER_1_CHARSET MASTER_1_COMMENT_2_1;
+eval CREATE TABLE tbl_a (
+    pkey int NOT NULL,
+    f float DEFAULT 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, f) VALUES (0,NULL);
+INSERT INTO tbl_a (pkey, f) VALUES (1,0.671437);
+DELETE FROM tbl_a WHERE pkey = 0;
+DELETE FROM tbl_a WHERE pkey = 1;
+save_master_pos;
+
+--connection slave1_1
+sync_with_master;
+
+--connection master_1
+SET SESSION sql_log_bin= 0;
+
+--connection child2_1
+eval $CHILD2_1_SELECT_ARGUMENT1;
+eval $CHILD2_1_SELECT_TABLES;
+
+--connection slave1_1
+SELECT pkey, f FROM tbl_a ORDER BY pkey;
+
+--echo
+--echo deinit
+--disable_warnings
+--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;
+
+--enable_warnings
diff --git a/storage/spider/mysql-test/spider/bugfix/t/delete_with_float_column_mariadb.cnf b/storage/spider/mysql-test/spider/bugfix/t/delete_with_float_column_mariadb.cnf
new file mode 100644
index 00000000000..45019d6c537
--- /dev/null
+++ b/storage/spider/mysql-test/spider/bugfix/t/delete_with_float_column_mariadb.cnf
@@ -0,0 +1,4 @@
+!include include/default_mysqld.cnf
+!include ../my_1_1.cnf
+!include ../my_2_1.cnf
+!include ../my_4_1.cnf
diff --git a/storage/spider/mysql-test/spider/bugfix/t/delete_with_float_column_mariadb.test b/storage/spider/mysql-test/spider/bugfix/t/delete_with_float_column_mariadb.test
new file mode 100644
index 00000000000..ce1a09d6287
--- /dev/null
+++ b/storage/spider/mysql-test/spider/bugfix/t/delete_with_float_column_mariadb.test
@@ -0,0 +1,5 @@
+--source ../include/delete_with_float_column_mariadb_init.inc
+--source delete_with_float_column.inc
+--source ../include/delete_with_float_column_mariadb_deinit.inc
+--echo
+--echo end of test
diff --git a/storage/spider/mysql-test/spider/bugfix/t/delete_with_float_column_mysql.cnf b/storage/spider/mysql-test/spider/bugfix/t/delete_with_float_column_mysql.cnf
new file mode 100644
index 00000000000..45019d6c537
--- /dev/null
+++ b/storage/spider/mysql-test/spider/bugfix/t/delete_with_float_column_mysql.cnf
@@ -0,0 +1,4 @@
+!include include/default_mysqld.cnf
+!include ../my_1_1.cnf
+!include ../my_2_1.cnf
+!include ../my_4_1.cnf
diff --git a/storage/spider/mysql-test/spider/bugfix/t/delete_with_float_column_mysql.test b/storage/spider/mysql-test/spider/bugfix/t/delete_with_float_column_mysql.test
new file mode 100644
index 00000000000..c687f947a91
--- /dev/null
+++ b/storage/spider/mysql-test/spider/bugfix/t/delete_with_float_column_mysql.test
@@ -0,0 +1,5 @@
+--source ../include/delete_with_float_column_mysql_init.inc
+--source delete_with_float_column.inc
+--source ../include/delete_with_float_column_mysql_deinit.inc
+--echo
+--echo end of test