1
###########################################################
2
# 2006-02-01: By JBM: Added 1022, ORDER BY
3
###########################################################
4
# See if queries that use both auto_increment and LAST_INSERT_ID()
6
############################################################
8
# Auto increment should work for a table with an auto_increment
9
# column and index but without primary key.
10
##############################################################
18
drop table if exists t1, t2, t3;
22
--echo # See if queries that use both auto_increment and LAST_INSERT_ID()
23
--echo # are replicated well
25
--echo # We also check how the foreign_key_check variable is replicated
28
-- source include/master-slave.inc
29
#should work for both SBR and RBR
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.
36
SET @old_concurrent_insert= @@global.concurrent_insert;
37
SET @@global.concurrent_insert= 0;
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());
48
select * from t1 ORDER BY a;
49
select * from t2 ORDER BY b;
51
#check if multi-line inserts,
52
#which set last_insert_id to the first id inserted,
53
#are replicated the same way
57
eval create table t1(a int auto_increment, key(a)) engine=$engine_type;
58
eval create table t2(b int auto_increment, c int, key(b), foreign key(b) references t1(a)) engine=$engine_type;
60
SET FOREIGN_KEY_CHECKS=0;
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 values (null,last_insert_id());
65
SET FOREIGN_KEY_CHECKS=1;
74
--echo # check if INSERT SELECT in auto_increment is well replicated (bug #490)
79
eval create table t1(a int auto_increment, key(a)) engine=$engine_type;
80
eval create table t2(b int auto_increment, c int, key(b)) engine=$engine_type;
81
insert into t1 values (10);
82
insert into t1 values (null),(null),(null);
83
insert into t2 values (5,0);
84
insert into t2 (c) select * from t1 ORDER BY a;
85
select * from t2 ORDER BY b;
89
select * from t1 ORDER BY a;
90
select * from t2 ORDER BY b;
99
--echo # Bug#8412: Error codes reported in binary log for CHARACTER SET,
100
--echo # FOREIGN_KEY_CHECKS
104
SET TIMESTAMP=1000000000;
105
eval CREATE TABLE t1 ( a INT UNIQUE ) engine=$engine_type;
106
SET FOREIGN_KEY_CHECKS=0;
107
# Duplicate Key Errors
108
--error 1022, ER_DUP_ENTRY
109
INSERT INTO t1 VALUES (1),(1);
110
sync_slave_with_master;
113
sync_slave_with_master;
116
--echo # Bug#14553: NULL in WHERE resets LAST_INSERT_ID
120
eval create table t1(a int auto_increment, key(a)) engine=$engine_type;
121
eval create table t2(a int) engine=$engine_type;
122
insert into t1 (a) values (null);
123
insert into t2 (a) select a from t1 where a is null;
124
insert into t2 (a) select a from t1 where a is null;
126
sync_slave_with_master;
134
--echo # End of 4.1 tests
138
--echo # BUG#15728: LAST_INSERT_ID function inside a stored function returns 0
140
--echo # The solution is not to reset last_insert_id on enter to sub-statement.
145
drop function if exists bug15728;
146
drop function if exists bug15728_insert;
147
drop table if exists t1, t2;
150
eval create table t1 (
151
id int not null auto_increment,
154
) engine=$engine_type;
155
create function bug15728() returns int(11)
156
return last_insert_id();
158
insert into t1 (last_id) values (0);
159
insert into t1 (last_id) values (last_insert_id());
160
insert into t1 (last_id) values (bug15728());
162
# Check that nested call replicates too.
163
eval create table t2 (
164
id int not null auto_increment,
167
) engine=$engine_type;
169
create function bug15728_insert() returns int(11) modifies sql data
171
insert into t2 (last_id) values (bug15728());
174
create trigger t1_bi before insert on t1 for each row
177
select bug15728_insert() into res;
178
set NEW.last_id = res;
182
insert into t1 (last_id) values (0);
186
# Check that nested call doesn't affect outer context.
187
select last_insert_id();
188
select bug15728_insert();
189
select last_insert_id();
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();
194
# BUG#20339 - stored procedure using LAST_INSERT_ID() does not
195
# replicate statement-based
197
drop procedure if exists foo;
200
create procedure foo()
203
insert into t2 (last_id) values (bug15728());
204
insert into t1 (last_id) values (bug15728());
218
drop function bug15728;
219
drop function bug15728_insert;
223
# test of BUG#20188 REPLACE or ON DUPLICATE KEY UPDATE in
224
# auto_increment breaks binlog
226
eval create table t1 (n int primary key auto_increment not null,
227
b int, unique(b)) engine=$engine_type;
229
# First, test that we do not call restore_auto_increment() too early
232
insert into t1 values(null,100);
233
replace into t1 values(null,50),(null,100),(null,150);
234
select * from t1 order by n;
238
insert into t1 values(null,100);
239
select * from t1 order by n;
240
sync_slave_with_master;
241
# make slave's table autoinc counter bigger
242
insert into t1 values(null,200),(null,300);
243
delete from t1 where b <> 100;
244
# check that slave's table content is identical to master
245
select * from t1 order by n;
246
# only the auto_inc counter differs.
249
replace into t1 values(null,100),(null,350);
250
select * from t1 order by n;
251
sync_slave_with_master;
252
select * from t1 order by n;
254
# Same test as for REPLACE, but for ON DUPLICATE KEY UPDATE
256
# We first check that if we update a row using a value larger than the
257
# table's counter, the counter for next row is bigger than the
258
# after-value of the updated row.
260
insert into t1 values (NULL,400),(3,500),(NULL,600) on duplicate key UPDATE n=1000;
261
select * from t1 order by n;
262
sync_slave_with_master;
263
select * from t1 order by n;
265
# and now test for the bug:
268
eval create table t1 (n int primary key auto_increment not null,
269
b int, unique(b)) engine=$engine_type;
270
insert into t1 values(null,100);
271
select * from t1 order by n;
272
sync_slave_with_master;
273
insert into t1 values(null,200),(null,300);
274
delete from t1 where b <> 100;
275
select * from t1 order by n;
278
insert into t1 values(null,100),(null,350) on duplicate key update n=2;
279
select * from t1 order by n;
280
sync_slave_with_master;
281
select * from t1 order by n;
285
sync_slave_with_master;
288
# BUG#24432 "INSERT... ON DUPLICATE KEY UPDATE skips auto_increment values"
292
# testcase with INSERT VALUES
293
eval CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY AUTO_INCREMENT, b INT,
294
UNIQUE(b)) ENGINE=$engine_type;
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;
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,
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
INSERT INTO t1 (field_1, field_2, field_3)
323
SELECT t2.field_a, t2.field_b, t2.field_c
325
ON DUPLICATE KEY UPDATE
326
t1.field_3 = t2.field_c;
327
# Inserting new record into t2
328
INSERT INTO t2 (field_a, field_b, field_c) VALUES (6, 'f', '6f');
330
INSERT INTO t1 (field_1, field_2, field_3)
331
SELECT t2.field_a, t2.field_b, t2.field_c
333
ON DUPLICATE KEY UPDATE
334
t1.field_3 = t2.field_c;
335
SELECT * FROM t1 ORDER BY id;
336
sync_slave_with_master;
337
SELECT * FROM t1 ORDER BY id;
342
# BUG#20339: stored procedure using LAST_INSERT_ID() does not
343
# replicate statement-based.
345
# There is another version of the test for bug#20339 above that is
346
# actually originates in 5.1, and this is the version that is merged
352
DROP PROCEDURE IF EXISTS p1;
353
DROP TABLE IF EXISTS t1, t2;
356
# Reset result of LAST_INSERT_ID().
357
SELECT LAST_INSERT_ID(0);
359
eval CREATE TABLE t1 (
360
id INT NOT NULL DEFAULT 0,
363
) ENGINE=$engine_type;
365
eval CREATE TABLE t2 (
366
id INT NOT NULL AUTO_INCREMENT,
369
) ENGINE=$engine_type;
372
CREATE PROCEDURE p1()
374
INSERT INTO t2 (last_id) VALUES (LAST_INSERT_ID());
375
INSERT INTO t1 (last_id) VALUES (LAST_INSERT_ID());
380
SELECT * FROM t1 ORDER BY id;
381
SELECT * FROM t2 ORDER BY id;
383
sync_slave_with_master;
384
SELECT * FROM t1 ORDER BY id;
385
SELECT * FROM t2 ORDER BY id;
394
# BUG#21726: Incorrect result with multiple invocations of
398
DROP PROCEDURE IF EXISTS p1;
399
DROP FUNCTION IF EXISTS f1;
400
DROP FUNCTION IF EXISTS f2;
401
DROP FUNCTION IF EXISTS f3;
402
DROP TABLE IF EXISTS t1, t2;
405
eval CREATE TABLE t1 (
406
i INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
408
) ENGINE=$engine_type;
409
eval CREATE TABLE t2 (i INT) ENGINE=$engine_type;
412
CREATE PROCEDURE p1()
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());
420
CREATE FUNCTION f1() RETURNS INT MODIFIES SQL DATA
422
INSERT INTO t1 (i) VALUES (NULL);
423
INSERT INTO t2 (i) VALUES (LAST_INSERT_ID());
424
INSERT INTO t1 (i) VALUES (NULL), (NULL);
425
INSERT INTO t2 (i) VALUES (LAST_INSERT_ID());
429
CREATE FUNCTION f2() RETURNS INT NOT DETERMINISTIC
430
RETURN LAST_INSERT_ID() |
432
CREATE FUNCTION f3() RETURNS INT MODIFIES SQL DATA
434
INSERT INTO t2 (i) VALUES (LAST_INSERT_ID());
439
INSERT INTO t1 VALUES (NULL, -1);
442
INSERT INTO t1 VALUES (NULL, f2()), (NULL, LAST_INSERT_ID()),
443
(NULL, LAST_INSERT_ID()), (NULL, f2()), (NULL, f2());
444
INSERT INTO t1 VALUES (NULL, f2());
445
# Test replication of substitution "IS NULL" -> "= LAST_INSERT_ID".
446
INSERT INTO t1 VALUES (NULL, 0), (NULL, LAST_INSERT_ID());
447
UPDATE t1 SET j= -1 WHERE i IS NULL;
449
# Test statement-based replication of function calls.
450
INSERT INTO t1 (i) VALUES (NULL);
452
# Here, we rely on having set @@concurrent_insert= 0 (see comment at
453
# the top of this file).
455
INSERT INTO t1 (i) VALUES (NULL);
460
SELECT * FROM t1 ORDER BY i;
461
SELECT * FROM t2 ORDER BY i;
463
sync_slave_with_master;
475
sync_slave_with_master;
478
--echo # End of 5.0 tests
481
# Tests in this file are tightly bound together. Recreate t2.
483
eval create table t2 (
484
id int not null auto_increment,
487
) engine=$engine_type;
490
# Test for BUG#20341 "stored function inserting into one
491
# auto_increment puts bad data in slave"
496
eval create table t1 (id tinyint primary key) engine=$engine_type;
499
create function insid() returns int
501
insert into t2 (last_id) values (0);
506
insert into t2 (id) values(1),(2),(3);
509
#inside SELECT, then inside INSERT
512
insert into t2 (id) values(5),(6),(7);
513
delete from t2 where id>=5;
515
insert into t1 select insid();
516
select * from t1 order by id;
517
select * from t2 order by id;
519
sync_slave_with_master;
520
select * from t1 order by id;
521
select * from t2 order by id;
528
eval create table t1 (n int primary key auto_increment not null,
529
b int, unique(b)) engine=$engine_type;
531
create procedure foo()
533
insert into t1 values(null,10);
534
insert ignore into t1 values(null,10);
535
insert ignore into t1 values(null,10);
536
insert into t2 values(null,3);
540
select * from t1 order by n;
541
select * from t2 order by id;
543
sync_slave_with_master;
544
select * from t1 order by n;
545
select * from t2 order by id;
550
SET @@global.concurrent_insert= @old_concurrent_insert;
551
sync_slave_with_master;