Debian Codegraph @

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

raw
1 #
2 # Setup
3 #
4 #
5 # See if queries that use both auto_increment and LAST_INSERT_ID()
6 # are replicated well
7 #
8 # We also check how the foreign_key_check variable is replicated
9 #
10 include/master-slave.inc
11 [connection master]
12 connection master;
13 SET @old_concurrent_insert= @@global.concurrent_insert;
14 SET @@global.concurrent_insert= 0;
15 connection master;
16 create table t1(a int auto_increment, key(a)) engine=myisam;
17 create table t2(b int auto_increment, c int, key(b)) engine=myisam;
18 insert into t1 values (1),(2),(3);
19 insert into t1 values (null);
20 insert into t2 values (null,last_insert_id());
21 connection slave;
22 select * from t1 ORDER BY a;
23 a
24 1
25 2
26 3
27 4
28 select * from t2 ORDER BY b;
29 b c
30 1 4
31 connection master;
32 drop table t1;
33 drop table t2;
34 create table t1(a int auto_increment, key(a)) engine=myisam;
35 create table t2(b int auto_increment, c int, key(b), foreign key(b) references t1(a)) engine=myisam;
36 SET FOREIGN_KEY_CHECKS=0;
37 insert into t1 values (10);
38 insert into t1 values (null),(null),(null);
39 insert into t2 values (5,0);
40 insert into t2 values (null,last_insert_id());
41 SET FOREIGN_KEY_CHECKS=1;
42 connection slave;
43 select * from t1;
44 a
45 10
46 11
47 12
48 13
49 select * from t2;
50 b c
51 5 0
52 6 11
53 connection master;
54 #
55 # check if INSERT SELECT in auto_increment is well replicated (bug #490)
56 #
57 drop table t2;
58 drop table t1;
59 create table t1(a int auto_increment, key(a)) engine=myisam;
60 create table t2(b int auto_increment, c int, key(b)) engine=myisam;
61 insert into t1 values (10);
62 insert into t1 values (null),(null),(null);
63 insert into t2 values (5,0);
64 insert into t2 (c) select * from t1 ORDER BY a;
65 select * from t2 ORDER BY b;
66 b c
67 5 0
68 6 10
69 7 11
70 8 12
71 9 13
72 connection slave;
73 select * from t1 ORDER BY a;
74 a
75 10
76 11
77 12
78 13
79 select * from t2 ORDER BY b;
80 b c
81 5 0
82 6 10
83 7 11
84 8 12
85 9 13
86 connection master;
87 drop table t1;
88 drop table t2;
89 connection slave;
90 #
91 # Bug#8412: Error codes reported in binary log for CHARACTER SET,
92 # FOREIGN_KEY_CHECKS
93 #
94 connection master;
95 SET TIMESTAMP=1000000000;
96 CREATE TABLE t1 ( a INT UNIQUE ) engine=myisam;
97 SET FOREIGN_KEY_CHECKS=0;
98 INSERT INTO t1 VALUES (1),(1);
99 Got one of the listed errors
100 connection slave;
101 connection master;
102 drop table t1;
103 connection slave;
104 #
105 # Bug#14553: NULL in WHERE resets LAST_INSERT_ID
106 #
107 connection master;
108 set @@session.sql_auto_is_null=1;
109 create table t1(a int auto_increment, key(a)) engine=myisam;
110 create table t2(a int) engine=myisam;
111 insert into t1 (a) values (null);
112 insert into t2 (a) select a from t1 where a is null;
113 insert into t2 (a) select a from t1 where a is null;
114 select * from t2;
115 a
116 1
117 connection slave;
118 connection slave;
119 select * from t2;
120 a
121 1
122 connection master;
123 drop table t1;
124 drop table t2;
125 #
126 # End of 4.1 tests
127 #
128 #
129 # BUG#15728: LAST_INSERT_ID function inside a stored function returns 0
130 #
131 # The solution is not to reset last_insert_id on enter to sub-statement.
132 #
133 connection master;
134 drop function if exists bug15728;
135 drop function if exists bug15728_insert;
136 drop table if exists t1, t2;
137 create table t1 (
138 id int not null auto_increment,
139 last_id int,
140 primary key (id)
141 ) engine=myisam;
142 create function bug15728() returns int(11)
143 return last_insert_id();
144 insert into t1 (last_id) values (0);
145 insert into t1 (last_id) values (last_insert_id());
146 insert into t1 (last_id) values (bug15728());
147 create table t2 (
148 id int not null auto_increment,
149 last_id int,
150 primary key (id)
151 ) engine=myisam;
152 create function bug15728_insert() returns int(11) modifies sql data
153 begin
154 insert into t2 (last_id) values (bug15728());
155 return bug15728();
156 end|
157 create trigger t1_bi before insert on t1 for each row
158 begin
159 declare res int;
160 select bug15728_insert() into res;
161 set NEW.last_id = res;
162 end|
163 insert into t1 (last_id) values (0);
164 drop trigger t1_bi;
165 select last_insert_id();
166 last_insert_id()
167 4
168 select bug15728_insert();
169 bug15728_insert()
170 2
171 select last_insert_id();
172 last_insert_id()
173 4
174 insert into t1 (last_id) values (bug15728());
175 select last_insert_id();
176 last_insert_id()
177 5
178 drop procedure if exists foo;
179 create procedure foo()
180 begin
181 declare res int;
182 insert into t2 (last_id) values (bug15728());
183 insert into t1 (last_id) values (bug15728());
184 end|
185 call foo();
186 select * from t1;
187 id last_id
188 1 0
189 2 1
190 3 2
191 4 1
192 5 4
193 6 3
194 select * from t2;
195 id last_id
196 1 3
197 2 4
198 3 5
199 connection slave;
200 select * from t1;
201 id last_id
202 1 0
203 2 1
204 3 2
205 4 1
206 5 4
207 6 3
208 select * from t2;
209 id last_id
210 1 3
211 2 4
212 3 5
213 connection master;
214 drop function bug15728;
215 drop function bug15728_insert;
216 drop table t1,t2;
217 drop procedure foo;
218 create table t1 (n int primary key auto_increment not null,
219 b int, unique(b)) engine=myisam;
220 set sql_log_bin=0;
221 insert into t1 values(null,100);
222 replace into t1 values(null,50),(null,100),(null,150);
223 select * from t1 order by n;
224 n b
225 2 50
226 3 100
227 4 150
228 truncate table t1;
229 set sql_log_bin=1;
230 insert into t1 values(null,100);
231 select * from t1 order by n;
232 n b
233 1 100
234 connection slave;
235 insert into t1 values(null,200),(null,300);
236 delete from t1 where b <> 100;
237 select * from t1 order by n;
238 n b
239 1 100
240 connection master;
241 replace into t1 values(null,100),(null,350);
242 select * from t1 order by n;
243 n b
244 2 100
245 3 350
246 connection slave;
247 select * from t1 order by n;
248 n b
249 2 100
250 3 350
251 connection master;
252 insert into t1 values (NULL,400),(3,500),(NULL,600) on duplicate key UPDATE n=1000;
253 select * from t1 order by n;
254 n b
255 2 100
256 4 400
257 1000 350
258 1001 600
259 connection slave;
260 select * from t1 order by n;
261 n b
262 2 100
263 4 400
264 1000 350
265 1001 600
266 connection master;
267 drop table t1;
268 create table t1 (n int primary key auto_increment not null,
269 b int, unique(b)) engine=myisam;
270 insert into t1 values(null,100);
271 select * from t1 order by n;
272 n b
273 1 100
274 connection slave;
275 insert into t1 values(null,200),(null,300);
276 delete from t1 where b <> 100;
277 select * from t1 order by n;
278 n b
279 1 100
280 connection master;
281 insert into t1 values(null,100),(null,350) on duplicate key update n=2;
282 select * from t1 order by n;
283 n b
284 2 100
285 3 350
286 connection slave;
287 select * from t1 order by n;
288 n b
289 2 100
290 3 350
291 connection master;
292 drop table t1;
293 connection slave;
294 connection master;
295 CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY AUTO_INCREMENT, b INT,
296 UNIQUE(b)) ENGINE=myisam;
297 INSERT INTO t1(b) VALUES(1),(1),(2) ON DUPLICATE KEY UPDATE t1.b=10;
298 SELECT * FROM t1 ORDER BY a;
299 a b
300 1 10
301 2 2
302 connection slave;
303 SELECT * FROM t1 ORDER BY a;
304 a b
305 1 10
306 2 2
307 connection master;
308 drop table t1;
309 CREATE TABLE t1 (
310 id bigint(20) unsigned NOT NULL auto_increment,
311 field_1 int(10) unsigned NOT NULL,
312 field_2 varchar(255) NOT NULL,
313 field_3 varchar(255) NOT NULL,
314 PRIMARY KEY (id),
315 UNIQUE KEY field_1 (field_1, field_2)
316 ) ENGINE=myisam;
317 CREATE TABLE t2 (
318 field_a int(10) unsigned NOT NULL,
319 field_b varchar(255) NOT NULL,
320 field_c varchar(255) NOT NULL
321 ) ENGINE=myisam;
322 INSERT INTO t2 (field_a, field_b, field_c) VALUES (1, 'a', '1a');
323 INSERT INTO t2 (field_a, field_b, field_c) VALUES (2, 'b', '2b');
324 INSERT INTO t2 (field_a, field_b, field_c) VALUES (3, 'c', '3c');
325 INSERT INTO t2 (field_a, field_b, field_c) VALUES (4, 'd', '4d');
326 INSERT INTO t2 (field_a, field_b, field_c) VALUES (5, 'e', '5e');
327 INSERT INTO t1 (field_1, field_2, field_3)
328 SELECT t2.field_a, t2.field_b, t2.field_c
329 FROM t2
330 ON DUPLICATE KEY UPDATE
331 t1.field_3 = t2.field_c;
332 INSERT INTO t2 (field_a, field_b, field_c) VALUES (6, 'f', '6f');
333 INSERT INTO t1 (field_1, field_2, field_3)
334 SELECT t2.field_a, t2.field_b, t2.field_c
335 FROM t2
336 ON DUPLICATE KEY UPDATE
337 t1.field_3 = t2.field_c;
338 SELECT * FROM t1 ORDER BY id;
339 id field_1 field_2 field_3
340 1 1 a 1a
341 2 2 b 2b
342 3 3 c 3c
343 4 4 d 4d
344 5 5 e 5e
345 6 6 f 6f
346 connection slave;
347 SELECT * FROM t1 ORDER BY id;
348 id field_1 field_2 field_3
349 1 1 a 1a
350 2 2 b 2b
351 3 3 c 3c
352 4 4 d 4d
353 5 5 e 5e
354 6 6 f 6f
355 connection master;
356 drop table t1, t2;
357 connection master;
358 DROP PROCEDURE IF EXISTS p1;
359 DROP TABLE IF EXISTS t1, t2;
360 SELECT LAST_INSERT_ID(0);
361 LAST_INSERT_ID(0)
362 0
363 CREATE TABLE t1 (
364 id INT NOT NULL DEFAULT 0,
365 last_id INT,
366 PRIMARY KEY (id)
367 ) ENGINE=myisam;
368 CREATE TABLE t2 (
369 id INT NOT NULL AUTO_INCREMENT,
370 last_id INT,
371 PRIMARY KEY (id)
372 ) ENGINE=myisam;
373 CREATE PROCEDURE p1()
374 BEGIN
375 INSERT INTO t2 (last_id) VALUES (LAST_INSERT_ID());
376 INSERT INTO t1 (last_id) VALUES (LAST_INSERT_ID());
377 END|
378 CALL p1();
379 SELECT * FROM t1 ORDER BY id;
380 id last_id
381 0 1
382 SELECT * FROM t2 ORDER BY id;
383 id last_id
384 1 0
385 connection slave;
386 SELECT * FROM t1 ORDER BY id;
387 id last_id
388 0 1
389 SELECT * FROM t2 ORDER BY id;
390 id last_id
391 1 0
392 connection master;
393 DROP PROCEDURE p1;
394 DROP TABLE t1, t2;
395 DROP PROCEDURE IF EXISTS p1;
396 DROP FUNCTION IF EXISTS f1;
397 DROP FUNCTION IF EXISTS f2;
398 DROP FUNCTION IF EXISTS f3;
399 DROP TABLE IF EXISTS t1, t2;
400 CREATE TABLE t1 (
401 i INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
402 j INT DEFAULT 0
403 ) ENGINE=myisam;
404 CREATE TABLE t2 (i INT) ENGINE=myisam;
405 CREATE PROCEDURE p1()
406 BEGIN
407 INSERT INTO t1 (i) VALUES (NULL);
408 INSERT INTO t2 (i) VALUES (LAST_INSERT_ID());
409 INSERT INTO t1 (i) VALUES (NULL), (NULL);
410 INSERT INTO t2 (i) VALUES (LAST_INSERT_ID());
411 END |
412 CREATE FUNCTION f1() RETURNS INT MODIFIES SQL DATA
413 BEGIN
414 INSERT INTO t1 (i) VALUES (NULL);
415 INSERT INTO t2 (i) VALUES (LAST_INSERT_ID());
416 INSERT INTO t1 (i) VALUES (NULL), (NULL);
417 INSERT INTO t2 (i) VALUES (LAST_INSERT_ID());
418 RETURN 0;
419 END |
420 CREATE FUNCTION f2() RETURNS INT NOT DETERMINISTIC
421 RETURN LAST_INSERT_ID() |
422 CREATE FUNCTION f3() RETURNS INT MODIFIES SQL DATA
423 BEGIN
424 INSERT INTO t2 (i) VALUES (LAST_INSERT_ID());
425 RETURN 0;
426 END |
427 INSERT INTO t1 VALUES (NULL, -1);
428 CALL p1();
429 SELECT f1();
430 f1()
431 0
432 INSERT INTO t1 VALUES (NULL, f2()), (NULL, LAST_INSERT_ID()),
433 (NULL, LAST_INSERT_ID()), (NULL, f2()), (NULL, f2());
434 INSERT INTO t1 VALUES (NULL, f2());
435 INSERT INTO t1 VALUES (NULL, 0), (NULL, LAST_INSERT_ID());
436 UPDATE t1 SET j= -1 WHERE i IS NULL;
437 INSERT INTO t1 (i) VALUES (NULL);
438 connection master1;
439 INSERT INTO t1 (i) VALUES (NULL);
440 connection master;
441 SELECT f3();
442 f3()
443 0
444 SELECT * FROM t1 ORDER BY i;
445 i j
446 1 -1
447 2 0
448 3 0
449 4 0
450 5 0
451 6 0
452 7 0
453 8 3
454 9 3
455 10 3
456 11 3
457 12 3
458 13 8
459 14 -1
460 15 13
461 16 0
462 17 0
463 SELECT * FROM t2 ORDER BY i;
464 i
465 2
466 3
467 5
468 6
469 16
470 connection slave;
471 SELECT * FROM t1;
472 i j
473 1 -1
474 2 0
475 3 0
476 4 0
477 5 0
478 6 0
479 7 0
480 8 3
481 9 3
482 10 3
483 11 3
484 12 3
485 13 8
486 14 -1
487 15 13
488 16 0
489 17 0
490 SELECT * FROM t2;
491 i
492 2
493 3
494 5
495 6
496 16
497 connection master;
498 DROP PROCEDURE p1;
499 DROP FUNCTION f1;
500 DROP FUNCTION f2;
501 DROP FUNCTION f3;
502 DROP TABLE t1, t2;
503 connection slave;
504 #
505 # End of 5.0 tests
506 #
507 connection master;
508 create table t2 (
509 id int not null auto_increment,
510 last_id int,
511 primary key (id)
512 ) engine=myisam;
513 connection master;
514 truncate table t2;
515 create table t1 (id tinyint primary key) engine=myisam;
516 create function insid() returns int
517 begin
518 insert into t2 (last_id) values (0);
519 return 0;
520 end|
521 set sql_log_bin=0;
522 insert into t2 (id) values(1),(2),(3);
523 delete from t2;
524 set sql_log_bin=1;
525 select insid();
526 insid()
527 0
528 set sql_log_bin=0;
529 insert into t2 (id) values(5),(6),(7);
530 delete from t2 where id>=5;
531 set sql_log_bin=1;
532 insert into t1 select insid();
533 select * from t1 order by id;
534 id
535 0
536 select * from t2 order by id;
537 id last_id
538 4 0
539 8 0
540 connection slave;
541 select * from t1 order by id;
542 id
543 0
544 select * from t2 order by id;
545 id last_id
546 4 0
547 8 0
548 connection master;
549 drop table t1;
550 drop function insid;
551 truncate table t2;
552 create table t1 (n int primary key auto_increment not null,
553 b int, unique(b)) engine=myisam;
554 create procedure foo()
555 begin
556 insert into t1 values(null,10);
557 insert ignore into t1 values(null,10);
558 insert ignore into t1 values(null,10);
559 insert into t2 values(null,3);
560 end|
561 call foo();
562 select * from t1 order by n;
563 n b
564 1 10
565 select * from t2 order by id;
566 id last_id
567 1 3
568 connection slave;
569 select * from t1 order by n;
570 n b
571 1 10
572 select * from t2 order by id;
573 id last_id
574 1 3
575 connection master;
576 drop table t1, t2;
577 drop procedure foo;
578 SET @@global.concurrent_insert= @old_concurrent_insert;
579 set @@session.sql_auto_is_null=default;
580 include/rpl_end.inc