mirror of
https://github.com/MariaDB/server.git
synced 2025-08-07 00:04:31 +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>
425 lines
9.7 KiB
Plaintext
425 lines
9.7 KiB
Plaintext
include/master-slave.inc
|
|
[connection master]
|
|
CREATE TABLE t1 (i INT, a INT, b TEXT, PRIMARY KEY(i, a)) ENGINE=InnoDB;
|
|
CREATE TABLE t2 (i INT, a INT, b TEXT, PRIMARY KEY(i, a)) ENGINE=MyISAM;
|
|
SET @b= REPEAT('$', 0);
|
|
BEGIN;
|
|
INSERT INTO t1 VALUES (0, 1, @b);
|
|
SAVEPOINT s1;
|
|
INSERT INTO t1 VALUES (0, 2, @b);
|
|
SAVEPOINT s2;
|
|
INSERT INTO t1 VALUES (0, 3, @b);
|
|
SAVEPOINT s3;
|
|
INSERT INTO t1 VALUES (0, 4, @b);
|
|
ROLLBACK TO s2;
|
|
INSERT INTO t1 VALUES (0, 5, @b);
|
|
ROLLBACK TO s2;
|
|
INSERT INTO t1 VALUES (0, 6, @b);
|
|
SAVEPOINT s4;
|
|
INSERT INTO t1 VALUES (0, 7, @b);
|
|
SAVEPOINT s5;
|
|
ROLLBACK TO s5;
|
|
INSERT INTO t1 VALUES (0, 8, @b);
|
|
COMMIT;
|
|
SELECT a, length(b) FROM t1 WHERE i=0 ORDER BY a;
|
|
a length(b)
|
|
1 0
|
|
2 0
|
|
6 0
|
|
7 0
|
|
8 0
|
|
BEGIN;
|
|
INSERT INTO t1 VALUES (0, 10, @b);
|
|
SAVEPOINT s10;
|
|
INSERT INTO t1 VALUES (0, 11, @b);
|
|
INSERT INTO t2 VALUES (0, 12, @b);
|
|
ROLLBACK TO s10;
|
|
Warnings:
|
|
Warning 1196 Some non-transactional changed tables couldn't be rolled back
|
|
COMMIT;
|
|
SELECT a, length(b) FROM t1 WHERE i=0 AND a>=10 ORDER BY a;
|
|
a length(b)
|
|
10 0
|
|
SELECT a, length(b) FROM t2 WHERE i=0 ORDER BY a;
|
|
a length(b)
|
|
12 0
|
|
BEGIN;
|
|
UPDATE t1 SET a=a+1000 WHERE i=0;
|
|
UPDATE t1 SET b='x' WHERE i=0;
|
|
ROLLBACK;
|
|
BEGIN;
|
|
INSERT INTO t1
|
|
VALUES (0, 101, @b), (0, 102, @b), (0, 103, @b), (0, 104, @b), (0, 105, @b);
|
|
UPDATE t1 SET a=a-104 WHERE i=0 AND a > 100;
|
|
ERROR 23000: Duplicate entry '0-1' for key 'PRIMARY'
|
|
UPDATE t1 SET a=a+10 WHERE i=0 AND a > 100;
|
|
COMMIT;
|
|
SELECT a, length(b) FROM t1 WHERE i=0 AND a >= 100 ORDER BY a;
|
|
a length(b)
|
|
111 0
|
|
112 0
|
|
113 0
|
|
114 0
|
|
115 0
|
|
SET @b= REPEAT('$', 10);
|
|
BEGIN;
|
|
INSERT INTO t1 VALUES (1, 1, @b);
|
|
SAVEPOINT s1;
|
|
INSERT INTO t1 VALUES (1, 2, @b);
|
|
SAVEPOINT s2;
|
|
INSERT INTO t1 VALUES (1, 3, @b);
|
|
SAVEPOINT s3;
|
|
INSERT INTO t1 VALUES (1, 4, @b);
|
|
ROLLBACK TO s2;
|
|
INSERT INTO t1 VALUES (1, 5, @b);
|
|
ROLLBACK TO s2;
|
|
INSERT INTO t1 VALUES (1, 6, @b);
|
|
SAVEPOINT s4;
|
|
INSERT INTO t1 VALUES (1, 7, @b);
|
|
SAVEPOINT s5;
|
|
ROLLBACK TO s5;
|
|
INSERT INTO t1 VALUES (1, 8, @b);
|
|
COMMIT;
|
|
SELECT a, length(b) FROM t1 WHERE i=1 ORDER BY a;
|
|
a length(b)
|
|
1 10
|
|
2 10
|
|
6 10
|
|
7 10
|
|
8 10
|
|
BEGIN;
|
|
INSERT INTO t1 VALUES (1, 10, @b);
|
|
SAVEPOINT s10;
|
|
INSERT INTO t1 VALUES (1, 11, @b);
|
|
INSERT INTO t2 VALUES (1, 12, @b);
|
|
ROLLBACK TO s10;
|
|
Warnings:
|
|
Warning 1196 Some non-transactional changed tables couldn't be rolled back
|
|
COMMIT;
|
|
SELECT a, length(b) FROM t1 WHERE i=1 AND a>=10 ORDER BY a;
|
|
a length(b)
|
|
10 10
|
|
SELECT a, length(b) FROM t2 WHERE i=1 ORDER BY a;
|
|
a length(b)
|
|
12 10
|
|
BEGIN;
|
|
UPDATE t1 SET a=a+1000 WHERE i=1;
|
|
UPDATE t1 SET b='x' WHERE i=1;
|
|
ROLLBACK;
|
|
BEGIN;
|
|
INSERT INTO t1
|
|
VALUES (1, 101, @b), (1, 102, @b), (1, 103, @b), (1, 104, @b), (1, 105, @b);
|
|
UPDATE t1 SET a=a-104 WHERE i=1 AND a > 100;
|
|
ERROR 23000: Duplicate entry '1-1' for key 'PRIMARY'
|
|
UPDATE t1 SET a=a+10 WHERE i=1 AND a > 100;
|
|
COMMIT;
|
|
SELECT a, length(b) FROM t1 WHERE i=1 AND a >= 100 ORDER BY a;
|
|
a length(b)
|
|
111 10
|
|
112 10
|
|
113 10
|
|
114 10
|
|
115 10
|
|
SET @b= REPEAT('$', 100);
|
|
BEGIN;
|
|
INSERT INTO t1 VALUES (2, 1, @b);
|
|
SAVEPOINT s1;
|
|
INSERT INTO t1 VALUES (2, 2, @b);
|
|
SAVEPOINT s2;
|
|
INSERT INTO t1 VALUES (2, 3, @b);
|
|
SAVEPOINT s3;
|
|
INSERT INTO t1 VALUES (2, 4, @b);
|
|
ROLLBACK TO s2;
|
|
INSERT INTO t1 VALUES (2, 5, @b);
|
|
ROLLBACK TO s2;
|
|
INSERT INTO t1 VALUES (2, 6, @b);
|
|
SAVEPOINT s4;
|
|
INSERT INTO t1 VALUES (2, 7, @b);
|
|
SAVEPOINT s5;
|
|
ROLLBACK TO s5;
|
|
INSERT INTO t1 VALUES (2, 8, @b);
|
|
COMMIT;
|
|
SELECT a, length(b) FROM t1 WHERE i=2 ORDER BY a;
|
|
a length(b)
|
|
1 100
|
|
2 100
|
|
6 100
|
|
7 100
|
|
8 100
|
|
BEGIN;
|
|
INSERT INTO t1 VALUES (2, 10, @b);
|
|
SAVEPOINT s10;
|
|
INSERT INTO t1 VALUES (2, 11, @b);
|
|
INSERT INTO t2 VALUES (2, 12, @b);
|
|
ROLLBACK TO s10;
|
|
Warnings:
|
|
Warning 1196 Some non-transactional changed tables couldn't be rolled back
|
|
COMMIT;
|
|
SELECT a, length(b) FROM t1 WHERE i=2 AND a>=10 ORDER BY a;
|
|
a length(b)
|
|
10 100
|
|
SELECT a, length(b) FROM t2 WHERE i=2 ORDER BY a;
|
|
a length(b)
|
|
12 100
|
|
BEGIN;
|
|
UPDATE t1 SET a=a+1000 WHERE i=2;
|
|
UPDATE t1 SET b='x' WHERE i=2;
|
|
ROLLBACK;
|
|
BEGIN;
|
|
INSERT INTO t1
|
|
VALUES (2, 101, @b), (2, 102, @b), (2, 103, @b), (2, 104, @b), (2, 105, @b);
|
|
UPDATE t1 SET a=a-104 WHERE i=2 AND a > 100;
|
|
ERROR 23000: Duplicate entry '2-1' for key 'PRIMARY'
|
|
UPDATE t1 SET a=a+10 WHERE i=2 AND a > 100;
|
|
COMMIT;
|
|
SELECT a, length(b) FROM t1 WHERE i=2 AND a >= 100 ORDER BY a;
|
|
a length(b)
|
|
111 100
|
|
112 100
|
|
113 100
|
|
114 100
|
|
115 100
|
|
SET @b= REPEAT('$', 642);
|
|
BEGIN;
|
|
INSERT INTO t1 VALUES (3, 1, @b);
|
|
SAVEPOINT s1;
|
|
INSERT INTO t1 VALUES (3, 2, @b);
|
|
SAVEPOINT s2;
|
|
INSERT INTO t1 VALUES (3, 3, @b);
|
|
SAVEPOINT s3;
|
|
INSERT INTO t1 VALUES (3, 4, @b);
|
|
ROLLBACK TO s2;
|
|
INSERT INTO t1 VALUES (3, 5, @b);
|
|
ROLLBACK TO s2;
|
|
INSERT INTO t1 VALUES (3, 6, @b);
|
|
SAVEPOINT s4;
|
|
INSERT INTO t1 VALUES (3, 7, @b);
|
|
SAVEPOINT s5;
|
|
ROLLBACK TO s5;
|
|
INSERT INTO t1 VALUES (3, 8, @b);
|
|
COMMIT;
|
|
SELECT a, length(b) FROM t1 WHERE i=3 ORDER BY a;
|
|
a length(b)
|
|
1 642
|
|
2 642
|
|
6 642
|
|
7 642
|
|
8 642
|
|
BEGIN;
|
|
INSERT INTO t1 VALUES (3, 10, @b);
|
|
SAVEPOINT s10;
|
|
INSERT INTO t1 VALUES (3, 11, @b);
|
|
INSERT INTO t2 VALUES (3, 12, @b);
|
|
ROLLBACK TO s10;
|
|
Warnings:
|
|
Warning 1196 Some non-transactional changed tables couldn't be rolled back
|
|
COMMIT;
|
|
SELECT a, length(b) FROM t1 WHERE i=3 AND a>=10 ORDER BY a;
|
|
a length(b)
|
|
10 642
|
|
SELECT a, length(b) FROM t2 WHERE i=3 ORDER BY a;
|
|
a length(b)
|
|
12 642
|
|
BEGIN;
|
|
UPDATE t1 SET a=a+1000 WHERE i=3;
|
|
UPDATE t1 SET b='x' WHERE i=3;
|
|
ROLLBACK;
|
|
BEGIN;
|
|
INSERT INTO t1
|
|
VALUES (3, 101, @b), (3, 102, @b), (3, 103, @b), (3, 104, @b), (3, 105, @b);
|
|
UPDATE t1 SET a=a-104 WHERE i=3 AND a > 100;
|
|
ERROR 23000: Duplicate entry '3-1' for key 'PRIMARY'
|
|
UPDATE t1 SET a=a+10 WHERE i=3 AND a > 100;
|
|
COMMIT;
|
|
SELECT a, length(b) FROM t1 WHERE i=3 AND a >= 100 ORDER BY a;
|
|
a length(b)
|
|
111 642
|
|
112 642
|
|
113 642
|
|
114 642
|
|
115 642
|
|
SET @b= REPEAT('$', 3930);
|
|
BEGIN;
|
|
INSERT INTO t1 VALUES (4, 1, @b);
|
|
SAVEPOINT s1;
|
|
INSERT INTO t1 VALUES (4, 2, @b);
|
|
SAVEPOINT s2;
|
|
INSERT INTO t1 VALUES (4, 3, @b);
|
|
SAVEPOINT s3;
|
|
INSERT INTO t1 VALUES (4, 4, @b);
|
|
ROLLBACK TO s2;
|
|
INSERT INTO t1 VALUES (4, 5, @b);
|
|
ROLLBACK TO s2;
|
|
INSERT INTO t1 VALUES (4, 6, @b);
|
|
SAVEPOINT s4;
|
|
INSERT INTO t1 VALUES (4, 7, @b);
|
|
SAVEPOINT s5;
|
|
ROLLBACK TO s5;
|
|
INSERT INTO t1 VALUES (4, 8, @b);
|
|
COMMIT;
|
|
SELECT a, length(b) FROM t1 WHERE i=4 ORDER BY a;
|
|
a length(b)
|
|
1 3930
|
|
2 3930
|
|
6 3930
|
|
7 3930
|
|
8 3930
|
|
BEGIN;
|
|
INSERT INTO t1 VALUES (4, 10, @b);
|
|
SAVEPOINT s10;
|
|
INSERT INTO t1 VALUES (4, 11, @b);
|
|
INSERT INTO t2 VALUES (4, 12, @b);
|
|
ROLLBACK TO s10;
|
|
Warnings:
|
|
Warning 1196 Some non-transactional changed tables couldn't be rolled back
|
|
COMMIT;
|
|
SELECT a, length(b) FROM t1 WHERE i=4 AND a>=10 ORDER BY a;
|
|
a length(b)
|
|
10 3930
|
|
SELECT a, length(b) FROM t2 WHERE i=4 ORDER BY a;
|
|
a length(b)
|
|
12 3930
|
|
BEGIN;
|
|
UPDATE t1 SET a=a+1000 WHERE i=4;
|
|
UPDATE t1 SET b='x' WHERE i=4;
|
|
ROLLBACK;
|
|
BEGIN;
|
|
INSERT INTO t1
|
|
VALUES (4, 101, @b), (4, 102, @b), (4, 103, @b), (4, 104, @b), (4, 105, @b);
|
|
UPDATE t1 SET a=a-104 WHERE i=4 AND a > 100;
|
|
ERROR 23000: Duplicate entry '4-1' for key 'PRIMARY'
|
|
UPDATE t1 SET a=a+10 WHERE i=4 AND a > 100;
|
|
COMMIT;
|
|
SELECT a, length(b) FROM t1 WHERE i=4 AND a >= 100 ORDER BY a;
|
|
a length(b)
|
|
111 3930
|
|
112 3930
|
|
113 3930
|
|
114 3930
|
|
115 3930
|
|
SET @b= REPEAT('$', 16000);
|
|
BEGIN;
|
|
INSERT INTO t1 VALUES (5, 1, @b);
|
|
SAVEPOINT s1;
|
|
INSERT INTO t1 VALUES (5, 2, @b);
|
|
SAVEPOINT s2;
|
|
INSERT INTO t1 VALUES (5, 3, @b);
|
|
SAVEPOINT s3;
|
|
INSERT INTO t1 VALUES (5, 4, @b);
|
|
ROLLBACK TO s2;
|
|
INSERT INTO t1 VALUES (5, 5, @b);
|
|
ROLLBACK TO s2;
|
|
INSERT INTO t1 VALUES (5, 6, @b);
|
|
SAVEPOINT s4;
|
|
INSERT INTO t1 VALUES (5, 7, @b);
|
|
SAVEPOINT s5;
|
|
ROLLBACK TO s5;
|
|
INSERT INTO t1 VALUES (5, 8, @b);
|
|
COMMIT;
|
|
SELECT a, length(b) FROM t1 WHERE i=5 ORDER BY a;
|
|
a length(b)
|
|
1 16000
|
|
2 16000
|
|
6 16000
|
|
7 16000
|
|
8 16000
|
|
BEGIN;
|
|
INSERT INTO t1 VALUES (5, 10, @b);
|
|
SAVEPOINT s10;
|
|
INSERT INTO t1 VALUES (5, 11, @b);
|
|
INSERT INTO t2 VALUES (5, 12, @b);
|
|
ROLLBACK TO s10;
|
|
Warnings:
|
|
Warning 1196 Some non-transactional changed tables couldn't be rolled back
|
|
COMMIT;
|
|
SELECT a, length(b) FROM t1 WHERE i=5 AND a>=10 ORDER BY a;
|
|
a length(b)
|
|
10 16000
|
|
SELECT a, length(b) FROM t2 WHERE i=5 ORDER BY a;
|
|
a length(b)
|
|
12 16000
|
|
BEGIN;
|
|
UPDATE t1 SET a=a+1000 WHERE i=5;
|
|
UPDATE t1 SET b='x' WHERE i=5;
|
|
ROLLBACK;
|
|
BEGIN;
|
|
INSERT INTO t1
|
|
VALUES (5, 101, @b), (5, 102, @b), (5, 103, @b), (5, 104, @b), (5, 105, @b);
|
|
UPDATE t1 SET a=a-104 WHERE i=5 AND a > 100;
|
|
ERROR 23000: Duplicate entry '5-1' for key 'PRIMARY'
|
|
UPDATE t1 SET a=a+10 WHERE i=5 AND a > 100;
|
|
COMMIT;
|
|
SELECT a, length(b) FROM t1 WHERE i=5 AND a >= 100 ORDER BY a;
|
|
a length(b)
|
|
111 16000
|
|
112 16000
|
|
113 16000
|
|
114 16000
|
|
115 16000
|
|
SET @b= REPEAT('$', 40000);
|
|
BEGIN;
|
|
INSERT INTO t1 VALUES (6, 1, @b);
|
|
SAVEPOINT s1;
|
|
INSERT INTO t1 VALUES (6, 2, @b);
|
|
SAVEPOINT s2;
|
|
INSERT INTO t1 VALUES (6, 3, @b);
|
|
SAVEPOINT s3;
|
|
INSERT INTO t1 VALUES (6, 4, @b);
|
|
ROLLBACK TO s2;
|
|
INSERT INTO t1 VALUES (6, 5, @b);
|
|
ROLLBACK TO s2;
|
|
INSERT INTO t1 VALUES (6, 6, @b);
|
|
SAVEPOINT s4;
|
|
INSERT INTO t1 VALUES (6, 7, @b);
|
|
SAVEPOINT s5;
|
|
ROLLBACK TO s5;
|
|
INSERT INTO t1 VALUES (6, 8, @b);
|
|
COMMIT;
|
|
SELECT a, length(b) FROM t1 WHERE i=6 ORDER BY a;
|
|
a length(b)
|
|
1 40000
|
|
2 40000
|
|
6 40000
|
|
7 40000
|
|
8 40000
|
|
BEGIN;
|
|
INSERT INTO t1 VALUES (6, 10, @b);
|
|
SAVEPOINT s10;
|
|
INSERT INTO t1 VALUES (6, 11, @b);
|
|
INSERT INTO t2 VALUES (6, 12, @b);
|
|
ROLLBACK TO s10;
|
|
Warnings:
|
|
Warning 1196 Some non-transactional changed tables couldn't be rolled back
|
|
COMMIT;
|
|
SELECT a, length(b) FROM t1 WHERE i=6 AND a>=10 ORDER BY a;
|
|
a length(b)
|
|
10 40000
|
|
SELECT a, length(b) FROM t2 WHERE i=6 ORDER BY a;
|
|
a length(b)
|
|
12 40000
|
|
BEGIN;
|
|
UPDATE t1 SET a=a+1000 WHERE i=6;
|
|
UPDATE t1 SET b='x' WHERE i=6;
|
|
ROLLBACK;
|
|
BEGIN;
|
|
INSERT INTO t1
|
|
VALUES (6, 101, @b), (6, 102, @b), (6, 103, @b), (6, 104, @b), (6, 105, @b);
|
|
UPDATE t1 SET a=a-104 WHERE i=6 AND a > 100;
|
|
ERROR 23000: Duplicate entry '6-1' for key 'PRIMARY'
|
|
UPDATE t1 SET a=a+10 WHERE i=6 AND a > 100;
|
|
COMMIT;
|
|
SELECT a, length(b) FROM t1 WHERE i=6 AND a >= 100 ORDER BY a;
|
|
a length(b)
|
|
111 40000
|
|
112 40000
|
|
113 40000
|
|
114 40000
|
|
115 40000
|
|
include/save_master_gtid.inc
|
|
connection slave;
|
|
include/sync_with_master_gtid.inc
|
|
*** Slave data checksums with master, all ok. ***
|
|
connection master;
|
|
DROP TABLE t1, t2;
|
|
include/rpl_end.inc
|