mirror of
https://github.com/MariaDB/server.git
synced 2026-02-08 19:58:40 +01:00
3fbb3591bc
When using the InnoDB-implemented binlog with another transactional storage engine, or with explicit user XA transactions, recover such transactions consistently from the binlog at server startup. When a transaction is prepared with an XID, the binlog records a "prepare" record containing the XID and link to the out-of-band replication event data. When a previously prepared transaction is committed, the commit record links to the oob data referenced from the prepare record, and the record is preceeded by an "XA complete" record containing the XID. If instead a prepared transaction is rolled back, just an "XA complete" record is binlogged with the XID and a "rollback" flag. While any prepared XA transactions are active, maintain in-memory reference counts in each binlog file, and in each binlog file record the file_no of the earliest binlog file containing any XID records of still active transactions. When the server restarts (possibly after crash), look up the file_no of the earliest binlog file that may contain active XID records, if any. Scan the binlogs from that point and record any XID prepare or complete records. For any XID prepare record, record oob data and reference count, recovering the in-memory state present before the server restart. Return a hash to the server layer containing each active XID in the binlog and its state (prepared, committed, rolled back). On the server layer, ask each engine for a list of pending XID in prepared state. If the binlog state of an XID is committed, commit in the engine. If the binlog state is rolled back or is missing, roll back in the engine. If the binlog state is prepared, _and_ all participating engines have the transaction prepared also, then leave the transaction prepared. If a binlog prepared transaction is missing from an engine, then roll it back in any other engines and in the binlog (this is to handle a crash in the middle of an XA PREPARE). The result is that multi-engine (or non-InnoDB) transactions, as well as user XA transactions, will be recovered after a crash consisent with the binlog content. Signed-off-by: Kristian Nielsen <knielsen@knielsen-hq.org>
262 lines
7.9 KiB
Text
262 lines
7.9 KiB
Text
include/reset_master.inc
|
|
CREATE TABLE t1(a INT PRIMARY KEY, b INT) ENGINE=InnoDB;
|
|
*** Test basic XA COMMIT from same or different client connection.
|
|
connect con1,localhost,root,,;
|
|
XA START 'a';
|
|
INSERT INTO t1 VALUES (1, 0);
|
|
INSERT INTO t1 VALUES (2, 0);
|
|
INSERT INTO t1 VALUES (3, 0);
|
|
XA END 'a';
|
|
XA PREPARE 'a';
|
|
connection default;
|
|
disconnect con1;
|
|
RESET MASTER;
|
|
ERROR HY000: Cannot execute RESET MASTER as the binlog is in use by an active transaction
|
|
connect con2,localhost,root,,;
|
|
XA COMMIT 'a';
|
|
connect con1_roll,localhost,root,,;
|
|
XA START 'a_roll';
|
|
INSERT INTO t1 VALUES (1001, 0);
|
|
INSERT INTO t1 VALUES (1002, 0);
|
|
INSERT INTO t1 VALUES (1003, 0);
|
|
XA END 'a_roll';
|
|
XA PREPARE 'a_roll';
|
|
connection default;
|
|
disconnect con1_roll;
|
|
connection con2;
|
|
XA ROLLBACK 'a_roll';
|
|
connection default;
|
|
XA START 'b';
|
|
UPDATE t1 SET b=2 WHERE a=1;
|
|
XA END 'b';
|
|
XA PREPARE 'b';
|
|
XA COMMIT 'b';
|
|
XA START 'b_roll';
|
|
UPDATE t1 SET b=3 WHERE a=1;
|
|
XA END 'b_roll';
|
|
XA PREPARE 'b_roll';
|
|
XA ROLLBACK 'b_roll';
|
|
XA START 'c';
|
|
UPDATE t1 SET b=3 WHERE a=3;
|
|
XA END 'c';
|
|
XA COMMIT 'c' ONE PHASE;
|
|
SELECT * FROM t1 ORDER BY a;
|
|
a b
|
|
1 2
|
|
2 0
|
|
3 3
|
|
CREATE TABLE t2 (a INT) ENGINE=MyISAM;
|
|
CREATE TABLE t3 (id INT PRIMARY KEY) ENGINE=InnoDB;
|
|
INSERT INTO t2 VALUES (1),(2);
|
|
INSERT INTO t3 VALUES (1),(2);
|
|
XA START '1';
|
|
REPLACE INTO t2 SELECT * FROM t2;
|
|
REPLACE INTO t3 SELECT * FROM t3;
|
|
XA END '1';
|
|
XA PREPARE '1';
|
|
XA RECOVER FORMAT='SQL';
|
|
formatID gtrid_length bqual_length data
|
|
1 1 0 '1'
|
|
XA ROLLBACK '1';
|
|
Warnings:
|
|
Warning 1196 Some non-transactional changed tables couldn't be rolled back
|
|
DROP TABLE t2, t3;
|
|
CREATE TABLE t2 (a INT PRIMARY KEY) ENGINE=InnoDB;
|
|
INSERT INTO t2 VALUES (1);
|
|
CREATE SEQUENCE s ENGINE=InnoDB;
|
|
XA START '2';
|
|
SELECT NEXT VALUE FOR s;
|
|
NEXT VALUE FOR s
|
|
1
|
|
REPLACE INTO t2 SELECT * FROM t2;
|
|
XA END '2';
|
|
XA PREPARE '2';
|
|
XA RECOVER FORMAT='SQL';
|
|
formatID gtrid_length bqual_length data
|
|
1 1 0 '2'
|
|
XA ROLLBACK '2';
|
|
DROP SEQUENCE s;
|
|
DROP TABLE t2;
|
|
include/show_binlog_events.inc
|
|
Log_name Pos Event_type Server_id End_log_pos Info
|
|
binlog-000000.ibb # Gtid # # BEGIN GTID #-#-#
|
|
binlog-000000.ibb # Query # # use `test`; INSERT INTO t1 VALUES (1, 0)
|
|
binlog-000000.ibb # Query # # use `test`; INSERT INTO t1 VALUES (2, 0)
|
|
binlog-000000.ibb # Query # # use `test`; INSERT INTO t1 VALUES (3, 0)
|
|
binlog-000000.ibb # Query # # COMMIT
|
|
binlog-000000.ibb # Gtid # # BEGIN GTID #-#-#
|
|
binlog-000000.ibb # Query # # use `test`; UPDATE t1 SET b=2 WHERE a=1
|
|
binlog-000000.ibb # Query # # COMMIT
|
|
binlog-000000.ibb # Gtid # # BEGIN GTID #-#-#
|
|
binlog-000000.ibb # Query # # use `test`; UPDATE t1 SET b=3 WHERE a=3
|
|
binlog-000000.ibb # Xid # # COMMIT /* XID */
|
|
binlog-000000.ibb # Gtid # # GTID #-#-#
|
|
binlog-000000.ibb # Query # # use `test`; CREATE TABLE t2 (a INT) ENGINE=MyISAM
|
|
binlog-000000.ibb # Gtid # # GTID #-#-#
|
|
binlog-000000.ibb # Query # # use `test`; CREATE TABLE t3 (id INT PRIMARY KEY) ENGINE=InnoDB
|
|
binlog-000000.ibb # Gtid # # BEGIN GTID #-#-#
|
|
binlog-000000.ibb # Query # # use `test`; INSERT INTO t2 VALUES (1),(2)
|
|
binlog-000000.ibb # Query # # COMMIT
|
|
binlog-000000.ibb # Gtid # # BEGIN GTID #-#-#
|
|
binlog-000000.ibb # Query # # use `test`; INSERT INTO t3 VALUES (1),(2)
|
|
binlog-000000.ibb # Xid # # COMMIT /* XID */
|
|
binlog-000000.ibb # Gtid # # BEGIN GTID #-#-#
|
|
binlog-000000.ibb # Annotate_rows # # REPLACE INTO t2 SELECT * FROM t2
|
|
binlog-000000.ibb # Table_map # # table_id: # (test.t2)
|
|
binlog-000000.ibb # Write_rows_v1 # # table_id: # flags: STMT_END_F
|
|
binlog-000000.ibb # Query # # COMMIT
|
|
binlog-000000.ibb # Gtid # # GTID #-#-#
|
|
binlog-000000.ibb # Query # # use `test`; DROP TABLE `t2`,`t3` /* generated by server */
|
|
binlog-000000.ibb # Gtid # # GTID #-#-#
|
|
binlog-000000.ibb # Query # # use `test`; CREATE TABLE t2 (a INT PRIMARY KEY) ENGINE=InnoDB
|
|
binlog-000000.ibb # Gtid # # BEGIN GTID #-#-#
|
|
binlog-000000.ibb # Query # # use `test`; INSERT INTO t2 VALUES (1)
|
|
binlog-000000.ibb # Xid # # COMMIT /* XID */
|
|
binlog-000000.ibb # Gtid # # GTID #-#-#
|
|
binlog-000000.ibb # Query # # use `test`; CREATE SEQUENCE s ENGINE=InnoDB
|
|
binlog-000000.ibb # Gtid # # BEGIN GTID #-#-#
|
|
binlog-000000.ibb # Annotate_rows # # SELECT NEXT VALUE FOR s
|
|
binlog-000000.ibb # Table_map # # table_id: # (test.s)
|
|
binlog-000000.ibb # Write_rows_v1 # # table_id: # flags: STMT_END_F
|
|
binlog-000000.ibb # Query # # COMMIT
|
|
binlog-000000.ibb # Gtid # # GTID #-#-#
|
|
binlog-000000.ibb # Query # # use `test`; DROP SEQUENCE `s` /* generated by server */
|
|
binlog-000000.ibb # Gtid # # GTID #-#-#
|
|
binlog-000000.ibb # Query # # use `test`; DROP TABLE `t2` /* generated by server */
|
|
*** Test that we will not purge a file that is needed by an active XA transaction.
|
|
connection default;
|
|
ALTER TABLE t1 ADD COLUMN c LONGBLOB;
|
|
UPDATE t1 SET b=10 WHERE a=1;
|
|
connection con2;
|
|
SET SESSION binlog_format=ROW;
|
|
XA START 'd';
|
|
INSERT INTO t1 VALUES (10, 2, REPEAT('#', 40000));
|
|
connect con3,localhost,root,,;
|
|
SET SESSION binlog_format=ROW;
|
|
XA START 'e';
|
|
INSERT INTO t1 VALUES (110, 2, REPEAT('#', 40000));
|
|
connection default;
|
|
UPDATE t1 SET b=11 WHERE a=1;
|
|
FLUSH BINARY LOGS;
|
|
UPDATE t1 SET b=12 WHERE a=1;
|
|
connection con2;
|
|
INSERT INTO t1 VALUES (11, 2, REPEAT('*', 40000));
|
|
connection con3;
|
|
INSERT INTO t1 VALUES (111, 2, REPEAT('*', 40000));
|
|
connection default;
|
|
UPDATE t1 SET b=13 WHERE a=1;
|
|
FLUSH BINARY LOGS;
|
|
UPDATE t1 SET b=14 WHERE a=1;
|
|
UPDATE t1 SET b=15 WHERE a=1;
|
|
FLUSH BINARY LOGS;
|
|
UPDATE t1 SET b=16 WHERE a=1;
|
|
FLUSH BINARY LOGS;
|
|
connection default;
|
|
SET @old_min_slaves= @@GLOBAL.slave_connections_needed_for_purge;
|
|
SET GLOBAL slave_connections_needed_for_purge= 0;
|
|
PURGE BINARY LOGS TO 'binlog-000001.ibb';
|
|
ERROR HY000: A purgeable log is in use, will not purge
|
|
connection con2;
|
|
XA END 'd';
|
|
XA PREPARE 'd';
|
|
connection con3;
|
|
XA END 'e';
|
|
XA PREPARE 'e';
|
|
connection default;
|
|
PURGE BINARY LOGS TO 'binlog-000001.ibb';
|
|
ERROR HY000: A purgeable log is in use, will not purge
|
|
connection con2;
|
|
XA COMMIT 'd';
|
|
connection con3;
|
|
XA ROLLBACK 'e';
|
|
connection default;
|
|
PURGE BINARY LOGS TO 'binlog-000001.ibb';
|
|
FLUSH BINARY LOGS;
|
|
UPDATE t1 SET b=17 WHERE a=1;
|
|
FLUSH BINARY LOGS;
|
|
UPDATE t1 SET b=18 WHERE a=1;
|
|
PURGE BINARY LOGS TO 'binlog-000001.ibb';
|
|
SET GLOBAL slave_connections_needed_for_purge= @old_min_slaves;
|
|
*** Test disconnecting in XA transaction before XA PREPARE.
|
|
connection con2;
|
|
SET SESSION binlog_format=MIXED;
|
|
XA BEGIN 'f';
|
|
CREATE TEMPORARY TABLE tmp ENGINE=InnoDB AS SELECT a FROM t1;
|
|
disconnect con2;
|
|
connection con3;
|
|
SET SESSION binlog_format=MIXED;
|
|
XA BEGIN 'g';
|
|
UPDATE t1 SET b=19 WHERE a=1;
|
|
XA END 'g';
|
|
disconnect con3;
|
|
connection default;
|
|
BEGIN;
|
|
SELECT b FROM t1 WHERE a=1 FOR UPDATE;
|
|
b
|
|
18
|
|
ROLLBACK;
|
|
*** Test duplicate SAVEPOINT with XA
|
|
XA BEGIN 'x';
|
|
DELETE FROM t1;
|
|
SAVEPOINT A;
|
|
SAVEPOINT B;
|
|
SAVEPOINT A;
|
|
ROLLBACK TO SAVEPOINT B;
|
|
XA END 'x';
|
|
XA ROLLBACK 'x';
|
|
*** Test server shutdown after XA PREPARE.
|
|
XA BEGIN 'y';
|
|
INSERT INTO t1 VALUES (201, 0, '');
|
|
XA END 'y';
|
|
XA PREPARE 'y';
|
|
# restart
|
|
XA ROLLBACK 'y';
|
|
*** Test binlogging of empty XA transaction.
|
|
SET binlog_format=row;
|
|
DELETE FROM t1 WHERE a=4242;
|
|
INSERT INTO t1 VALUES (4242, 0, 'z1');
|
|
XA BEGIN 'z';
|
|
SELECT a, b, LENGTH(c) FROM t1 ORDER BY a LIMIT 3;
|
|
a b LENGTH(c)
|
|
1 18 NULL
|
|
2 0 NULL
|
|
3 3 NULL
|
|
INSERT IGNORE INTO t1 VALUES (4242, 0, 'z2');
|
|
Warnings:
|
|
Warning 1062 Duplicate entry '4242' for key 'PRIMARY'
|
|
SELECT a, b, LENGTH(c) FROM t1 WHERE a>3 ORDER BY a LIMIT 1;
|
|
a b LENGTH(c)
|
|
10 2 40000
|
|
XA END 'z';
|
|
XA PREPARE 'z';
|
|
XA COMMIT 'z';
|
|
XA BEGIN 'z';
|
|
UPDATE t1 SET b=10 WHERE a=4242;
|
|
XA END 'z';
|
|
XA PREPARE 'z';
|
|
XA COMMIT 'z';
|
|
SELECT a, b, LENGTH(c) FROM t1 WHERE a=4242;
|
|
a b LENGTH(c)
|
|
4242 10 2
|
|
*** Test XA PREPARE when the transaction is empty because statements rolled back on error
|
|
CREATE TRIGGER u AFTER INSERT ON t1 FOR EACH ROW BEGIN RELEASE SAVEPOINT A ; END //
|
|
XA BEGIN 'xid44';
|
|
INSERT IGNORE INTO t1(a) VALUES (10000);
|
|
ERROR 42000: SAVEPOINT A does not exist
|
|
XA END 'xid44';
|
|
XA PREPARE 'xid44';
|
|
XA COMMIT 'xid44';
|
|
DROP TRIGGER u;
|
|
XA BEGIN 'xid44';
|
|
SAVEPOINT A;
|
|
ROLLBACK TO A;
|
|
ROLLBACK TO A;
|
|
INSERT INTO t1(a) VALUES (10001);
|
|
XA END 'xid44';
|
|
XA PREPARE 'xid44';
|
|
# restart
|
|
XA COMMIT 'xid44';
|
|
SELECT * FROM t1 WHERE a >= 10000 ORDER BY a;
|
|
a b c
|
|
10001 NULL NULL
|
|
DROP TABLE t1;
|