1# Test CREATE OR REPLACE TABLE in replication 2--source include/have_innodb.inc 3 4--let $rpl_topology=1->2 5--source include/rpl_init.inc 6 7# Create help tables 8create table t2 (a int) engine=myisam; 9insert into t2 values (0),(1),(2),(2); 10create temporary table t3 (a_in_temporary int) engine=myisam; 11 12--echo # 13--echo # Check how create table and create or replace table are logged 14--echo # 15 16save_master_pos; 17connection server_2; 18sync_with_master; 19create table t1 (to_be_deleted int); 20 21connection server_1; 22CREATE TABLE t1 AS SELECT 1 AS f1; 23CREATE OR REPLACE TABLE t1 AS SELECT 2 AS f1; 24CREATE OR REPLACE table t1 like t2; 25CREATE OR REPLACE table t1 like t3; 26drop table t1; 27 28--echo binlog from server 1 29--source include/show_binlog_events.inc 30save_master_pos; 31connection server_2; 32sync_with_master; 33--echo binlog from server 2 34--source include/show_binlog_events.inc 35 36connection server_1; 37 38--echo # 39--echo # Ensure that also failed create_or_replace are logged 40--echo # 41 42--let $binlog_start=query_get_value(SHOW MASTER STATUS, Position, 1) 43 44create table t1 (a int); 45--error ER_TABLE_MUST_HAVE_COLUMNS 46create or replace table t1; 47drop table if exists t1; 48# The following is not logged as t1 does not exists; 49--error ER_DUP_ENTRY 50create or replace table t1 (a int primary key) select a from t2; 51 52create table t1 (a int); 53# This should as a delete as we will delete t1 54--error ER_DUP_ENTRY 55create or replace table t1 (a int primary key) select a from t2; 56 57# Same with temporary table 58create temporary table t9 (a int); 59--error ER_DUP_ENTRY 60create or replace temporary table t9 (a int primary key) select a from t2; 61 62--echo binlog from server 1 63--source include/show_binlog_events.inc 64save_master_pos; 65connection server_2; 66sync_with_master; 67show tables; 68connection server_1; 69 70--let $binlog_start=query_get_value(SHOW MASTER STATUS, Position, 1) 71create table t1 (a int); 72--error ER_DUP_FIELDNAME 73create or replace table t1 (a int, a int) select * from t2; 74--source include/show_binlog_events.inc 75 76drop table if exists t1,t2; 77drop temporary table if exists t9; 78 79--echo # 80--echo # Ensure that CREATE are run as CREATE OR REPLACE on slave 81--echo # 82 83save_master_pos; 84connection server_2; 85sync_with_master; 86create table t1 (server_2_to_be_delete int); 87connection server_1; 88create table t1 (new_table int); 89 90save_master_pos; 91connection server_2; 92sync_with_master; 93 94show create table t1; 95connection server_1; 96drop table t1; 97 98--echo # 99--echo # Check how CREATE is logged on slave in case of conflicts 100--echo # 101 102save_master_pos; 103connection server_2; 104sync_with_master; 105--let $binlog_start=query_get_value(SHOW MASTER STATUS, Position, 1) 106create table t1 (server_2_to_be_delete int); 107create table t2 (server_2_to_be_delete int); 108create table t4 (server_2_to_be_delete int); 109set @org_binlog_format=@@binlog_format; 110set @@global.binlog_format="ROW"; 111stop slave; 112--source include/wait_for_slave_to_stop.inc 113start slave; 114--source include/wait_for_slave_to_start.inc 115connection server_1; 116create temporary table t9 (a int); 117insert into t9 values(1); 118create table t1 (new_table int); 119create table t2 select * from t9; 120create table t4 like t9; 121create table t5 select * from t9; 122save_master_pos; 123connection server_2; 124sync_with_master; 125--echo binlog from server 2 126--source include/show_binlog_events.inc 127set @@global.binlog_format=@org_binlog_format; 128stop slave; 129--source include/wait_for_slave_to_stop.inc 130start slave; 131--source include/wait_for_slave_to_start.inc 132connection server_1; 133drop table t1,t2,t4,t5,t9; 134 135--echo # 136--echo # Ensure that DROP TABLE is run as DROP IF NOT EXISTS 137--echo # 138 139create table t1 (server_1_ver_1 int); 140create table t4 (server_1_ver_2 int); 141 142save_master_pos; 143connection server_2; 144sync_with_master; 145--let $binlog_start=query_get_value(SHOW MASTER STATUS, Position, 1) 146 147# Drop the table on the slave 148drop table t1; 149connection server_1; 150drop table t1,t4; 151create table t1 (server_2_ver_2 int); 152save_master_pos; 153connection server_2; 154sync_with_master; 155show create table t1; 156--echo binlog from server 2 157--source include/show_binlog_events.inc 158connection server_1; 159drop table t1; 160 161--echo # 162--echo # Ensure that CREATE ... SELECT is recorded as one GTID on the slave 163--echo # 164 165save_master_pos; 166connection server_2; 167sync_with_master; 168--let $binlog_start=query_get_value(SHOW MASTER STATUS, Position, 1) 169connection server_1; 170 171create table t1 (a int); 172insert into t1 values (0),(1),(2); 173create table t2 engine=myisam select * from t1; 174create or replace table t2 engine=innodb select * from t1; 175save_master_pos; 176connection server_2; 177sync_with_master; 178--echo binlog from server 2 179--source include/show_binlog_events.inc 180connection server_1; 181drop table t1; 182 183--echo # 184--echo # Check logging of drop temporary table 185--echo # 186 187drop temporary table t3; 188 189--let $binlog_start=query_get_value(SHOW MASTER STATUS, Position, 1) 190 191set @org_binlog_format=@@binlog_format; 192set binlog_format="STATEMENT"; 193create temporary table t5 (a int); 194drop temporary table t5; 195set binlog_format="ROW"; 196create temporary table t6 (a int); 197drop temporary table t6; 198set binlog_format="STATEMENT"; 199create temporary table t7 (a int); 200set binlog_format="ROW"; 201drop temporary table t7; 202create temporary table t8 (a int); 203--error ER_TEMP_TABLE_PREVENTS_SWITCH_OUT_OF_RBR 204set binlog_format="STATEMENT"; 205drop temporary table t8; 206set @@binlog_format=@org_binlog_format; 207 208# MDEV-20091: 209# 1. No DROP should be logged for non-existing tmp table, nor 210# 2. at the connection close when its creation has not been logged. 211set @@session.binlog_format=default; 212drop temporary table if exists t9; 213 214--connect(con1,localhost,root,,) 215set session binlog_format=default; 216create temporary table t9 (i int); 217--echo *** Must be no DROP logged for t9 when there was no CREATE, at disconnect too *** 218--disconnect con1 219 220--connection server_1 221--source include/show_binlog_events.inc 222 223# Clean up 224drop table t2; 225 226--source include/rpl_end.inc 227