1############################################################################### 2# Bug#76727: SLAVE ASSERTION IN UNPACK_ROW WITH ROLLBACK TO 3# SAVEPOINT IN ERROR HANDLER 4# 5# Problem: 6# ======== 7# "SAVEPOINT", "ROLLBACK TO savepoint" wipe out table map on slave during 8# execution binary log events. For trigger the map is written to binary log once 9# for all trigger body and if trigger contains "SAVEPOINT" or 10# "ROLLBACK TO savepoint" statements any trigger's events after these 11# statements will not have table map. This results in an assert on slave. 12# 13# Test: 14# ===== 15# Test case 1: 16# Create a trigger with exception handler which rolls back to a savepoint. 17# Test proves that there will not be any assert during execution of rolling 18# back to savepoint. 19# 20# Test case 2: 21# Create a trigger which calls a procedure which in turn calls an exception 22# handler which rolls back to a savepoint. Prove that it doesn't cause any 23# assertion during execution. 24# 25# Test case 3: 26# Create a simple trigger which does SAVEPOINT and ROLLBACK TO SAVEPOINT 27# and doesn't follow with any other DML statement. Prove that it doesn't cause 28# any assertion during execution. 29# 30# Test case 4: 31# Create a trigger with SAVEPOINT and follows with a DML without ROLLBACK TO 32# savepoint. Ensure that data is replicated properly. 33# 34# Test case 5: 35# Create a trigger with SAVEPOINT and it does nothing. Do few DMLs following 36# the trigger ensure that the data is replicated properly 37# 38# Test case 6: 39# Create a stored function which does SAVEPOINT and ROLLBACK TO 40# SAVEPOINT. Do few inserts following the stored function call and ensure that 41# no assert is generated on slave and all the rows are replicated to slave. 42# 43# Test case 7: 44# Create a stored function which creates a SAVEPOINT alone and follows with 45# DMLs without ROLLBACK TO savepoint. Ensure that data is replicated properly. 46# 47# Test case 8: 48# Create a stored function which has SAVEPOINT inside it and does noting. It 49# should follow with other DMLs. Ensure that data is replicated properly. 50############################################################################### 51--source include/have_binlog_format_row.inc 52--source include/have_innodb.inc 53--source include/master-slave.inc 54 55--echo #Test case 1: 56CREATE TABLE t1 (f1 INTEGER PRIMARY KEY) ENGINE=INNODB; 57CREATE TABLE t2 (f1 INTEGER PRIMARY KEY) ENGINE=INNODB; 58CREATE TABLE t3 (f1 INTEGER PRIMARY KEY) ENGINE=INNODB; 59DELIMITER |; 60 61CREATE TRIGGER tr1 AFTER INSERT ON t1 FOR EACH ROW 62BEGIN 63 DECLARE EXIT HANDLER FOR SQLEXCEPTION 64 BEGIN 65 ROLLBACK TO event_logging_1; 66 INSERT t3 VALUES (1); 67 END; 68 SAVEPOINT event_logging_1; 69 INSERT INTO t2 VALUES (1); 70 RELEASE SAVEPOINT event_logging_1; 71END| 72DELIMITER ;| 73 74INSERT INTO t2 VALUES (1); 75INSERT INTO t1 VALUES (1); 76 77--source include/show_binlog_events.inc 78 79--sync_slave_with_master 80 81connection master; 82 83DROP TRIGGER tr1; 84DELETE FROM t1; 85DELETE FROM t2; 86DELETE FROM t3; 87 88--echo # Test case 2: 89 90DELIMITER |; 91 92CREATE PROCEDURE p1() 93BEGIN 94 DECLARE EXIT HANDLER FOR SQLEXCEPTION 95 BEGIN 96 ROLLBACK TO event_logging_2; 97 INSERT t3 VALUES (3); 98 END; 99 SAVEPOINT event_logging_2; 100 INSERT INTO t2 VALUES (1); 101 RELEASE SAVEPOINT event_logging_2; 102END| 103 104CREATE TRIGGER tr1 AFTER INSERT ON t1 FOR EACH ROW CALL p1()| 105 106DELIMITER ;| 107 108INSERT INTO t2 VALUES (1); 109INSERT INTO t1 VALUES (1); 110 111--source include/show_binlog_events.inc 112 113--sync_slave_with_master 114 115connection master; 116 117DROP TABLE t1; 118DROP TABLE t2; 119DROP TABLE t3; 120 121DROP PROCEDURE p1; 122 123--echo # Test case 3: 124--source include/rpl_reset.inc 125connection master; 126 127CREATE TABLE t (f1 int(10) unsigned NOT NULL, PRIMARY KEY (f1)) ENGINE=InnoDB; 128 129--delimiter | 130CREATE TRIGGER t_insert_trig AFTER INSERT ON t FOR EACH ROW 131BEGIN 132 SAVEPOINT savepoint_1; 133 ROLLBACK TO savepoint_1; 134END | 135--delimiter ; 136 137INSERT INTO t VALUES (2); 138INSERT INTO t VALUES (3); 139 140--source include/show_binlog_events.inc 141 142SELECT * FROM t; 143 144--source include/sync_slave_sql_with_master.inc 145 146SELECT * FROM t; 147 148connection master; 149DROP TABLE t; 150 151--echo # Test case 4: 152--source include/rpl_reset.inc 153connection master; 154CREATE TABLE t (f1 int(10) unsigned NOT NULL) ENGINE=InnoDB; 155CREATE TABLE t1 (f1 int(10) unsigned NOT NULL) ENGINE=InnoDB; 156 157--delimiter | 158CREATE TRIGGER t_insert_trig BEFORE INSERT ON t FOR EACH ROW 159BEGIN 160 SAVEPOINT savepoint_1; 161 INSERT INTO t1 VALUES (5); 162END | 163--delimiter ; 164 165INSERT INTO t VALUES (2), (3); 166INSERT INTO t1 VALUES (30); 167--source include/show_binlog_events.inc 168 169SELECT * FROM t; 170SELECT * FROM t1; 171--source include/sync_slave_sql_with_master.inc 172 173SELECT * FROM t; 174SELECT * FROM t1; 175 176connection master; 177DROP TABLE t; 178DROP TABLE t1; 179 180--echo # Test case 5: 181--source include/rpl_reset.inc 182connection master; 183CREATE TABLE t (f1 int(10) unsigned NOT NULL) ENGINE=InnoDB; 184CREATE TABLE t1 (f1 int(10) unsigned NOT NULL) ENGINE=InnoDB; 185 186--delimiter | 187CREATE TRIGGER t_insert_trig BEFORE INSERT ON t 188FOR EACH ROW 189BEGIN 190 191SAVEPOINT savepoint_1; 192END | 193 194--delimiter ; 195 196INSERT INTO t VALUES (2), (3); 197INSERT INTO t1 VALUES (30); 198--source include/show_binlog_events.inc 199 200SELECT * FROM t; 201SELECT * FROM t1; 202--source include/sync_slave_sql_with_master.inc 203 204SELECT * FROM t; 205SELECT * FROM t1; 206 207connection master; 208DROP TABLE t; 209DROP TABLE t1; 210 211--echo # Test case 6: 212--source include/rpl_reset.inc 213connection master; 214CREATE TABLE t1 (f1 INTEGER ) ENGINE=INNODB; 215CREATE TABLE t2 (f1 INTEGER ) ENGINE=INNODB; 216 217--delimiter | 218 219CREATE FUNCTION f1() RETURNS INT 220BEGIN 221 SAVEPOINT event_logging_2; 222 INSERT INTO t1 VALUES (1); 223 ROLLBACK TO event_logging_2; 224 RETURN 0; 225END| 226 227--delimiter ; 228 229BEGIN; 230INSERT INTO t2 VALUES (1), (f1()), (2), (4); 231COMMIT; 232INSERT INTO t2 VALUES (10); 233--source include/show_binlog_events.inc 234 235connection master; 236SELECT * FROM t2; 237SELECT * FROM t1; 238--source include/sync_slave_sql_with_master.inc 239SELECT * FROM t2; 240SELECT * FROM t1; 241 242connection master; 243DROP TABLE t1; 244DROP TABLE t2; 245DROP FUNCTION f1; 246 247--echo # Test case 7: 248--source include/rpl_reset.inc 249connection master; 250CREATE TABLE t1 (f1 INTEGER ) ENGINE=INNODB; 251CREATE TABLE t2 (f1 INTEGER ) ENGINE=INNODB; 252 253--delimiter | 254 255CREATE FUNCTION f1() RETURNS INT 256BEGIN 257 SAVEPOINT event_logging_2; 258 INSERT INTO t1 VALUES (1); 259 RETURN 0; 260END| 261 262--delimiter ; 263 264BEGIN; 265INSERT INTO t2 VALUES (1), (f1()), (2), (4); 266COMMIT; 267INSERT INTO t2 VALUES (10); 268--source include/show_binlog_events.inc 269 270connection master; 271SELECT * FROM t2; 272SELECT * FROM t1; 273--source include/sync_slave_sql_with_master.inc 274SELECT * FROM t2; 275SELECT * FROM t1; 276 277connection master; 278DROP TABLE t1; 279DROP TABLE t2; 280DROP FUNCTION f1; 281 282--echo # Test case 8: 283--source include/rpl_reset.inc 284connection master; 285CREATE TABLE t1 (f1 INTEGER ) ENGINE=INNODB; 286 287--delimiter | 288 289CREATE FUNCTION f1() RETURNS INT 290BEGIN 291 SAVEPOINT event_logging_2; 292 RETURN 0; 293END| 294 295--delimiter ; 296 297BEGIN; 298INSERT INTO t1 VALUES (1), (f1()), (2), (4); 299COMMIT; 300INSERT INTO t1 VALUES (10); 301--source include/show_binlog_events.inc 302 303connection master; 304SELECT * FROM t1; 305--source include/sync_slave_sql_with_master.inc 306SELECT * FROM t1; 307 308connection master; 309DROP TABLE t1; 310DROP FUNCTION f1; 311 312--source include/rpl_end.inc 313