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
6connection master;
7--disable_warnings
8CREATE SCHEMA IF NOT EXISTS mysqlslap;
9USE 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
18CREATE TABLE t1 (id INT primary key auto_increment, name VARCHAR(64)) ENGINE=MyISAM;
19let $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
22FLUSH TABLE t1; # another way to be sure INSERT DELAYED has inserted
23SELECT COUNT(*) FROM t1;
24# when bug existed slave failed below ("duplicate key" error at random INSERT)
25sync_slave_with_master;
26use mysqlslap;
27SELECT 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
35connection master;
36truncate table t1;
37# first scenario: duplicate on first row
38insert delayed into t1 values(10, "my name");
39flush table t1;
40if  (`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}
47if  (`SELECT @@global.binlog_format != 'STATEMENT'`)
48{
49  insert delayed into t1 values(10, "is Bond"), (20, "James Bond");
50}
51flush table t1;
52select * from t1;
53sync_slave_with_master;
54# when bug existed in statement-based binlogging, t1 on slave had
55# different content from on master
56select * from t1;
57
58# second scenario: duplicate on second row
59connection master;
60delete from t1 where id!=10;
61if  (`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}
69if  (`SELECT @@global.binlog_format != 'STATEMENT'`)
70{
71  insert delayed into t1 values(20, "is Bond"), (10, "James Bond");
72}
73flush table t1; # to wait for INSERT DELAYED to be done
74select * from t1;
75sync_slave_with_master;
76# when bug existed in statement-based binlogging, query was binlogged
77# with error_code=0 so slave stopped
78select * from t1;
79
80# clean up
81connection master;
82USE test;
83DROP SCHEMA mysqlslap;
84sync_slave_with_master;
85use test;
86connection master;
87
88#
89# Bug #29571: INSERT DELAYED IGNORE written to binary log on the master but
90# on the slave
91#
92if  (`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
103CREATE TABLE t1(a int, UNIQUE(a));
104--let $_start= query_get_value(SHOW MASTER STATUS, Position, 1)
105
106INSERT DELAYED IGNORE INTO t1 VALUES(1);
107--disable_warnings
108INSERT DELAYED IGNORE INTO t1 VALUES(1);
109--enable_warnings
110flush table t1; # to wait for INSERT DELAYED to be done
111if  (`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}
132select * from t1;
133
134sync_slave_with_master;
135echo On slave;
136if  (`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}
146select * from t1;
147
148
149# clean up
150connection master;
151drop table t1;
152sync_slave_with_master;
153if  (`SELECT @@global.binlog_format = 'STATEMENT'`)
154{
155  #flush the logs after the test
156  FLUSH LOGS;
157  connection master;
158  FLUSH LOGS;
159}
160connection master;
161
162
163--echo End of 5.0 tests
164