Debian Codegraph @

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