mirror of
https://github.com/MariaDB/server.git
synced 2025-11-04 12:56:14 +01:00
151 lines
6.6 KiB
Text
151 lines
6.6 KiB
Text
include/master-slave.inc
|
|
[connection master]
|
|
call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT");
|
|
########################################################################
|
|
# Environment
|
|
########################################################################
|
|
connection master;
|
|
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');
|
|
connect conn1, 127.0.0.1,root,,;
|
|
connect conn2, 127.0.0.1,root,,;
|
|
########################################################################
|
|
# Testing ER_LOCK_WAIT_TIMEOUT
|
|
########################################################################
|
|
connection conn1;
|
|
SET AUTOCOMMIT = 1;
|
|
BEGIN;
|
|
UPDATE t SET f = 'yellow 2' WHERE i = 3;
|
|
Warnings:
|
|
Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement accesses nontransactional table as well as transactional or temporary table, and writes to any of them
|
|
connection conn2;
|
|
SET AUTOCOMMIT = 1;
|
|
BEGIN;
|
|
UPDATE IGNORE 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");
|
|
connection conn1;
|
|
COMMIT;
|
|
connection conn2;
|
|
ROLLBACK;
|
|
Warnings:
|
|
Warning 1196 Some non-transactional changed tables couldn't be rolled back
|
|
include/show_binlog_events.inc
|
|
Log_name Pos Event_type Server_id End_log_pos Info
|
|
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
|
|
master-bin.000001 # Query # # use `test`; INSERT INTO n VALUES (now(),"brown")
|
|
master-bin.000001 # Query # # COMMIT
|
|
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
|
|
master-bin.000001 # Query # # use `test`; UPDATE t SET f = 'yellow 2' WHERE i = 3
|
|
master-bin.000001 # Xid # # COMMIT /* XID */
|
|
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
|
|
master-bin.000001 # Query # # use `test`; UPDATE IGNORE t SET f = 'magenta 2' WHERE f = 'red'
|
|
master-bin.000001 # Query # # use `test`; INSERT INTO t VALUES (5 + (2 * 10),"brown")
|
|
master-bin.000001 # Query # # ROLLBACK
|
|
connection conn1;
|
|
SET AUTOCOMMIT = 1;
|
|
BEGIN;
|
|
UPDATE t SET f = 'gray 2' WHERE i = 3;
|
|
Warnings:
|
|
Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement accesses nontransactional table as well as transactional or temporary table, and writes to any of them
|
|
connection conn2;
|
|
SET AUTOCOMMIT = 1;
|
|
BEGIN;
|
|
UPDATE IGNORE 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");
|
|
connection conn1;
|
|
COMMIT;
|
|
connection conn2;
|
|
COMMIT;
|
|
include/show_binlog_events.inc
|
|
Log_name Pos Event_type Server_id End_log_pos Info
|
|
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
|
|
master-bin.000001 # Query # # use `test`; INSERT INTO n VALUES (now(),"brown")
|
|
master-bin.000001 # Query # # COMMIT
|
|
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
|
|
master-bin.000001 # Query # # use `test`; UPDATE t SET f = 'gray 2' WHERE i = 3
|
|
master-bin.000001 # Xid # # COMMIT /* XID */
|
|
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
|
|
master-bin.000001 # Query # # use `test`; UPDATE IGNORE t SET f = 'dark blue 2' WHERE f = 'red'
|
|
master-bin.000001 # Query # # use `test`; INSERT INTO t VALUES (6 + (2 * 10),"brown")
|
|
master-bin.000001 # Xid # # COMMIT /* XID */
|
|
connection conn1;
|
|
SET AUTOCOMMIT = 0;
|
|
UPDATE t SET f = 'yellow 1' WHERE i = 3;
|
|
Warnings:
|
|
Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement accesses nontransactional table as well as transactional or temporary table, and writes to any of them
|
|
connection conn2;
|
|
SET AUTOCOMMIT = 0;
|
|
UPDATE IGNORE 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");
|
|
connection conn1;
|
|
COMMIT;
|
|
connection conn2;
|
|
ROLLBACK;
|
|
Warnings:
|
|
Warning 1196 Some non-transactional changed tables couldn't be rolled back
|
|
include/show_binlog_events.inc
|
|
Log_name Pos Event_type Server_id End_log_pos Info
|
|
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
|
|
master-bin.000001 # Query # # use `test`; INSERT INTO n VALUES (now(),"brown")
|
|
master-bin.000001 # Query # # COMMIT
|
|
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
|
|
master-bin.000001 # Query # # use `test`; UPDATE t SET f = 'yellow 1' WHERE i = 3
|
|
master-bin.000001 # Xid # # COMMIT /* XID */
|
|
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
|
|
master-bin.000001 # Query # # use `test`; UPDATE IGNORE t SET f = 'magenta 1' WHERE f = 'red'
|
|
master-bin.000001 # Query # # use `test`; INSERT INTO t VALUES (5 + (1 * 10),"brown")
|
|
master-bin.000001 # Query # # ROLLBACK
|
|
connection conn1;
|
|
SET AUTOCOMMIT = 0;
|
|
UPDATE t SET f = 'gray 1' WHERE i = 3;
|
|
Warnings:
|
|
Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement accesses nontransactional table as well as transactional or temporary table, and writes to any of them
|
|
connection conn2;
|
|
SET AUTOCOMMIT = 0;
|
|
UPDATE IGNORE 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");
|
|
connection conn1;
|
|
COMMIT;
|
|
connection conn2;
|
|
COMMIT;
|
|
include/show_binlog_events.inc
|
|
Log_name Pos Event_type Server_id End_log_pos Info
|
|
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
|
|
master-bin.000001 # Query # # use `test`; INSERT INTO n VALUES (now(),"brown")
|
|
master-bin.000001 # Query # # COMMIT
|
|
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
|
|
master-bin.000001 # Query # # use `test`; UPDATE t SET f = 'gray 1' WHERE i = 3
|
|
master-bin.000001 # Xid # # COMMIT /* XID */
|
|
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
|
|
master-bin.000001 # Query # # use `test`; UPDATE IGNORE t SET f = 'dark blue 1' WHERE f = 'red'
|
|
master-bin.000001 # Query # # use `test`; INSERT INTO t VALUES (6 + (1 * 10),"brown")
|
|
master-bin.000001 # Xid # # COMMIT /* XID */
|
|
connection master;
|
|
connection slave;
|
|
include/rpl_diff.inc
|
|
include/rpl_diff.inc
|
|
########################################################################
|
|
# Cleanup
|
|
########################################################################
|
|
connection master;
|
|
DROP TRIGGER tr;
|
|
DROP TABLE t;
|
|
DROP TABLE n;
|
|
connection slave;
|
|
connection master;
|
|
disconnect conn1;
|
|
disconnect conn2;
|
|
include/rpl_end.inc
|