diff options
| -rw-r--r-- | mysql-test/include/sync_slave_sql_with_master.inc | 53 | ||||
| -rw-r--r-- | mysql-test/suite/rpl/r/rpl_row_rollback_to_savepoint.result | 502 | ||||
| -rw-r--r-- | mysql-test/suite/rpl/t/rpl_row_rollback_to_savepoint.test | 312 | ||||
| -rw-r--r-- | sql/log.cc | 25 |
4 files changed, 889 insertions, 3 deletions
diff --git a/mysql-test/include/sync_slave_sql_with_master.inc b/mysql-test/include/sync_slave_sql_with_master.inc new file mode 100644 index 00000000000..609c7f1b827 --- /dev/null +++ b/mysql-test/include/sync_slave_sql_with_master.inc @@ -0,0 +1,53 @@ +# ==== Purpose ==== +# +# Waits until the slave SQL thread has been synced, i.e., all events +# have been copied over to slave. This is like mtr's built-in command +# sync_slave_with_master, but more flexible (e.g., you can set a +# custom timeout and you can force it to use GTIDs instead of filename +# and offset). +# +# +# ==== Usage ==== +# +# [--let $sync_slave_connection= <connection_name>] +# [--let $use_gtids= 1] +# [--let $slave_timeout= NUMBER] +# [--let $rpl_debug= 1] +# --source include/sync_slave_io_with_master.inc +# +# Must be called on the master. Will change connection to the slave. +# +# Parameters: +# +# $use_gtids +# If set, uses GTIDs instead of filename and offset for positions. +# +# $sync_slave_connection +# By default, this script switches connection to 'slave'. If +# $sync_slave_connection is set, then '$sync_slave_connection' is +# used instead of 'slave'. +# +# $slave_timeout +# See include/wait_for_slave_param.inc. +# +# $rpl_debug +# See include/rpl_init.inc + + +--let $include_filename= sync_slave_sql_with_master.inc +--source include/begin_include_file.inc + +save_master_pos; + +--let $rpl_connection_name= slave +if ($sync_slave_connection) +{ + --let $rpl_connection_name= $sync_slave_connection +} +--source include/rpl_connection.inc + +sync_with_master; + +--let $include_filename= sync_slave_sql_with_master.inc +--let $skip_restore_connection= 1 +--source include/end_include_file.inc diff --git a/mysql-test/suite/rpl/r/rpl_row_rollback_to_savepoint.result b/mysql-test/suite/rpl/r/rpl_row_rollback_to_savepoint.result new file mode 100644 index 00000000000..bf7ced0dd89 --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_row_rollback_to_savepoint.result @@ -0,0 +1,502 @@ +include/master-slave.inc +[connection master] +#Test case 1: +CREATE TABLE t1 (f1 INTEGER PRIMARY KEY) ENGINE=INNODB; +CREATE TABLE t2 (f1 INTEGER PRIMARY KEY) ENGINE=INNODB; +CREATE TABLE t3 (f1 INTEGER PRIMARY KEY) ENGINE=INNODB; +CREATE TRIGGER tr1 AFTER INSERT ON t1 FOR EACH ROW +BEGIN +DECLARE EXIT HANDLER FOR SQLEXCEPTION +BEGIN +ROLLBACK TO event_logging_1; +INSERT t3 VALUES (1); +END; +SAVEPOINT event_logging_1; +INSERT INTO t2 VALUES (1); +RELEASE SAVEPOINT event_logging_1; +END| +INSERT INTO t2 VALUES (1); +INSERT INTO t1 VALUES (1); +include/show_binlog_events.inc +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; CREATE TABLE t1 (f1 INTEGER PRIMARY KEY) ENGINE=INNODB +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; CREATE TABLE t2 (f1 INTEGER PRIMARY KEY) ENGINE=INNODB +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; CREATE TABLE t3 (f1 INTEGER PRIMARY KEY) ENGINE=INNODB +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; CREATE DEFINER=`root`@`localhost` TRIGGER tr1 AFTER INSERT ON t1 FOR EACH ROW +BEGIN +DECLARE EXIT HANDLER FOR SQLEXCEPTION +BEGIN +ROLLBACK TO event_logging_1; +INSERT t3 VALUES (1); +END; +SAVEPOINT event_logging_1; +INSERT INTO t2 VALUES (1); +RELEASE SAVEPOINT event_logging_1; +END +master-bin.000001 # Gtid # # BEGIN GTID #-#-# +master-bin.000001 # Table_map # # table_id: # (test.t2) +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Gtid # # BEGIN GTID #-#-# +master-bin.000001 # Table_map # # table_id: # (test.t1) +master-bin.000001 # Table_map # # table_id: # (test.t2) +master-bin.000001 # Table_map # # table_id: # (test.t3) +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # SAVEPOINT `event_logging_1` +master-bin.000001 # Table_map # # table_id: # (test.t1) +master-bin.000001 # Table_map # # table_id: # (test.t2) +master-bin.000001 # Table_map # # table_id: # (test.t3) +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +master-bin.000001 # Xid # # COMMIT /* XID */ +[connection master] +DROP TRIGGER tr1; +DELETE FROM t1; +DELETE FROM t2; +DELETE FROM t3; +# Test case 2: +CREATE PROCEDURE p1() +BEGIN +DECLARE EXIT HANDLER FOR SQLEXCEPTION +BEGIN +ROLLBACK TO event_logging_2; +INSERT t3 VALUES (3); +END; +SAVEPOINT event_logging_2; +INSERT INTO t2 VALUES (1); +RELEASE SAVEPOINT event_logging_2; +END| +CREATE TRIGGER tr1 AFTER INSERT ON t1 FOR EACH ROW CALL p1()| +INSERT INTO t2 VALUES (1); +INSERT INTO t1 VALUES (1); +include/show_binlog_events.inc +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; CREATE TABLE t1 (f1 INTEGER PRIMARY KEY) ENGINE=INNODB +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; CREATE TABLE t2 (f1 INTEGER PRIMARY KEY) ENGINE=INNODB +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; CREATE TABLE t3 (f1 INTEGER PRIMARY KEY) ENGINE=INNODB +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; CREATE DEFINER=`root`@`localhost` TRIGGER tr1 AFTER INSERT ON t1 FOR EACH ROW +BEGIN +DECLARE EXIT HANDLER FOR SQLEXCEPTION +BEGIN +ROLLBACK TO event_logging_1; +INSERT t3 VALUES (1); +END; +SAVEPOINT event_logging_1; +INSERT INTO t2 VALUES (1); +RELEASE SAVEPOINT event_logging_1; +END +master-bin.000001 # Gtid # # BEGIN GTID #-#-# +master-bin.000001 # Table_map # # table_id: # (test.t2) +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Gtid # # BEGIN GTID #-#-# +master-bin.000001 # Table_map # # table_id: # (test.t1) +master-bin.000001 # Table_map # # table_id: # (test.t2) +master-bin.000001 # Table_map # # table_id: # (test.t3) +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # SAVEPOINT `event_logging_1` +master-bin.000001 # Table_map # # table_id: # (test.t1) +master-bin.000001 # Table_map # # table_id: # (test.t2) +master-bin.000001 # Table_map # # table_id: # (test.t3) +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; DROP TRIGGER tr1 +master-bin.000001 # Gtid # # BEGIN GTID #-#-# +master-bin.000001 # Table_map # # table_id: # (test.t1) +master-bin.000001 # Delete_rows_v1 # # table_id: # flags: STMT_END_F +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Gtid # # BEGIN GTID #-#-# +master-bin.000001 # Table_map # # table_id: # (test.t2) +master-bin.000001 # Delete_rows_v1 # # table_id: # flags: STMT_END_F +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Gtid # # BEGIN GTID #-#-# +master-bin.000001 # Table_map # # table_id: # (test.t3) +master-bin.000001 # Delete_rows_v1 # # table_id: # flags: STMT_END_F +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`() +BEGIN +DECLARE EXIT HANDLER FOR SQLEXCEPTION +BEGIN +ROLLBACK TO event_logging_2; +INSERT t3 VALUES (3); +END; +SAVEPOINT event_logging_2; +INSERT INTO t2 VALUES (1); +RELEASE SAVEPOINT event_logging_2; +END +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; CREATE DEFINER=`root`@`localhost` TRIGGER tr1 AFTER INSERT ON t1 FOR EACH ROW CALL p1() +master-bin.000001 # Gtid # # BEGIN GTID #-#-# +master-bin.000001 # Table_map # # table_id: # (test.t2) +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Gtid # # BEGIN GTID #-#-# +master-bin.000001 # Table_map # # table_id: # (test.t1) +master-bin.000001 # Table_map # # table_id: # (test.t2) +master-bin.000001 # Table_map # # table_id: # (test.t3) +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # SAVEPOINT `event_logging_2` +master-bin.000001 # Table_map # # table_id: # (test.t1) +master-bin.000001 # Table_map # # table_id: # (test.t2) +master-bin.000001 # Table_map # # table_id: # (test.t3) +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +master-bin.000001 # Xid # # COMMIT /* XID */ +[connection master] +DROP TABLE t1; +DROP TABLE t2; +DROP TABLE t3; +DROP PROCEDURE p1; +# Test case 3: +include/rpl_reset.inc +[connection master] +CREATE TABLE t (f1 int(10) unsigned NOT NULL, PRIMARY KEY (f1)) ENGINE=InnoDB; +CREATE TRIGGER t_insert_trig AFTER INSERT ON t FOR EACH ROW +BEGIN +SAVEPOINT savepoint_1; +ROLLBACK TO savepoint_1; +END | +INSERT INTO t VALUES (2); +INSERT INTO t VALUES (3); +include/show_binlog_events.inc +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; CREATE TABLE t (f1 int(10) unsigned NOT NULL, PRIMARY KEY (f1)) ENGINE=InnoDB +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; CREATE DEFINER=`root`@`localhost` TRIGGER t_insert_trig AFTER INSERT ON t FOR EACH ROW +BEGIN +SAVEPOINT savepoint_1; +ROLLBACK TO savepoint_1; +END +master-bin.000001 # Gtid # # BEGIN GTID #-#-# +master-bin.000001 # Table_map # # table_id: # (test.t) +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # SAVEPOINT `savepoint_1` +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Gtid # # BEGIN GTID #-#-# +master-bin.000001 # Table_map # # table_id: # (test.t) +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # SAVEPOINT `savepoint_1` +master-bin.000001 # Xid # # COMMIT /* XID */ +SELECT * FROM t; +f1 +2 +3 +include/sync_slave_sql_with_master.inc +SELECT * FROM t; +f1 +2 +3 +[connection master] +DROP TABLE t; +# Test case 4: +include/rpl_reset.inc +[connection master] +CREATE TABLE t (f1 int(10) unsigned NOT NULL) ENGINE=InnoDB; +CREATE TABLE t1 (f1 int(10) unsigned NOT NULL) ENGINE=InnoDB; +CREATE TRIGGER t_insert_trig BEFORE INSERT ON t FOR EACH ROW +BEGIN +SAVEPOINT savepoint_1; +INSERT INTO t1 VALUES (5); +END | +INSERT INTO t VALUES (2), (3); +INSERT INTO t1 VALUES (30); +include/show_binlog_events.inc +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; CREATE TABLE t (f1 int(10) unsigned NOT NULL) ENGINE=InnoDB +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; CREATE TABLE t1 (f1 int(10) unsigned NOT NULL) ENGINE=InnoDB +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; CREATE DEFINER=`root`@`localhost` TRIGGER t_insert_trig BEFORE INSERT ON t FOR EACH ROW +BEGIN +SAVEPOINT savepoint_1; +INSERT INTO t1 VALUES (5); +END +master-bin.000001 # Gtid # # BEGIN GTID #-#-# +master-bin.000001 # Table_map # # table_id: # (test.t) +master-bin.000001 # Table_map # # table_id: # (test.t1) +master-bin.000001 # Write_rows_v1 # # table_id: # +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # SAVEPOINT `savepoint_1` +master-bin.000001 # Table_map # # table_id: # (test.t) +master-bin.000001 # Table_map # # table_id: # (test.t1) +master-bin.000001 # Write_rows_v1 # # table_id: # +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Gtid # # BEGIN GTID #-#-# +master-bin.000001 # Table_map # # table_id: # (test.t1) +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +master-bin.000001 # Xid # # COMMIT /* XID */ +SELECT * FROM t; +f1 +2 +3 +SELECT * FROM t1; +f1 +5 +5 +30 +include/sync_slave_sql_with_master.inc +SELECT * FROM t; +f1 +2 +3 +SELECT * FROM t1; +f1 +5 +5 +30 +[connection master] +DROP TABLE t; +DROP TABLE t1; +# Test case 5: +include/rpl_reset.inc +[connection master] +CREATE TABLE t (f1 int(10) unsigned NOT NULL) ENGINE=InnoDB; +CREATE TABLE t1 (f1 int(10) unsigned NOT NULL) ENGINE=InnoDB; +CREATE TRIGGER t_insert_trig BEFORE INSERT ON t +FOR EACH ROW +BEGIN +SAVEPOINT savepoint_1; +END | +INSERT INTO t VALUES (2), (3); +INSERT INTO t1 VALUES (30); +include/show_binlog_events.inc +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; CREATE TABLE t (f1 int(10) unsigned NOT NULL) ENGINE=InnoDB +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; CREATE TABLE t1 (f1 int(10) unsigned NOT NULL) ENGINE=InnoDB +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; CREATE DEFINER=`root`@`localhost` TRIGGER t_insert_trig BEFORE INSERT ON t +FOR EACH ROW +BEGIN +SAVEPOINT savepoint_1; +END +master-bin.000001 # Gtid # # BEGIN GTID #-#-# +master-bin.000001 # Table_map # # table_id: # (test.t) +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # SAVEPOINT `savepoint_1` +master-bin.000001 # Table_map # # table_id: # (test.t) +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Gtid # # BEGIN GTID #-#-# +master-bin.000001 # Table_map # # table_id: # (test.t1) +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +master-bin.000001 # Xid # # COMMIT /* XID */ +SELECT * FROM t; +f1 +2 +3 +SELECT * FROM t1; +f1 +30 +include/sync_slave_sql_with_master.inc +SELECT * FROM t; +f1 +2 +3 +SELECT * FROM t1; +f1 +30 +[connection master] +DROP TABLE t; +DROP TABLE t1; +# Test case 6: +include/rpl_reset.inc +[connection master] +CREATE TABLE t1 (f1 INTEGER ) ENGINE=INNODB; +CREATE TABLE t2 (f1 INTEGER ) ENGINE=INNODB; +CREATE FUNCTION f1() RETURNS INT +BEGIN +SAVEPOINT event_logging_2; +INSERT INTO t1 VALUES (1); +ROLLBACK TO event_logging_2; +RETURN 0; +END| +BEGIN; +INSERT INTO t2 VALUES (1), (f1()), (2), (4); +COMMIT; +INSERT INTO t2 VALUES (10); +include/show_binlog_events.inc +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; CREATE TABLE t1 (f1 INTEGER ) ENGINE=INNODB +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; CREATE TABLE t2 (f1 INTEGER ) ENGINE=INNODB +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; CREATE DEFINER=`root`@`localhost` FUNCTION `f1`() RETURNS int(11) +BEGIN +SAVEPOINT event_logging_2; +INSERT INTO t1 VALUES (1); +ROLLBACK TO event_logging_2; +RETURN 0; +END +master-bin.000001 # Gtid # # BEGIN GTID #-#-# +master-bin.000001 # Table_map # # table_id: # (test.t2) +master-bin.000001 # Table_map # # table_id: # (test.t1) +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # SAVEPOINT `event_logging_2` +master-bin.000001 # Table_map # # table_id: # (test.t2) +master-bin.000001 # Table_map # # table_id: # (test.t1) +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Gtid # # BEGIN GTID #-#-# +master-bin.000001 # Table_map # # table_id: # (test.t2) +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +master-bin.000001 # Xid # # COMMIT /* XID */ +[connection master] +SELECT * FROM t2; +f1 +1 +0 +2 +4 +10 +SELECT * FROM t1; +f1 +include/sync_slave_sql_with_master.inc +SELECT * FROM t2; +f1 +1 +0 +2 +4 +10 +SELECT * FROM t1; +f1 +[connection master] +DROP TABLE t1; +DROP TABLE t2; +DROP FUNCTION f1; +# Test case 7: +include/rpl_reset.inc +[connection master] +CREATE TABLE t1 (f1 INTEGER ) ENGINE=INNODB; +CREATE TABLE t2 (f1 INTEGER ) ENGINE=INNODB; +CREATE FUNCTION f1() RETURNS INT +BEGIN +SAVEPOINT event_logging_2; +INSERT INTO t1 VALUES (1); +RETURN 0; +END| +BEGIN; +INSERT INTO t2 VALUES (1), (f1()), (2), (4); +COMMIT; +INSERT INTO t2 VALUES (10); +include/show_binlog_events.inc +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; CREATE TABLE t1 (f1 INTEGER ) ENGINE=INNODB +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; CREATE TABLE t2 (f1 INTEGER ) ENGINE=INNODB +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; CREATE DEFINER=`root`@`localhost` FUNCTION `f1`() RETURNS int(11) +BEGIN +SAVEPOINT event_logging_2; +INSERT INTO t1 VALUES (1); +RETURN 0; +END +master-bin.000001 # Gtid # # BEGIN GTID #-#-# +master-bin.000001 # Table_map # # table_id: # (test.t2) +master-bin.000001 # Table_map # # table_id: # (test.t1) +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # SAVEPOINT `event_logging_2` +master-bin.000001 # Table_map # # table_id: # (test.t2) +master-bin.000001 # Table_map # # table_id: # (test.t1) +master-bin.000001 # Write_rows_v1 # # table_id: # +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Gtid # # BEGIN GTID #-#-# +master-bin.000001 # Table_map # # table_id: # (test.t2) +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +master-bin.000001 # Xid # # COMMIT /* XID */ +[connection master] +SELECT * FROM t2; +f1 +1 +0 +2 +4 +10 +SELECT * FROM t1; +f1 +1 +include/sync_slave_sql_with_master.inc +SELECT * FROM t2; +f1 +1 +0 +2 +4 +10 +SELECT * FROM t1; +f1 +1 +[connection master] +DROP TABLE t1; +DROP TABLE t2; +DROP FUNCTION f1; +# Test case 8: +include/rpl_reset.inc +[connection master] +CREATE TABLE t1 (f1 INTEGER ) ENGINE=INNODB; +CREATE FUNCTION f1() RETURNS INT +BEGIN +SAVEPOINT event_logging_2; +RETURN 0; +END| +BEGIN; +INSERT INTO t1 VALUES (1), (f1()), (2), (4); +COMMIT; +INSERT INTO t1 VALUES (10); +include/show_binlog_events.inc +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; CREATE TABLE t1 (f1 INTEGER ) ENGINE=INNODB +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; CREATE DEFINER=`root`@`localhost` FUNCTION `f1`() RETURNS int(11) +BEGIN +SAVEPOINT event_logging_2; +RETURN 0; +END +master-bin.000001 # Gtid # # BEGIN GTID #-#-# +master-bin.000001 # Table_map # # table_id: # (test.t1) +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # SAVEPOINT `event_logging_2` +master-bin.000001 # Table_map # # table_id: # (test.t1) +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Gtid # # BEGIN GTID #-#-# +master-bin.000001 # Table_map # # table_id: # (test.t1) +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +master-bin.000001 # Xid # # COMMIT /* XID */ +[connection master] +SELECT * FROM t1; +f1 +1 +0 +2 +4 +10 +include/sync_slave_sql_with_master.inc +SELECT * FROM t1; +f1 +1 +0 +2 +4 +10 +[connection master] +DROP TABLE t1; +DROP FUNCTION f1; +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_row_rollback_to_savepoint.test b/mysql-test/suite/rpl/t/rpl_row_rollback_to_savepoint.test new file mode 100644 index 00000000000..7ad756ea545 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_row_rollback_to_savepoint.test @@ -0,0 +1,312 @@ +############################################################################### +# Bug#76727: SLAVE ASSERTION IN UNPACK_ROW WITH ROLLBACK TO +# SAVEPOINT IN ERROR HANDLER +# +# Problem: +# ======== +# "SAVEPOINT", "ROLLBACK TO savepoint" wipe out table map on slave during +# execution binary log events. For trigger the map is written to binary log once +# for all trigger body and if trigger contains "SAVEPOINT" or +# "ROLLBACK TO savepoint" statements any trigger's events after these +# statements will not have table map. This results in an assert on slave. +# +# Test: +# ===== +# Test case 1: +# Create a trigger with exception handler which rolls back to a savepoint. +# Test proves that there will not be any assert during execution of rolling +# back to savepoint. +# +# Test case 2: +# Create a trigger which calls a procedure which in turn calls an exception +# handler which rolls back to a savepoint. Prove that it doesn't cause any +# assertion during execution. +# +# Test case 3: +# Create a simple trigger which does SAVEPOINT and ROLLBACK TO SAVEPOINT +# and doesn't follow with any other DML statement. Prove that it doesn't cause +# any assertion during execution. +# +# Test case 4: +# Create a trigger with SAVEPOINT and follows with a DML without ROLLBACK TO +# savepoint. Ensure that data is replicated properly. +# +# Test case 5: +# Create a trigger with SAVEPOINT and it does nothing. Do few DMLs following +# the trigger ensure that the data is replicated properly +# +# Test case 6: +# Create a stored function which does SAVEPOINT and ROLLBACK TO +# SAVEPOINT. Do few inserts following the stored function call and ensure that +# no assert is generated on slave and all the rows are replicated to slave. +# +# Test case 7: +# Create a stored function which creates a SAVEPOINT alone and follows with +# DMLs without ROLLBACK TO savepoint. Ensure that data is replicated properly. +# +# Test case 8: +# Create a stored function which has SAVEPOINT inside it and does noting. It +# should follow with other DMLs. Ensure that data is replicated properly. +############################################################################### +--source include/have_binlog_format_row.inc +--source include/have_innodb.inc +--source include/master-slave.inc + +--echo #Test case 1: +CREATE TABLE t1 (f1 INTEGER PRIMARY KEY) ENGINE=INNODB; +CREATE TABLE t2 (f1 INTEGER PRIMARY KEY) ENGINE=INNODB; +CREATE TABLE t3 (f1 INTEGER PRIMARY KEY) ENGINE=INNODB; +DELIMITER |; + +CREATE TRIGGER tr1 AFTER INSERT ON t1 FOR EACH ROW +BEGIN + DECLARE EXIT HANDLER FOR SQLEXCEPTION + BEGIN + ROLLBACK TO event_logging_1; + INSERT t3 VALUES (1); + END; + SAVEPOINT event_logging_1; + INSERT INTO t2 VALUES (1); + RELEASE SAVEPOINT event_logging_1; +END| +DELIMITER ;| + +INSERT INTO t2 VALUES (1); +INSERT INTO t1 VALUES (1); + +--source include/show_binlog_events.inc + +--sync_slave_with_master + +--source include/rpl_connection_master.inc + +DROP TRIGGER tr1; +DELETE FROM t1; +DELETE FROM t2; +DELETE FROM t3; + +--echo # Test case 2: + +DELIMITER |; + +CREATE PROCEDURE p1() +BEGIN + DECLARE EXIT HANDLER FOR SQLEXCEPTION + BEGIN + ROLLBACK TO event_logging_2; + INSERT t3 VALUES (3); + END; + SAVEPOINT event_logging_2; + INSERT INTO t2 VALUES (1); + RELEASE SAVEPOINT event_logging_2; +END| + +CREATE TRIGGER tr1 AFTER INSERT ON t1 FOR EACH ROW CALL p1()| + +DELIMITER ;| + +INSERT INTO t2 VALUES (1); +INSERT INTO t1 VALUES (1); + +--source include/show_binlog_events.inc + +--sync_slave_with_master + +--source include/rpl_connection_master.inc + +DROP TABLE t1; +DROP TABLE t2; +DROP TABLE t3; + +DROP PROCEDURE p1; + +--echo # Test case 3: +--source include/rpl_reset.inc +--source include/rpl_connection_master.inc + +CREATE TABLE t (f1 int(10) unsigned NOT NULL, PRIMARY KEY (f1)) ENGINE=InnoDB; + +--delimiter | +CREATE TRIGGER t_insert_trig AFTER INSERT ON t FOR EACH ROW +BEGIN + SAVEPOINT savepoint_1; + ROLLBACK TO savepoint_1; +END | +--delimiter ; + +INSERT INTO t VALUES (2); +INSERT INTO t VALUES (3); + +--source include/show_binlog_events.inc + +SELECT * FROM t; + +--source include/sync_slave_sql_with_master.inc + +SELECT * FROM t; + +--source include/rpl_connection_master.inc +DROP TABLE t; + +--echo # Test case 4: +--source include/rpl_reset.inc +--source include/rpl_connection_master.inc +CREATE TABLE t (f1 int(10) unsigned NOT NULL) ENGINE=InnoDB; +CREATE TABLE t1 (f1 int(10) unsigned NOT NULL) ENGINE=InnoDB; + +--delimiter | +CREATE TRIGGER t_insert_trig BEFORE INSERT ON t FOR EACH ROW +BEGIN + SAVEPOINT savepoint_1; + INSERT INTO t1 VALUES (5); +END | +--delimiter ; + +INSERT INTO t VALUES (2), (3); +INSERT INTO t1 VALUES (30); +--source include/show_binlog_events.inc + +SELECT * FROM t; +SELECT * FROM t1; +--source include/sync_slave_sql_with_master.inc + +SELECT * FROM t; +SELECT * FROM t1; + +--source include/rpl_connection_master.inc +DROP TABLE t; +DROP TABLE t1; + +--echo # Test case 5: +--source include/rpl_reset.inc +--source include/rpl_connection_master.inc +CREATE TABLE t (f1 int(10) unsigned NOT NULL) ENGINE=InnoDB; +CREATE TABLE t1 (f1 int(10) unsigned NOT NULL) ENGINE=InnoDB; + +--delimiter | +CREATE TRIGGER t_insert_trig BEFORE INSERT ON t +FOR EACH ROW +BEGIN + +SAVEPOINT savepoint_1; +END | + +--delimiter ; + +INSERT INTO t VALUES (2), (3); +INSERT INTO t1 VALUES (30); +--source include/show_binlog_events.inc + +SELECT * FROM t; +SELECT * FROM t1; +--source include/sync_slave_sql_with_master.inc + +SELECT * FROM t; +SELECT * FROM t1; + +--source include/rpl_connection_master.inc +DROP TABLE t; +DROP TABLE t1; + +--echo # Test case 6: +--source include/rpl_reset.inc +--source include/rpl_connection_master.inc +CREATE TABLE t1 (f1 INTEGER ) ENGINE=INNODB; +CREATE TABLE t2 (f1 INTEGER ) ENGINE=INNODB; + +--delimiter | + +CREATE FUNCTION f1() RETURNS INT +BEGIN + SAVEPOINT event_logging_2; + INSERT INTO t1 VALUES (1); + ROLLBACK TO event_logging_2; + RETURN 0; +END| + +--delimiter ; + +BEGIN; +INSERT INTO t2 VALUES (1), (f1()), (2), (4); +COMMIT; +INSERT INTO t2 VALUES (10); +--source include/show_binlog_events.inc + +--source include/rpl_connection_master.inc +SELECT * FROM t2; +SELECT * FROM t1; +--source include/sync_slave_sql_with_master.inc +SELECT * FROM t2; +SELECT * FROM t1; + +--source include/rpl_connection_master.inc +DROP TABLE t1; +DROP TABLE t2; +DROP FUNCTION f1; + +--echo # Test case 7: +--source include/rpl_reset.inc +--source include/rpl_connection_master.inc +CREATE TABLE t1 (f1 INTEGER ) ENGINE=INNODB; +CREATE TABLE t2 (f1 INTEGER ) ENGINE=INNODB; + +--delimiter | + +CREATE FUNCTION f1() RETURNS INT +BEGIN + SAVEPOINT event_logging_2; + INSERT INTO t1 VALUES (1); + RETURN 0; +END| + +--delimiter ; + +BEGIN; +INSERT INTO t2 VALUES (1), (f1()), (2), (4); +COMMIT; +INSERT INTO t2 VALUES (10); +--source include/show_binlog_events.inc + +--source include/rpl_connection_master.inc +SELECT * FROM t2; +SELECT * FROM t1; +--source include/sync_slave_sql_with_master.inc +SELECT * FROM t2; +SELECT * FROM t1; + +--source include/rpl_connection_master.inc +DROP TABLE t1; +DROP TABLE t2; +DROP FUNCTION f1; + +--echo # Test case 8: +--source include/rpl_reset.inc +--source include/rpl_connection_master.inc +CREATE TABLE t1 (f1 INTEGER ) ENGINE=INNODB; + +--delimiter | + +CREATE FUNCTION f1() RETURNS INT +BEGIN + SAVEPOINT event_logging_2; + RETURN 0; +END| + +--delimiter ; + +BEGIN; +INSERT INTO t1 VALUES (1), (f1()), (2), (4); +COMMIT; +INSERT INTO t1 VALUES (10); +--source include/show_binlog_events.inc + +--source include/rpl_connection_master.inc +SELECT * FROM t1; +--source include/sync_slave_sql_with_master.inc +SELECT * FROM t1; + +--source include/rpl_connection_master.inc +DROP TABLE t1; +DROP FUNCTION f1; + +--source include/rpl_end.inc diff --git a/sql/log.cc b/sql/log.cc index ddf6bd07695..0152997e8b0 100644 --- a/sql/log.cc +++ b/sql/log.cc @@ -2218,6 +2218,7 @@ static int binlog_savepoint_set(handlerton *hton, THD *thd, void *sv) DBUG_RETURN(0); char buf[1024]; + String log_query(buf, sizeof(buf), &my_charset_bin); if (log_query.copy(STRING_WITH_LEN("SAVEPOINT "), &my_charset_bin) || append_identifier(thd, &log_query, @@ -2272,6 +2273,17 @@ static int binlog_savepoint_rollback(handlerton *hton, THD *thd, void *sv) binlog_trans_log_truncate(thd, *(my_off_t*)sv); + /* + When a SAVEPOINT is executed inside a stored function/trigger we force the + pending event to be flushed with a STMT_END_F flag and clear the table maps + as well to ensure that following DMLs will have a clean state to start + with. ROLLBACK inside a stored routine has to finalize possibly existing + current row-based pending event with cleaning up table maps. That ensures + that following DMLs will have a clean state to start with. + */ + if (thd->in_sub_stmt) + thd->clear_binlog_table_maps(); + DBUG_RETURN(0); } @@ -6043,10 +6055,17 @@ bool MYSQL_BIN_LOG::write(Log_event *event_info, my_bool *with_annotate) /* We only end the statement if we are in a top-level statement. If we are inside a stored function, we do not end the statement since - this will close all tables on the slave. + this will close all tables on the slave. But there can be a special case + where we are inside a stored function/trigger and a SAVEPOINT is being + set in side the stored function/trigger. This SAVEPOINT execution will + force the pending event to be flushed without an STMT_END_F flag. This + will result in a case where following DMLs will be considered as part of + same statement and result in data loss on slave. Hence in this case we + force the end_stmt to be true. */ - bool const end_stmt= - thd->locked_tables_mode && thd->lex->requires_prelocking(); + bool const end_stmt= (thd->in_sub_stmt && thd->lex->sql_command == + SQLCOM_SAVEPOINT) ? true : + (thd->locked_tables_mode && thd->lex->requires_prelocking()); if (thd->binlog_flush_pending_rows_event(end_stmt, using_trans)) DBUG_RETURN(error); |