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