| 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 |