Debian Codegraph @

mysql-test/suite/rpl/t/create_or_replace.inc

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