1
# Testcase for BUG#6287 "Slave skips auto_increment values in Replication with InnoDB"
2
# The bug was that if on master, INSERT IGNORE ignored some
3
# rows, and the table was InnoDB with auto_inc column, then on slave
4
# some rows received an auto_inc bigger than on master.
5
# Slave needs to be started with --innodb to store table in InnoDB.
6
# Same test for MyISAM (which had no bug).
9
a int unsigned not null auto_increment primary key,
12
) ENGINE=$engine_type;
14
eval CREATE TABLE t2 (
15
a int unsigned, # to force INSERT SELECT to have a certain order
17
) ENGINE=$engine_type;
20
INSERT INTO t1 VALUES (NULL, 1);
21
INSERT INTO t1 VALUES (NULL, 2);
22
INSERT INTO t1 VALUES (NULL, 3);
23
INSERT INTO t1 VALUES (NULL, 4);
25
# An alternation of values which will conflict in t1 and will not.
27
INSERT INTO t2 VALUES (1, 1);
28
INSERT INTO t2 VALUES (2, 2);
29
INSERT INTO t2 VALUES (3, 5);
30
INSERT INTO t2 VALUES (4, 3);
31
INSERT INTO t2 VALUES (5, 4);
32
INSERT INTO t2 VALUES (6, 6);
34
INSERT IGNORE INTO t1 SELECT NULL, t2.b FROM t2 ORDER BY t2.a;
38
SELECT * FROM t1 ORDER BY a;
40
sync_slave_with_master;
41
SELECT * FROM t1 ORDER BY a;
43
# Now do the same for MyISAM
47
eval CREATE TABLE t1 (
48
a int unsigned not null auto_increment primary key,
51
) ENGINE=$engine_type2;
53
INSERT INTO t1 VALUES (1, 1);
54
INSERT INTO t1 VALUES (2, 2);
55
INSERT INTO t1 VALUES (3, 3);
56
INSERT INTO t1 VALUES (4, 4);
58
INSERT IGNORE INTO t1 SELECT NULL, t2.b FROM t2 ORDER BY t2.a;
60
SELECT * FROM t1 ORDER BY a;
62
sync_slave_with_master;
63
SELECT * FROM t1 ORDER BY a;
67
sync_slave_with_master;