mirror/mariadb
1
0
Fork 0
mirror of https://github.com/MariaDB/server.git synced 2026-02-20 17:49:02 +01:00
Code Issues Projects Releases Packages Wiki Activity
mariadb/mysql-test/suite/binlog_in_engine/savepoint.result
Kristian Nielsen 7081f2a58e Binlog-in-engine: New binlog implementation integrated in InnoDB 
Implement an improved binlog implementation that is integrated into
the storage engine. The new implementation is enabled with the
--binlog-storage-engine option. Initially the InnoDB storage engine
implements the binlog.

Integrating the binlog in the storage engine improves performance,
since it makes the InnoDB redo log the single source of truth and
avoids the need for expensive two-phase commit between binlog and
engine. It also makes it possible to disable durability (set
--innodb-flush-log-at-trx-commit=0) to further improve performance,
while still preserving the ability to recover the binlog and database
into a consistent state after a crash.

The new binlog implementation also greatly improves the internal
design and implementation of the binlog, and enables future
enhancements for replication.

This is a squash of the original 11.4-based patch series.

Signed-off-by: Kristian Nielsen <knielsen@knielsen-hq.org>
2026-01-23 03:21:03 +01:00

518 lines
12 KiB
Text
Raw Permalink Blame History

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. ***
*** Test re-using savepoint names in the same transaction
TRUNCATE TABLE t1;
INSERT INTO t1 VALUES (1, 0, 'a');
BEGIN;
INSERT INTO t1 VALUES (2, 0, 'b');
SAVEPOINT A;
SAVEPOINT B;
SAVEPOINT A;
ROLLBACK TO SAVEPOINT B;
COMMIT;
BEGIN;
SAVEPOINT s1;
INSERT INTO t1 VALUES (3, 1, 's1');
SAVEPOINT s2;
INSERT INTO t1 VALUES (4, 1, 's2');
SAVEPOINT s1;
SAVEPOINT s3;
INSERT INTO t1 VALUES (5, 1, 's3');
ROLLBACK TO SAVEPOINT s1;
SAVEPOINT s2;
INSERT INTO t1 VALUES (6, 2, REPEAT('<s2>', 10000));
SAVEPOINT s4;
INSERT INTO t1 VALUES (7, 2, 's4');
SAVEPOINT s1;
INSERT INTO t1 VALUES (8, 2, REPEAT('<s1>', 10000));
SAVEPOINT s3;
INSERT INTO t1 VALUES (9, 2, REPEAT('<s3>', 10000));
SAVEPOINT s2;
SAVEPOINT s5;
INSERT INTO t1 VALUES (10, 2, 's5');
SAVEPOINT s1;
INSERT INTO t1 VALUES (11, 2, REPEAT('<s1>', 10000));
SAVEPOINT s5;
SAVEPOINT s6;
INSERT INTO t1 VALUES (12, 2, 's6');
SAVEPOINT s4;
INSERT INTO t1 VALUES (13, 2, 's4');
ROLLBACK TO SAVEPOINT s6;
INSERT INTO t1 VALUES (14, 2, REPEAT('<s6>', 10000));
ROLLBACK TO SAVEPOINT s1;
COMMIT;
SELECT i, a, LENGTH(b) FROM t1 ORDER BY i, a;
i a LENGTH(b)
1 0 1
2 0 1
3 1 2
4 1 2
6 2 40000
7 2 2
8 2 40000
9 2 40000
10 2 2
*** Test an edge case where the savepoint is just at the cache-size boundary.
*** Test RELEASE SAVEPOINT.
BEGIN;
SAVEPOINT s1;
INSERT INTO t1(i, a) VALUES (20, 0);
SAVEPOINT s2;
INSERT INTO t1(i, a) VALUES (20, 1);
SAVEPOINT s3;
INSERT INTO t1(i, a, b) VALUES (20, 2, REPEAT('2', 40000));
SAVEPOINT s4;
SAVEPOINT s5;
INSERT INTO t1(i, a) VALUES (20, 3);
SAVEPOINT s6;
INSERT INTO t1(i, a, b) VALUES (20, 4, REPEAT('4', 40000));
RELEASE SAVEPOINT s5;
SAVEPOINT s7;
ROLLBACK TO s7;
SAVEPOINT s8;
INSERT INTO t1(i, a) VALUES (20, 5);
ROLLBACK TO s4;
INSERT INTO t1(i, a) VALUES (20, 6);
COMMIT;
SELECT i, a, LENGTH(b) FROM t1 WHERE i=20 ORDER BY a;
i a LENGTH(b)
20 0 NULL
20 1 NULL
20 2 40000
20 6 NULL
*** Test savepoint combined with non-transactional OOB spill.
BEGIN;
INSERT INTO t1 VALUES (30, 0, REPEAT('a', 40000));
INSERT INTO t1 VALUES (30, 1, REPEAT('b', 20000));
SAVEPOINT s1;
INSERT INTO t2 VALUES (30, 2, REPEAT('x', 63000));
COMMIT;
SELECT a, LENGTH(b) FROM t1 WHERE i=30 ORDER BY a;
a LENGTH(b)
0 40000
1 20000
SELECT a, LENGTH(b) FROM t2 WHERE i=30 ORDER BY a;
a LENGTH(b)
2 63000
connection master;
DROP TABLE t1, t2;
include/rpl_end.inc
Reference in a new issue View git blame Copy permalink