diff options
Diffstat (limited to 'mysql-test/suite/rpl/r')
28 files changed, 1560 insertions, 131 deletions
diff --git a/mysql-test/suite/rpl/r/create_or_replace_mix.result b/mysql-test/suite/rpl/r/create_or_replace_mix.result new file mode 100644 index 00000000000..839032a305c --- /dev/null +++ b/mysql-test/suite/rpl/r/create_or_replace_mix.result @@ -0,0 +1,259 @@ +include/rpl_init.inc [topology=1->2] +create table t2 (a int) engine=myisam; +insert into t2 values (0),(1),(2),(2); +create temporary table t3 (a_in_temporary int) engine=myisam; +# +# Check how create table and create or replace table are logged +# +create table t1 (to_be_deleted int); +CREATE TABLE t1 AS SELECT 1 AS f1; +CREATE OR REPLACE TABLE t1 AS SELECT 2 AS f1; +CREATE OR REPLACE table t1 like t2; +CREATE OR REPLACE table t1 like t3; +drop table t1; +binlog from server 1 +include/show_binlog_events.inc +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; create table t2 (a int) engine=myisam +master-bin.000001 # Gtid # # BEGIN GTID #-#-# +master-bin.000001 # Query # # use `test`; insert into t2 values (0),(1),(2),(2) +master-bin.000001 # Query # # COMMIT +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; create temporary table t3 (a_in_temporary int) engine=myisam +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; CREATE TABLE t1 AS SELECT 1 AS f1 +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; CREATE OR REPLACE TABLE t1 AS SELECT 2 AS f1 +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; CREATE OR REPLACE table t1 like t2 +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; CREATE OR REPLACE table t1 like t3 +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; DROP TABLE `t1` /* generated by server */ +binlog from server 2 +include/show_binlog_events.inc +Log_name Pos Event_type Server_id End_log_pos Info +slave-bin.000001 # Gtid # # GTID #-#-# +slave-bin.000001 # Query # # use `test`; create table t2 (a int) engine=myisam +slave-bin.000001 # Gtid # # BEGIN GTID #-#-# +slave-bin.000001 # Query # # use `test`; insert into t2 values (0),(1),(2),(2) +slave-bin.000001 # Query # # COMMIT +slave-bin.000001 # Gtid # # GTID #-#-# +slave-bin.000001 # Query # # use `test`; create temporary table t3 (a_in_temporary int) engine=myisam +slave-bin.000001 # Gtid # # GTID #-#-# +slave-bin.000001 # Query # # use `test`; create table t1 (to_be_deleted int) +slave-bin.000001 # Gtid # # GTID #-#-# +slave-bin.000001 # Query # # use `test`; CREATE TABLE t1 AS SELECT 1 AS f1 +slave-bin.000001 # Gtid # # GTID #-#-# +slave-bin.000001 # Query # # use `test`; CREATE OR REPLACE TABLE t1 AS SELECT 2 AS f1 +slave-bin.000001 # Gtid # # GTID #-#-# +slave-bin.000001 # Query # # use `test`; CREATE OR REPLACE table t1 like t2 +slave-bin.000001 # Gtid # # GTID #-#-# +slave-bin.000001 # Query # # use `test`; CREATE OR REPLACE table t1 like t3 +slave-bin.000001 # Gtid # # GTID #-#-# +slave-bin.000001 # Query # # use `test`; DROP TABLE IF EXISTS `t1` /* generated by server */ +# +# Ensure that also failed create_or_replace are logged +# +create table t1 (a int); +create or replace table t1; +ERROR 42000: A table must have at least 1 column +drop table if exists t1; +Warnings: +Note 1051 Unknown table 'test.t1' +create or replace table t1 (a int primary key) select a from t2; +ERROR 23000: Duplicate entry '2' for key 'PRIMARY' +create table t1 (a int); +create or replace table t1 (a int primary key) select a from t2; +ERROR 23000: Duplicate entry '2' for key 'PRIMARY' +create temporary table t9 (a int); +create or replace temporary table t9 (a int primary key) select a from t2; +ERROR 23000: Duplicate entry '2' for key 'PRIMARY' +binlog from server 1 +include/show_binlog_events.inc +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; create table t1 (a int) +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; create or replace table t1 +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; DROP TABLE IF EXISTS `t1` /* generated by server */ +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; create table t1 (a int) +master-bin.000001 # Gtid # # BEGIN GTID #-#-# +master-bin.000001 # Query # # use `test`; DROP TABLE IF EXISTS `test`.`t1`/* Generated to handle failed CREATE OR REPLACE */ +master-bin.000001 # Query # # ROLLBACK +master-bin.000001 # Gtid # # BEGIN GTID #-#-# +master-bin.000001 # Query # # DROP TEMPORARY TABLE IF EXISTS `test`.`t9`/* Generated to handle failed CREATE OR REPLACE */ +master-bin.000001 # Query # # ROLLBACK +show tables; +Tables_in_test +t2 +create table t1 (a int); +create or replace table t1 (a int, a int) select * from t2; +ERROR 42S21: Duplicate column name 'a' +include/show_binlog_events.inc +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; create table t1 (a int) +drop table if exists t1,t2; +Warnings: +Note 1051 Unknown table 'test.t1' +drop temporary table if exists t9; +Warnings: +Note 1051 Unknown table 'test.t9' +# +# Ensure that CREATE are run as CREATE OR REPLACE on slave +# +create table t1 (server_2_to_be_delete int); +create table t1 (new_table int); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `new_table` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +# +# Check how CREATE is logged on slave in case of conflicts +# +create table t1 (server_2_to_be_delete int); +create table t2 (server_2_to_be_delete int); +create table t4 (server_2_to_be_delete int); +set @org_binlog_format=@@binlog_format; +set @@global.binlog_format="ROW"; +stop slave; +include/wait_for_slave_to_stop.inc +start slave; +include/wait_for_slave_to_start.inc +create temporary table t9 (a int); +insert into t9 values(1); +create table t1 (new_table int); +create table t2 select * from t9; +create table t4 like t9; +create table t5 select * from t9; +binlog from server 2 +include/show_binlog_events.inc +Log_name Pos Event_type Server_id End_log_pos Info +slave-bin.000001 # Gtid # # GTID #-#-# +slave-bin.000001 # Query # # use `test`; create table t1 (server_2_to_be_delete int) +slave-bin.000001 # Gtid # # GTID #-#-# +slave-bin.000001 # Query # # use `test`; create table t2 (server_2_to_be_delete int) +slave-bin.000001 # Gtid # # GTID #-#-# +slave-bin.000001 # Query # # use `test`; create table t4 (server_2_to_be_delete int) +slave-bin.000001 # Gtid # # GTID #-#-# +slave-bin.000001 # Query # # use `test`; create table t1 (new_table int) +slave-bin.000001 # Gtid # # BEGIN GTID #-#-# +slave-bin.000001 # Query # # use `test`; CREATE TABLE `t2` ( + `a` int(11) DEFAULT NULL +) +slave-bin.000001 # Table_map # # table_id: # (test.t2) +slave-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +slave-bin.000001 # Query # # COMMIT +slave-bin.000001 # Gtid # # GTID #-#-# +slave-bin.000001 # Query # # use `test`; CREATE TABLE `t4` ( + `a` int(11) DEFAULT NULL +) +slave-bin.000001 # Gtid # # BEGIN GTID #-#-# +slave-bin.000001 # Query # # use `test`; CREATE TABLE `t5` ( + `a` int(11) DEFAULT NULL +) +slave-bin.000001 # Table_map # # table_id: # (test.t5) +slave-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +slave-bin.000001 # Query # # COMMIT +set @@global.binlog_format=@org_binlog_format; +stop slave; +include/wait_for_slave_to_stop.inc +start slave; +include/wait_for_slave_to_start.inc +drop table t1,t2,t4,t5,t9; +# +# Ensure that DROP TABLE is run as DROP IF NOT EXISTS +# +create table t1 (server_1_ver_1 int); +create table t4 (server_1_ver_2 int); +drop table t1; +drop table t1,t4; +create table t1 (server_2_ver_2 int); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `server_2_ver_2` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +binlog from server 2 +include/show_binlog_events.inc +Log_name Pos Event_type Server_id End_log_pos Info +slave-bin.000001 # Gtid # # GTID #-#-# +slave-bin.000001 # Query # # use `test`; DROP TABLE `t1` /* generated by server */ +slave-bin.000001 # Gtid # # GTID #-#-# +slave-bin.000001 # Query # # use `test`; DROP TABLE IF EXISTS `t1`,`t4` /* generated by server */ +slave-bin.000001 # Gtid # # GTID #-#-# +slave-bin.000001 # Query # # use `test`; create table t1 (server_2_ver_2 int) +drop table t1; +# +# Ensure that CREATE ... SELECT is recorded as one GTID on the slave +# +create table t1 (a int); +insert into t1 values (0),(1),(2); +create table t2 engine=myisam select * from t1; +create or replace table t2 engine=innodb select * from t1; +binlog from server 2 +include/show_binlog_events.inc +Log_name Pos Event_type Server_id End_log_pos Info +slave-bin.000001 # Gtid # # GTID #-#-# +slave-bin.000001 # Query # # use `test`; create table t1 (a int) +slave-bin.000001 # Gtid # # BEGIN GTID #-#-# +slave-bin.000001 # Table_map # # table_id: # (test.t1) +slave-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +slave-bin.000001 # Query # # COMMIT +slave-bin.000001 # Gtid # # BEGIN GTID #-#-# +slave-bin.000001 # Query # # use `test`; CREATE TABLE `t2` ( + `a` int(11) DEFAULT NULL +) ENGINE=MyISAM +slave-bin.000001 # Table_map # # table_id: # (test.t2) +slave-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +slave-bin.000001 # Query # # COMMIT +slave-bin.000001 # Gtid # # BEGIN GTID #-#-# +slave-bin.000001 # Query # # use `test`; CREATE OR REPLACE TABLE `t2` ( + `a` int(11) DEFAULT NULL +) ENGINE=InnoDB +slave-bin.000001 # Table_map # # table_id: # (test.t2) +slave-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +slave-bin.000001 # Xid # # COMMIT /* XID */ +drop table t1; +# +# Check logging of drop temporary table +# +drop temporary table t3; +set @org_binlog_format=@@binlog_format; +set binlog_format="STATEMENT"; +create temporary table t5 (a int); +drop temporary table t5; +set binlog_format="ROW"; +create temporary table t6 (a int); +drop temporary table t6; +set binlog_format="STATEMENT"; +create temporary table t7 (a int); +set binlog_format="ROW"; +drop temporary table t7; +create temporary table t8 (a int); +set binlog_format="STATEMENT"; +ERROR HY000: Cannot switch out of the row-based binary log format when the session has open temporary tables +drop temporary table t8; +set @@binlog_format=@org_binlog_format; +include/show_binlog_events.inc +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; create temporary table t5 (a int) +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; DROP TEMPORARY TABLE `t5` /* generated by server */ +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # DROP TEMPORARY TABLE IF EXISTS `test`.`t6` /* generated by server */ +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; create temporary table t7 (a int) +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # DROP TEMPORARY TABLE IF EXISTS `test`.`t7` /* generated by server */ +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # DROP TEMPORARY TABLE IF EXISTS `test`.`t8` /* generated by server */ +drop table t2; +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/r/create_or_replace_row.result b/mysql-test/suite/rpl/r/create_or_replace_row.result new file mode 100644 index 00000000000..6e29d02e3bc --- /dev/null +++ b/mysql-test/suite/rpl/r/create_or_replace_row.result @@ -0,0 +1,281 @@ +include/rpl_init.inc [topology=1->2] +create table t2 (a int) engine=myisam; +insert into t2 values (0),(1),(2),(2); +create temporary table t3 (a_in_temporary int) engine=myisam; +# +# Check how create table and create or replace table are logged +# +create table t1 (to_be_deleted int); +CREATE TABLE t1 AS SELECT 1 AS f1; +CREATE OR REPLACE TABLE t1 AS SELECT 2 AS f1; +CREATE OR REPLACE table t1 like t2; +CREATE OR REPLACE table t1 like t3; +drop table t1; +binlog from server 1 +include/show_binlog_events.inc +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; create table t2 (a int) engine=myisam +master-bin.000001 # Gtid # # BEGIN GTID #-#-# +master-bin.000001 # Table_map # # table_id: # (test.t2) +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # COMMIT +master-bin.000001 # Gtid # # BEGIN GTID #-#-# +master-bin.000001 # Query # # use `test`; CREATE TABLE `t1` ( + `f1` int(1) NOT NULL DEFAULT '0' +) +master-bin.000001 # Table_map # # table_id: # (test.t1) +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # COMMIT +master-bin.000001 # Gtid # # BEGIN GTID #-#-# +master-bin.000001 # Query # # use `test`; CREATE OR REPLACE TABLE `t1` ( + `f1` int(1) NOT NULL DEFAULT '0' +) +master-bin.000001 # Table_map # # table_id: # (test.t1) +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # COMMIT +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; CREATE OR REPLACE table t1 like t2 +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; CREATE OR REPLACE TABLE `t1` ( + `a_in_temporary` int(11) DEFAULT NULL +) +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; DROP TABLE `t1` /* generated by server */ +binlog from server 2 +include/show_binlog_events.inc +Log_name Pos Event_type Server_id End_log_pos Info +slave-bin.000001 # Gtid # # GTID #-#-# +slave-bin.000001 # Query # # use `test`; create table t2 (a int) engine=myisam +slave-bin.000001 # Gtid # # BEGIN GTID #-#-# +slave-bin.000001 # Table_map # # table_id: # (test.t2) +slave-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +slave-bin.000001 # Query # # COMMIT +slave-bin.000001 # Gtid # # GTID #-#-# +slave-bin.000001 # Query # # use `test`; create table t1 (to_be_deleted int) +slave-bin.000001 # Gtid # # BEGIN GTID #-#-# +slave-bin.000001 # Query # # use `test`; CREATE TABLE `t1` ( + `f1` int(1) NOT NULL DEFAULT '0' +) +slave-bin.000001 # Table_map # # table_id: # (test.t1) +slave-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +slave-bin.000001 # Query # # COMMIT +slave-bin.000001 # Gtid # # BEGIN GTID #-#-# +slave-bin.000001 # Query # # use `test`; CREATE OR REPLACE TABLE `t1` ( + `f1` int(1) NOT NULL DEFAULT '0' +) +slave-bin.000001 # Table_map # # table_id: # (test.t1) +slave-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +slave-bin.000001 # Query # # COMMIT +slave-bin.000001 # Gtid # # GTID #-#-# +slave-bin.000001 # Query # # use `test`; CREATE OR REPLACE table t1 like t2 +slave-bin.000001 # Gtid # # GTID #-#-# +slave-bin.000001 # Query # # use `test`; CREATE OR REPLACE TABLE `t1` ( + `a_in_temporary` int(11) DEFAULT NULL +) +slave-bin.000001 # Gtid # # GTID #-#-# +slave-bin.000001 # Query # # use `test`; DROP TABLE IF EXISTS `t1` /* generated by server */ +# +# Ensure that also failed create_or_replace are logged +# +create table t1 (a int); +create or replace table t1; +ERROR 42000: A table must have at least 1 column +drop table if exists t1; +Warnings: +Note 1051 Unknown table 'test.t1' +create or replace table t1 (a int primary key) select a from t2; +ERROR 23000: Duplicate entry '2' for key 'PRIMARY' +create table t1 (a int); +create or replace table t1 (a int primary key) select a from t2; +ERROR 23000: Duplicate entry '2' for key 'PRIMARY' +create temporary table t9 (a int); +create or replace temporary table t9 (a int primary key) select a from t2; +ERROR 23000: Duplicate entry '2' for key 'PRIMARY' +binlog from server 1 +include/show_binlog_events.inc +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; create table t1 (a int) +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; create or replace table t1 +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; DROP TABLE IF EXISTS `t1` /* generated by server */ +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; create table t1 (a int) +master-bin.000001 # Gtid # # BEGIN GTID #-#-# +master-bin.000001 # Query # # use `test`; DROP TABLE IF EXISTS `test`.`t1`/* Generated to handle failed CREATE OR REPLACE */ +master-bin.000001 # Query # # ROLLBACK +master-bin.000001 # Gtid # # BEGIN GTID #-#-# +master-bin.000001 # Query # # DROP TEMPORARY TABLE IF EXISTS `test`.`t9`/* Generated to handle failed CREATE OR REPLACE */ +master-bin.000001 # Query # # ROLLBACK +show tables; +Tables_in_test +t2 +create table t1 (a int); +create or replace table t1 (a int, a int) select * from t2; +ERROR 42S21: Duplicate column name 'a' +include/show_binlog_events.inc +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; create table t1 (a int) +drop table if exists t1,t2; +Warnings: +Note 1051 Unknown table 'test.t1' +drop temporary table if exists t9; +Warnings: +Note 1051 Unknown table 'test.t9' +# +# Ensure that CREATE are run as CREATE OR REPLACE on slave +# +create table t1 (server_2_to_be_delete int); +create table t1 (new_table int); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `new_table` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +# +# Check how CREATE is logged on slave in case of conflicts +# +create table t1 (server_2_to_be_delete int); +create table t2 (server_2_to_be_delete int); +create table t4 (server_2_to_be_delete int); +set @org_binlog_format=@@binlog_format; +set @@global.binlog_format="ROW"; +stop slave; +include/wait_for_slave_to_stop.inc +start slave; +include/wait_for_slave_to_start.inc +create temporary table t9 (a int); +insert into t9 values(1); +create table t1 (new_table int); +create table t2 select * from t9; +create table t4 like t9; +create table t5 select * from t9; +binlog from server 2 +include/show_binlog_events.inc +Log_name Pos Event_type Server_id End_log_pos Info +slave-bin.000001 # Gtid # # GTID #-#-# +slave-bin.000001 # Query # # use `test`; create table t1 (server_2_to_be_delete int) +slave-bin.000001 # Gtid # # GTID #-#-# +slave-bin.000001 # Query # # use `test`; create table t2 (server_2_to_be_delete int) +slave-bin.000001 # Gtid # # GTID #-#-# +slave-bin.000001 # Query # # use `test`; create table t4 (server_2_to_be_delete int) +slave-bin.000001 # Gtid # # GTID #-#-# +slave-bin.000001 # Query # # use `test`; create table t1 (new_table int) +slave-bin.000001 # Gtid # # BEGIN GTID #-#-# +slave-bin.000001 # Query # # use `test`; CREATE TABLE `t2` ( + `a` int(11) DEFAULT NULL +) +slave-bin.000001 # Table_map # # table_id: # (test.t2) +slave-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +slave-bin.000001 # Query # # COMMIT +slave-bin.000001 # Gtid # # GTID #-#-# +slave-bin.000001 # Query # # use `test`; CREATE TABLE `t4` ( + `a` int(11) DEFAULT NULL +) +slave-bin.000001 # Gtid # # BEGIN GTID #-#-# +slave-bin.000001 # Query # # use `test`; CREATE TABLE `t5` ( + `a` int(11) DEFAULT NULL +) +slave-bin.000001 # Table_map # # table_id: # (test.t5) +slave-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +slave-bin.000001 # Query # # COMMIT +set @@global.binlog_format=@org_binlog_format; +stop slave; +include/wait_for_slave_to_stop.inc +start slave; +include/wait_for_slave_to_start.inc +drop table t1,t2,t4,t5,t9; +# +# Ensure that DROP TABLE is run as DROP IF NOT EXISTS +# +create table t1 (server_1_ver_1 int); +create table t4 (server_1_ver_2 int); +drop table t1; +drop table t1,t4; +create table t1 (server_2_ver_2 int); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `server_2_ver_2` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +binlog from server 2 +include/show_binlog_events.inc +Log_name Pos Event_type Server_id End_log_pos Info +slave-bin.000001 # Gtid # # GTID #-#-# +slave-bin.000001 # Query # # use `test`; DROP TABLE `t1` /* generated by server */ +slave-bin.000001 # Gtid # # GTID #-#-# +slave-bin.000001 # Query # # use `test`; DROP TABLE IF EXISTS `t1`,`t4` /* generated by server */ +slave-bin.000001 # Gtid # # GTID #-#-# +slave-bin.000001 # Query # # use `test`; create table t1 (server_2_ver_2 int) +drop table t1; +# +# Ensure that CREATE ... SELECT is recorded as one GTID on the slave +# +create table t1 (a int); +insert into t1 values (0),(1),(2); +create table t2 engine=myisam select * from t1; +create or replace table t2 engine=innodb select * from t1; +binlog from server 2 +include/show_binlog_events.inc +Log_name Pos Event_type Server_id End_log_pos Info +slave-bin.000001 # Gtid # # GTID #-#-# +slave-bin.000001 # Query # # use `test`; create table t1 (a int) +slave-bin.000001 # Gtid # # BEGIN GTID #-#-# +slave-bin.000001 # Table_map # # table_id: # (test.t1) +slave-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +slave-bin.000001 # Query # # COMMIT +slave-bin.000001 # Gtid # # BEGIN GTID #-#-# +slave-bin.000001 # Query # # use `test`; CREATE TABLE `t2` ( + `a` int(11) DEFAULT NULL +) ENGINE=MyISAM +slave-bin.000001 # Table_map # # table_id: # (test.t2) +slave-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +slave-bin.000001 # Query # # COMMIT +slave-bin.000001 # Gtid # # BEGIN GTID #-#-# +slave-bin.000001 # Query # # use `test`; CREATE OR REPLACE TABLE `t2` ( + `a` int(11) DEFAULT NULL +) ENGINE=InnoDB +slave-bin.000001 # Table_map # # table_id: # (test.t2) +slave-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +slave-bin.000001 # Xid # # COMMIT /* XID */ +drop table t1; +# +# Check logging of drop temporary table +# +drop temporary table t3; +set @org_binlog_format=@@binlog_format; +set binlog_format="STATEMENT"; +create temporary table t5 (a int); +drop temporary table t5; +set binlog_format="ROW"; +create temporary table t6 (a int); +drop temporary table t6; +set binlog_format="STATEMENT"; +create temporary table t7 (a int); +set binlog_format="ROW"; +drop temporary table t7; +create temporary table t8 (a int); +set binlog_format="STATEMENT"; +ERROR HY000: Cannot switch out of the row-based binary log format when the session has open temporary tables +drop temporary table t8; +set @@binlog_format=@org_binlog_format; +include/show_binlog_events.inc +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; create temporary table t5 (a int) +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; DROP TEMPORARY TABLE `t5` /* generated by server */ +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # DROP TEMPORARY TABLE IF EXISTS `test`.`t6` /* generated by server */ +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; create temporary table t7 (a int) +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # DROP TEMPORARY TABLE IF EXISTS `test`.`t7` /* generated by server */ +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # DROP TEMPORARY TABLE IF EXISTS `test`.`t8` /* generated by server */ +drop table t2; +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/r/create_or_replace_statement.result b/mysql-test/suite/rpl/r/create_or_replace_statement.result new file mode 100644 index 00000000000..8550976e87a --- /dev/null +++ b/mysql-test/suite/rpl/r/create_or_replace_statement.result @@ -0,0 +1,248 @@ +include/rpl_init.inc [topology=1->2] +create table t2 (a int) engine=myisam; +insert into t2 values (0),(1),(2),(2); +create temporary table t3 (a_in_temporary int) engine=myisam; +# +# Check how create table and create or replace table are logged +# +create table t1 (to_be_deleted int); +CREATE TABLE t1 AS SELECT 1 AS f1; +CREATE OR REPLACE TABLE t1 AS SELECT 2 AS f1; +CREATE OR REPLACE table t1 like t2; +CREATE OR REPLACE table t1 like t3; +drop table t1; +binlog from server 1 +include/show_binlog_events.inc +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; create table t2 (a int) engine=myisam +master-bin.000001 # Gtid # # BEGIN GTID #-#-# +master-bin.000001 # Query # # use `test`; insert into t2 values (0),(1),(2),(2) +master-bin.000001 # Query # # COMMIT +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; create temporary table t3 (a_in_temporary int) engine=myisam +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; CREATE TABLE t1 AS SELECT 1 AS f1 +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; CREATE OR REPLACE TABLE t1 AS SELECT 2 AS f1 +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; CREATE OR REPLACE table t1 like t2 +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; CREATE OR REPLACE table t1 like t3 +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; DROP TABLE `t1` /* generated by server */ +binlog from server 2 +include/show_binlog_events.inc +Log_name Pos Event_type Server_id End_log_pos Info +slave-bin.000001 # Gtid # # GTID #-#-# +slave-bin.000001 # Query # # use `test`; create table t2 (a int) engine=myisam +slave-bin.000001 # Gtid # # BEGIN GTID #-#-# +slave-bin.000001 # Query # # use `test`; insert into t2 values (0),(1),(2),(2) +slave-bin.000001 # Query # # COMMIT +slave-bin.000001 # Gtid # # GTID #-#-# +slave-bin.000001 # Query # # use `test`; create temporary table t3 (a_in_temporary int) engine=myisam +slave-bin.000001 # Gtid # # GTID #-#-# +slave-bin.000001 # Query # # use `test`; create table t1 (to_be_deleted int) +slave-bin.000001 # Gtid # # GTID #-#-# +slave-bin.000001 # Query # # use `test`; CREATE TABLE t1 AS SELECT 1 AS f1 +slave-bin.000001 # Gtid # # GTID #-#-# +slave-bin.000001 # Query # # use `test`; CREATE OR REPLACE TABLE t1 AS SELECT 2 AS f1 +slave-bin.000001 # Gtid # # GTID #-#-# +slave-bin.000001 # Query # # use `test`; CREATE OR REPLACE table t1 like t2 +slave-bin.000001 # Gtid # # GTID #-#-# +slave-bin.000001 # Query # # use `test`; CREATE OR REPLACE table t1 like t3 +slave-bin.000001 # Gtid # # GTID #-#-# +slave-bin.000001 # Query # # use `test`; DROP TABLE IF EXISTS `t1` /* generated by server */ +# +# Ensure that also failed create_or_replace are logged +# +create table t1 (a int); +create or replace table t1; +ERROR 42000: A table must have at least 1 column +drop table if exists t1; +Warnings: +Note 1051 Unknown table 'test.t1' +create or replace table t1 (a int primary key) select a from t2; +ERROR 23000: Duplicate entry '2' for key 'PRIMARY' +create table t1 (a int); +create or replace table t1 (a int primary key) select a from t2; +ERROR 23000: Duplicate entry '2' for key 'PRIMARY' +create temporary table t9 (a int); +create or replace temporary table t9 (a int primary key) select a from t2; +ERROR 23000: Duplicate entry '2' for key 'PRIMARY' +binlog from server 1 +include/show_binlog_events.inc +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; create table t1 (a int) +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; create or replace table t1 +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; DROP TABLE IF EXISTS `t1` /* generated by server */ +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; create table t1 (a int) +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; DROP TABLE IF EXISTS `test`.`t1`/* Generated to handle failed CREATE OR REPLACE */ +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; create temporary table t9 (a int) +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # DROP TEMPORARY TABLE IF EXISTS `test`.`t9`/* Generated to handle failed CREATE OR REPLACE */ +show tables; +Tables_in_test +t2 +create table t1 (a int); +create or replace table t1 (a int, a int) select * from t2; +ERROR 42S21: Duplicate column name 'a' +include/show_binlog_events.inc +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; create table t1 (a int) +drop table if exists t1,t2; +Warnings: +Note 1051 Unknown table 'test.t1' +drop temporary table if exists t9; +Warnings: +Note 1051 Unknown table 'test.t9' +# +# Ensure that CREATE are run as CREATE OR REPLACE on slave +# +create table t1 (server_2_to_be_delete int); +create table t1 (new_table int); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `new_table` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +# +# Check how CREATE is logged on slave in case of conflicts +# +create table t1 (server_2_to_be_delete int); +create table t2 (server_2_to_be_delete int); +create table t4 (server_2_to_be_delete int); +set @org_binlog_format=@@binlog_format; +set @@global.binlog_format="ROW"; +stop slave; +include/wait_for_slave_to_stop.inc +start slave; +include/wait_for_slave_to_start.inc +create temporary table t9 (a int); +insert into t9 values(1); +create table t1 (new_table int); +create table t2 select * from t9; +create table t4 like t9; +create table t5 select * from t9; +binlog from server 2 +include/show_binlog_events.inc +Log_name Pos Event_type Server_id End_log_pos Info +slave-bin.000001 # Gtid # # GTID #-#-# +slave-bin.000001 # Query # # use `test`; create table t1 (server_2_to_be_delete int) +slave-bin.000001 # Gtid # # GTID #-#-# +slave-bin.000001 # Query # # use `test`; create table t2 (server_2_to_be_delete int) +slave-bin.000001 # Gtid # # GTID #-#-# +slave-bin.000001 # Query # # use `test`; create table t4 (server_2_to_be_delete int) +slave-bin.000001 # Gtid # # GTID #-#-# +slave-bin.000001 # Query # # use `test`; create table t1 (new_table int) +slave-bin.000001 # Gtid # # BEGIN GTID #-#-# +slave-bin.000001 # Query # # use `test`; CREATE OR REPLACE TABLE `t2` ( + `a` int(11) DEFAULT NULL +) +slave-bin.000001 # Table_map # # table_id: # (test.t2) +slave-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +slave-bin.000001 # Query # # COMMIT +slave-bin.000001 # Gtid # # GTID #-#-# +slave-bin.000001 # Query # # use `test`; CREATE TABLE `t4` ( + `a` int(11) DEFAULT NULL +) +slave-bin.000001 # Gtid # # BEGIN GTID #-#-# +slave-bin.000001 # Query # # use `test`; CREATE TABLE `t5` ( + `a` int(11) DEFAULT NULL +) +slave-bin.000001 # Table_map # # table_id: # (test.t5) +slave-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +slave-bin.000001 # Query # # COMMIT +set @@global.binlog_format=@org_binlog_format; +stop slave; +include/wait_for_slave_to_stop.inc +start slave; +include/wait_for_slave_to_start.inc +drop table t1,t2,t4,t5,t9; +# +# Ensure that DROP TABLE is run as DROP IF NOT EXISTS +# +create table t1 (server_1_ver_1 int); +create table t4 (server_1_ver_2 int); +drop table t1; +drop table t1,t4; +create table t1 (server_2_ver_2 int); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `server_2_ver_2` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +binlog from server 2 +include/show_binlog_events.inc +Log_name Pos Event_type Server_id End_log_pos Info +slave-bin.000001 # Gtid # # GTID #-#-# +slave-bin.000001 # Query # # use `test`; DROP TABLE `t1` /* generated by server */ +slave-bin.000001 # Gtid # # GTID #-#-# +slave-bin.000001 # Query # # use `test`; DROP TABLE IF EXISTS `t1`,`t4` /* generated by server */ +slave-bin.000001 # Gtid # # GTID #-#-# +slave-bin.000001 # Query # # use `test`; create table t1 (server_2_ver_2 int) +drop table t1; +# +# Ensure that CREATE ... SELECT is recorded as one GTID on the slave +# +create table t1 (a int); +insert into t1 values (0),(1),(2); +create table t2 engine=myisam select * from t1; +create or replace table t2 engine=innodb select * from t1; +binlog from server 2 +include/show_binlog_events.inc +Log_name Pos Event_type Server_id End_log_pos Info +slave-bin.000001 # Gtid # # GTID #-#-# +slave-bin.000001 # Query # # use `test`; create table t1 (a int) +slave-bin.000001 # Gtid # # BEGIN GTID #-#-# +slave-bin.000001 # Query # # use `test`; insert into t1 values (0),(1),(2) +slave-bin.000001 # Query # # COMMIT +slave-bin.000001 # Gtid # # GTID #-#-# +slave-bin.000001 # Query # # use `test`; create table t2 engine=myisam select * from t1 +slave-bin.000001 # Gtid # # GTID #-#-# +slave-bin.000001 # Query # # use `test`; create or replace table t2 engine=innodb select * from t1 +drop table t1; +# +# Check logging of drop temporary table +# +drop temporary table t3; +set @org_binlog_format=@@binlog_format; +set binlog_format="STATEMENT"; +create temporary table t5 (a int); +drop temporary table t5; +set binlog_format="ROW"; +create temporary table t6 (a int); +drop temporary table t6; +set binlog_format="STATEMENT"; +create temporary table t7 (a int); +set binlog_format="ROW"; +drop temporary table t7; +create temporary table t8 (a int); +set binlog_format="STATEMENT"; +ERROR HY000: Cannot switch out of the row-based binary log format when the session has open temporary tables +drop temporary table t8; +set @@binlog_format=@org_binlog_format; +include/show_binlog_events.inc +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; create temporary table t5 (a int) +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; DROP TEMPORARY TABLE `t5` /* generated by server */ +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # DROP TEMPORARY TABLE IF EXISTS `test`.`t6` /* generated by server */ +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; create temporary table t7 (a int) +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # DROP TEMPORARY TABLE IF EXISTS `test`.`t7` /* generated by server */ +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # DROP TEMPORARY TABLE IF EXISTS `test`.`t8` /* generated by server */ +drop table t2; +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/r/rpl_000011.result b/mysql-test/suite/rpl/r/rpl_000011.result index 8a59eb746ad..12859a17f95 100644 --- a/mysql-test/suite/rpl/r/rpl_000011.result +++ b/mysql-test/suite/rpl/r/rpl_000011.result @@ -2,7 +2,13 @@ include/master-slave.inc [connection master] create table t1 (n int); insert into t1 values(1); +show global status like 'com_insert'; +Variable_name Value +Com_insert 1 stop slave; +show global status like 'com_insert'; +Variable_name Value +Com_insert 1 include/wait_for_slave_to_stop.inc start slave; include/wait_for_slave_to_start.inc diff --git a/mysql-test/suite/rpl/r/rpl_ddl.result b/mysql-test/suite/rpl/r/rpl_ddl.result index 246ae3790a6..0173bf1e28d 100644 --- a/mysql-test/suite/rpl/r/rpl_ddl.result +++ b/mysql-test/suite/rpl/r/rpl_ddl.result @@ -1617,6 +1617,7 @@ user use test; -------- switch to master ------- +DROP TEMPORARY TABLE mysqltest1.t22; DROP DATABASE mysqltest1; DROP DATABASE mysqltest3; include/rpl_end.inc diff --git a/mysql-test/suite/rpl/r/rpl_drop_view.result b/mysql-test/suite/rpl/r/rpl_drop_view.result index 0688ce42bc2..70a87816e4b 100644 --- a/mysql-test/suite/rpl/r/rpl_drop_view.result +++ b/mysql-test/suite/rpl/r/rpl_drop_view.result @@ -9,9 +9,9 @@ create view v1 as select * from t1; create view v2 as select * from t2; create view v3 as select * from t3; drop view not_exist_view; -ERROR 42S02: Unknown table 'not_exist_view' +ERROR 42S02: Unknown table 'test.not_exist_view' drop view v1, not_exist_view; -ERROR 42S02: Unknown table 'not_exist_view' +ERROR 42S02: Unknown table 'test.not_exist_view' select * from v1; ERROR 42S02: Table 'test.v1' doesn't exist drop view v2, v3; diff --git a/mysql-test/suite/rpl/r/rpl_gtid_basic.result b/mysql-test/suite/rpl/r/rpl_gtid_basic.result index 404424f14a8..fd33221814d 100644 --- a/mysql-test/suite/rpl/r/rpl_gtid_basic.result +++ b/mysql-test/suite/rpl/r/rpl_gtid_basic.result @@ -61,7 +61,13 @@ include/stop_slave.inc INSERT INTO t1 VALUES (5, "m1a"); INSERT INTO t2 VALUES (5, "i1a"); CHANGE MASTER TO master_host = '127.0.0.1', master_port = MASTER_PORT, +MASTER_USE_GTID=SLAVE_POS; +SET GLOBAL sql_slave_skip_counter=1; +ERROR HY000: When using GTID, @@sql_slave_skip_counter can not be used. Instead, setting @@gtid_slave_pos explicitly can be used to skip to after a given GTID position. +CHANGE MASTER TO master_host = '127.0.0.1', master_port = MASTER_PORT, MASTER_USE_GTID=CURRENT_POS; +SET GLOBAL sql_slave_skip_counter=10; +ERROR HY000: When using GTID, @@sql_slave_skip_counter can not be used. Instead, setting @@gtid_slave_pos explicitly can be used to skip to after a given GTID position. include/start_slave.inc SELECT * FROM t1 ORDER BY a; a b @@ -99,13 +105,13 @@ a b 5 j1a *** Now move C to D, after letting it fall a little behind *** include/stop_slave.inc -BEGIN; INSERT INTO t2 VALUES (6, "i6b"); INSERT INTO t2 VALUES (7, "i7b"); -COMMIT; +include/save_master_gtid.inc CHANGE MASTER TO master_host = '127.0.0.1', master_port = SERVER_MYPORT_4, MASTER_USE_GTID=CURRENT_POS; include/start_slave.inc +include/sync_with_master_gtid.inc SELECT * FROM t2 ORDER BY a; a b 1 i1 @@ -116,6 +122,7 @@ a b 6 i6b 7 i7b *** Now change everything back to what it was, to make rpl_end.inc happy +include/sync_with_master_gtid.inc include/stop_slave.inc CHANGE MASTER TO master_host = '127.0.0.1', master_port = MASTER_MYPORT; include/start_slave.inc @@ -123,10 +130,12 @@ include/wait_for_slave_to_start.inc include/stop_slave.inc CHANGE MASTER TO master_host = '127.0.0.1', master_port = SLAVE_MYPORT; include/start_slave.inc +include/sync_with_master_gtid.inc include/stop_slave.inc CHANGE MASTER TO master_host = '127.0.0.1', master_port = SERVER_MYPORT_3; include/start_slave.inc DROP TABLE t1,t2; +include/save_master_gtid.inc *** A few more checks for BINLOG_GTID_POS function *** SELECT BINLOG_GTID_POS(); ERROR 42000: Incorrect parameter count in the call to native function 'BINLOG_GTID_POS' @@ -163,6 +172,7 @@ NULL Warnings: Warning 1916 Got overflow when converting '18446744073709551616' to INT. Value truncated. *** Some tests of @@GLOBAL.gtid_binlog_state *** +include/sync_with_master_gtid.inc include/stop_slave.inc SET @old_state= @@GLOBAL.gtid_binlog_state; SET GLOBAL gtid_binlog_state = ''; @@ -196,10 +206,121 @@ SET GLOBAL gtid_binlog_state = @old_state; CREATE TABLE t1 (a INT PRIMARY KEY); SET gtid_seq_no=100; INSERT INTO t1 VALUES (1); +include/save_master_gtid.inc include/start_slave.inc +include/sync_with_master_gtid.inc SELECT * FROM t1; a 1 Gtid_IO_Pos = '0-1-100' +*** Test @@LAST_GTID and MASTER_GTID_WAIT() *** +DROP TABLE t1; +CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB; +include/stop_slave.inc +SELECT @@last_gtid; +@@last_gtid + +SET gtid_seq_no=110; +SELECT @@last_gtid; +@@last_gtid + +BEGIN; +SELECT @@last_gtid; +@@last_gtid + +INSERT INTO t1 VALUES (2); +SELECT @@last_gtid; +@@last_gtid + +COMMIT; +SELECT @@last_gtid; +@@last_gtid +0-1-110 +SET @pos= '0-1-110'; +SELECT master_gtid_wait(NULL); +master_gtid_wait(NULL) +NULL +SELECT master_gtid_wait('', NULL); +master_gtid_wait('', NULL) +0 +SELECT master_gtid_wait(@pos, 0.5); +master_gtid_wait(@pos, 0.5) +-1 +SELECT * FROM t1 ORDER BY a; +a +SELECT master_gtid_wait(@pos); +include/start_slave.inc +master_gtid_wait(@pos) +0 +SELECT * FROM t1 ORDER BY a; +a +2 +include/stop_slave.inc +SET gtid_domain_id= 1; +INSERT INTO t1 VALUES (3); +SET @pos= '1-1-1,0-1-110'; +SELECT master_gtid_wait(@pos, 0); +master_gtid_wait(@pos, 0) +-1 +SELECT * FROM t1 WHERE a >= 3; +a +SELECT master_gtid_wait(@pos, -1); +include/start_slave.inc +master_gtid_wait(@pos, -1) +0 +SELECT * FROM t1 WHERE a >= 3; +a +3 +SELECT master_gtid_wait('1-1-1', 0); +master_gtid_wait('1-1-1', 0) +0 +SELECT master_gtid_wait('2-1-1,1-1-4,0-1-110'); +SELECT master_gtid_wait('0-1-1000', 0.5); +SELECT master_gtid_wait('0-1-2000'); +SELECT master_gtid_wait('2-1-10'); +SELECT master_gtid_wait('2-1-6', 1); +SELECT master_gtid_wait('2-1-5'); +SELECT master_gtid_wait('2-1-10'); +SELECT master_gtid_wait('2-1-5,1-1-4,0-1-110'); +SELECT master_gtid_wait('2-1-2'); +SELECT master_gtid_wait('1-1-1'); +master_gtid_wait('1-1-1') +0 +SELECT master_gtid_wait('0-1-109'); +SELECT master_gtid_wait('2-1-2', 0.5); +master_gtid_wait('2-1-2', 0.5) +-1 +KILL QUERY KILL_ID; +ERROR 70100: Query execution was interrupted +SET gtid_domain_id=2; +SET gtid_seq_no=2; +INSERT INTO t1 VALUES (4); +master_gtid_wait('2-1-2') +0 +KILL CONNECTION KILL_ID; +ERROR HY000: Lost connection to MySQL server during query +SET gtid_domain_id=1; +SET gtid_seq_no=4; +INSERT INTO t1 VALUES (5); +SET gtid_domain_id=2; +SET gtid_seq_no=5; +INSERT INTO t1 VALUES (6); +master_gtid_wait('2-1-5,1-1-4,0-1-110') +0 +master_gtid_wait('2-1-1,1-1-4,0-1-110') +0 +master_gtid_wait('0-1-1000', 0.5) +-1 +master_gtid_wait('2-1-6', 1) +-1 +master_gtid_wait('0-1-109') +0 +SET gtid_domain_id=2; +SET gtid_seq_no=10; +INSERT INTO t1 VALUES (7); +master_gtid_wait('2-1-10') +0 +master_gtid_wait('2-1-10') +0 DROP TABLE t1; include/rpl_end.inc diff --git a/mysql-test/suite/rpl/r/rpl_gtid_crash.result b/mysql-test/suite/rpl/r/rpl_gtid_crash.result index 209c83ebe17..fdbd1cc6898 100644 --- a/mysql-test/suite/rpl/r/rpl_gtid_crash.result +++ b/mysql-test/suite/rpl/r/rpl_gtid_crash.result @@ -13,10 +13,14 @@ MASTER_USE_GTID=CURRENT_POS; INSERT INTO t1 VALUES (2,1); INSERT INTO t1 VALUES (3,1); include/start_slave.inc +include/save_master_gtid.inc SET SESSION debug_dbug="+d,crash_dispatch_command_before"; SELECT 1; Got one of the listed errors +include/sync_with_master_gtid.inc INSERT INTO t1 VALUES (1000, 3); +include/save_master_gtid.inc +include/sync_with_master_gtid.inc DROP TABLE t1; *** Test crashing the master mysqld and check that binlog state is recovered. *** include/stop_slave.inc @@ -65,22 +69,32 @@ include/stop_slave.inc SET GLOBAL debug_dbug="+d,inject_crash_before_write_rpl_slave_state"; START SLAVE; INSERT INTO t1 VALUES (4); +include/save_master_gtid.inc +include/sync_with_master_gtid.inc include/stop_slave.inc SET GLOBAL debug_dbug="+d,crash_commit_before"; START SLAVE; INSERT INTO t1 VALUES (5); +include/save_master_gtid.inc +include/sync_with_master_gtid.inc include/stop_slave.inc SET GLOBAL debug_dbug="+d,crash_commit_after"; START SLAVE; INSERT INTO t1 VALUES (6); +include/save_master_gtid.inc +include/sync_with_master_gtid.inc include/stop_slave.inc SET GLOBAL debug_dbug="+d,inject_crash_before_flush_rli"; START SLAVE; INSERT INTO t1 VALUES (7); +include/save_master_gtid.inc +include/sync_with_master_gtid.inc include/stop_slave.inc SET GLOBAL debug_dbug="+d,inject_crash_after_flush_rli"; START SLAVE; INSERT INTO t1 VALUES (8); +include/save_master_gtid.inc +include/sync_with_master_gtid.inc SELECT * FROM t1 ORDER BY a; a 1 diff --git a/mysql-test/suite/rpl/r/rpl_gtid_errorhandling.result b/mysql-test/suite/rpl/r/rpl_gtid_errorhandling.result index 98a8c0b2d87..385a374888c 100644 --- a/mysql-test/suite/rpl/r/rpl_gtid_errorhandling.result +++ b/mysql-test/suite/rpl/r/rpl_gtid_errorhandling.result @@ -57,6 +57,7 @@ include/stop_slave.inc RESET MASTER; INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (4); +include/save_master_gtid.inc SET sql_log_bin = 0; INSERT INTO t1 VALUES (2); SET sql_log_bin = 1; @@ -84,6 +85,7 @@ Warning 1948 Specified value for @@gtid_slave_pos contains no value for replicat RESET MASTER; SET GLOBAL gtid_slave_pos = "0-1-1"; START SLAVE; +include/sync_with_master_gtid.inc SELECT * FROM t1 ORDER BY a; a 1 @@ -93,6 +95,7 @@ a *** MDEV-4688: Empty value of @@GLOBAL.gtid_slave_pos *** include/stop_slave.inc INSERT INTO t1 VALUES (5); +include/save_master_gtid.inc SET @old_dbug= @@GLOBAL.debug_dbug; SET GLOBAL debug_dbug="+d,dummy_disable_default_dbug_output"; SET GLOBAL debug_dbug="+d,gtid_fail_after_record_gtid"; @@ -112,6 +115,7 @@ a 4 SET GLOBAL debug_dbug= @old_dbug; START SLAVE SQL_THREAD; +include/sync_with_master_gtid.inc SELECT * FROM t1 ORDER BY a; a 1 @@ -136,6 +140,8 @@ SET GLOBAL gtid_slave_pos = "0-1-3"; START SLAVE; include/wait_for_slave_to_start.inc INSERT INTO t1 VALUES (6); +include/save_master_gtid.inc +include/sync_with_master_gtid.inc SELECT * FROM t1 ORDER BY a; a 1 diff --git a/mysql-test/suite/rpl/r/rpl_gtid_ignored.result b/mysql-test/suite/rpl/r/rpl_gtid_ignored.result index 7d6e65bcb6f..80df3af9232 100644 --- a/mysql-test/suite/rpl/r/rpl_gtid_ignored.result +++ b/mysql-test/suite/rpl/r/rpl_gtid_ignored.result @@ -66,5 +66,7 @@ a 9 DROP TABLE t1; SET GLOBAL gtid_strict_mode= @old_gtid_strict_mode; +SET debug_sync = "reset"; SET GLOBAL gtid_strict_mode= @old_gtid_strict_mode; +SET debug_sync = "reset"; include/rpl_end.inc diff --git a/mysql-test/suite/rpl/r/rpl_gtid_nobinlog.result b/mysql-test/suite/rpl/r/rpl_gtid_nobinlog.result index 5a9acb157c6..9aee74d0b46 100644 --- a/mysql-test/suite/rpl/r/rpl_gtid_nobinlog.result +++ b/mysql-test/suite/rpl/r/rpl_gtid_nobinlog.result @@ -23,6 +23,7 @@ START SLAVE; include/wait_for_slave_to_start.inc INSERT INTO t1 VALUES (3, 2); INSERT INTO t1 VALUES (4, 2); +include/save_master_gtid.inc include/show_binlog_events.inc Log_name Pos Event_type Server_id End_log_pos Info slave-bin.000001 # Gtid # # BEGIN GTID #-#-# @@ -31,6 +32,7 @@ slave-bin.000001 # Query # # COMMIT slave-bin.000001 # Gtid # # BEGIN GTID #-#-# slave-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES (4, 2) slave-bin.000001 # Query # # COMMIT +include/sync_with_master_gtid.inc SELECT * FROM t1 ORDER BY a; a b 1 1 @@ -41,9 +43,11 @@ include/stop_slave.inc RESET SLAVE; INSERT INTO t1 VALUES (5, 1); INSERT INTO t1 VALUES (6, 1); +include/save_master_gtid.inc CHANGE MASTER TO master_host = '127.0.0.1', master_port = MASTER_PORT, master_use_gtid = current_pos; START SLAVE; +include/sync_with_master_gtid.inc SELECT * FROM t1 ORDER BY a; a b 1 1 diff --git a/mysql-test/suite/rpl/r/rpl_gtid_startpos.result b/mysql-test/suite/rpl/r/rpl_gtid_startpos.result index 9be5903b2e9..9ff51ff6b8a 100644 --- a/mysql-test/suite/rpl/r/rpl_gtid_startpos.result +++ b/mysql-test/suite/rpl/r/rpl_gtid_startpos.result @@ -43,10 +43,12 @@ SET sql_log_bin=1; *** Test that we give warning when explict @@gtid_slave_pos=xxx that conflicts with what is in our binary log *** include/stop_slave.inc INSERT INTO t1 VALUES(3); +include/save_master_gtid.inc SET GLOBAL gtid_slave_pos='0-1-3'; CHANGE MASTER TO master_host = '127.0.0.1', master_port = MASTER_PORT, MASTER_USE_GTID=CURRENT_POS; include/start_slave.inc +include/sync_with_master_gtid.inc SELECT * FROM t1 ORDER by a; a 1 @@ -54,6 +56,7 @@ a 3 include/stop_slave.inc INSERT INTO t1 VALUES (4); +include/save_master_gtid.inc INSERT INTO t1 VALUES (10); DELETE FROM t1 WHERE a=10; SET GLOBAL gtid_slave_pos='0-1-4'; @@ -62,6 +65,7 @@ Warning 1947 Specified GTID 0-1-4 conflicts with the binary log which contains a RESET MASTER; SET GLOBAL gtid_slave_pos='0-1-4'; START SLAVE; +include/sync_with_master_gtid.inc SELECT * FROM t1 ORDER by a; a 1 @@ -110,6 +114,8 @@ DROP TABLE t1; SET SQL_LOG_BIN=1; RESET SLAVE; SET GLOBAL gtid_slave_pos=""; +SET @save_slave_ddl_exec_mode=@@global.slave_ddl_exec_mode; +SET GLOBAL slave_ddl_exec_mode=STRICT; include/start_slave.inc SELECT * FROM t1 ORDER BY a; a @@ -125,6 +131,8 @@ STOP SLAVE IO_THREAD; CHANGE MASTER TO MASTER_USE_GTID=CURRENT_POS; include/start_slave.inc INSERT INTO t1 VALUES(3); +include/save_master_gtid.inc +include/sync_with_master_gtid.inc SELECT * FROM t1 ORDER BY a; a 1 @@ -136,6 +144,8 @@ SET SQL_LOG_BIN=1; *** Test reconnecting slave with GTID after purge logs on master. *** FLUSH LOGS; INSERT INTO t1 VALUES (4); +include/save_master_gtid.inc +include/sync_with_master_gtid.inc include/stop_slave.inc FLUSH LOGS; FLUSH LOGS; @@ -144,8 +154,10 @@ show binary logs; Log_name File_size master-bin.000004 # INSERT INTO t1 VALUES (5); +include/save_master_gtid.inc CHANGE MASTER TO master_host = '127.0.0.1', master_port = MASTER_PORT; include/start_slave.inc +include/sync_with_master_gtid.inc SELECT * FROM t1 ORDER BY a; a 1 @@ -160,7 +172,9 @@ SET GLOBAL gtid_slave_pos=""; RESET MASTER; TRUNCATE TABLE t1; INSERT INTO t1 VALUES (10); +include/save_master_gtid.inc include/start_slave.inc +include/sync_with_master_gtid.inc SELECT * FROM t1; a 10 @@ -225,4 +239,5 @@ a 1 2 DROP TABLE t1; +set @@global.slave_ddl_exec_mode=@save_slave_ddl_exec_mode; include/rpl_end.inc diff --git a/mysql-test/suite/rpl/r/rpl_gtid_stop_start.result b/mysql-test/suite/rpl/r/rpl_gtid_stop_start.result index 61c2fc0a0e9..ddcbaf8dffd 100644 --- a/mysql-test/suite/rpl/r/rpl_gtid_stop_start.result +++ b/mysql-test/suite/rpl/r/rpl_gtid_stop_start.result @@ -14,10 +14,12 @@ master-bin.000002 # INSERT INTO t1 VALUES (2); FLUSH LOGS; INSERT INTO t1 VALUES (3); +include/save_master_gtid.inc show binary logs; Log_name File_size master-bin.000002 # master-bin.000003 # +include/sync_with_master_gtid.inc SELECT * FROM t1 ORDER BY a; a 1 @@ -43,6 +45,8 @@ master-bin.000003 # master-bin.000004 # master-bin.000005 # INSERT INTO t1 VALUES(5); +include/save_master_gtid.inc +include/sync_with_master_gtid.inc SELECT * FROM t1 ORDER BY a; a 1 diff --git a/mysql-test/suite/rpl/r/rpl_mariadb_slave_capability.result b/mysql-test/suite/rpl/r/rpl_mariadb_slave_capability.result index f6ce29101f8..9c19062255c 100644 --- a/mysql-test/suite/rpl/r/rpl_mariadb_slave_capability.result +++ b/mysql-test/suite/rpl/r/rpl_mariadb_slave_capability.result @@ -62,6 +62,32 @@ slave-relay-bin.000007 # Query # # # Dummy ev slave-relay-bin.000007 # Table_map # # table_id: # (test.t1) slave-relay-bin.000007 # Write_rows_v1 # # table_id: # flags: STMT_END_F slave-relay-bin.000007 # Query # # COMMIT +*** MDEV-5754: MySQL 5.5 slaves cannot replicate from MariaDB 10.0 *** +CREATE TABLE t2 (a INT PRIMARY KEY) ENGINE=InnoDB; +SET debug_sync='commit_after_release_LOCK_prepare_ordered SIGNAL master_queued1 WAIT_FOR master_cont1'; +INSERT INTO t2 VALUES (1); +SET debug_sync='now WAIT_FOR master_queued1'; +SET debug_sync='commit_after_release_LOCK_prepare_ordered SIGNAL master_queued2'; +INSERT INTO t2 VALUES (2); +SET debug_sync='now WAIT_FOR master_queued2'; +SET debug_sync='now SIGNAL master_cont1'; +SET debug_sync='RESET'; +SET debug_sync='RESET'; +SET debug_sync='RESET'; +include/show_binlog_events.inc +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000003 # Gtid # # BEGIN GTID #-#-# cid=# +master-bin.000003 # Table_map # # table_id: # (test.t2) +master-bin.000003 # Write_rows_v1 # # table_id: # flags: STMT_END_F +master-bin.000003 # Xid # # COMMIT /* XID */ +master-bin.000003 # Gtid # # BEGIN GTID #-#-# cid=# +master-bin.000003 # Table_map # # table_id: # (test.t2) +master-bin.000003 # Write_rows_v1 # # table_id: # flags: STMT_END_F +master-bin.000003 # Xid # # COMMIT /* XID */ +SELECT * FROM t2 ORDER BY a; +a +1 +2 # Test that slave which cannot tolerate holes in binlog stream but # knows the event does not get dummy event include/stop_slave.inc @@ -95,5 +121,5 @@ select @@global.replicate_annotate_row_events; set @@global.debug_dbug= @old_slave_dbug; Clean up. set @@global.binlog_checksum = @old_master_binlog_checksum; -DROP TABLE t1; +DROP TABLE t1, t2; include/rpl_end.inc diff --git a/mysql-test/suite/rpl/r/rpl_mdev382.result b/mysql-test/suite/rpl/r/rpl_mdev382.result index 6cd6aa09ede..bc245a073cd 100644 --- a/mysql-test/suite/rpl/r/rpl_mdev382.result +++ b/mysql-test/suite/rpl/r/rpl_mdev382.result @@ -315,7 +315,7 @@ CREATE TABLE `t``1` ( `a``` INT PRIMARY KEY) ENGINE=innodb; CREATE TABLE `t``2` ( `b``` INT PRIMARY KEY, `c``` INT NOT NULL, FOREIGN KEY fk (`c```) REFERENCES `t``1`(`a```)) ENGINE=innodb; TRUNCATE `t``1`; -ERROR 42000: Cannot truncate a table referenced in a foreign key constraint (`db1``; select 'oops!'`.`t``2`, CONSTRAINT `t``2_ibfk_1` FOREIGN KEY (`c```) REFERENCES `db1``; select 'oops!'`.`t``1` (`a```)) +ERROR 42000: Cannot truncate a table referenced in a foreign key constraint (`db1``; select 'oops!'`.`t``2`, CONSTRAINT `fk` FOREIGN KEY (`c```) REFERENCES `db1``; select 'oops!'`.`t``1` (`a```)) DROP TABLE `t``2`; DROP TABLE `t``1`; *** Test correct quoting of DELETE FROM statement binlogged for HEAP table that is emptied due to server restart diff --git a/mysql-test/suite/rpl/r/rpl_old_master.result b/mysql-test/suite/rpl/r/rpl_old_master.result new file mode 100644 index 00000000000..df5bbe34256 --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_old_master.result @@ -0,0 +1,27 @@ +include/master-slave.inc +[connection master] +include/stop_slave.inc +include/rpl_stop_server.inc [server_number=1] +include/rpl_start_server.inc [server_number=1] +SET @old_parallel= @@GLOBAL.slave_parallel_threads; +SET GLOBAL slave_parallel_threads=10; +CHANGE MASTER TO master_host='127.0.0.1', master_port=SERVER_MYPORT_1, master_user='root', master_log_file='master-bin.000001', master_log_pos=4; +include/start_slave.inc +CREATE TABLE t2 (a INT PRIMARY KEY) ENGINE=InnoDB; +INSERT INTO t2 VALUES (1); +SELECT * FROM t1 ORDER BY a; +a b +1 1 +2 2 +3 4 +4 8 +5 16 +SELECT * FROM t2; +a +1 +include/stop_slave.inc +SET GLOBAL slave_parallel_threads=@old_parallel; +DROP TABLE t1; +include/start_slave.inc +DROP TABLE t2; +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/r/rpl_parallel.result b/mysql-test/suite/rpl/r/rpl_parallel.result index d196e7ea25c..20b75cbbdab 100644 --- a/mysql-test/suite/rpl/r/rpl_parallel.result +++ b/mysql-test/suite/rpl/r/rpl_parallel.result @@ -115,6 +115,7 @@ SET GLOBAL slave_parallel_threads=10; SET debug_sync='RESET'; include/start_slave.inc *** Test that group-committed transactions on the master can replicate in parallel on the slave. *** +SET debug_sync='RESET'; FLUSH LOGS; CREATE TABLE t3 (a INT PRIMARY KEY, b INT) ENGINE=InnoDB; INSERT INTO t3 VALUES (1,1), (3,3), (5,5), (7,7); @@ -141,6 +142,7 @@ INSERT INTO t3 VALUES (6, foo(16, '')); SET debug_sync='now WAIT_FOR master_queued3'; SET debug_sync='now SIGNAL master_cont1'; +SET debug_sync='RESET'; SELECT * FROM t3 ORDER BY a; a b 1 1 @@ -213,6 +215,9 @@ slave-bin.000003 # Query # # use `test`; INSERT INTO t3 VALUES (6, foo(16, slave-bin.000003 # Xid # # COMMIT /* XID */ *** Test STOP SLAVE in parallel mode *** include/stop_slave.inc +SET debug_sync='RESET'; +SET GLOBAL slave_parallel_threads=0; +SET GLOBAL slave_parallel_threads=10; SET binlog_direct_non_transactional_updates=0; SET sql_log_bin=0; CALL mtr.add_suppression("Statement is unsafe because it accesses a non-transactional table after accessing a transactional table within the same transaction"); @@ -227,10 +232,15 @@ INSERT INTO t3 VALUES(21, 21); INSERT INTO t3 VALUES(22, 22); SET binlog_format=@old_format; BEGIN; -INSERT INTO t2 VALUES (21); +INSERT INTO t2 VALUES (21); START SLAVE; +SET @old_dbug= @@GLOBAL.debug_dbug; +SET GLOBAL debug_dbug="+d,rpl_parallel_wait_for_done_trigger"; STOP SLAVE; +SET debug_sync='now WAIT_FOR wait_for_done_waiting'; ROLLBACK; +SET GLOBAL debug_dbug=@old_dbug; +SET debug_sync='RESET'; include/wait_for_slave_to_stop.inc SELECT * FROM t1 WHERE a >= 20 ORDER BY a; a @@ -292,6 +302,7 @@ a b 32 32 33 33 34 34 +SET debug_sync='RESET'; SET sql_log_bin=0; CALL mtr.add_suppression("Query execution was interrupted"); CALL mtr.add_suppression("Commit failed due to failure of an earlier commit on which this one depends"); @@ -303,11 +314,12 @@ SET debug_sync='now WAIT_FOR t1_ready'; KILL THD_ID; SET debug_sync='now WAIT_FOR t2_killed'; SET debug_sync='now SIGNAL t1_cont'; -include/wait_for_slave_sql_error.inc [errno=1317,1963] +include/wait_for_slave_sql_error.inc [errno=1317,1964] STOP SLAVE IO_THREAD; SELECT * FROM t3 WHERE a >= 30 ORDER BY a; a b 31 31 +SET debug_sync='RESET'; SET GLOBAL slave_parallel_threads=0; SET GLOBAL slave_parallel_threads=10; SET sql_log_bin=0; @@ -379,16 +391,15 @@ a b 42 42 43 43 44 44 +SET debug_sync='RESET'; SET debug_sync='now WAIT_FOR t2_query'; SET debug_sync='now SIGNAL t2_cont'; SET debug_sync='now WAIT_FOR t1_ready'; KILL THD_ID; SET debug_sync='now WAIT_FOR t2_killed'; SET debug_sync='now SIGNAL t1_cont'; -include/wait_for_slave_sql_error.inc [errno=1317,1963] -SELECT * FROM t3 WHERE a >= 40 ORDER BY a; -a b -41 41 +include/wait_for_slave_sql_error.inc [errno=1317,1964] +SET debug_sync='RESET'; SET GLOBAL slave_parallel_threads=0; SET GLOBAL slave_parallel_threads=10; SET sql_log_bin=0; @@ -463,16 +474,18 @@ a b 52 52 53 53 54 54 +SET debug_sync='RESET'; SET debug_sync='now WAIT_FOR t2_query'; SET debug_sync='now SIGNAL t2_cont'; SET debug_sync='now WAIT_FOR t1_ready'; KILL THD_ID; SET debug_sync='now WAIT_FOR t2_killed'; SET debug_sync='now SIGNAL t1_cont'; -include/wait_for_slave_sql_error.inc [errno=1317,1963] +include/wait_for_slave_sql_error.inc [errno=1317,1964] SELECT * FROM t3 WHERE a >= 50 ORDER BY a; a b 51 51 +SET debug_sync='RESET'; SET GLOBAL slave_parallel_threads=0; SET GLOBAL slave_parallel_threads=10; SET sql_log_bin=0; @@ -514,14 +527,18 @@ include/start_slave.inc include/stop_slave.inc SET GLOBAL binlog_format=@old_format; SET GLOBAL slave_parallel_threads=0; -SET GLOBAL slave_parallel_threads=3; +SET GLOBAL slave_parallel_threads=4; include/start_slave.inc *** 4. Test killing thread that is waiting to start transaction until previous transaction commits *** SET binlog_format=statement; SET gtid_domain_id=2; +BEGIN; +INSERT INTO t3 VALUES (70, foo(70, +'rpl_parallel_start_waiting_for_prior SIGNAL t4_waiting', '')); INSERT INTO t3 VALUES (60, foo(60, 'ha_write_row_end SIGNAL d2_query WAIT_FOR d2_cont2', 'rpl_parallel_end_of_group SIGNAL d2_done WAIT_FOR d2_cont')); +COMMIT; SET gtid_domain_id=0; SET debug_sync='now WAIT_FOR d2_query'; SET gtid_domain_id=1; @@ -540,15 +557,27 @@ INSERT INTO t3 VALUES (63, foo(63, 'ha_write_row_end SIGNAL d0_query WAIT_FOR d0_cont2', 'rpl_parallel_end_of_group SIGNAL d0_done WAIT_FOR d0_cont')); SET debug_sync='now WAIT_FOR d0_query'; +SET gtid_domain_id=3; +BEGIN; +INSERT INTO t3 VALUES (68, foo(68, +'rpl_parallel_start_waiting_for_prior SIGNAL t2_waiting', '')); +INSERT INTO t3 VALUES (69, foo(69, +'ha_write_row_end SIGNAL d3_query WAIT_FOR d3_cont2', +'rpl_parallel_end_of_group SIGNAL d3_done WAIT_FOR d3_cont')); +COMMIT; +SET gtid_domain_id=0; +SET debug_sync='now WAIT_FOR d3_query'; SET debug_sync='now SIGNAL d2_cont2'; SET debug_sync='now WAIT_FOR d2_done'; SET debug_sync='now SIGNAL d1_cont2'; SET debug_sync='now WAIT_FOR d1_done'; SET debug_sync='now SIGNAL d0_cont2'; SET debug_sync='now WAIT_FOR d0_done'; +SET debug_sync='now SIGNAL d3_cont2'; +SET debug_sync='now WAIT_FOR d3_done'; SET binlog_format=statement; INSERT INTO t3 VALUES (64, foo(64, -'commit_before_prepare_ordered SIGNAL t1_waiting WAIT_FOR t1_cont', '')); +'rpl_parallel_before_mark_start_commit SIGNAL t1_waiting WAIT_FOR t1_cont', '')); SET debug_sync='commit_after_release_LOCK_prepare_ordered SIGNAL master_queued2 WAIT_FOR master_cont2'; INSERT INTO t3 VALUES (65, foo(65, '', '')); SET debug_sync='now WAIT_FOR master_queued2'; @@ -569,23 +598,34 @@ a b 65 65 66 66 67 67 +68 68 +69 69 +70 70 +SET debug_sync='RESET'; SET debug_sync='now SIGNAL d0_cont'; SET debug_sync='now WAIT_FOR t1_waiting'; +SET debug_sync='now SIGNAL d3_cont'; +SET debug_sync='now WAIT_FOR t2_waiting'; SET debug_sync='now SIGNAL d1_cont'; SET debug_sync='now WAIT_FOR t3_waiting'; SET debug_sync='now SIGNAL d2_cont'; +SET debug_sync='now WAIT_FOR t4_waiting'; KILL THD_ID; SET debug_sync='now WAIT_FOR t3_killed'; SET debug_sync='now SIGNAL t1_cont'; -include/wait_for_slave_sql_error.inc [errno=1317,1927,1963] +include/wait_for_slave_sql_error.inc [errno=1317,1927,1964] STOP SLAVE IO_THREAD; -SELECT * FROM t3 WHERE a >= 60 ORDER BY a; +SELECT * FROM t3 WHERE a >= 60 AND a != 65 ORDER BY a; a b 60 60 61 61 62 62 63 63 64 64 +68 68 +69 69 +70 70 +SET debug_sync='RESET'; SET GLOBAL slave_parallel_threads=0; SET GLOBAL slave_parallel_threads=10; SET sql_log_bin=0; @@ -597,11 +637,11 @@ RETURN x; END || SET sql_log_bin=1; -INSERT INTO t3 VALUES (69,0); +UPDATE t3 SET b=b+1 WHERE a=60; include/start_slave.inc SELECT * FROM t3 WHERE a >= 60 ORDER BY a; a b -60 60 +60 61 61 61 62 62 63 63 @@ -609,7 +649,9 @@ a b 65 65 66 66 67 67 -69 0 +68 68 +69 69 +70 70 SET sql_log_bin=0; DROP FUNCTION foo; CREATE FUNCTION foo(x INT, d1 VARCHAR(500), d2 VARCHAR(500)) @@ -634,47 +676,154 @@ include/start_slave.inc SET @old_max_queued= @@GLOBAL.slave_parallel_max_queued; SET GLOBAL slave_parallel_max_queued=9000; SET binlog_format=statement; -INSERT INTO t3 VALUES (70, foo(0, +INSERT INTO t3 VALUES (80, foo(0, 'ha_write_row_end SIGNAL query_waiting WAIT_FOR query_cont', '')); SET debug_sync='now WAIT_FOR query_waiting'; SET @old_dbug= @@GLOBAL.debug_dbug; SET GLOBAL debug_dbug="+d,rpl_parallel_wait_queue_max"; -INSERT INTO t3 VALUES (72, 0); -SELECT * FROM t3 WHERE a >= 70 ORDER BY a; +SELECT * FROM t3 WHERE a >= 80 ORDER BY a; a b -70 0 -71 10000 -72 0 +80 0 +81 10000 SET debug_sync='now WAIT_FOR wait_queue_ready'; KILL THD_ID; SET debug_sync='now WAIT_FOR wait_queue_killed'; SET debug_sync='now SIGNAL query_cont'; -include/wait_for_slave_sql_error.inc [errno=1317,1927,1963] +include/wait_for_slave_sql_error.inc [errno=1317,1927,1964] STOP SLAVE IO_THREAD; -SELECT * FROM t3 WHERE a >= 70 ORDER BY a; -a b -70 0 -71 10000 SET GLOBAL debug_dbug=@old_dbug; SET GLOBAL slave_parallel_max_queued= @old_max_queued; -INSERT INTO t3 VALUES (73,0); +INSERT INTO t3 VALUES (82,0); +SET binlog_format=@old_format; +SET debug_sync='RESET'; include/start_slave.inc -SELECT * FROM t3 WHERE a >= 70 ORDER BY a; +SELECT * FROM t3 WHERE a >= 80 ORDER BY a; a b -70 0 -71 10000 -72 0 -73 0 +80 0 +81 10000 +82 0 include/stop_slave.inc SET GLOBAL binlog_format=@old_format; SET GLOBAL slave_parallel_threads=0; SET GLOBAL slave_parallel_threads=10; include/start_slave.inc +*** MDEV-5788 Incorrect free of rgi->deferred_events in parallel replication *** +include/stop_slave.inc +SET GLOBAL replicate_ignore_table="test.t3"; +SET GLOBAL slave_parallel_threads=2; +include/start_slave.inc +INSERT INTO t3 VALUES (100, rand()); +INSERT INTO t3 VALUES (101, rand()); +INSERT INTO t3 VALUES (102, rand()); +INSERT INTO t3 VALUES (103, rand()); +INSERT INTO t3 VALUES (104, rand()); +INSERT INTO t3 VALUES (105, rand()); +include/stop_slave.inc +SET GLOBAL replicate_ignore_table=""; +include/start_slave.inc +INSERT INTO t3 VALUES (106, rand()); +INSERT INTO t3 VALUES (107, rand()); +SELECT * FROM t3 WHERE a >= 100 ORDER BY a; +a b +106 # +107 # +*** MDEV-5921: In parallel replication, an error is not correctly signalled to the next transaction *** +include/stop_slave.inc +SET GLOBAL slave_parallel_threads=10; +include/start_slave.inc +INSERT INTO t3 VALUES (110, 1); +SELECT * FROM t3 WHERE a >= 110 ORDER BY a; +a b +110 1 +SET sql_log_bin=0; +INSERT INTO t3 VALUES (111, 666); +SET sql_log_bin=1; +SET debug_sync='commit_after_release_LOCK_prepare_ordered SIGNAL master_queued1 WAIT_FOR master_cont1'; +INSERT INTO t3 VALUES (111, 2); +SET debug_sync='now WAIT_FOR master_queued1'; +SET debug_sync='commit_after_release_LOCK_prepare_ordered SIGNAL master_queued2'; +INSERT INTO t3 VALUES (112, 3); +SET debug_sync='now WAIT_FOR master_queued2'; +SET debug_sync='now SIGNAL master_cont1'; +SET debug_sync='RESET'; +include/wait_for_slave_sql_error.inc [errno=1062] +include/wait_for_slave_sql_to_stop.inc +SELECT * FROM t3 WHERE a >= 110 ORDER BY a; +a b +110 1 +111 666 +SET sql_log_bin=0; +DELETE FROM t3 WHERE a=111 AND b=666; +SET sql_log_bin=1; +START SLAVE SQL_THREAD; +SELECT * FROM t3 WHERE a >= 110 ORDER BY a; +a b +110 1 +111 2 +112 3 +***MDEV-5914: Parallel replication deadlock due to InnoDB lock conflicts *** +include/stop_slave.inc +CREATE TABLE t4 (a INT PRIMARY KEY, b INT, KEY b_idx(b)) ENGINE=InnoDB; +INSERT INTO t4 VALUES (1,NULL), (2,2), (3,NULL), (4,4), (5, NULL), (6, 6); +SET debug_sync='commit_after_release_LOCK_prepare_ordered SIGNAL master_queued1 WAIT_FOR master_cont1'; +UPDATE t4 SET b=NULL WHERE a=6; +SET debug_sync='now WAIT_FOR master_queued1'; +SET debug_sync='commit_after_release_LOCK_prepare_ordered SIGNAL master_queued2'; +DELETE FROM t4 WHERE b <= 3; +SET debug_sync='now WAIT_FOR master_queued2'; +SET debug_sync='now SIGNAL master_cont1'; +SET debug_sync='RESET'; +include/start_slave.inc +include/stop_slave.inc +SELECT * FROM t4 ORDER BY a; +a b +1 NULL +3 NULL +4 4 +5 NULL +6 NULL +DELETE FROM t4; +INSERT INTO t4 VALUES (1,NULL), (2,2), (3,NULL), (4,4), (5, NULL), (6, 6); +SET debug_sync='commit_after_release_LOCK_prepare_ordered SIGNAL master_queued1 WAIT_FOR master_cont1'; +INSERT INTO t4 VALUES (7, NULL); +SET debug_sync='now WAIT_FOR master_queued1'; +SET debug_sync='commit_after_release_LOCK_prepare_ordered SIGNAL master_queued2'; +DELETE FROM t4 WHERE b <= 3; +SET debug_sync='now WAIT_FOR master_queued2'; +SET debug_sync='now SIGNAL master_cont1'; +SET debug_sync='RESET'; +include/start_slave.inc +SELECT * FROM t4 ORDER BY a; +a b +1 NULL +3 NULL +4 4 +5 NULL +6 6 +7 NULL +*** MDEV-5938: Exec_master_log_pos not updated at log rotate in parallel replication *** +include/stop_slave.inc +SET GLOBAL slave_parallel_threads=1; +SET DEBUG_SYNC= 'RESET'; +include/start_slave.inc +CREATE TABLE t5 (a INT PRIMARY KEY, b INT); +INSERT INTO t5 VALUES (1,1); +INSERT INTO t5 VALUES (2,2), (3,8); +INSERT INTO t5 VALUES (4,16); +test_check +OK +test_check +OK +FLUSH LOGS; +test_check +OK +test_check +OK include/stop_slave.inc SET GLOBAL slave_parallel_threads=@old_parallel_threads; include/start_slave.inc SET DEBUG_SYNC= 'RESET'; DROP function foo; -DROP TABLE t1,t2,t3; +DROP TABLE t1,t2,t3,t4,t5; SET DEBUG_SYNC= 'RESET'; include/rpl_end.inc diff --git a/mysql-test/suite/rpl/r/rpl_parallel2.result b/mysql-test/suite/rpl/r/rpl_parallel2.result new file mode 100644 index 00000000000..49be484f419 --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_parallel2.result @@ -0,0 +1,18 @@ +include/rpl_init.inc [topology=1->2] +*** MDEV-5509: Incorrect value for Seconds_Behind_Master if parallel replication *** +SET @old_parallel_threads=@@GLOBAL.slave_parallel_threads; +include/stop_slave.inc +SET GLOBAL slave_parallel_threads=5; +include/start_slave.inc +CREATE TABLE t1 (a INT PRIMARY KEY, b INT); +CALL mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave"); +INSERT INTO t1 VALUES (1,sleep(2)); +Warnings: +Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave. +Seconds_Behind_Master should be zero here because the slave is fully caught up and idle. +Seconds_Behind_Master = '0' +include/stop_slave.inc +SET GLOBAL slave_parallel_threads=@old_parallel_threads; +include/start_slave.inc +DROP TABLE t1; +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/r/rpl_parallel_no_log_slave_updates.result b/mysql-test/suite/rpl/r/rpl_parallel_no_log_slave_updates.result index 067d92a962f..0f0b6c9f02a 100644 --- a/mysql-test/suite/rpl/r/rpl_parallel_no_log_slave_updates.result +++ b/mysql-test/suite/rpl/r/rpl_parallel_no_log_slave_updates.result @@ -116,7 +116,9 @@ SET GLOBAL slave_parallel_threads=10; include/start_slave.inc include/stop_slave.inc SET GLOBAL slave_parallel_threads=@old_parallel_threads; +SET debug_sync = 'reset'; include/start_slave.inc DROP function foo; DROP TABLE t3; +SET debug_sync = 'reset'; include/rpl_end.inc diff --git a/mysql-test/suite/rpl/r/rpl_row_create_table.result b/mysql-test/suite/rpl/r/rpl_row_create_table.result index 393e2fdb851..9c04f580c07 100644 --- a/mysql-test/suite/rpl/r/rpl_row_create_table.result +++ b/mysql-test/suite/rpl/r/rpl_row_create_table.result @@ -1,23 +1,24 @@ -stop slave; -drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; -reset master; -reset slave; -drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; -start slave; -**** Resetting master and slave **** -include/stop_slave.inc -RESET SLAVE; -RESET MASTER; -include/start_slave.inc -CREATE TABLE t1 (a INT, b INT); +include/master-slave.inc +[connection master] +include/wait_for_slave_to_stop.inc +include/wait_for_slave_to_start.inc +include/rpl_reset.inc +CREATE TABLE t1 (a INT); +CREATE OR REPLACE TABLE t1 (a INT, b INT); CREATE TABLE t2 (a INT, b INT) ENGINE=Merge; CREATE TABLE t3 (a INT, b INT) CHARSET=utf8; CREATE TABLE t4 (a INT, b INT) ENGINE=Merge CHARSET=utf8; -show binlog events from <binlog_start>; +include/show_binlog_events.inc Log_name Pos Event_type Server_id End_log_pos Info -master-bin.000001 # Query # # use `test`; CREATE TABLE t1 (a INT, b INT) +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; CREATE TABLE t1 (a INT) +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; CREATE OR REPLACE TABLE t1 (a INT, b INT) +master-bin.000001 # Gtid # # GTID #-#-# master-bin.000001 # Query # # use `test`; CREATE TABLE t2 (a INT, b INT) ENGINE=Merge +master-bin.000001 # Gtid # # GTID #-#-# master-bin.000001 # Query # # use `test`; CREATE TABLE t3 (a INT, b INT) CHARSET=utf8 +master-bin.000001 # Gtid # # GTID #-#-# master-bin.000001 # Query # # use `test`; CREATE TABLE t4 (a INT, b INT) ENGINE=Merge CHARSET=utf8 **** On Master **** SHOW CREATE TABLE t1; @@ -111,15 +112,10 @@ NULL 3 6 NULL 4 2 NULL 5 10 NULL 6 12 -**** Resetting master and slave **** -include/stop_slave.inc -RESET SLAVE; -RESET MASTER; -include/start_slave.inc +include/rpl_reset.inc CREATE TABLE t7 (UNIQUE(b)) SELECT a,b FROM tt3; ERROR 23000: Duplicate entry '2' for key 'b' -show binlog events from <binlog_start>; -Log_name Pos Event_type Server_id End_log_pos Info +include/show_binlog_events.inc CREATE TABLE t7 (a INT, b INT UNIQUE); INSERT INTO t7 SELECT a,b FROM tt3; ERROR 23000: Duplicate entry '2' for key 'b' @@ -128,23 +124,20 @@ a b 1 2 2 4 3 6 -show binlog events from <binlog_start>; +include/show_binlog_events.inc Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Gtid # # GTID #-#-# master-bin.000001 # Query # # use `test`; CREATE TABLE t7 (a INT, b INT UNIQUE) -master-bin.000001 # Query # # BEGIN +master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Table_map # # table_id: # (test.t7) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F master-bin.000001 # Query # # COMMIT SELECT * FROM t7 ORDER BY a,b; a b 1 2 2 4 3 6 -**** Resetting master and slave **** -include/stop_slave.inc -RESET SLAVE; -RESET MASTER; -include/start_slave.inc +include/rpl_reset.inc CREATE TEMPORARY TABLE tt4 (a INT, b INT); INSERT INTO tt4 VALUES (4,8), (5,10), (6,12); BEGIN; @@ -152,11 +145,11 @@ INSERT INTO t7 SELECT a,b FROM tt4; ROLLBACK; Warnings: Warning 1196 Some non-transactional changed tables couldn't be rolled back -show binlog events from <binlog_start>; +include/show_binlog_events.inc Log_name Pos Event_type Server_id End_log_pos Info -master-bin.000001 # Query # # BEGIN +master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Table_map # # table_id: # (test.t7) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F master-bin.000001 # Query # # COMMIT SELECT * FROM t7 ORDER BY a,b; a b @@ -174,11 +167,7 @@ a b 4 8 5 10 6 12 -**** Resetting master and slave **** -include/stop_slave.inc -RESET SLAVE; -RESET MASTER; -include/start_slave.inc +include/rpl_reset.inc CREATE TABLE t8 LIKE t4; CREATE TABLE t9 LIKE tt4; CREATE TEMPORARY TABLE tt5 LIKE t4; @@ -197,9 +186,11 @@ Create Table CREATE TABLE `t9` ( `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 -show binlog events from <binlog_start>; +include/show_binlog_events.inc Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Gtid # # GTID #-#-# master-bin.000001 # Query # # use `test`; CREATE TABLE t8 LIKE t4 +master-bin.000001 # Gtid # # GTID #-#-# master-bin.000001 # Query # # use `test`; CREATE TABLE `t9` ( `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL @@ -219,15 +210,12 @@ Create Table CREATE TABLE `t9` ( ) ENGINE=MEMORY DEFAULT CHARSET=latin1 DROP TABLE IF EXISTS t1,t2,t3,t4,t5,t6,t7,t8,t9; STOP SLAVE; +include/wait_for_slave_to_stop.inc SET GLOBAL storage_engine=@storage_engine; START SLAVE; +include/wait_for_slave_to_start.inc ================ BUG#22864 ================ -stop slave; -drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; -reset master; -reset slave; -drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; -start slave; +include/rpl_reset.inc SET AUTOCOMMIT=0; CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (1),(2),(3); @@ -270,37 +258,38 @@ a 1 2 3 -show binlog events from <binlog_start>; +include/show_binlog_events.inc Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Gtid # # GTID #-#-# master-bin.000001 # Query # # use `test`; CREATE TABLE t1 (a INT) -master-bin.000001 # Query # # BEGIN +master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Table_map # # table_id: # (test.t1) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F master-bin.000001 # Query # # COMMIT -master-bin.000001 # Query # # BEGIN +master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Query # # use `test`; CREATE TABLE `t2` ( `a` int(11) DEFAULT NULL ) ENGINE=InnoDB master-bin.000001 # Table_map # # table_id: # (test.t2) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F master-bin.000001 # Xid # # COMMIT /* XID */ -master-bin.000001 # Query # # BEGIN +master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Query # # use `test`; CREATE TABLE `t3` ( `a` int(11) DEFAULT NULL ) ENGINE=InnoDB master-bin.000001 # Table_map # # table_id: # (test.t3) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F master-bin.000001 # Xid # # COMMIT /* XID */ -master-bin.000001 # Query # # BEGIN +master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Query # # use `test`; CREATE TABLE `t4` ( `a` int(11) DEFAULT NULL ) ENGINE=InnoDB master-bin.000001 # Table_map # # table_id: # (test.t4) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F master-bin.000001 # Xid # # COMMIT /* XID */ -master-bin.000001 # Query # # BEGIN +master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Table_map # # table_id: # (test.t1) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F master-bin.000001 # Query # # COMMIT SHOW TABLES; Tables_in_test @@ -333,10 +322,7 @@ a 3 DROP TABLE IF EXISTS t1,t2,t3,t4; SET AUTOCOMMIT=1; -STOP SLAVE; -RESET SLAVE; -RESET MASTER; -START SLAVE; +include/rpl_reset.inc CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (1),(2),(3); CREATE TABLE t2 (a INT) ENGINE=INNODB; @@ -355,19 +341,21 @@ a 4 6 9 -show binlog events from <binlog_start>; +include/show_binlog_events.inc Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Gtid # # GTID #-#-# master-bin.000001 # Query # # use `test`; CREATE TABLE t1 (a INT) -master-bin.000001 # Query # # BEGIN +master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Table_map # # table_id: # (test.t1) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F master-bin.000001 # Query # # COMMIT +master-bin.000001 # Gtid # # GTID #-#-# master-bin.000001 # Query # # use `test`; CREATE TABLE t2 (a INT) ENGINE=INNODB -master-bin.000001 # Query # # BEGIN +master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Table_map # # table_id: # (test.t2) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F master-bin.000001 # Table_map # # table_id: # (test.t2) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F master-bin.000001 # Xid # # COMMIT /* XID */ SELECT * FROM t2 ORDER BY a; a @@ -377,11 +365,7 @@ a 6 9 TRUNCATE TABLE t2; -**** Resetting master and slave **** -include/stop_slave.inc -RESET SLAVE; -RESET MASTER; -include/start_slave.inc +include/rpl_reset.inc BEGIN; INSERT INTO t2 SELECT a*a FROM t1; CREATE TEMPORARY TABLE tt2 @@ -394,8 +378,14 @@ Warnings: Warning 1196 Some non-transactional changed tables couldn't be rolled back SELECT * FROM t2 ORDER BY a; a -show binlog events from <binlog_start>; +include/show_binlog_events.inc Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Gtid # # BEGIN GTID #-#-# +master-bin.000001 # Table_map # # table_id: # (test.t2) +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +master-bin.000001 # Table_map # # table_id: # (test.t2) +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # ROLLBACK SELECT * FROM t2 ORDER BY a; a DROP TABLE t1,t2; @@ -412,35 +402,28 @@ a 1 2 DROP TABLE t1; -stop slave; -drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; -reset master; -reset slave; -drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; -start slave; +include/rpl_reset.inc DROP DATABASE IF EXISTS mysqltest1; CREATE DATABASE mysqltest1; CREATE TABLE mysqltest1.without_select (f1 BIGINT); CREATE TABLE mysqltest1.with_select AS SELECT 1 AS f1; -show binlog events from <binlog_start>; +include/show_binlog_events.inc Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Gtid # # GTID #-#-# master-bin.000001 # Query # # DROP DATABASE IF EXISTS mysqltest1 +master-bin.000001 # Gtid # # GTID #-#-# master-bin.000001 # Query # # CREATE DATABASE mysqltest1 +master-bin.000001 # Gtid # # GTID #-#-# master-bin.000001 # Query # # use `test`; CREATE TABLE mysqltest1.without_select (f1 BIGINT) -master-bin.000001 # Query # # BEGIN +master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Query # # use `test`; CREATE TABLE `mysqltest1`.`with_select` ( `f1` int(1) NOT NULL DEFAULT '0' ) master-bin.000001 # Table_map # # table_id: # (mysqltest1.with_select) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F master-bin.000001 # Query # # COMMIT DROP DATABASE mysqltest1; -stop slave; -drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; -reset master; -reset slave; -drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; -start slave; +include/rpl_reset.inc CREATE TEMPORARY TABLE t7(c1 INT); CREATE TABLE t5(c1 INT); CREATE TABLE t4(c1 INT); @@ -456,9 +439,9 @@ CREATE TABLE IF NOT EXISTS bug48506_t3 LIKE t7; CREATE TABLE IF NOT EXISTS bug48506_t4 LIKE t7; SHOW TABLES LIKE 'bug48506%'; Tables_in_test (bug48506%) -bug48506_t4 DROP VIEW IF EXISTS bug48506_t1, bug48506_t2, bug48506_t3; DROP TEMPORARY TABLES t7; DROP TABLES t4, t5; DROP TABLES IF EXISTS bug48506_t4; +include/rpl_end.inc end of the tests diff --git a/mysql-test/suite/rpl/r/rpl_row_log.result b/mysql-test/suite/rpl/r/rpl_row_log.result index d3d5b42c80a..83ec26486e0 100644 --- a/mysql-test/suite/rpl/r/rpl_row_log.result +++ b/mysql-test/suite/rpl/r/rpl_row_log.result @@ -6,6 +6,8 @@ reset master; reset slave; start slave; include/wait_for_slave_to_start.inc +set @save_slave_ddl_exec_mode=@@global.slave_ddl_exec_mode; +set @@global.slave_ddl_exec_mode=STRICT; create table t1(n int not null auto_increment primary key)ENGINE=MyISAM; insert into t1 values (NULL); drop table t1; @@ -287,4 +289,5 @@ a b 5 1 6 1 drop table t1; +set @@global.slave_ddl_exec_mode=@save_slave_ddl_exec_mode; include/rpl_end.inc diff --git a/mysql-test/suite/rpl/r/rpl_row_log_innodb.result b/mysql-test/suite/rpl/r/rpl_row_log_innodb.result index 5dd5ff1b4ff..3b9733a18e8 100644 --- a/mysql-test/suite/rpl/r/rpl_row_log_innodb.result +++ b/mysql-test/suite/rpl/r/rpl_row_log_innodb.result @@ -6,6 +6,8 @@ reset master; reset slave; start slave; include/wait_for_slave_to_start.inc +set @save_slave_ddl_exec_mode=@@global.slave_ddl_exec_mode; +set @@global.slave_ddl_exec_mode=STRICT; create table t1(n int not null auto_increment primary key)ENGINE=InnoDB; insert into t1 values (NULL); drop table t1; @@ -287,4 +289,5 @@ a b 5 1 6 1 drop table t1; +set @@global.slave_ddl_exec_mode=@save_slave_ddl_exec_mode; include/rpl_end.inc diff --git a/mysql-test/suite/rpl/r/rpl_row_show_relaylog_events.result b/mysql-test/suite/rpl/r/rpl_row_show_relaylog_events.result index 9872316dc97..a132b50a471 100644 --- a/mysql-test/suite/rpl/r/rpl_row_show_relaylog_events.result +++ b/mysql-test/suite/rpl/r/rpl_row_show_relaylog_events.result @@ -172,7 +172,7 @@ include/show_events.inc Log_name Pos Event_type Server_id End_log_pos Info slave-bin.000002 # Binlog_checkpoint # # slave-bin.000002 slave-bin.000002 # Gtid # # GTID #-#-# -slave-bin.000002 # Query # # use `test`; DROP TABLE `t1` /* generated by server */ +slave-bin.000002 # Query # # use `test`; DROP TABLE IF EXISTS `t1` /* generated by server */ ******** [slave] SHOW BINLOG EVENTS IN <FILE> LIMIT 2 ******** include/show_events.inc Log_name Pos Event_type Server_id End_log_pos Info @@ -181,7 +181,7 @@ slave-bin.000002 # Gtid # # GTID #-#-# ******** [slave] SHOW BINLOG EVENTS IN <FILE> LIMIT 2,3 ******** include/show_events.inc Log_name Pos Event_type Server_id End_log_pos Info -slave-bin.000002 # Query # # use `test`; DROP TABLE `t1` /* generated by server */ +slave-bin.000002 # Query # # use `test`; DROP TABLE IF EXISTS `t1` /* generated by server */ ******** [slave] SHOW BINLOG EVENTS ******** include/show_events.inc Log_name Pos Event_type Server_id End_log_pos Info diff --git a/mysql-test/suite/rpl/r/rpl_row_triggers.result b/mysql-test/suite/rpl/r/rpl_row_triggers.result new file mode 100644 index 00000000000..82dbc727993 --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_row_triggers.result @@ -0,0 +1,240 @@ +include/master-slave.inc +[connection master] +# Test of row replication with triggers on the slave side +CREATE TABLE t1 (C1 CHAR(1) primary key, C2 CHAR(1)) engine=innodb; +SELECT * FROM t1; +C1 C2 +SET @old_slave_exec_mode= @@global.slave_exec_mode; +SET @old_slave_run_triggers_for_rbr= @@global.slave_run_triggers_for_rbr; +SET @@global.slave_exec_mode= IDEMPOTENT; +SET @@global.slave_run_triggers_for_rbr= YES; +SELECT * FROM t1; +C1 C2 +create table t2 (id char(2) primary key, cnt int, o char(1), n char(1)); +insert into t2 values +('u0', 0, ' ', ' '),('u1', 0, ' ', ' '), +('d0', 0, ' ', ' '),('d1', 0, ' ', ' '), +('i0', 0, ' ', ' '),('i1', 0, ' ', ' '); +create trigger t1_cnt_b before update on t1 for each row +update t2 set cnt=cnt+1, o=old.C1, n=new.C1 where id = 'u0'; +create trigger t1_cnt_db before delete on t1 for each row +update t2 set cnt=cnt+1, o=old.C1, n=' ' where id = 'd0'; +create trigger t1_cnt_ib before insert on t1 for each row +update t2 set cnt=cnt+1, n=new.C1, o=' ' where id = 'i0'; +create trigger t1_cnt_a after update on t1 for each row +update t2 set cnt=cnt+1, o=old.C1, n=new.C1 where id = 'u1'; +create trigger t1_cnt_da after delete on t1 for each row +update t2 set cnt=cnt+1, o=old.C1, n=' ' where id = 'd1'; +create trigger t1_cnt_ia after insert on t1 for each row +update t2 set cnt=cnt+1, n=new.C1, o=' ' where id = 'i1'; +SELECT * FROM t2 order by id; +id cnt o n +d0 0 +d1 0 +i0 0 +i1 0 +u0 0 +u1 0 +# INSERT triggers test +insert into t1 values ('a','b'); +SELECT * FROM t2 order by id; +id cnt o n +d0 0 +d1 0 +i0 1 a +i1 1 a +u0 0 +u1 0 +# UPDATE triggers test +update t1 set C1= 'd'; +SELECT * FROM t2 order by id; +id cnt o n +d0 0 +d1 0 +i0 1 a +i1 1 a +u0 1 a d +u1 1 a d +# DELETE triggers test +delete from t1 where C1='d'; +SELECT * FROM t2 order by id; +id cnt o n +d0 1 d +d1 1 d +i0 1 a +i1 1 a +u0 1 a d +u1 1 a d +# INSERT triggers which cause also UPDATE test (insert duplicate row) +insert into t1 values ('0','1'); +SELECT * FROM t2 order by id; +id cnt o n +d0 1 d +d1 1 d +i0 2 0 +i1 2 0 +u0 1 a d +u1 1 a d +insert into t1 values ('0','1'); +SELECT * FROM t2 order by id; +id cnt o n +d0 1 d +d1 1 d +i0 3 0 +i1 3 0 +u0 2 0 0 +u1 2 0 0 +# INSERT triggers which cause also DELETE test +# (insert duplicate row in table referenced by foreign key) +insert into t1 values ('1','1'); +CREATE TABLE t3 (C1 CHAR(1) primary key, FOREIGN KEY (C1) REFERENCES t1(C1) ) engine=innodb; +insert into t1 values ('1','1'); +SELECT * FROM t2 order by id; +id cnt o n +d0 2 1 +d1 2 1 +i0 5 1 +i1 5 1 +u0 2 0 0 +u1 2 0 0 +drop table t3,t1; +SET @@global.slave_exec_mode= @old_slave_exec_mode; +SET @@global.slave_run_triggers_for_rbr= @old_slave_run_triggers_for_rbr; +drop table t2; +CREATE TABLE t1 (i INT) ENGINE=InnoDB; +CREATE TABLE t2 (i INT) ENGINE=InnoDB; +SET @old_slave_run_triggers_for_rbr= @@global.slave_run_triggers_for_rbr; +SET GLOBAL slave_run_triggers_for_rbr=YES; +CREATE TRIGGER tr AFTER INSERT ON t1 FOR EACH ROW +INSERT INTO t2 VALUES (new.i); +BEGIN; +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (2); +COMMIT; +select * from t2; +i +1 +2 +SET @@global.slave_run_triggers_for_rbr= @old_slave_run_triggers_for_rbr; +drop tables t2,t1; +# Triggers on slave do not work if master has some +CREATE TABLE t1 (C1 CHAR(1) primary key, C2 CHAR(1)) engine=innodb; +SELECT * FROM t1; +C1 C2 +create trigger t1_dummy before delete on t1 for each row +set @dummy= 1; +SET @old_slave_exec_mode= @@global.slave_exec_mode; +SET @old_slave_run_triggers_for_rbr= @@global.slave_run_triggers_for_rbr; +SET @@global.slave_exec_mode= IDEMPOTENT; +SET @@global.slave_run_triggers_for_rbr= YES; +SELECT * FROM t1; +C1 C2 +create table t2 (id char(2) primary key, cnt int, o char(1), n char(1)); +insert into t2 values +('u0', 0, ' ', ' '),('u1', 0, ' ', ' '), +('d0', 0, ' ', ' '),('d1', 0, ' ', ' '), +('i0', 0, ' ', ' '),('i1', 0, ' ', ' '); +create trigger t1_cnt_b before update on t1 for each row +update t2 set cnt=cnt+1, o=old.C1, n=new.C1 where id = 'u0'; +create trigger t1_cnt_ib before insert on t1 for each row +update t2 set cnt=cnt+1, n=new.C1, o=' ' where id = 'i0'; +create trigger t1_cnt_a after update on t1 for each row +update t2 set cnt=cnt+1, o=old.C1, n=new.C1 where id = 'u1'; +create trigger t1_cnt_da after delete on t1 for each row +update t2 set cnt=cnt+1, o=old.C1, n=' ' where id = 'd1'; +create trigger t1_cnt_ia after insert on t1 for each row +update t2 set cnt=cnt+1, n=new.C1, o=' ' where id = 'i1'; +SELECT * FROM t2 order by id; +id cnt o n +d0 0 +d1 0 +i0 0 +i1 0 +u0 0 +u1 0 +# INSERT triggers test +insert into t1 values ('a','b'); +SELECT * FROM t2 order by id; +id cnt o n +d0 0 +d1 0 +i0 0 +i1 0 +u0 0 +u1 0 +# UPDATE triggers test +update t1 set C1= 'd'; +SELECT * FROM t2 order by id; +id cnt o n +d0 0 +d1 0 +i0 0 +i1 0 +u0 0 +u1 0 +# DELETE triggers test +delete from t1 where C1='d'; +SELECT * FROM t2 order by id; +id cnt o n +d0 0 +d1 0 +i0 0 +i1 0 +u0 0 +u1 0 +# INSERT triggers which cause also UPDATE test (insert duplicate row) +insert into t1 values ('0','1'); +SELECT * FROM t2 order by id; +id cnt o n +d0 0 +d1 0 +i0 1 0 +i1 1 0 +u0 0 +u1 0 +insert into t1 values ('0','1'); +SELECT * FROM t2 order by id; +id cnt o n +d0 0 +d1 0 +i0 1 0 +i1 1 0 +u0 0 +u1 0 +# INSERT triggers which cause also DELETE test +# (insert duplicate row in table referenced by foreign key) +insert into t1 values ('1','1'); +CREATE TABLE t3 (C1 CHAR(1) primary key, FOREIGN KEY (C1) REFERENCES t1(C1) ) engine=innodb; +insert into t1 values ('1','1'); +SELECT * FROM t2 order by id; +id cnt o n +d0 0 +d1 0 +i0 2 1 +i1 2 1 +u0 0 +u1 0 +drop table t3,t1; +SET @@global.slave_exec_mode= @old_slave_exec_mode; +SET @@global.slave_run_triggers_for_rbr= @old_slave_run_triggers_for_rbr; +drop table t2; +# +# MDEV-5513: Trigger is applied to the rows after first one +# +create table t1 (a int, b int); +create table tlog (a int); +set sql_log_bin=0; +create trigger tr1 after insert on t1 for each row insert into tlog values (1); +set sql_log_bin=1; +set @slave_run_triggers_for_rbr.saved = @@slave_run_triggers_for_rbr; +set global slave_run_triggers_for_rbr=1; +create trigger tr2 before insert on t1 for each row set new.b = new.a; +insert into t1 values (1,10),(2,20),(3,30); +select * from t1; +a b +1 10 +2 20 +3 30 +set global slave_run_triggers_for_rbr = @slave_run_triggers_for_rbr.saved; +drop table t1, tlog; +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/r/rpl_row_triggers_sbr.result b/mysql-test/suite/rpl/r/rpl_row_triggers_sbr.result new file mode 100644 index 00000000000..96197393de9 --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_row_triggers_sbr.result @@ -0,0 +1,14 @@ +include/master-slave.inc +[connection master] +set binlog_format = row; +create table t1 (i int); +create table t2 (i int); +SET @old_slave_run_triggers_for_rbr= @@global.slave_run_triggers_for_rbr; +set global slave_run_triggers_for_rbr=YES; +create trigger tr_before before insert on t1 for each row +insert into t2 values (1); +insert into t1 values (1); +include/wait_for_slave_sql_error_and_skip.inc [errno=1666] +drop tables t1,t2; +SET @@global.slave_run_triggers_for_rbr= @old_slave_run_triggers_for_rbr; +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/r/rpl_stm_log.result b/mysql-test/suite/rpl/r/rpl_stm_log.result index 360a0e526de..da925035c9c 100644 --- a/mysql-test/suite/rpl/r/rpl_stm_log.result +++ b/mysql-test/suite/rpl/r/rpl_stm_log.result @@ -6,6 +6,8 @@ reset master; reset slave; start slave; include/wait_for_slave_to_start.inc +set @save_slave_ddl_exec_mode=@@global.slave_ddl_exec_mode; +set @@global.slave_ddl_exec_mode=STRICT; create table t1(n int not null auto_increment primary key)ENGINE=MyISAM; insert into t1 values (NULL); drop table t1; @@ -286,4 +288,5 @@ a b 5 1 6 1 drop table t1; +set @@global.slave_ddl_exec_mode=@save_slave_ddl_exec_mode; include/rpl_end.inc diff --git a/mysql-test/suite/rpl/r/rpl_stm_mix_show_relaylog_events.result b/mysql-test/suite/rpl/r/rpl_stm_mix_show_relaylog_events.result index 57d84eef52e..69e144b5cd1 100644 --- a/mysql-test/suite/rpl/r/rpl_stm_mix_show_relaylog_events.result +++ b/mysql-test/suite/rpl/r/rpl_stm_mix_show_relaylog_events.result @@ -154,7 +154,7 @@ include/show_events.inc Log_name Pos Event_type Server_id End_log_pos Info slave-bin.000002 # Binlog_checkpoint # # slave-bin.000002 slave-bin.000002 # Gtid # # GTID #-#-# -slave-bin.000002 # Query # # use `test`; DROP TABLE `t1` /* generated by server */ +slave-bin.000002 # Query # # use `test`; DROP TABLE IF EXISTS `t1` /* generated by server */ ******** [slave] SHOW BINLOG EVENTS IN <FILE> LIMIT 2 ******** include/show_events.inc Log_name Pos Event_type Server_id End_log_pos Info @@ -163,7 +163,7 @@ slave-bin.000002 # Gtid # # GTID #-#-# ******** [slave] SHOW BINLOG EVENTS IN <FILE> LIMIT 2,3 ******** include/show_events.inc Log_name Pos Event_type Server_id End_log_pos Info -slave-bin.000002 # Query # # use `test`; DROP TABLE `t1` /* generated by server */ +slave-bin.000002 # Query # # use `test`; DROP TABLE IF EXISTS `t1` /* generated by server */ ******** [slave] SHOW BINLOG EVENTS ******** include/show_events.inc Log_name Pos Event_type Server_id End_log_pos Info diff --git a/mysql-test/suite/rpl/r/rpl_temp_table_mix_row.result b/mysql-test/suite/rpl/r/rpl_temp_table_mix_row.result index a0102baacfe..1933b0b82f8 100644 --- a/mysql-test/suite/rpl/r/rpl_temp_table_mix_row.result +++ b/mysql-test/suite/rpl/r/rpl_temp_table_mix_row.result @@ -62,7 +62,7 @@ slave-bin.000001 # Query # # use `test`; ALTER TABLE t1_tmp ADD COLUMN b INT slave-bin.000001 # Gtid # # GTID #-#-# slave-bin.000001 # Query # # DROP TEMPORARY TABLE IF EXISTS `test`.`t1_tmp` /* generated by server */ slave-bin.000001 # Gtid # # GTID #-#-# -slave-bin.000001 # Query # # use `test`; DROP TABLE `t2` /* generated by server */ +slave-bin.000001 # Query # # use `test`; DROP TABLE IF EXISTS `t2` /* generated by server */ slave-bin.000001 # Gtid # # BEGIN GTID #-#-# slave-bin.000001 # Table_map # # table_id: # (test.t1) slave-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F @@ -73,7 +73,7 @@ slave-bin.000001 # Gtid # # BEGIN GTID #-#-# slave-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES (2) slave-bin.000001 # Xid # # COMMIT /* XID */ slave-bin.000001 # Gtid # # GTID #-#-# -slave-bin.000001 # Query # # use `test`; DROP TABLE `t3`,`t1` /* generated by server */ +slave-bin.000001 # Query # # use `test`; DROP TABLE IF EXISTS `t3`,`t1` /* generated by server */ # Bug#55478 Row events wrongly apply on the temporary table of the same name # ========================================================================== |