1# 2# Bug45677 3# This test verifies the following two properties: 4# P1) insert/update in an autoinc column causes statement to 5# be logged in row format if binlog_format=mixed. 6# P2) if binlog_format=mixed, and a trigger or function contains 7# two or more inserts/updates in a table that has an autoinc 8# column, then the slave should not go out of sync, even if 9# there are concurrent transactions. 10# 11# Property (P1) is tested by executing an insert and an update on 12# a table that has an autoinc column, and verifying that these 13# statements result in row events in the binlog. 14# Property (P2) is tested by setting up the test scenario and 15# verifying that the tables are identical on master and slave. 16# 17 18source include/have_binlog_format_mixed.inc; 19source include/have_innodb.inc; 20source include/master-slave.inc; 21 22--echo # Test case1: INVOKES A TRIGGER with after insert action 23let $trigger_action = after insert; 24source include/rpl_auto_increment_invoke_trigger.test; 25 26--echo # Test case2: INVOKES A TRIGGER with before insert action 27let $trigger_action = before insert; 28source include/rpl_auto_increment_invoke_trigger.test; 29 30--echo # Test case3: INVOKES A TRIGGER with after update action 31let $trigger_action = after update; 32source include/rpl_auto_increment_invoke_trigger.test; 33 34--echo # Test case4: INVOKES A TRIGGER with before update action 35let $trigger_action = before update; 36source include/rpl_auto_increment_invoke_trigger.test; 37 38--echo # Test case5: INVOKES A TRIGGER with after delete action 39let $trigger_action = after delete; 40source include/rpl_auto_increment_invoke_trigger.test; 41 42--echo # Test case6: INVOKES A TRIGGER with before delete action 43let $trigger_action = before delete; 44source include/rpl_auto_increment_invoke_trigger.test; 45 46--echo # Test case7: CALLS A FUNCTION which INVOKES A TRIGGER with after insert action 47let $insert_action = after insert; 48source include/rpl_autoinc_func_invokes_trigger.test; 49 50--echo # Test case8: CALLS A FUNCTION which INVOKES A TRIGGER with before insert action 51let $insert_action = before insert; 52source include/rpl_autoinc_func_invokes_trigger.test; 53 54--echo # Test case9: INSERT DATA INTO VIEW WHICH INVOKES TRIGGERS with after insert action 55let $insert_action = after insert; 56source include/rpl_auto_increment_insert_view.test; 57 58--echo # Test case10: INSERT DATA INTO VIEW WHICH INVOKES TRIGGERS with before insert action 59let $insert_action = before insert; 60source include/rpl_auto_increment_insert_view.test; 61 62--echo # Test case11: INVOKES A FUNCTION TO INSERT TWO OR MORE VALUES INTO A TABLE WITH AUTOINC COLUMN 63connection master; 64create table t1(a int) engine=innodb; 65create table t2(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb; 66delimiter //; 67CREATE FUNCTION f1_two_inserts() RETURNS INTEGER 68BEGIN 69 INSERT INTO t2(a) values(2); 70 INSERT INTO t2(a) values(2); 71 RETURN 1; 72END// 73delimiter ;// 74begin; 75insert into t1(a) values(f1_two_inserts()); 76 77connection master1; 78#The default autocommit is set to 1, so the statement is auto committed 79insert into t2(a) values(4),(5); 80 81connection master; 82commit; 83insert into t1(a) values(f1_two_inserts()); 84commit; 85 86connection master; 87--echo #Test result for INVOKES A FUNCTION TO INSERT TWO OR MORE VALUES on master 88select * from t2 ORDER BY i1; 89 90sync_slave_with_master; 91connection slave; 92--echo #Test result for INVOKES A FUNCTION TO INSERT TWO OR MORE VALUES on slave 93select * from t2 ORDER BY i1; 94 95connection master; 96drop table t1; 97drop table t2; 98drop function f1_two_inserts; 99sync_slave_with_master; 100 101--echo # Test case12: INVOKES A FUNCTION TO UPDATE TWO OR MORE VALUES OF A TABLE WITH AUTOINC COLUMN 102connection master; 103create table t1(a int) engine=innodb; 104create table t2(i1 int not null auto_increment, a int, b int, primary key(i1)) engine=innodb; 105delimiter //; 106CREATE FUNCTION f1_two_updates() RETURNS INTEGER 107BEGIN 108 update t2 set a = a + 5 where b = 1; 109 update t2 set a = a + 5 where b = 2; 110 update t2 set a = a + 5 where b = 3; 111 update t2 set a = a + 5 where b = 4; 112 RETURN 1; 113END// 114delimiter ;// 115 116connection master1; 117#The default autocommit is set to 1, so the statement is auto committed 118insert into t2(a,b) values(1,1); 119insert into t2(a,b) values(2,2); 120insert into t2(a,b) values(3,3); 121insert into t2(a,b) values(4,4); 122insert into t1(a) values(f1_two_updates()); 123 124connection master; 125begin; 126insert into t1(a) values(f1_two_updates()); 127commit; 128 129connection master; 130--echo #Test result for INVOKES A FUNCTION TO UPDATE TWO OR MORE VALUES on master 131select * from t2 ORDER BY i1; 132 133sync_slave_with_master; 134connection slave; 135--echo #Test result for INVOKES A FUNCTION TO UPDATE TWO OR MORE VALUES on slave 136select * from t2 ORDER BY i1; 137 138connection master; 139drop table t1; 140drop table t2; 141drop function f1_two_updates; 142sync_slave_with_master; 143 144--echo # Test case13: UPDATE MORE THAN ONE TABLES ON TOP-STATEMENT 145connection master; 146create table t1(i1 int not null auto_increment, a int, b int, primary key(i1)) engine=innodb; 147create table t2(i1 int not null auto_increment, a int, b int, primary key(i1)) engine=innodb; 148begin; 149let $binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1); 150insert into t1(a,b) values(1,1),(2,2); 151insert into t2(a,b) values(1,1),(2,2); 152update t1,t2 set t1.a=t1.a+5, t2.a=t2.a+5 where t1.b=t2.b; 153insert into t1(a,b) values(3,3); 154insert into t2(a,b) values(3,3); 155commit; 156--echo # To verify if it works fine when these statements are not be marked as unsafe 157source include/show_binlog_events.inc; 158 159sync_slave_with_master; 160--echo #Test if the results are consistent on master and slave 161--echo #for 'UPDATE MORE THAN ONE TABLES ON TOP-STATEMENT' 162let $diff_tables= master:t1, slave:t1; 163source include/diff_tables.inc; 164let $diff_tables= master:t2, slave:t2; 165source include/diff_tables.inc; 166 167connection master; 168drop table t1; 169drop table t2; 170sync_slave_with_master; 171 172--echo # Test case14: INSERT DATA INTO VIEW WHICH INVOLVED MORE THAN ONE TABLES 173connection master; 174CREATE TABLE t1(i1 int not null auto_increment, c1 INT, primary key(i1)) engine=innodb; 175CREATE TABLE t2(i1 int not null auto_increment, c2 INT, primary key(i1)) engine=innodb; 176let $binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1); 177begin; 178INSERT INTO t1(c1) VALUES (11), (12); 179INSERT INTO t2(c2) VALUES (13), (14); 180 181CREATE VIEW v15 AS SELECT c1, c2 FROM t1, t2; 182 183INSERT INTO v15(c1) VALUES (15),(16); 184INSERT INTO v15(c2) VALUES (17),(18); 185 186connection master1; 187INSERT INTO v15(c1) VALUES (19),(20); 188INSERT INTO v15(c2) VALUES (21),(22); 189 190connection master; 191INSERT INTO v15(c1) VALUES (23), (24); 192INSERT INTO v15(c2) VALUES (25), (26); 193commit; 194--echo # To verify if it works fine when these statements are not be marked as unsafe 195source include/show_binlog_events.inc; 196 197sync_slave_with_master; 198--echo #Test if the results are consistent on master and slave 199--echo #for 'INSERT DATA INTO VIEW WHICH INVOLVED MORE THAN ONE TABLES' 200let $diff_tables= master:t1, slave:t1; 201source include/diff_tables.inc; 202let $diff_tables= master:t2, slave:t2; 203source include/diff_tables.inc; 204 205connection master; 206drop table t1; 207drop table t2; 208drop view v15; 209sync_slave_with_master; 210 211--source include/rpl_end.inc 212