mirror of
https://github.com/MariaDB/server.git
synced 2025-08-08 11:22:35 +03:00
585785c7bc
When updating non-transactional tables inside a multi-statement transaction, and binlog_direct_non_transactional_updates=1, then the non-transactional updates are binlogged directly through the statement cache while the transaction cache is still being added to in the main transaction. Thus, move the engine_binlog_info out from binlog_cache_mngr and into the individual stmt/trx binlog_cache_data, so that we can have separate engine_binlog_info active for the statement and the transaction cache. Signed-off-by: Kristian Nielsen <knielsen@knielsen-hq.org>
416 lines
12 KiB
Plaintext
416 lines
12 KiB
Plaintext
include/master-slave.inc
|
|
[connection master]
|
|
CREATE TABLE t1(a INT PRIMARY KEY, b INT, c LONGTEXT) ENGINE=InnoDB;
|
|
CREATE TABLE t2(a INT PRIMARY KEY, b INT, c LONGTEXT) ENGINE=Aria;
|
|
SET @c= REPEAT('*', 20);
|
|
SET SESSION binlog_format=statement;
|
|
SET SESSION binlog_direct_non_transactional_updates= 0;
|
|
INSERT INTO t1 VALUES (1 + 0, 0, @c), (2 + 0, 0, @c), (3 + 0, 0, @c);
|
|
INSERT INTO t2 VALUES (1 + 0, 1, @c), (2 + 0, 1, @c), (3 + 0, 1, @c);
|
|
BEGIN;
|
|
UPDATE t1 SET b=2, c=CONCAT('!', c) WHERE a=1+0;
|
|
UPDATE t2 SET b=2, c=CONCAT('!', c) WHERE a=2+0;
|
|
Warnings:
|
|
Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it accesses a non-transactional table after accessing a transactional table within the same transaction
|
|
UPDATE t1 SET b=2, c=CONCAT('!', c) WHERE a=3+0;
|
|
INSERT INTO t2 VALUES (4 + 0, 2, @c);
|
|
Warnings:
|
|
Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it accesses a non-transactional table after accessing a transactional table within the same transaction
|
|
COMMIT;
|
|
SET SESSION binlog_direct_non_transactional_updates= 1;
|
|
INSERT INTO t1 VALUES (1 + 10, 0, @c), (2 + 10, 0, @c), (3 + 10, 0, @c);
|
|
INSERT INTO t2 VALUES (1 + 10, 1, @c), (2 + 10, 1, @c), (3 + 10, 1, @c);
|
|
BEGIN;
|
|
UPDATE t1 SET b=2, c=CONCAT('!', c) WHERE a=1+10;
|
|
UPDATE t2 SET b=2, c=CONCAT('!', c) WHERE a=2+10;
|
|
UPDATE t1 SET b=2, c=CONCAT('!', c) WHERE a=3+10;
|
|
INSERT INTO t2 VALUES (4 + 10, 2, @c);
|
|
COMMIT;
|
|
SET SESSION binlog_format=row;
|
|
SET SESSION binlog_direct_non_transactional_updates= 0;
|
|
INSERT INTO t1 VALUES (1 + 100, 0, @c), (2 + 100, 0, @c), (3 + 100, 0, @c);
|
|
INSERT INTO t2 VALUES (1 + 100, 1, @c), (2 + 100, 1, @c), (3 + 100, 1, @c);
|
|
BEGIN;
|
|
UPDATE t1 SET b=2, c=CONCAT('!', c) WHERE a=1+100;
|
|
UPDATE t2 SET b=2, c=CONCAT('!', c) WHERE a=2+100;
|
|
UPDATE t1 SET b=2, c=CONCAT('!', c) WHERE a=3+100;
|
|
INSERT INTO t2 VALUES (4 + 100, 2, @c);
|
|
COMMIT;
|
|
SET SESSION binlog_direct_non_transactional_updates= 1;
|
|
INSERT INTO t1 VALUES (1 + 110, 0, @c), (2 + 110, 0, @c), (3 + 110, 0, @c);
|
|
INSERT INTO t2 VALUES (1 + 110, 1, @c), (2 + 110, 1, @c), (3 + 110, 1, @c);
|
|
BEGIN;
|
|
UPDATE t1 SET b=2, c=CONCAT('!', c) WHERE a=1+110;
|
|
UPDATE t2 SET b=2, c=CONCAT('!', c) WHERE a=2+110;
|
|
UPDATE t1 SET b=2, c=CONCAT('!', c) WHERE a=3+110;
|
|
INSERT INTO t2 VALUES (4 + 110, 2, @c);
|
|
COMMIT;
|
|
SET @c= REPEAT('%', 1024);
|
|
SET SESSION binlog_format=statement;
|
|
SET SESSION binlog_direct_non_transactional_updates= 0;
|
|
INSERT INTO t1 VALUES (1 + 1000, 0, @c), (2 + 1000, 0, @c), (3 + 1000, 0, @c);
|
|
INSERT INTO t2 VALUES (1 + 1000, 1, @c), (2 + 1000, 1, @c), (3 + 1000, 1, @c);
|
|
BEGIN;
|
|
UPDATE t1 SET b=2, c=CONCAT('!', c) WHERE a=1+1000;
|
|
UPDATE t2 SET b=2, c=CONCAT('!', c) WHERE a=2+1000;
|
|
Warnings:
|
|
Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it accesses a non-transactional table after accessing a transactional table within the same transaction
|
|
UPDATE t1 SET b=2, c=CONCAT('!', c) WHERE a=3+1000;
|
|
INSERT INTO t2 VALUES (4 + 1000, 2, @c);
|
|
Warnings:
|
|
Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it accesses a non-transactional table after accessing a transactional table within the same transaction
|
|
COMMIT;
|
|
SET SESSION binlog_direct_non_transactional_updates= 1;
|
|
INSERT INTO t1 VALUES (1 + 1010, 0, @c), (2 + 1010, 0, @c), (3 + 1010, 0, @c);
|
|
INSERT INTO t2 VALUES (1 + 1010, 1, @c), (2 + 1010, 1, @c), (3 + 1010, 1, @c);
|
|
BEGIN;
|
|
UPDATE t1 SET b=2, c=CONCAT('!', c) WHERE a=1+1010;
|
|
UPDATE t2 SET b=2, c=CONCAT('!', c) WHERE a=2+1010;
|
|
UPDATE t1 SET b=2, c=CONCAT('!', c) WHERE a=3+1010;
|
|
INSERT INTO t2 VALUES (4 + 1010, 2, @c);
|
|
COMMIT;
|
|
SET SESSION binlog_format=row;
|
|
SET SESSION binlog_direct_non_transactional_updates= 0;
|
|
INSERT INTO t1 VALUES (1 + 1100, 0, @c), (2 + 1100, 0, @c), (3 + 1100, 0, @c);
|
|
INSERT INTO t2 VALUES (1 + 1100, 1, @c), (2 + 1100, 1, @c), (3 + 1100, 1, @c);
|
|
BEGIN;
|
|
UPDATE t1 SET b=2, c=CONCAT('!', c) WHERE a=1+1100;
|
|
UPDATE t2 SET b=2, c=CONCAT('!', c) WHERE a=2+1100;
|
|
UPDATE t1 SET b=2, c=CONCAT('!', c) WHERE a=3+1100;
|
|
INSERT INTO t2 VALUES (4 + 1100, 2, @c);
|
|
COMMIT;
|
|
SET SESSION binlog_direct_non_transactional_updates= 1;
|
|
INSERT INTO t1 VALUES (1 + 1110, 0, @c), (2 + 1110, 0, @c), (3 + 1110, 0, @c);
|
|
INSERT INTO t2 VALUES (1 + 1110, 1, @c), (2 + 1110, 1, @c), (3 + 1110, 1, @c);
|
|
BEGIN;
|
|
UPDATE t1 SET b=2, c=CONCAT('!', c) WHERE a=1+1110;
|
|
UPDATE t2 SET b=2, c=CONCAT('!', c) WHERE a=2+1110;
|
|
UPDATE t1 SET b=2, c=CONCAT('!', c) WHERE a=3+1110;
|
|
INSERT INTO t2 VALUES (4 + 1110, 2, @c);
|
|
COMMIT;
|
|
SET @c= REPEAT('.', 18000);
|
|
SET SESSION binlog_format=statement;
|
|
SET SESSION binlog_direct_non_transactional_updates= 0;
|
|
INSERT INTO t1 VALUES (1 + 2000, 0, @c), (2 + 2000, 0, @c), (3 + 2000, 0, @c);
|
|
INSERT INTO t2 VALUES (1 + 2000, 1, @c), (2 + 2000, 1, @c), (3 + 2000, 1, @c);
|
|
BEGIN;
|
|
UPDATE t1 SET b=2, c=CONCAT('!', c) WHERE a=1+2000;
|
|
UPDATE t2 SET b=2, c=CONCAT('!', c) WHERE a=2+2000;
|
|
Warnings:
|
|
Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it accesses a non-transactional table after accessing a transactional table within the same transaction
|
|
UPDATE t1 SET b=2, c=CONCAT('!', c) WHERE a=3+2000;
|
|
INSERT INTO t2 VALUES (4 + 2000, 2, @c);
|
|
Warnings:
|
|
Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it accesses a non-transactional table after accessing a transactional table within the same transaction
|
|
COMMIT;
|
|
SET SESSION binlog_direct_non_transactional_updates= 1;
|
|
INSERT INTO t1 VALUES (1 + 2010, 0, @c), (2 + 2010, 0, @c), (3 + 2010, 0, @c);
|
|
INSERT INTO t2 VALUES (1 + 2010, 1, @c), (2 + 2010, 1, @c), (3 + 2010, 1, @c);
|
|
BEGIN;
|
|
UPDATE t1 SET b=2, c=CONCAT('!', c) WHERE a=1+2010;
|
|
UPDATE t2 SET b=2, c=CONCAT('!', c) WHERE a=2+2010;
|
|
UPDATE t1 SET b=2, c=CONCAT('!', c) WHERE a=3+2010;
|
|
INSERT INTO t2 VALUES (4 + 2010, 2, @c);
|
|
COMMIT;
|
|
SET SESSION binlog_format=row;
|
|
SET SESSION binlog_direct_non_transactional_updates= 0;
|
|
INSERT INTO t1 VALUES (1 + 2100, 0, @c), (2 + 2100, 0, @c), (3 + 2100, 0, @c);
|
|
INSERT INTO t2 VALUES (1 + 2100, 1, @c), (2 + 2100, 1, @c), (3 + 2100, 1, @c);
|
|
BEGIN;
|
|
UPDATE t1 SET b=2, c=CONCAT('!', c) WHERE a=1+2100;
|
|
UPDATE t2 SET b=2, c=CONCAT('!', c) WHERE a=2+2100;
|
|
UPDATE t1 SET b=2, c=CONCAT('!', c) WHERE a=3+2100;
|
|
INSERT INTO t2 VALUES (4 + 2100, 2, @c);
|
|
COMMIT;
|
|
SET SESSION binlog_direct_non_transactional_updates= 1;
|
|
INSERT INTO t1 VALUES (1 + 2110, 0, @c), (2 + 2110, 0, @c), (3 + 2110, 0, @c);
|
|
INSERT INTO t2 VALUES (1 + 2110, 1, @c), (2 + 2110, 1, @c), (3 + 2110, 1, @c);
|
|
BEGIN;
|
|
UPDATE t1 SET b=2, c=CONCAT('!', c) WHERE a=1+2110;
|
|
UPDATE t2 SET b=2, c=CONCAT('!', c) WHERE a=2+2110;
|
|
UPDATE t1 SET b=2, c=CONCAT('!', c) WHERE a=3+2110;
|
|
INSERT INTO t2 VALUES (4 + 2110, 2, @c);
|
|
COMMIT;
|
|
SET @c= REPEAT('.', 40000);
|
|
SET SESSION binlog_format=statement;
|
|
SET SESSION binlog_direct_non_transactional_updates= 0;
|
|
INSERT INTO t1 VALUES (1 + 3000, 0, @c), (2 + 3000, 0, @c), (3 + 3000, 0, @c);
|
|
INSERT INTO t2 VALUES (1 + 3000, 1, @c), (2 + 3000, 1, @c), (3 + 3000, 1, @c);
|
|
BEGIN;
|
|
UPDATE t1 SET b=2, c=CONCAT('!', c) WHERE a=1+3000;
|
|
UPDATE t2 SET b=2, c=CONCAT('!', c) WHERE a=2+3000;
|
|
Warnings:
|
|
Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it accesses a non-transactional table after accessing a transactional table within the same transaction
|
|
UPDATE t1 SET b=2, c=CONCAT('!', c) WHERE a=3+3000;
|
|
INSERT INTO t2 VALUES (4 + 3000, 2, @c);
|
|
Warnings:
|
|
Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it accesses a non-transactional table after accessing a transactional table within the same transaction
|
|
COMMIT;
|
|
SET SESSION binlog_direct_non_transactional_updates= 1;
|
|
INSERT INTO t1 VALUES (1 + 3010, 0, @c), (2 + 3010, 0, @c), (3 + 3010, 0, @c);
|
|
INSERT INTO t2 VALUES (1 + 3010, 1, @c), (2 + 3010, 1, @c), (3 + 3010, 1, @c);
|
|
BEGIN;
|
|
UPDATE t1 SET b=2, c=CONCAT('!', c) WHERE a=1+3010;
|
|
UPDATE t2 SET b=2, c=CONCAT('!', c) WHERE a=2+3010;
|
|
UPDATE t1 SET b=2, c=CONCAT('!', c) WHERE a=3+3010;
|
|
INSERT INTO t2 VALUES (4 + 3010, 2, @c);
|
|
COMMIT;
|
|
SET SESSION binlog_format=row;
|
|
SET SESSION binlog_direct_non_transactional_updates= 0;
|
|
INSERT INTO t1 VALUES (1 + 3100, 0, @c), (2 + 3100, 0, @c), (3 + 3100, 0, @c);
|
|
INSERT INTO t2 VALUES (1 + 3100, 1, @c), (2 + 3100, 1, @c), (3 + 3100, 1, @c);
|
|
BEGIN;
|
|
UPDATE t1 SET b=2, c=CONCAT('!', c) WHERE a=1+3100;
|
|
UPDATE t2 SET b=2, c=CONCAT('!', c) WHERE a=2+3100;
|
|
UPDATE t1 SET b=2, c=CONCAT('!', c) WHERE a=3+3100;
|
|
INSERT INTO t2 VALUES (4 + 3100, 2, @c);
|
|
COMMIT;
|
|
SET SESSION binlog_direct_non_transactional_updates= 1;
|
|
INSERT INTO t1 VALUES (1 + 3110, 0, @c), (2 + 3110, 0, @c), (3 + 3110, 0, @c);
|
|
INSERT INTO t2 VALUES (1 + 3110, 1, @c), (2 + 3110, 1, @c), (3 + 3110, 1, @c);
|
|
BEGIN;
|
|
UPDATE t1 SET b=2, c=CONCAT('!', c) WHERE a=1+3110;
|
|
UPDATE t2 SET b=2, c=CONCAT('!', c) WHERE a=2+3110;
|
|
UPDATE t1 SET b=2, c=CONCAT('!', c) WHERE a=3+3110;
|
|
INSERT INTO t2 VALUES (4 + 3110, 2, @c);
|
|
COMMIT;
|
|
SELECT a, b, length(c) FROM t1 ORDER BY a;
|
|
a b length(c)
|
|
1 2 21
|
|
2 0 20
|
|
3 2 21
|
|
11 2 21
|
|
12 0 20
|
|
13 2 21
|
|
101 2 21
|
|
102 0 20
|
|
103 2 21
|
|
111 2 21
|
|
112 0 20
|
|
113 2 21
|
|
1001 2 1025
|
|
1002 0 1024
|
|
1003 2 1025
|
|
1011 2 1025
|
|
1012 0 1024
|
|
1013 2 1025
|
|
1101 2 1025
|
|
1102 0 1024
|
|
1103 2 1025
|
|
1111 2 1025
|
|
1112 0 1024
|
|
1113 2 1025
|
|
2001 2 18001
|
|
2002 0 18000
|
|
2003 2 18001
|
|
2011 2 18001
|
|
2012 0 18000
|
|
2013 2 18001
|
|
2101 2 18001
|
|
2102 0 18000
|
|
2103 2 18001
|
|
2111 2 18001
|
|
2112 0 18000
|
|
2113 2 18001
|
|
3001 2 40001
|
|
3002 0 40000
|
|
3003 2 40001
|
|
3011 2 40001
|
|
3012 0 40000
|
|
3013 2 40001
|
|
3101 2 40001
|
|
3102 0 40000
|
|
3103 2 40001
|
|
3111 2 40001
|
|
3112 0 40000
|
|
3113 2 40001
|
|
SELECT a, b, length(c) FROM t2 ORDER BY a;
|
|
a b length(c)
|
|
1 1 20
|
|
2 2 21
|
|
3 1 20
|
|
4 2 20
|
|
11 1 20
|
|
12 2 21
|
|
13 1 20
|
|
14 2 20
|
|
101 1 20
|
|
102 2 21
|
|
103 1 20
|
|
104 2 20
|
|
111 1 20
|
|
112 2 21
|
|
113 1 20
|
|
114 2 20
|
|
1001 1 1024
|
|
1002 2 1025
|
|
1003 1 1024
|
|
1004 2 1024
|
|
1011 1 1024
|
|
1012 2 1025
|
|
1013 1 1024
|
|
1014 2 1024
|
|
1101 1 1024
|
|
1102 2 1025
|
|
1103 1 1024
|
|
1104 2 1024
|
|
1111 1 1024
|
|
1112 2 1025
|
|
1113 1 1024
|
|
1114 2 1024
|
|
2001 1 18000
|
|
2002 2 18001
|
|
2003 1 18000
|
|
2004 2 18000
|
|
2011 1 18000
|
|
2012 2 18001
|
|
2013 1 18000
|
|
2014 2 18000
|
|
2101 1 18000
|
|
2102 2 18001
|
|
2103 1 18000
|
|
2104 2 18000
|
|
2111 1 18000
|
|
2112 2 18001
|
|
2113 1 18000
|
|
2114 2 18000
|
|
3001 1 40000
|
|
3002 2 40001
|
|
3003 1 40000
|
|
3004 2 40000
|
|
3011 1 40000
|
|
3012 2 40001
|
|
3013 1 40000
|
|
3014 2 40000
|
|
3101 1 40000
|
|
3102 2 40001
|
|
3103 1 40000
|
|
3104 2 40000
|
|
3111 1 40000
|
|
3112 2 40001
|
|
3113 1 40000
|
|
3114 2 40000
|
|
include/save_master_gtid.inc
|
|
connection slave;
|
|
include/sync_with_master_gtid.inc
|
|
SELECT a, b, length(c) FROM t1 ORDER BY a;
|
|
a b length(c)
|
|
1 2 21
|
|
2 0 20
|
|
3 2 21
|
|
11 2 21
|
|
12 0 20
|
|
13 2 21
|
|
101 2 21
|
|
102 0 20
|
|
103 2 21
|
|
111 2 21
|
|
112 0 20
|
|
113 2 21
|
|
1001 2 1025
|
|
1002 0 1024
|
|
1003 2 1025
|
|
1011 2 1025
|
|
1012 0 1024
|
|
1013 2 1025
|
|
1101 2 1025
|
|
1102 0 1024
|
|
1103 2 1025
|
|
1111 2 1025
|
|
1112 0 1024
|
|
1113 2 1025
|
|
2001 2 18001
|
|
2002 0 18000
|
|
2003 2 18001
|
|
2011 2 18001
|
|
2012 0 18000
|
|
2013 2 18001
|
|
2101 2 18001
|
|
2102 0 18000
|
|
2103 2 18001
|
|
2111 2 18001
|
|
2112 0 18000
|
|
2113 2 18001
|
|
3001 2 40001
|
|
3002 0 40000
|
|
3003 2 40001
|
|
3011 2 40001
|
|
3012 0 40000
|
|
3013 2 40001
|
|
3101 2 40001
|
|
3102 0 40000
|
|
3103 2 40001
|
|
3111 2 40001
|
|
3112 0 40000
|
|
3113 2 40001
|
|
SELECT a, b, length(c) FROM t2 ORDER BY a;
|
|
a b length(c)
|
|
1 1 20
|
|
2 2 21
|
|
3 1 20
|
|
4 2 20
|
|
11 1 20
|
|
12 2 21
|
|
13 1 20
|
|
14 2 20
|
|
101 1 20
|
|
102 2 21
|
|
103 1 20
|
|
104 2 20
|
|
111 1 20
|
|
112 2 21
|
|
113 1 20
|
|
114 2 20
|
|
1001 1 1024
|
|
1002 2 1025
|
|
1003 1 1024
|
|
1004 2 1024
|
|
1011 1 1024
|
|
1012 2 1025
|
|
1013 1 1024
|
|
1014 2 1024
|
|
1101 1 1024
|
|
1102 2 1025
|
|
1103 1 1024
|
|
1104 2 1024
|
|
1111 1 1024
|
|
1112 2 1025
|
|
1113 1 1024
|
|
1114 2 1024
|
|
2001 1 18000
|
|
2002 2 18001
|
|
2003 1 18000
|
|
2004 2 18000
|
|
2011 1 18000
|
|
2012 2 18001
|
|
2013 1 18000
|
|
2014 2 18000
|
|
2101 1 18000
|
|
2102 2 18001
|
|
2103 1 18000
|
|
2104 2 18000
|
|
2111 1 18000
|
|
2112 2 18001
|
|
2113 1 18000
|
|
2114 2 18000
|
|
3001 1 40000
|
|
3002 2 40001
|
|
3003 1 40000
|
|
3004 2 40000
|
|
3011 1 40000
|
|
3012 2 40001
|
|
3013 1 40000
|
|
3014 2 40000
|
|
3101 1 40000
|
|
3102 2 40001
|
|
3103 1 40000
|
|
3104 2 40000
|
|
3111 1 40000
|
|
3112 2 40001
|
|
3113 1 40000
|
|
3114 2 40000
|
|
connection master;
|
|
DROP TABLE t1, t2;
|
|
CALL mtr.add_suppression('Statement is unsafe because it accesses a non-transactional table after accessing a transactional table');
|
|
include/rpl_end.inc
|