Debian Codegraph @

mysql-test/suite/rpl/r/create_or_replace_row.result

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