Debian Codegraph @

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

raw
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).
7
8 --connection master
9 eval CREATE TABLE t1 (
10 a int unsigned not null auto_increment primary key,
11 b int unsigned,
12 unique (b)
13 ) ENGINE=$engine_type;
14
15 eval CREATE TABLE t2 (
16 a int unsigned, # to force INSERT SELECT to have a certain order
17 b int unsigned
18 ) ENGINE=$engine_type;
19
20
21 INSERT INTO t1 VALUES (NULL, 1);
22 INSERT INTO t1 VALUES (NULL, 2);
23 INSERT INTO t1 VALUES (NULL, 3);
24 INSERT INTO t1 VALUES (NULL, 4);
25
26 # An alternation of values which will conflict in t1 and will not.
27
28 INSERT INTO t2 VALUES (1, 1);
29 INSERT INTO t2 VALUES (2, 2);
30 INSERT INTO t2 VALUES (3, 5);
31 INSERT INTO t2 VALUES (4, 3);
32 INSERT INTO t2 VALUES (5, 4);
33 INSERT INTO t2 VALUES (6, 6);
34 --disable_warnings ONCE
35 INSERT IGNORE INTO t1 SELECT NULL, t2.b FROM t2 ORDER BY t2.a;
36 --let $assert_cond= COUNT(*) = 6 FROM t1
37 --let $assert_text= Count of elements in t1 should be 6.
38 --source include/assert.inc
39
40 # Compare master and slave
41 --sync_slave_with_master
42 --let $diff_tables= master:test.t1 , slave:test.t1
43 --source include/diff_tables.inc
44
45 # BUG#59338 Inconsistency in binlog for statements that don't change any rows STATEMENT SBR
46 # An insert ignore that does not update anything must be written to the binary log in SBR
47 # and MIXED modes. We check this property by counting occurrences in t1 before and after
48 # the insert and comparing the binlog positions. The count should be the same in both points
49 # and the statement should be in the binary log.
50 --connection master
51 --let $binlog_file= query_get_value("SHOW MASTER STATUS", File, 1)
52 --let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1)
53 --let $statement_file=INSERT IGNORE INTO t1 SELECT NULL, t2.b FROM t2 ORDER BY t2.a
54 --disable_warnings ONCE
55 --eval $statement_file
56
57 --let $assert_cond= COUNT(*) = 6 FROM t1
58 --let $assert_text= Count of elements in t1 should be 6.
59 --source include/assert.inc
60
61 if (`SELECT @@BINLOG_FORMAT != 'STATEMENT'`)
62 {
63 --let $binlog_position_cmp= =
64 --let $assert_cond= [SHOW MASTER STATUS, Position, 1] $binlog_position_cmp $binlog_start
65 --let $assert_text= In SBR or MIXED modes, the event in the binlog should be the same that was executed. In RBR mode, binlog position should stay unchanged.
66 }
67 if (`SELECT @@BINLOG_FORMAT = 'STATEMENT'`)
68 {
69 --let $assert_cond= \'[\'SHOW BINLOG EVENTS IN "$binlog_file" FROM $binlog_start LIMIT 2, 1\', Info, 1]\' LIKE \'%$statement_file\'
70 --let $assert_text= In SBR or MIXED modes, the event in the binlog should be the same that was executed. In RBR mode, binlog position should stay unchanged.
71 }
72
73 --source include/assert.inc
74
75 # An insert duplicate that does not update anything must be written to the binary
76 # log in SBR and MIXED modes. We check this property by summing a before and after
77 # the update and comparing the binlog positions. The sum should be the same at both
78 # points and the statement should be in the binary log.
79 --disable_warnings
80 DROP TABLE t1;
81 DROP TABLE t2;
82 --enable_warnings
83 eval CREATE TABLE t1 (
84 a INT UNSIGNED NOT NULL PRIMARY KEY
85 ) ENGINE=$engine_type;
86
87 eval CREATE TABLE t2 (
88 a INT UNSIGNED
89 ) ENGINE=$engine_type;
90
91 INSERT INTO t1 VALUES (1);
92 INSERT INTO t2 VALUES (1);
93
94 --let $binlog_file= query_get_value("SHOW MASTER STATUS", File, 1)
95 --let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1)
96 --let $statement_file=INSERT INTO t1 SELECT t2.a FROM t2 ORDER BY t2.a ON DUPLICATE KEY UPDATE t1.a= t1.a
97 --disable_warnings ONCE
98 --eval $statement_file
99
100 --let $assert_cond= SUM(a) = 1 FROM t1
101 --let $assert_text= Sum of elements in t1 should be 1.
102 --source include/assert.inc
103
104 if (`SELECT @@BINLOG_FORMAT != 'STATEMENT'`)
105 {
106 --let $binlog_position_cmp= =
107 --let $assert_cond= [SHOW MASTER STATUS, Position, 1] $binlog_position_cmp $binlog_start
108 --let $assert_text= In SBR or MIXED modes, the event in the binlog should be the same that was executed. In RBR mode, binlog position should stay unchanged.
109 }
110 if (`SELECT @@BINLOG_FORMAT = 'STATEMENT'`)
111 {
112 --let $assert_cond= \'[\'SHOW BINLOG EVENTS IN "$binlog_file" FROM $binlog_start LIMIT 1, 1\', Info, 1]\' LIKE \'%$statement_file\'
113 --let $assert_text= In SBR or MIXED modes, the event in the binlog should be the same that was executed. In RBR mode, binlog position should stay unchanged.
114 }
115 --source include/assert.inc
116
117
118 # Clean up
119 --connection master
120 drop table t1, t2;
121 --sync_slave_with_master