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
SELECT MASTER_POS_WAIT("binlog-000000.ibb", 4096);
ERROR HY000: master_pos_wait() is not available when --binlog-storage-engine is enabled
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