EVOLUTION-MANAGER

Edit File: create_or_replace_mix.result

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 # connection server_2; create table t1 (to_be_deleted int); connection server_1; 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 */ connection server_2; 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 */ connection server_1; # # 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 connection server_2; show tables; Tables_in_test t2 connection server_1; 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 # connection server_2; create table t1 (server_2_to_be_delete int); connection server_1; create table t1 (new_table int); connection server_2; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `new_table` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 connection server_1; drop table t1; # # Check how CREATE is logged on slave in case of conflicts # connection server_2; 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 connection server_1; 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; connection server_2; 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 # Annotate_rows # # create table t2 select * from t9 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 ) ENGINE=MyISAM slave-bin.000001 # Gtid # # BEGIN GTID #-#-# slave-bin.000001 # Query # # use `test`; CREATE TABLE `t5` ( `a` int(11) DEFAULT NULL ) slave-bin.000001 # Annotate_rows # # create table t5 select * from t9 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 connection server_1; 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); connection server_2; drop table t1; connection server_1; drop table t1,t4; create table t1 (server_2_ver_2 int); connection server_2; 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) connection server_1; drop table t1; # # Ensure that CREATE ... SELECT is recorded as one GTID on the slave # connection server_2; connection server_1; 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; connection server_2; 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 # Annotate_rows # # insert into t1 values (0),(1),(2) 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 # Annotate_rows # # create table t2 engine=myisam select * from t1 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 # Annotate_rows # # create or replace table t2 engine=innodb select * from t1 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 */ connection server_1; 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 # # 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 */ drop table t2; include/rpl_end.inc