Debian Codegraph @

mysql-test/suite/rpl_nogtid/r/rpl_switch_stm_row_mixed.result

raw
1 include/master-slave.inc
2 Warnings:
3 Note #### Sending passwords in plain text without SSL/TLS is extremely insecure.
4 Note #### Storing MySQL user name or password information in the connection metadata repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START REPLICA; see the 'START REPLICA Syntax' in the MySQL Manual for more information.
5 [connection master]
6 drop database if exists mysqltest1;
7 create database mysqltest1;
8 use mysqltest1;
9 set @my_binlog_format= @@global.binlog_format;
10 Warnings:
11 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
12 set session binlog_format='mixed';
13 Warnings:
14 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
15 show session variables like "binlog_format%";
16 Variable_name Value
17 binlog_format MIXED
18 set session binlog_format='statement';
19 Warnings:
20 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
21 show session variables like "binlog_format%";
22 Variable_name Value
23 binlog_format STATEMENT
24 set session binlog_format='row';
25 Warnings:
26 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
27 show session variables like "binlog_format%";
28 Variable_name Value
29 binlog_format ROW
30 set global binlog_format=DEFAULT;
31 Warnings:
32 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
33 show global variables like "binlog_format%";
34 Variable_name Value
35 binlog_format ROW
36 set global binlog_format=MIXED;
37 Warnings:
38 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
39 show global variables like "binlog_format%";
40 Variable_name Value
41 binlog_format MIXED
42 set global binlog_format=STATEMENT;
43 Warnings:
44 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
45 show global variables like "binlog_format%";
46 Variable_name Value
47 binlog_format STATEMENT
48 set global binlog_format='ROW';
49 Warnings:
50 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
51 show global variables like "binlog_format%";
52 Variable_name Value
53 binlog_format ROW
54 show session variables like "binlog_format%";
55 Variable_name Value
56 binlog_format ROW
57 select @@global.binlog_format, @@session.binlog_format;
58 @@global.binlog_format @@session.binlog_format
59 ROW ROW
60 Warnings:
61 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
62 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
63 CREATE TABLE t1 (a varchar(100));
64 prepare stmt1 from 'insert into t1 select concat(UUID(),?)';
65 set @string="emergency_1_";
66 insert into t1 values("work_2_");
67 execute stmt1 using @string;
68 deallocate prepare stmt1;
69 prepare stmt1 from 'insert into t1 select ?';
70 insert into t1 values(concat(UUID(),"work_3_"));
71 execute stmt1 using @string;
72 deallocate prepare stmt1;
73 insert into t1 values(concat("for_4_",UUID()));
74 insert into t1 select "yesterday_5_";
75 create temporary table tmp(a char(100));
76 insert into tmp values("see_6_");
77 set binlog_format=statement;
78 ERROR HY000: Changing @@session.binlog_format is disallowed when the session has open temporary table(s). You could wait until these temporary table(s) are dropped and try again.
79 insert into t1 select * from tmp;
80 drop temporary table tmp;
81 set binlog_format=statement;
82 Warnings:
83 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
84 show global variables like "binlog_format%";
85 Variable_name Value
86 binlog_format ROW
87 show session variables like "binlog_format%";
88 Variable_name Value
89 binlog_format STATEMENT
90 select @@global.binlog_format, @@session.binlog_format;
91 @@global.binlog_format @@session.binlog_format
92 ROW STATEMENT
93 Warnings:
94 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
95 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
96 set global binlog_format=statement;
97 Warnings:
98 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
99 show global variables like "binlog_format%";
100 Variable_name Value
101 binlog_format STATEMENT
102 show session variables like "binlog_format%";
103 Variable_name Value
104 binlog_format STATEMENT
105 select @@global.binlog_format, @@session.binlog_format;
106 @@global.binlog_format @@session.binlog_format
107 STATEMENT STATEMENT
108 Warnings:
109 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
110 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
111 prepare stmt1 from 'insert into t1 select ?';
112 set @string="emergency_7_";
113 insert into t1 values("work_8_");
114 execute stmt1 using @string;
115 deallocate prepare stmt1;
116 prepare stmt1 from 'insert into t1 select ?';
117 insert into t1 values("work_9_");
118 execute stmt1 using @string;
119 deallocate prepare stmt1;
120 insert into t1 values("for_10_");
121 insert into t1 select "yesterday_11_";
122 set binlog_format=statement;
123 Warnings:
124 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
125 select @@global.binlog_format, @@session.binlog_format;
126 @@global.binlog_format @@session.binlog_format
127 STATEMENT STATEMENT
128 Warnings:
129 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
130 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
131 set global binlog_format=statement;
132 Warnings:
133 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
134 select @@global.binlog_format, @@session.binlog_format;
135 @@global.binlog_format @@session.binlog_format
136 STATEMENT STATEMENT
137 Warnings:
138 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
139 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
140 prepare stmt1 from 'insert into t1 select ?';
141 set @string="emergency_12_";
142 insert into t1 values("work_13_");
143 execute stmt1 using @string;
144 deallocate prepare stmt1;
145 prepare stmt1 from 'insert into t1 select ?';
146 insert into t1 values("work_14_");
147 execute stmt1 using @string;
148 deallocate prepare stmt1;
149 insert into t1 values("for_15_");
150 insert into t1 select "yesterday_16_";
151 set global binlog_format=mixed;
152 Warnings:
153 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
154 select @@global.binlog_format, @@session.binlog_format;
155 @@global.binlog_format @@session.binlog_format
156 MIXED STATEMENT
157 Warnings:
158 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
159 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
160 set binlog_format=default;
161 Warnings:
162 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
163 select @@global.binlog_format, @@session.binlog_format;
164 @@global.binlog_format @@session.binlog_format
165 MIXED MIXED
166 Warnings:
167 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
168 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
169 prepare stmt1 from 'insert into t1 select concat(UUID(),?)';
170 set @string="emergency_17_";
171 insert into t1 values("work_18_");
172 execute stmt1 using @string;
173 deallocate prepare stmt1;
174 prepare stmt1 from 'insert into t1 select ?';
175 insert into t1 values(concat(UUID(),"work_19_"));
176 execute stmt1 using @string;
177 deallocate prepare stmt1;
178 insert into t1 values(concat("for_20_",UUID()));
179 insert into t1 select "yesterday_21_";
180 prepare stmt1 from 'insert into t1 select ?';
181 insert into t1 values(concat(UUID(),"work_22_"));
182 execute stmt1 using @string;
183 deallocate prepare stmt1;
184 insert into t1 values(concat("for_23_",UUID()));
185 insert into t1 select "yesterday_24_";
186 create table t2 select rpad(UUID(),100,' ');
187 create table t3 select 1 union select UUID();
188 create table t5 select * from t1 where 3 in (select 1 union select 2 union select curdate() union select 3);
189 insert ignore into t5 select UUID() from t1 where 3 in (select 1 union select 2 union select 3);
190 create procedure foo()
191 begin
192 insert into t1 values("work_25_");
193 insert into t1 values(concat("for_26_",UUID()));
194 insert into t1 select "yesterday_27_";
195 end|
196 create procedure foo2()
197 begin
198 insert into t1 values(concat("emergency_28_",UUID()));
199 insert into t1 values("work_29_");
200 insert into t1 values(concat("for_30_",UUID()));
201 set session binlog_format='row'; # accepted for stored procs
202 insert into t1 values("more work_31_");
203 set session binlog_format=mixed;
204 end|
205 create function foo3() returns bigint unsigned
206 begin
207 set session binlog_format='row'; # rejected for stored funcs
208 insert into t1 values("alarm");
209 return 100;
210 end|
211 create procedure foo4(x varchar(100))
212 begin
213 insert into t1 values(concat("work_250_",x));
214 insert into t1 select "yesterday_270_";
215 end|
216 call foo();
217 call foo2();
218 Warnings:
219 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
220 call foo4("hello");
221 call foo4(UUID());
222 call foo4("world");
223 select foo3();
224 ERROR HY000: Cannot change the binary logging format inside a stored function or trigger
225 select * from t1 where a="alarm";
226 a
227 drop function foo3;
228 create function foo3() returns bigint unsigned
229 begin
230 insert into t1 values("foo3_32_");
231 call foo();
232 return 100;
233 end|
234 insert into t2 select foo3();
235 prepare stmt1 from 'insert into t2 select foo3()';
236 execute stmt1;
237 execute stmt1;
238 deallocate prepare stmt1;
239 create function foo4() returns bigint unsigned
240 begin
241 insert into t2 select foo3();
242 return 100;
243 end|
244 select foo4();
245 foo4()
246 100
247 prepare stmt1 from 'select foo4()';
248 execute stmt1;
249 foo4()
250 100
251 execute stmt1;
252 foo4()
253 100
254 deallocate prepare stmt1;
255 create function foo5() returns bigint unsigned
256 begin
257 insert into t2 select UUID();
258 return 100;
259 end|
260 select foo5();
261 foo5()
262 100
263 prepare stmt1 from 'select foo5()';
264 execute stmt1;
265 foo5()
266 100
267 execute stmt1;
268 foo5()
269 100
270 deallocate prepare stmt1;
271 create function foo6(x varchar(100)) returns bigint unsigned
272 begin
273 insert into t2 select x;
274 return 100;
275 end|
276 select foo6("foo6_1_");
277 foo6("foo6_1_")
278 100
279 select foo6(concat("foo6_2_",UUID()));
280 foo6(concat("foo6_2_",UUID()))
281 100
282 prepare stmt1 from 'select foo6(concat("foo6_3_",UUID()))';
283 execute stmt1;
284 foo6(concat("foo6_3_",UUID()))
285 100
286 execute stmt1;
287 foo6(concat("foo6_3_",UUID()))
288 100
289 deallocate prepare stmt1;
290 create view v1 as select uuid();
291 create table t11 (data varchar(255));
292 insert into t11 select * from v1;
293 insert into t11 select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='mysqltest1' and TABLE_NAME IN ('v1','t11');
294 prepare stmt1 from "insert into t11 select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='mysqltest1' and TABLE_NAME IN ('v1','t11')";
295 execute stmt1;
296 execute stmt1;
297 deallocate prepare stmt1;
298 create trigger t11_bi before insert on t11 for each row
299 begin
300 set NEW.data = concat(NEW.data,UUID());
301 end|
302 insert into t11 values("try_560_");
303 insert delayed into t2 values("delay_1_");
304 Warnings:
305 Warning 3005 INSERT DELAYED is no longer supported. The statement was converted to INSERT.
306 insert delayed into t2 values(concat("delay_2_",UUID()));
307 Warnings:
308 Warning 3005 INSERT DELAYED is no longer supported. The statement was converted to INSERT.
309 insert delayed into t2 values("delay_6_");
310 Warnings:
311 Warning 3005 INSERT DELAYED is no longer supported. The statement was converted to INSERT.
312 insert delayed into t2 values(rand());
313 Warnings:
314 Warning 3005 INSERT DELAYED is no longer supported. The statement was converted to INSERT.
315 set @a=2.345;
316 insert delayed into t2 values(@a);
317 Warnings:
318 Warning 3005 INSERT DELAYED is no longer supported. The statement was converted to INSERT.
319 include/sync_slave_sql_with_master.inc
320 create table t20 select * from t1;
321 create table t21 select * from t2;
322 create table t22 select * from t3;
323 drop table t1,t2,t3;
324 create table t1 (a int primary key auto_increment, b varchar(100));
325 create table t2 (a int primary key auto_increment, b varchar(100));
326 create table t3 (b varchar(100));
327 create function f (x varchar(100)) returns int deterministic
328 begin
329 insert into t1 values(null,x);
330 insert into t2 values(null,x);
331 return 1;
332 end|
333 select f("try_41_");
334 f("try_41_")
335 1
336 include/sync_slave_sql_with_master.inc
337 use mysqltest1;
338 insert into t2 values(2,null),(3,null),(4,null);
339 delete from t2 where a>=2;
340 select f("try_42_");
341 f("try_42_")
342 1
343 include/sync_slave_sql_with_master.inc
344 insert into t2 values(3,null),(4,null);
345 delete from t2 where a>=3;
346 prepare stmt1 from 'select f(?)';
347 set @string="try_43_";
348 insert into t1 values(null,"try_44_");
349 execute stmt1 using @string;
350 f(?)
351 1
352 deallocate prepare stmt1;
353 include/sync_slave_sql_with_master.inc
354 create table t12 select * from t1;
355 drop table t1;
356 create table t1 (a int, b varchar(100), key(a));
357 select f("try_45_");
358 f("try_45_")
359 1
360 create table t13 select * from t1;
361 drop table t1;
362 create table t1 (a int primary key auto_increment, b varchar(100));
363 drop function f;
364 create table t14 (unique (a)) select * from t2;
365 truncate table t2;
366 create function f1 (x varchar(100)) returns int deterministic
367 begin
368 insert into t1 values(null,x);
369 return 1;
370 end|
371 create function f2 (x varchar(100)) returns int deterministic
372 begin
373 insert into t2 values(null,x);
374 return 1;
375 end|
376 select f1("try_46_"),f2("try_47_");
377 f1("try_46_") f2("try_47_")
378 1 1
379 include/sync_slave_sql_with_master.inc
380 insert into t2 values(2,null),(3,null),(4,null);
381 delete from t2 where a>=2;
382 select f1("try_48_"),f2("try_49_");
383 f1("try_48_") f2("try_49_")
384 1 1
385 insert into t3 values(concat("try_50_",f1("try_51_"),f2("try_52_")));
386 include/sync_slave_sql_with_master.inc
387 drop function f2;
388 create function f2 (x varchar(100)) returns int deterministic
389 begin
390 declare y int;
391 insert into t1 values(null,x);
392 set y = (select count(*) from t2);
393 return y;
394 end|
395 select f1("try_53_"),f2("try_54_");
396 f1("try_53_") f2("try_54_")
397 1 3
398 include/sync_slave_sql_with_master.inc
399 drop function f2;
400 create trigger t1_bi before insert on t1 for each row
401 begin
402 insert into t2 values(null,"try_55_");
403 end|
404 insert into t1 values(null,"try_56_");
405 alter table t1 modify a int, drop primary key;
406 insert into t1 values(null,"try_57_");
407 include/sync_slave_sql_with_master.inc
408 CREATE TEMPORARY TABLE t15 SELECT UUID();
409 create table t16 like t15;
410 INSERT INTO t16 SELECT * FROM t15;
411 insert into t16 values("try_65_");
412 drop table t15;
413 insert into t16 values("try_66_");
414 include/sync_slave_sql_with_master.inc
415 select count(*) from t1;
416 count(*)
417 7
418 select count(*) from t2;
419 count(*)
420 5
421 select count(*) from t3;
422 count(*)
423 1
424 select count(*) from t5;
425 count(*)
426 58
427 select count(*) from t11;
428 count(*)
429 8
430 select count(*) from t20;
431 count(*)
432 66
433 select count(*) from t21;
434 count(*)
435 19
436 select count(*) from t22;
437 count(*)
438 2
439 select count(*) from t12;
440 count(*)
441 4
442 select count(*) from t13;
443 count(*)
444 1
445 select count(*) from t14;
446 count(*)
447 4
448 select count(*) from t16;
449 count(*)
450 3
451 include/sync_slave_sql_with_master.inc
452 DROP TABLE IF EXISTS t11;
453 SET SESSION BINLOG_FORMAT=STATEMENT;
454 Warnings:
455 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
456 CREATE TABLE t11 (song VARCHAR(255));
457 LOCK TABLES t11 WRITE;
458 SET SESSION BINLOG_FORMAT='ROW';
459 Warnings:
460 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
461 INSERT INTO t11 VALUES('Several Species of Small Furry Animals Gathered Together in a Cave and Grooving With a Pict');
462 SET SESSION BINLOG_FORMAT=STATEMENT;
463 Warnings:
464 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
465 INSERT INTO t11 VALUES('Careful With That Axe, Eugene');
466 UNLOCK TABLES;
467 SELECT * FROM t11;
468 song Several Species of Small Furry Animals Gathered Together in a Cave and Grooving With a Pict
469 song Careful With That Axe, Eugene
470 include/sync_slave_sql_with_master.inc
471 USE mysqltest1;
472 SELECT * FROM t11;
473 song Several Species of Small Furry Animals Gathered Together in a Cave and Grooving With a Pict
474 song Careful With That Axe, Eugene
475 DROP TABLE IF EXISTS t12;
476 SET SESSION BINLOG_FORMAT=MIXED;
477 Warnings:
478 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
479 CREATE TABLE t12 (data LONG);
480 LOCK TABLES t12 WRITE;
481 INSERT INTO t12 VALUES(UUID());
482 UNLOCK TABLES;
483 include/sync_slave_sql_with_master.inc
484 CREATE FUNCTION my_user()
485 RETURNS CHAR(64)
486 BEGIN
487 DECLARE user CHAR(64);
488 SELECT USER() INTO user;
489 RETURN user;
490 END $$
491 CREATE FUNCTION my_current_user()
492 RETURNS CHAR(64)
493 BEGIN
494 DECLARE user CHAR(64);
495 SELECT CURRENT_USER() INTO user;
496 RETURN user;
497 END $$
498 DROP TABLE IF EXISTS t13;
499 CREATE TABLE t13 (data CHAR(64));
500 INSERT INTO t13 VALUES (USER());
501 INSERT INTO t13 VALUES (my_user());
502 INSERT INTO t13 VALUES (CURRENT_USER());
503 INSERT INTO t13 VALUES (my_current_user());
504 include/sync_slave_sql_with_master.inc
505 drop database mysqltest1;
506 include/sync_slave_sql_with_master.inc
507 set global binlog_format =@my_binlog_format;
508 Warnings:
509 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
510 include/rpl_end.inc