^^&&^^

Edit File: rpl_stm_mix_rollback_to_savepoint.result

include/master-slave.inc
Warnings:
Note	####	Sending passwords in plain text without SSL/TLS is extremely insecure.
Note	####	Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
[connection master]
#Test case 1:
CREATE TABLE t1 (f1 INTEGER PRIMARY KEY);
CREATE TABLE t2 (f1 INTEGER PRIMARY KEY);
CREATE TABLE t3 (f1 INTEGER PRIMARY KEY);
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	#	Query	#	#	use `test`; CREATE TABLE t1 (f1 INTEGER PRIMARY KEY)
master-bin.000001	#	Query	#	#	use `test`; CREATE TABLE t2 (f1 INTEGER PRIMARY KEY)
master-bin.000001	#	Query	#	#	use `test`; CREATE TABLE t3 (f1 INTEGER PRIMARY KEY)
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	#	Query	#	#	BEGIN
master-bin.000001	#	Query	#	#	use `test`; INSERT INTO t2 VALUES (1)
master-bin.000001	#	Xid	#	#	COMMIT /* XID */
master-bin.000001	#	Query	#	#	BEGIN
master-bin.000001	#	Query	#	#	use `test`; INSERT INTO t1 VALUES (1)
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	#	Query	#	#	use `test`; CREATE TABLE t1 (f1 INTEGER PRIMARY KEY)
master-bin.000001	#	Query	#	#	use `test`; CREATE TABLE t2 (f1 INTEGER PRIMARY KEY)
master-bin.000001	#	Query	#	#	use `test`; CREATE TABLE t3 (f1 INTEGER PRIMARY KEY)
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	#	Query	#	#	BEGIN
master-bin.000001	#	Query	#	#	use `test`; INSERT INTO t2 VALUES (1)
master-bin.000001	#	Xid	#	#	COMMIT /* XID */
master-bin.000001	#	Query	#	#	BEGIN
master-bin.000001	#	Query	#	#	use `test`; INSERT INTO t1 VALUES (1)
master-bin.000001	#	Xid	#	#	COMMIT /* XID */
master-bin.000001	#	Query	#	#	use `test`; DROP TRIGGER tr1
master-bin.000001	#	Query	#	#	BEGIN
master-bin.000001	#	Query	#	#	use `test`; DELETE FROM t1
master-bin.000001	#	Xid	#	#	COMMIT /* XID */
master-bin.000001	#	Query	#	#	BEGIN
master-bin.000001	#	Query	#	#	use `test`; DELETE FROM t2
master-bin.000001	#	Xid	#	#	COMMIT /* XID */
master-bin.000001	#	Query	#	#	BEGIN
master-bin.000001	#	Query	#	#	use `test`; DELETE FROM t3
master-bin.000001	#	Xid	#	#	COMMIT /* XID */
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	#	Query	#	#	use `test`; CREATE DEFINER=`root`@`localhost` TRIGGER tr1 AFTER INSERT ON t1 FOR EACH ROW CALL p1()
master-bin.000001	#	Query	#	#	BEGIN
master-bin.000001	#	Query	#	#	use `test`; INSERT INTO t2 VALUES (1)
master-bin.000001	#	Xid	#	#	COMMIT /* XID */
master-bin.000001	#	Query	#	#	BEGIN
master-bin.000001	#	Query	#	#	use `test`; INSERT INTO t1 VALUES (1)
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	#	Query	#	#	use `test`; CREATE TABLE t (f1 int(10) unsigned NOT NULL, PRIMARY KEY (f1)) ENGINE=InnoDB
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	#	Query	#	#	BEGIN
master-bin.000001	#	Query	#	#	use `test`; INSERT INTO t VALUES (2)
master-bin.000001	#	Xid	#	#	COMMIT /* XID */
master-bin.000001	#	Query	#	#	BEGIN
master-bin.000001	#	Query	#	#	use `test`; INSERT INTO t VALUES (3)
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	#	Query	#	#	use `test`; CREATE TABLE t (f1 int(10) unsigned NOT NULL) ENGINE=InnoDB
master-bin.000001	#	Query	#	#	use `test`; CREATE TABLE t1 (f1 int(10) unsigned NOT NULL) ENGINE=InnoDB
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	#	Query	#	#	BEGIN
master-bin.000001	#	Query	#	#	use `test`; INSERT INTO t VALUES (2), (3)
master-bin.000001	#	Xid	#	#	COMMIT /* XID */
master-bin.000001	#	Query	#	#	BEGIN
master-bin.000001	#	Query	#	#	use `test`; INSERT INTO t1 VALUES (30)
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	#	Query	#	#	use `test`; CREATE TABLE t (f1 int(10) unsigned NOT NULL) ENGINE=InnoDB
master-bin.000001	#	Query	#	#	use `test`; CREATE TABLE t1 (f1 int(10) unsigned NOT NULL) ENGINE=InnoDB
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	#	Query	#	#	BEGIN
master-bin.000001	#	Query	#	#	use `test`; INSERT INTO t VALUES (2), (3)
master-bin.000001	#	Xid	#	#	COMMIT /* XID */
master-bin.000001	#	Query	#	#	BEGIN
master-bin.000001	#	Query	#	#	use `test`; INSERT INTO t1 VALUES (30)
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	#	Query	#	#	use `test`; CREATE TABLE t1 (f1 INTEGER ) ENGINE=INNODB
master-bin.000001	#	Query	#	#	use `test`; CREATE TABLE t2 (f1 INTEGER ) ENGINE=INNODB
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	#	Query	#	#	BEGIN
master-bin.000001	#	Query	#	#	use `test`; INSERT INTO t2 VALUES (1), (f1()), (2), (4)
master-bin.000001	#	Xid	#	#	COMMIT /* XID */
master-bin.000001	#	Query	#	#	BEGIN
master-bin.000001	#	Query	#	#	use `test`; INSERT INTO t2 VALUES (10)
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	#	Query	#	#	use `test`; CREATE TABLE t1 (f1 INTEGER ) ENGINE=INNODB
master-bin.000001	#	Query	#	#	use `test`; CREATE TABLE t2 (f1 INTEGER ) ENGINE=INNODB
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	#	Query	#	#	BEGIN
master-bin.000001	#	Query	#	#	use `test`; INSERT INTO t2 VALUES (1), (f1()), (2), (4)
master-bin.000001	#	Xid	#	#	COMMIT /* XID */
master-bin.000001	#	Query	#	#	BEGIN
master-bin.000001	#	Query	#	#	use `test`; INSERT INTO t2 VALUES (10)
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	#	Query	#	#	use `test`; CREATE TABLE t1 (f1 INTEGER ) ENGINE=INNODB
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	#	Query	#	#	BEGIN
master-bin.000001	#	Query	#	#	use `test`; INSERT INTO t1 VALUES (1), (f1()), (2), (4)
master-bin.000001	#	Xid	#	#	COMMIT /* XID */
master-bin.000001	#	Query	#	#	BEGIN
master-bin.000001	#	Query	#	#	use `test`; INSERT INTO t1 VALUES (10)
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