Debian Codegraph @

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

raw
1 # The two bugs below (BUG#25507 and BUG#26116) existed only in
2 # statement-based binlogging; we test that now they are fixed;
3 # we also test that mixed and row-based binlogging work too,
4 # for completeness.
5
6 connection master;
7 --disable_warnings
8 CREATE SCHEMA IF NOT EXISTS mysqlslap;
9 USE mysqlslap;
10 --enable_warnings
11
12 #
13 # BUG#25507 "multi-row insert delayed + auto increment causes
14 # duplicate key entries on slave";
15 # happened only in statement-based binlogging.
16 #
17
18 CREATE TABLE t1 (id INT primary key auto_increment, name VARCHAR(64)) ENGINE=MyISAM;
19 let $query = "INSERT DELAYED INTO t1 VALUES (null, 'Dr. No'), (null, 'From Russia With Love'), (null, 'Goldfinger'), (null, 'Thunderball'), (null, 'You Only Live Twice')";
20 --exec $MYSQL_SLAP --silent --concurrency=5 --iterations=200 --query=$query --delimiter=";"
21
22 FLUSH TABLE t1; # another way to be sure INSERT DELAYED has inserted
23 SELECT COUNT(*) FROM t1;
24 # when bug existed slave failed below ("duplicate key" error at random INSERT)
25 sync_slave_with_master;
26 use mysqlslap;
27 SELECT COUNT(*) FROM t1;
28
29 #
30 # BUG#26116 "If multi-row INSERT DELAYED has errors,
31 # statement-based binlogging breaks";
32 # happened only in statement-based binlogging.
33 #
34
35 connection master;
36 truncate table t1;
37 # first scenario: duplicate on first row
38 insert delayed into t1 values(10, "my name");
39 flush table t1;
40 if (`SELECT @@global.binlog_format = 'STATEMENT'`)
41 {
42 # statement below will be converted to non-delayed INSERT and so
43 # will stop at first error, guaranteeing replication.
44 --error ER_DUP_ENTRY
45 insert delayed into t1 values(10, "is Bond"), (20, "James Bond");
46 }
47 if (`SELECT @@global.binlog_format != 'STATEMENT'`)
48 {
49 insert delayed into t1 values(10, "is Bond"), (20, "James Bond");
50 }
51 flush table t1;
52 select * from t1;
53 sync_slave_with_master;
54 # when bug existed in statement-based binlogging, t1 on slave had
55 # different content from on master
56 select * from t1;
57
58 # second scenario: duplicate on second row
59 connection master;
60 delete from t1 where id!=10;
61 if (`SELECT @@global.binlog_format = 'STATEMENT'`)
62 {
63 # statement below will be converted to non-delayed INSERT and so
64 # will be binlogged with its ER_DUP_ENTRY error code, guaranteeing
65 # replication (slave will hit the same error code and so be fine).
66 --error ER_DUP_ENTRY
67 insert delayed into t1 values(20, "is Bond"), (10, "James Bond");
68 }
69 if (`SELECT @@global.binlog_format != 'STATEMENT'`)
70 {
71 insert delayed into t1 values(20, "is Bond"), (10, "James Bond");
72 }
73 flush table t1; # to wait for INSERT DELAYED to be done
74 select * from t1;
75 sync_slave_with_master;
76 # when bug existed in statement-based binlogging, query was binlogged
77 # with error_code=0 so slave stopped
78 select * from t1;
79
80 # clean up
81 connection master;
82 USE test;
83 DROP SCHEMA mysqlslap;
84 sync_slave_with_master;
85 use test;
86 connection master;
87
88 #
89 # Bug #29571: INSERT DELAYED IGNORE written to binary log on the master but
90 # on the slave
91 #
92 if (`SELECT @@global.binlog_format = 'STATEMENT'`)
93 {
94 #flush the logs before the test
95 connection slave;
96 FLUSH LOGS;
97 source include/wait_for_binlog_checkpoint.inc;
98 connection master;
99 FLUSH LOGS;
100 source include/wait_for_binlog_checkpoint.inc;
101 }
102
103 CREATE TABLE t1(a int, UNIQUE(a));
104 --let $_start= query_get_value(SHOW MASTER STATUS, Position, 1)
105
106 INSERT DELAYED IGNORE INTO t1 VALUES(1);
107 --disable_warnings
108 INSERT DELAYED IGNORE INTO t1 VALUES(1);
109 --enable_warnings
110 flush table t1; # to wait for INSERT DELAYED to be done
111 if (`SELECT @@global.binlog_format = 'STATEMENT'`)
112 {
113 #must show two INSERT DELAYED
114 --let $binlog_file= query_get_value(SHOW MASTER STATUS, File, 1)
115
116 # The first INSERT DELAYED
117 --let $stmt= query_get_value(SHOW BINLOG EVENTS IN '$binlog_file' FROM $_start, Info, 2)
118 --echo $stmt
119
120 # The second INSERT DELAYED statement is the 3 item if two INSERT DELAYED are
121 # handled together
122 --let $stmt= query_get_value(SHOW BINLOG EVENTS IN '$binlog_file' FROM $_start, Info, 3)
123
124 # The second INSERT DELAYED statement is the 5 item if two INSERT DELAYED are
125 # handled separately
126 if ($stmt == COMMIT)
127 {
128 --let $stmt= query_get_value(SHOW BINLOG EVENTS IN '$binlog_file' FROM $_start, Info, 5)
129 }
130 --echo $stmt
131 }
132 select * from t1;
133
134 sync_slave_with_master;
135 echo On slave;
136 if (`SELECT @@global.binlog_format = 'STATEMENT'`)
137 {
138 #must show two INSERT DELAYED
139 --let $binlog_file= query_get_value(SHOW MASTER STATUS, File, 1)
140 # The show_binlog_events.inc already skips format_description, gtid list, and
141 # one binlog checkpoint events. Skip three more, second binlog checkpoint,
142 # Gtid, and CREATE TABLE, so we start at the first DML event
143 --let $binlog_limit= 3,6
144 --source include/show_binlog_events.inc
145 }
146 select * from t1;
147
148
149 # clean up
150 connection master;
151 drop table t1;
152 sync_slave_with_master;
153 if (`SELECT @@global.binlog_format = 'STATEMENT'`)
154 {
155 #flush the logs after the test
156 FLUSH LOGS;
157 connection master;
158 FLUSH LOGS;
159 }
160 connection master;
161
162
163 --echo End of 5.0 tests