rpl_concurrency_error.result 5.07 KB
Newer Older
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119
stop slave;
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
reset master;
reset slave;
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
start slave;
########################################################################
#                             Environment
########################################################################
CREATE TABLE t (i INT, PRIMARY KEY(i), f CHAR(8)) engine = Innodb;
CREATE TABLE n (d DATETIME, f CHAR(32)) engine = MyIsam;
CREATE TRIGGER tr AFTER UPDATE ON t FOR EACH ROW 
BEGIN 
INSERT INTO n VALUES ( now(), concat( 'updated t: ', old.f, ' -> ', new.f ) ); 
END |
INSERT INTO t VALUES (4,'black'), (2,'red'), (3,'yelow'), (1,'cyan');
########################################################################
#                     Testing ER_LOCK_WAIT_TIMEOUT
########################################################################
SET AUTOCOMMIT = 1;
BEGIN;
UPDATE t SET f = 'yellow 2' WHERE i = 3;
SET AUTOCOMMIT = 1;
BEGIN;
UPDATE t SET f = 'magenta 2' WHERE f = 'red';
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
INSERT INTO t VALUES (5 + (2 * 10),"brown");
INSERT INTO n VALUES (now(),"brown");
COMMIT;
ROLLBACK;
Warnings:
Warning	1196	Some non-transactional changed tables couldn't be rolled back
show binlog events from <binlog_start>;
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
master-bin.000001	#	Query	#	#	BEGIN
master-bin.000001	#	Query	#	#	use `test`; UPDATE t SET f = 'magenta 2' WHERE f = 'red'
master-bin.000001	#	Query	#	#	ROLLBACK
master-bin.000001	#	Query	#	#	BEGIN
master-bin.000001	#	Query	#	#	use `test`; UPDATE t SET f = 'yellow 2' WHERE i = 3
master-bin.000001	#	Xid	#	#	COMMIT /* XID */
master-bin.000001	#	Query	#	#	BEGIN
master-bin.000001	#	Query	#	#	use `test`; INSERT INTO t VALUES (5 + (2 * 10),"brown")
master-bin.000001	#	Query	#	#	use `test`; INSERT INTO n VALUES (now(),"brown")
master-bin.000001	#	Query	#	#	ROLLBACK
SET AUTOCOMMIT = 1;
BEGIN;
UPDATE t SET f = 'gray 2' WHERE i = 3;
SET AUTOCOMMIT = 1;
BEGIN;
UPDATE t SET f = 'dark blue 2' WHERE f = 'red';
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
INSERT INTO t VALUES (6 + (2 * 10),"brown");
INSERT INTO n VALUES (now(),"brown");
COMMIT;
COMMIT;
show binlog events from <binlog_start>;
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
master-bin.000001	#	Query	#	#	BEGIN
master-bin.000001	#	Query	#	#	use `test`; UPDATE t SET f = 'dark blue 2' WHERE f = 'red'
master-bin.000001	#	Query	#	#	ROLLBACK
master-bin.000001	#	Query	#	#	BEGIN
master-bin.000001	#	Query	#	#	use `test`; UPDATE t SET f = 'gray 2' WHERE i = 3
master-bin.000001	#	Xid	#	#	COMMIT /* XID */
master-bin.000001	#	Query	#	#	BEGIN
master-bin.000001	#	Query	#	#	use `test`; INSERT INTO t VALUES (6 + (2 * 10),"brown")
master-bin.000001	#	Query	#	#	use `test`; INSERT INTO n VALUES (now(),"brown")
master-bin.000001	#	Xid	#	#	COMMIT /* XID */
SET AUTOCOMMIT = 0;
UPDATE t SET f = 'yellow 1' WHERE i = 3;
SET AUTOCOMMIT = 0;
UPDATE t SET f = 'magenta 1' WHERE f = 'red';
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
INSERT INTO t VALUES (5 + (1 * 10),"brown");
INSERT INTO n VALUES (now(),"brown");
COMMIT;
ROLLBACK;
Warnings:
Warning	1196	Some non-transactional changed tables couldn't be rolled back
show binlog events from <binlog_start>;
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
master-bin.000001	#	Query	#	#	BEGIN
master-bin.000001	#	Query	#	#	use `test`; UPDATE t SET f = 'magenta 1' WHERE f = 'red'
master-bin.000001	#	Query	#	#	ROLLBACK
master-bin.000001	#	Query	#	#	BEGIN
master-bin.000001	#	Query	#	#	use `test`; UPDATE t SET f = 'yellow 1' WHERE i = 3
master-bin.000001	#	Xid	#	#	COMMIT /* XID */
master-bin.000001	#	Query	#	#	BEGIN
master-bin.000001	#	Query	#	#	use `test`; INSERT INTO t VALUES (5 + (1 * 10),"brown")
master-bin.000001	#	Query	#	#	use `test`; INSERT INTO n VALUES (now(),"brown")
master-bin.000001	#	Query	#	#	ROLLBACK
SET AUTOCOMMIT = 0;
UPDATE t SET f = 'gray 1' WHERE i = 3;
SET AUTOCOMMIT = 0;
UPDATE t SET f = 'dark blue 1' WHERE f = 'red';
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
INSERT INTO t VALUES (6 + (1 * 10),"brown");
INSERT INTO n VALUES (now(),"brown");
COMMIT;
COMMIT;
show binlog events from <binlog_start>;
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
master-bin.000001	#	Query	#	#	BEGIN
master-bin.000001	#	Query	#	#	use `test`; UPDATE t SET f = 'dark blue 1' WHERE f = 'red'
master-bin.000001	#	Query	#	#	ROLLBACK
master-bin.000001	#	Query	#	#	BEGIN
master-bin.000001	#	Query	#	#	use `test`; UPDATE t SET f = 'gray 1' WHERE i = 3
master-bin.000001	#	Xid	#	#	COMMIT /* XID */
master-bin.000001	#	Query	#	#	BEGIN
master-bin.000001	#	Query	#	#	use `test`; INSERT INTO t VALUES (6 + (1 * 10),"brown")
master-bin.000001	#	Query	#	#	use `test`; INSERT INTO n VALUES (now(),"brown")
master-bin.000001	#	Xid	#	#	COMMIT /* XID */
source include/diff_master_slave.inc;
source include/diff_master_slave.inc;
########################################################################
#                                Cleanup
########################################################################
DROP TRIGGER tr;
DROP TABLE t;
DROP TABLE n;