1include/rpl_init.inc [topology=1->2] 2create table t2 (a int) engine=myisam; 3insert into t2 values (0),(1),(2),(2); 4create temporary table t3 (a_in_temporary int) engine=myisam; 5# 6# Check how create table and create or replace table are logged 7# 8connection server_2; 9create table t1 (to_be_deleted int); 10connection server_1; 11CREATE TABLE t1 AS SELECT 1 AS f1; 12CREATE OR REPLACE TABLE t1 AS SELECT 2 AS f1; 13CREATE OR REPLACE table t1 like t2; 14CREATE OR REPLACE table t1 like t3; 15drop table t1; 16binlog from server 1 17include/show_binlog_events.inc 18Log_name Pos Event_type Server_id End_log_pos Info 19master-bin.000001 # Gtid # # GTID #-#-# 20master-bin.000001 # Query # # use `test`; create table t2 (a int) engine=myisam 21master-bin.000001 # Gtid # # BEGIN GTID #-#-# 22master-bin.000001 # Annotate_rows # # insert into t2 values (0),(1),(2),(2) 23master-bin.000001 # Table_map # # table_id: # (test.t2) 24master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F 25master-bin.000001 # Query # # COMMIT 26master-bin.000001 # Gtid # # BEGIN GTID #-#-# 27master-bin.000001 # Query # # use `test`; CREATE TABLE `t1` ( 28 `f1` int(1) NOT NULL 29) 30master-bin.000001 # Annotate_rows # # CREATE TABLE t1 AS SELECT 1 AS f1 31master-bin.000001 # Table_map # # table_id: # (test.t1) 32master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F 33master-bin.000001 # Query # # COMMIT 34master-bin.000001 # Gtid # # BEGIN GTID #-#-# 35master-bin.000001 # Query # # use `test`; CREATE OR REPLACE TABLE `t1` ( 36 `f1` int(1) NOT NULL 37) 38master-bin.000001 # Annotate_rows # # CREATE OR REPLACE TABLE t1 AS SELECT 2 AS f1 39master-bin.000001 # Table_map # # table_id: # (test.t1) 40master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F 41master-bin.000001 # Query # # COMMIT 42master-bin.000001 # Gtid # # GTID #-#-# 43master-bin.000001 # Query # # use `test`; CREATE OR REPLACE table t1 like t2 44master-bin.000001 # Gtid # # GTID #-#-# 45master-bin.000001 # Query # # use `test`; CREATE OR REPLACE TABLE `t1` ( 46 `a_in_temporary` int(11) DEFAULT NULL 47) ENGINE=MyISAM 48master-bin.000001 # Gtid # # GTID #-#-# 49master-bin.000001 # Query # # use `test`; DROP TABLE `t1` /* generated by server */ 50connection server_2; 51binlog from server 2 52include/show_binlog_events.inc 53Log_name Pos Event_type Server_id End_log_pos Info 54slave-bin.000001 # Gtid # # GTID #-#-# 55slave-bin.000001 # Query # # use `test`; create table t2 (a int) engine=myisam 56slave-bin.000001 # Gtid # # BEGIN GTID #-#-# 57slave-bin.000001 # Annotate_rows # # insert into t2 values (0),(1),(2),(2) 58slave-bin.000001 # Table_map # # table_id: # (test.t2) 59slave-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F 60slave-bin.000001 # Query # # COMMIT 61slave-bin.000001 # Gtid # # GTID #-#-# 62slave-bin.000001 # Query # # use `test`; create table t1 (to_be_deleted int) 63slave-bin.000001 # Gtid # # BEGIN GTID #-#-# 64slave-bin.000001 # Query # # use `test`; CREATE TABLE `t1` ( 65 `f1` int(1) NOT NULL 66) 67slave-bin.000001 # Annotate_rows # # CREATE TABLE t1 AS SELECT 1 AS f1 68slave-bin.000001 # Table_map # # table_id: # (test.t1) 69slave-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F 70slave-bin.000001 # Query # # COMMIT 71slave-bin.000001 # Gtid # # BEGIN GTID #-#-# 72slave-bin.000001 # Query # # use `test`; CREATE OR REPLACE TABLE `t1` ( 73 `f1` int(1) NOT NULL 74) 75slave-bin.000001 # Annotate_rows # # CREATE OR REPLACE TABLE t1 AS SELECT 2 AS f1 76slave-bin.000001 # Table_map # # table_id: # (test.t1) 77slave-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F 78slave-bin.000001 # Query # # COMMIT 79slave-bin.000001 # Gtid # # GTID #-#-# 80slave-bin.000001 # Query # # use `test`; CREATE OR REPLACE table t1 like t2 81slave-bin.000001 # Gtid # # GTID #-#-# 82slave-bin.000001 # Query # # use `test`; CREATE OR REPLACE TABLE `t1` ( 83 `a_in_temporary` int(11) DEFAULT NULL 84) ENGINE=MyISAM 85slave-bin.000001 # Gtid # # GTID #-#-# 86slave-bin.000001 # Query # # use `test`; DROP TABLE IF EXISTS `t1` /* generated by server */ 87connection server_1; 88# 89# Ensure that also failed create_or_replace are logged 90# 91create table t1 (a int); 92create or replace table t1; 93ERROR 42000: A table must have at least 1 column 94drop table if exists t1; 95Warnings: 96Note 1051 Unknown table 'test.t1' 97create or replace table t1 (a int primary key) select a from t2; 98ERROR 23000: Duplicate entry '2' for key 'PRIMARY' 99create table t1 (a int); 100create or replace table t1 (a int primary key) select a from t2; 101ERROR 23000: Duplicate entry '2' for key 'PRIMARY' 102create temporary table t9 (a int); 103create or replace temporary table t9 (a int primary key) select a from t2; 104ERROR 23000: Duplicate entry '2' for key 'PRIMARY' 105binlog from server 1 106include/show_binlog_events.inc 107Log_name Pos Event_type Server_id End_log_pos Info 108master-bin.000001 # Gtid # # GTID #-#-# 109master-bin.000001 # Query # # use `test`; create table t1 (a int) 110master-bin.000001 # Gtid # # GTID #-#-# 111master-bin.000001 # Query # # use `test`; create or replace table t1 112master-bin.000001 # Gtid # # GTID #-#-# 113master-bin.000001 # Query # # use `test`; DROP TABLE IF EXISTS `t1` /* generated by server */ 114master-bin.000001 # Gtid # # GTID #-#-# 115master-bin.000001 # Query # # use `test`; create table t1 (a int) 116master-bin.000001 # Gtid # # BEGIN GTID #-#-# 117master-bin.000001 # Query # # use `test`; DROP TABLE IF EXISTS `test`.`t1`/* Generated to handle failed CREATE OR REPLACE */ 118master-bin.000001 # Query # # ROLLBACK 119connection server_2; 120show tables; 121Tables_in_test 122t2 123connection server_1; 124create table t1 (a int); 125create or replace table t1 (a int, a int) select * from t2; 126ERROR 42S21: Duplicate column name 'a' 127include/show_binlog_events.inc 128Log_name Pos Event_type Server_id End_log_pos Info 129master-bin.000001 # Gtid # # GTID #-#-# 130master-bin.000001 # Query # # use `test`; create table t1 (a int) 131master-bin.000001 # Gtid # # BEGIN GTID #-#-# 132master-bin.000001 # Query # # use `test`; DROP TABLE IF EXISTS `test`.`t1`/* Generated to handle failed CREATE OR REPLACE */ 133master-bin.000001 # Query # # ROLLBACK 134drop table if exists t1,t2; 135Warnings: 136Note 1051 Unknown table 'test.t1' 137drop temporary table if exists t9; 138Warnings: 139Note 1051 Unknown table 'test.t9' 140# 141# Ensure that CREATE are run as CREATE OR REPLACE on slave 142# 143connection server_2; 144create table t1 (server_2_to_be_delete int); 145connection server_1; 146create table t1 (new_table int); 147connection server_2; 148show create table t1; 149Table Create Table 150t1 CREATE TABLE `t1` ( 151 `new_table` int(11) DEFAULT NULL 152) ENGINE=MyISAM DEFAULT CHARSET=latin1 153connection server_1; 154drop table t1; 155# 156# Check how CREATE is logged on slave in case of conflicts 157# 158connection server_2; 159create table t1 (server_2_to_be_delete int); 160create table t2 (server_2_to_be_delete int); 161create table t4 (server_2_to_be_delete int); 162set @org_binlog_format=@@binlog_format; 163set @@global.binlog_format="ROW"; 164stop slave; 165include/wait_for_slave_to_stop.inc 166start slave; 167include/wait_for_slave_to_start.inc 168connection server_1; 169create temporary table t9 (a int); 170insert into t9 values(1); 171create table t1 (new_table int); 172create table t2 select * from t9; 173create table t4 like t9; 174create table t5 select * from t9; 175connection server_2; 176binlog from server 2 177include/show_binlog_events.inc 178Log_name Pos Event_type Server_id End_log_pos Info 179slave-bin.000001 # Gtid # # GTID #-#-# 180slave-bin.000001 # Query # # use `test`; create table t1 (server_2_to_be_delete int) 181slave-bin.000001 # Gtid # # GTID #-#-# 182slave-bin.000001 # Query # # use `test`; create table t2 (server_2_to_be_delete int) 183slave-bin.000001 # Gtid # # GTID #-#-# 184slave-bin.000001 # Query # # use `test`; create table t4 (server_2_to_be_delete int) 185slave-bin.000001 # Gtid # # GTID #-#-# 186slave-bin.000001 # Query # # use `test`; create table t1 (new_table int) 187slave-bin.000001 # Gtid # # BEGIN GTID #-#-# 188slave-bin.000001 # Query # # use `test`; CREATE TABLE `t2` ( 189 `a` int(11) DEFAULT NULL 190) 191slave-bin.000001 # Annotate_rows # # create table t2 select * from t9 192slave-bin.000001 # Table_map # # table_id: # (test.t2) 193slave-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F 194slave-bin.000001 # Query # # COMMIT 195slave-bin.000001 # Gtid # # GTID #-#-# 196slave-bin.000001 # Query # # use `test`; CREATE TABLE `t4` ( 197 `a` int(11) DEFAULT NULL 198) ENGINE=MyISAM 199slave-bin.000001 # Gtid # # BEGIN GTID #-#-# 200slave-bin.000001 # Query # # use `test`; CREATE TABLE `t5` ( 201 `a` int(11) DEFAULT NULL 202) 203slave-bin.000001 # Annotate_rows # # create table t5 select * from t9 204slave-bin.000001 # Table_map # # table_id: # (test.t5) 205slave-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F 206slave-bin.000001 # Query # # COMMIT 207set @@global.binlog_format=@org_binlog_format; 208stop slave; 209include/wait_for_slave_to_stop.inc 210start slave; 211include/wait_for_slave_to_start.inc 212connection server_1; 213drop table t1,t2,t4,t5,t9; 214# 215# Ensure that DROP TABLE is run as DROP IF NOT EXISTS 216# 217create table t1 (server_1_ver_1 int); 218create table t4 (server_1_ver_2 int); 219connection server_2; 220drop table t1; 221connection server_1; 222drop table t1,t4; 223create table t1 (server_2_ver_2 int); 224connection server_2; 225show create table t1; 226Table Create Table 227t1 CREATE TABLE `t1` ( 228 `server_2_ver_2` int(11) DEFAULT NULL 229) ENGINE=MyISAM DEFAULT CHARSET=latin1 230binlog from server 2 231include/show_binlog_events.inc 232Log_name Pos Event_type Server_id End_log_pos Info 233slave-bin.000001 # Gtid # # GTID #-#-# 234slave-bin.000001 # Query # # use `test`; DROP TABLE `t1` /* generated by server */ 235slave-bin.000001 # Gtid # # GTID #-#-# 236slave-bin.000001 # Query # # use `test`; DROP TABLE IF EXISTS `t1`,`t4` /* generated by server */ 237slave-bin.000001 # Gtid # # GTID #-#-# 238slave-bin.000001 # Query # # use `test`; create table t1 (server_2_ver_2 int) 239connection server_1; 240drop table t1; 241# 242# Ensure that CREATE ... SELECT is recorded as one GTID on the slave 243# 244connection server_2; 245connection server_1; 246create table t1 (a int); 247insert into t1 values (0),(1),(2); 248create table t2 engine=myisam select * from t1; 249create or replace table t2 engine=innodb select * from t1; 250connection server_2; 251binlog from server 2 252include/show_binlog_events.inc 253Log_name Pos Event_type Server_id End_log_pos Info 254slave-bin.000001 # Gtid # # GTID #-#-# 255slave-bin.000001 # Query # # use `test`; create table t1 (a int) 256slave-bin.000001 # Gtid # # BEGIN GTID #-#-# 257slave-bin.000001 # Annotate_rows # # insert into t1 values (0),(1),(2) 258slave-bin.000001 # Table_map # # table_id: # (test.t1) 259slave-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F 260slave-bin.000001 # Query # # COMMIT 261slave-bin.000001 # Gtid # # BEGIN GTID #-#-# 262slave-bin.000001 # Query # # use `test`; CREATE TABLE `t2` ( 263 `a` int(11) DEFAULT NULL 264) ENGINE=MyISAM 265slave-bin.000001 # Annotate_rows # # create table t2 engine=myisam select * from t1 266slave-bin.000001 # Table_map # # table_id: # (test.t2) 267slave-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F 268slave-bin.000001 # Query # # COMMIT 269slave-bin.000001 # Gtid # # BEGIN GTID #-#-# 270slave-bin.000001 # Query # # use `test`; CREATE OR REPLACE TABLE `t2` ( 271 `a` int(11) DEFAULT NULL 272) ENGINE=InnoDB 273slave-bin.000001 # Annotate_rows # # create or replace table t2 engine=innodb select * from t1 274slave-bin.000001 # Table_map # # table_id: # (test.t2) 275slave-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F 276slave-bin.000001 # Xid # # COMMIT /* XID */ 277connection server_1; 278drop table t1; 279# 280# Check logging of drop temporary table 281# 282drop temporary table t3; 283set @org_binlog_format=@@binlog_format; 284set binlog_format="STATEMENT"; 285create temporary table t5 (a int); 286drop temporary table t5; 287set binlog_format="ROW"; 288create temporary table t6 (a int); 289drop temporary table t6; 290set binlog_format="STATEMENT"; 291create temporary table t7 (a int); 292set binlog_format="ROW"; 293drop temporary table t7; 294create temporary table t8 (a int); 295set binlog_format="STATEMENT"; 296ERROR HY000: Cannot switch out of the row-based binary log format when the session has open temporary tables 297drop temporary table t8; 298set @@binlog_format=@org_binlog_format; 299set @@session.binlog_format=default; 300drop temporary table if exists t9; 301Warnings: 302Note 1051 Unknown table 'test.t9' 303connect con1,localhost,root,,; 304set session binlog_format=default; 305create temporary table t9 (i int); 306*** Must be no DROP logged for t9 when there was no CREATE, at disconnect too *** 307disconnect con1; 308connection server_1; 309include/show_binlog_events.inc 310Log_name Pos Event_type Server_id End_log_pos Info 311master-bin.000001 # Gtid # # GTID #-#-# 312master-bin.000001 # Query # # use `test`; create temporary table t5 (a int) 313master-bin.000001 # Gtid # # GTID #-#-# 314master-bin.000001 # Query # # use `test`; DROP TEMPORARY TABLE `t5` /* generated by server */ 315master-bin.000001 # Gtid # # GTID #-#-# 316master-bin.000001 # Query # # use `test`; create temporary table t7 (a int) 317master-bin.000001 # Gtid # # GTID #-#-# 318master-bin.000001 # Query # # DROP TEMPORARY TABLE IF EXISTS `test`.`t7` /* generated by server */ 319drop table t2; 320include/rpl_end.inc 321