Debian Codegraph @

mysql-test/suite/rpl/include/rpl_insert_id.test

raw
1 ###########################################################
2 # See if queries that use both auto_increment and LAST_INSERT_ID()
3 # are replicated well
4 ############################################################
5 # REQUIREMENT
6 # Auto increment should work for a table with an auto_increment
7 # column and index but without primary key.
8 ##############################################################
9
10 --echo #
11 --echo # Setup
12 --echo #
13
14 --echo #
15 --echo # See if queries that use both auto_increment and LAST_INSERT_ID()
16 --echo # are replicated well
17 --echo #
18 --echo # We also check how the foreign_key_check variable is replicated
19 --echo #
20
21 -- source include/master-slave.inc
22 #should work for both SBR and RBR
23
24 --disable_query_log
25 call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT");
26 --connection slave
27 call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT");
28 --enable_query_log
29
30
31 # If concurrent inserts are on, it is not guaranteed that the rows
32 # inserted by INSERT are immediately accessible by SELECT in another
33 # thread. This would cause problems near the line 'connection master1'
34 # below. So we turn off concurrent inserts.
35 connection master;
36 SET @old_concurrent_insert= @@global.concurrent_insert;
37 SET @@global.concurrent_insert= 0;
38
39 connection master;
40 eval create table t1(a int auto_increment, key(a)) engine=$engine_type;
41 eval create table t2(b int auto_increment, c int, key(b)) engine=$engine_type;
42 insert into t1 values (1),(2),(3);
43 insert into t1 values (null);
44 insert into t2 values (null,last_insert_id());
45 sync_slave_with_master;
46 select * from t1 ORDER BY a;
47 select * from t2 ORDER BY b;
48 connection master;
49 #check if multi-line inserts,
50 #which set last_insert_id to the first id inserted,
51 #are replicated the same way
52 drop table t1;
53 drop table t2;
54 --disable_warnings
55 eval create table t1(a int auto_increment, key(a)) engine=$engine_type;
56 eval create table t2(b int auto_increment, c int, key(b), foreign key(b) references t1(a)) engine=$engine_type;
57 --enable_warnings
58 SET FOREIGN_KEY_CHECKS=0;
59 insert into t1 values (10);
60 insert into t1 values (null),(null),(null);
61 insert into t2 values (5,0);
62 insert into t2 values (null,last_insert_id());
63 SET FOREIGN_KEY_CHECKS=1;
64 sync_slave_with_master;
65 select * from t1;
66 select * from t2;
67 connection master;
68
69 --echo #
70 --echo # check if INSERT SELECT in auto_increment is well replicated (bug #490)
71 --echo #
72
73 drop table t2;
74 drop table t1;
75 eval create table t1(a int auto_increment, key(a)) engine=$engine_type;
76 eval create table t2(b int auto_increment, c int, key(b)) engine=$engine_type;
77 insert into t1 values (10);
78 insert into t1 values (null),(null),(null);
79 insert into t2 values (5,0);
80 --disable_warnings ONCE
81 insert into t2 (c) select * from t1 ORDER BY a;
82 select * from t2 ORDER BY b;
83 sync_slave_with_master;
84 select * from t1 ORDER BY a;
85 select * from t2 ORDER BY b;
86 connection master;
87 drop table t1;
88 drop table t2;
89 sync_slave_with_master;
90
91 --echo #
92 --echo # Bug#8412: Error codes reported in binary log for CHARACTER SET,
93 --echo # FOREIGN_KEY_CHECKS
94 --echo #
95
96 connection master;
97 SET TIMESTAMP=1000000000;
98 eval CREATE TABLE t1 ( a INT UNIQUE ) engine=$engine_type;
99 SET FOREIGN_KEY_CHECKS=0;
100 # Duplicate Key Errors
101 --error 1022, ER_DUP_ENTRY
102 INSERT INTO t1 VALUES (1),(1);
103 sync_slave_with_master;
104 connection master;
105 drop table t1;
106 sync_slave_with_master;
107
108 --echo #
109 --echo # Bug#14553: NULL in WHERE resets LAST_INSERT_ID
110 --echo #
111
112 connection master;
113 set @@session.sql_auto_is_null=1;
114 eval create table t1(a int auto_increment, key(a)) engine=$engine_type;
115 eval create table t2(a int) engine=$engine_type;
116 insert into t1 (a) values (null);
117 --disable_warnings
118 insert into t2 (a) select a from t1 where a is null;
119 insert into t2 (a) select a from t1 where a is null;
120 --enable_warnings
121 select * from t2;
122 sync_slave_with_master;
123 connection slave;
124 select * from t2;
125 connection master;
126 drop table t1;
127 drop table t2;
128
129 --echo #
130 --echo # End of 4.1 tests
131 --echo #
132
133 --echo #
134 --echo # BUG#15728: LAST_INSERT_ID function inside a stored function returns 0
135 --echo #
136 --echo # The solution is not to reset last_insert_id on enter to sub-statement.
137 --echo #
138
139 connection master;
140 --disable_warnings
141 drop function if exists bug15728;
142 drop function if exists bug15728_insert;
143 drop table if exists t1, t2;
144 --enable_warnings
145
146 eval create table t1 (
147 id int not null auto_increment,
148 last_id int,
149 primary key (id)
150 ) engine=$engine_type;
151 create function bug15728() returns int(11)
152 return last_insert_id();
153
154 insert into t1 (last_id) values (0);
155 insert into t1 (last_id) values (last_insert_id());
156 insert into t1 (last_id) values (bug15728());
157
158 # Check that nested call replicates too.
159 eval create table t2 (
160 id int not null auto_increment,
161 last_id int,
162 primary key (id)
163 ) engine=$engine_type;
164 delimiter |;
165 create function bug15728_insert() returns int(11) modifies sql data
166 begin
167 insert into t2 (last_id) values (bug15728());
168 return bug15728();
169 end|
170 create trigger t1_bi before insert on t1 for each row
171 begin
172 declare res int;
173 select bug15728_insert() into res;
174 set NEW.last_id = res;
175 end|
176 delimiter ;|
177
178 --disable_warnings ONCE
179 insert into t1 (last_id) values (0);
180
181 drop trigger t1_bi;
182
183 # Check that nested call doesn't affect outer context.
184 select last_insert_id();
185 --disable_ps2_protocol
186 --disable_warnings ONCE
187 select bug15728_insert();
188 select last_insert_id();
189 --enable_ps2_protocol
190 insert into t1 (last_id) values (bug15728());
191 # This should be exactly one greater than in the previous call.
192 select last_insert_id();
193
194 # BUG#20339 - stored procedure using LAST_INSERT_ID() does not
195 # replicate statement-based
196 --disable_warnings ONCE
197 drop procedure if exists foo;
198 delimiter |;
199 create procedure foo()
200 begin
201 declare res int;
202 insert into t2 (last_id) values (bug15728());
203 insert into t1 (last_id) values (bug15728());
204 end|
205 delimiter ;|
206 call foo();
207
208 select * from t1;
209 select * from t2;
210 sync_slave_with_master;
211 select * from t1;
212 select * from t2;
213 connection master;
214
215 drop function bug15728;
216 drop function bug15728_insert;
217 drop table t1,t2;
218 drop procedure foo;
219
220 # test of BUG#20188 REPLACE or ON DUPLICATE KEY UPDATE in
221 # auto_increment breaks binlog
222
223 eval create table t1 (n int primary key auto_increment not null,
224 b int, unique(b)) engine=$engine_type;
225
226 # First, test that we do not call restore_auto_increment() too early
227 # in write_record():
228 set sql_log_bin=0;
229 insert into t1 values(null,100);
230 replace into t1 values(null,50),(null,100),(null,150);
231 select * from t1 order by n;
232 truncate table t1;
233 set sql_log_bin=1;
234
235 insert into t1 values(null,100);
236 select * from t1 order by n;
237 sync_slave_with_master;
238 # make slave's table autoinc counter bigger
239 insert into t1 values(null,200),(null,300);
240 delete from t1 where b <> 100;
241 # check that slave's table content is identical to master
242 select * from t1 order by n;
243 # only the auto_inc counter differs.
244
245 connection master;
246 replace into t1 values(null,100),(null,350);
247 select * from t1 order by n;
248 sync_slave_with_master;
249 select * from t1 order by n;
250
251 # Same test as for REPLACE, but for ON DUPLICATE KEY UPDATE
252
253 # We first check that if we update a row using a value larger than the
254 # table's counter, the counter for next row is bigger than the
255 # after-value of the updated row.
256 connection master;
257 --disable_warnings ONCE
258 insert into t1 values (NULL,400),(3,500),(NULL,600) on duplicate key UPDATE n=1000;
259 select * from t1 order by n;
260 sync_slave_with_master;
261 select * from t1 order by n;
262
263 # and now test for the bug:
264 connection master;
265 drop table t1;
266 eval create table t1 (n int primary key auto_increment not null,
267 b int, unique(b)) engine=$engine_type;
268 insert into t1 values(null,100);
269 select * from t1 order by n;
270 sync_slave_with_master;
271 insert into t1 values(null,200),(null,300);
272 delete from t1 where b <> 100;
273 select * from t1 order by n;
274
275 connection master;
276 --disable_warnings ONCE
277 insert into t1 values(null,100),(null,350) on duplicate key update n=2;
278 select * from t1 order by n;
279 sync_slave_with_master;
280 select * from t1 order by n;
281
282 connection master;
283 drop table t1;
284 sync_slave_with_master;
285
286 #
287 # BUG#24432 "INSERT... ON DUPLICATE KEY UPDATE skips auto_increment values"
288 #
289
290 connection master;
291 # testcase with INSERT VALUES
292 eval CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY AUTO_INCREMENT, b INT,
293 UNIQUE(b)) ENGINE=$engine_type;
294 --disable_warnings ONCE
295 INSERT INTO t1(b) VALUES(1),(1),(2) ON DUPLICATE KEY UPDATE t1.b=10;
296 SELECT * FROM t1 ORDER BY a;
297 sync_slave_with_master;
298 SELECT * FROM t1 ORDER BY a;
299 connection master;
300 drop table t1;
301
302 # tescase with INSERT SELECT
303 eval CREATE TABLE t1 (
304 id bigint(20) unsigned NOT NULL auto_increment,
305 field_1 int(10) unsigned NOT NULL,
306 field_2 varchar(255) NOT NULL,
307 field_3 varchar(255) NOT NULL,
308 PRIMARY KEY (id),
309 UNIQUE KEY field_1 (field_1, field_2)
310 ) ENGINE=$engine_type;
311 eval CREATE TABLE t2 (
312 field_a int(10) unsigned NOT NULL,
313 field_b varchar(255) NOT NULL,
314 field_c varchar(255) NOT NULL
315 ) ENGINE=$engine_type;
316 INSERT INTO t2 (field_a, field_b, field_c) VALUES (1, 'a', '1a');
317 INSERT INTO t2 (field_a, field_b, field_c) VALUES (2, 'b', '2b');
318 INSERT INTO t2 (field_a, field_b, field_c) VALUES (3, 'c', '3c');
319 INSERT INTO t2 (field_a, field_b, field_c) VALUES (4, 'd', '4d');
320 INSERT INTO t2 (field_a, field_b, field_c) VALUES (5, 'e', '5e');
321 # Updating table t1 based on values from table t2
322 --disable_warnings
323 INSERT INTO t1 (field_1, field_2, field_3)
324 SELECT t2.field_a, t2.field_b, t2.field_c
325 FROM t2
326 ON DUPLICATE KEY UPDATE
327 t1.field_3 = t2.field_c;
328 --enable_warnings
329 # Inserting new record into t2
330 INSERT INTO t2 (field_a, field_b, field_c) VALUES (6, 'f', '6f');
331 # Updating t1 again
332 --disable_warnings
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 --enable_warnings
339 SELECT * FROM t1 ORDER BY id;
340 sync_slave_with_master;
341 SELECT * FROM t1 ORDER BY id;
342 connection master;
343 drop table t1, t2;
344
345 #
346 # BUG#20339: stored procedure using LAST_INSERT_ID() does not
347 # replicate statement-based.
348 #
349 # There is another version of the test for bug#20339 above that is
350 # actually originates in 5.1, and this is the version that is merged
351 # from 5.0.
352 #
353 connection master;
354
355 --disable_warnings
356 DROP PROCEDURE IF EXISTS p1;
357 DROP TABLE IF EXISTS t1, t2;
358 --enable_warnings
359
360 # Reset result of LAST_INSERT_ID().
361 SELECT LAST_INSERT_ID(0);
362
363 eval CREATE TABLE t1 (
364 id INT NOT NULL DEFAULT 0,
365 last_id INT,
366 PRIMARY KEY (id)
367 ) ENGINE=$engine_type;
368
369 eval CREATE TABLE t2 (
370 id INT NOT NULL AUTO_INCREMENT,
371 last_id INT,
372 PRIMARY KEY (id)
373 ) ENGINE=$engine_type;
374
375 delimiter |;
376 CREATE PROCEDURE p1()
377 BEGIN
378 INSERT INTO t2 (last_id) VALUES (LAST_INSERT_ID());
379 INSERT INTO t1 (last_id) VALUES (LAST_INSERT_ID());
380 END|
381 delimiter ;|
382
383 CALL p1();
384 SELECT * FROM t1 ORDER BY id;
385 SELECT * FROM t2 ORDER BY id;
386
387 sync_slave_with_master;
388 SELECT * FROM t1 ORDER BY id;
389 SELECT * FROM t2 ORDER BY id;
390
391 connection master;
392
393 DROP PROCEDURE p1;
394 DROP TABLE t1, t2;
395
396
397 #
398 # BUG#21726: Incorrect result with multiple invocations of
399 # LAST_INSERT_ID
400 #
401 --disable_warnings
402 DROP PROCEDURE IF EXISTS p1;
403 DROP FUNCTION IF EXISTS f1;
404 DROP FUNCTION IF EXISTS f2;
405 DROP FUNCTION IF EXISTS f3;
406 DROP TABLE IF EXISTS t1, t2;
407 --enable_warnings
408
409 eval CREATE TABLE t1 (
410 i INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
411 j INT DEFAULT 0
412 ) ENGINE=$engine_type;
413 eval CREATE TABLE t2 (i INT) ENGINE=$engine_type;
414
415 delimiter |;
416 CREATE PROCEDURE p1()
417 BEGIN
418 INSERT INTO t1 (i) VALUES (NULL);
419 INSERT INTO t2 (i) VALUES (LAST_INSERT_ID());
420 INSERT INTO t1 (i) VALUES (NULL), (NULL);
421 INSERT INTO t2 (i) VALUES (LAST_INSERT_ID());
422 END |
423
424 CREATE FUNCTION f1() RETURNS INT MODIFIES SQL DATA
425 BEGIN
426 INSERT INTO t1 (i) VALUES (NULL);
427 INSERT INTO t2 (i) VALUES (LAST_INSERT_ID());
428 INSERT INTO t1 (i) VALUES (NULL), (NULL);
429 INSERT INTO t2 (i) VALUES (LAST_INSERT_ID());
430 RETURN 0;
431 END |
432
433 CREATE FUNCTION f2() RETURNS INT NOT DETERMINISTIC
434 RETURN LAST_INSERT_ID() |
435
436 CREATE FUNCTION f3() RETURNS INT MODIFIES SQL DATA
437 BEGIN
438 INSERT INTO t2 (i) VALUES (LAST_INSERT_ID());
439 RETURN 0;
440 END |
441 delimiter ;|
442
443 INSERT INTO t1 VALUES (NULL, -1);
444 CALL p1();
445 --disable_ps2_protocol
446 --disable_warnings ONCE
447 SELECT f1();
448 --enable_ps2_protocol
449 INSERT INTO t1 VALUES (NULL, f2()), (NULL, LAST_INSERT_ID()),
450 (NULL, LAST_INSERT_ID()), (NULL, f2()), (NULL, f2());
451 INSERT INTO t1 VALUES (NULL, f2());
452 # Test replication of substitution "IS NULL" -> "= LAST_INSERT_ID".
453 INSERT INTO t1 VALUES (NULL, 0), (NULL, LAST_INSERT_ID());
454 UPDATE t1 SET j= -1 WHERE i IS NULL;
455
456 # Test statement-based replication of function calls.
457 INSERT INTO t1 (i) VALUES (NULL);
458
459 # Here, we rely on having set @@concurrent_insert= 0 (see comment at
460 # the top of this file).
461 connection master1;
462 INSERT INTO t1 (i) VALUES (NULL);
463
464 connection master;
465 --disable_ps2_protocol
466 SELECT f3();
467 --enable_ps2_protocol
468
469 SELECT * FROM t1 ORDER BY i;
470 SELECT * FROM t2 ORDER BY i;
471
472 sync_slave_with_master;
473 SELECT * FROM t1;
474 SELECT * FROM t2;
475
476 connection master;
477 DROP PROCEDURE p1;
478 DROP FUNCTION f1;
479 DROP FUNCTION f2;
480 DROP FUNCTION f3;
481 DROP TABLE t1, t2;
482
483
484 sync_slave_with_master;
485
486 --echo #
487 --echo # End of 5.0 tests
488 --echo #
489
490 # Tests in this file are tightly bound together. Recreate t2.
491 connection master;
492 eval create table t2 (
493 id int not null auto_increment,
494 last_id int,
495 primary key (id)
496 ) engine=$engine_type;
497
498
499 # Test for BUG#20341 "stored function inserting into one
500 # auto_increment puts bad data in slave"
501
502 connection master;
503 truncate table t2;
504 # no auto_increment
505 eval create table t1 (id tinyint primary key) engine=$engine_type;
506
507 delimiter |;
508 create function insid() returns int
509 begin
510 insert into t2 (last_id) values (0);
511 return 0;
512 end|
513 delimiter ;|
514 set sql_log_bin=0;
515 insert into t2 (id) values(1),(2),(3);
516 delete from t2;
517 set sql_log_bin=1;
518 #inside SELECT, then inside INSERT
519 --disable_ps2_protocol
520 --disable_warnings ONCE
521 select insid();
522 --enable_ps2_protocol
523 set sql_log_bin=0;
524 insert into t2 (id) values(5),(6),(7);
525 delete from t2 where id>=5;
526 set sql_log_bin=1;
527 --disable_warnings ONCE
528 insert into t1 select insid();
529 select * from t1 order by id;
530 select * from t2 order by id;
531
532 sync_slave_with_master;
533 select * from t1 order by id;
534 select * from t2 order by id;
535
536 connection master;
537 drop table t1;
538 drop function insid;
539
540 truncate table t2;
541 eval create table t1 (n int primary key auto_increment not null,
542 b int, unique(b)) engine=$engine_type;
543 delimiter |;
544 create procedure foo()
545 begin
546 insert into t1 values(null,10);
547 insert ignore into t1 values(null,10);
548 insert ignore into t1 values(null,10);
549 insert into t2 values(null,3);
550 end|
551 delimiter ;|
552 --disable_warnings ONCE
553 call foo();
554 select * from t1 order by n;
555 select * from t2 order by id;
556
557 sync_slave_with_master;
558 select * from t1 order by n;
559 select * from t2 order by id;
560
561 connection master;
562 drop table t1, t2;
563 drop procedure foo;
564 SET @@global.concurrent_insert= @old_concurrent_insert;
565 set @@session.sql_auto_is_null=default;
566
567 --source include/rpl_end.inc