From bdcecfa22cc2eb255610f5574ff0a91c8d247066 Mon Sep 17 00:00:00 2001 From: Daniele Sciascia Date: Mon, 16 Mar 2020 15:03:00 +0100 Subject: MDEV-22021: Galera database could get inconsistent with rollback to savepoint When binlog is disabled, WSREP will not behave correctly when SAVEPOINT ROLLBACK is executed and we will not rollback transaction. --- mysql-test/suite/galera/r/MDEV-22021.result | 57 ++++++++++++++++++++ mysql-test/suite/galera/t/MDEV-22021.combinations | 4 ++ mysql-test/suite/galera/t/MDEV-22021.test | 60 ++++++++++++++++++++++ mysql-test/suite/galera_sr/r/GCF-571.result | 6 +-- mysql-test/suite/galera_sr/r/GCF-620.result | 2 +- .../r/galera_sr_rollback_savepoint.result | 2 +- .../galera_sr/r/mysql-wsrep-features#22.result | 2 +- 7 files changed, 127 insertions(+), 6 deletions(-) create mode 100644 mysql-test/suite/galera/r/MDEV-22021.result create mode 100644 mysql-test/suite/galera/t/MDEV-22021.combinations create mode 100644 mysql-test/suite/galera/t/MDEV-22021.test (limited to 'mysql-test') diff --git a/mysql-test/suite/galera/r/MDEV-22021.result b/mysql-test/suite/galera/r/MDEV-22021.result new file mode 100644 index 00000000000..a217e66e2f0 --- /dev/null +++ b/mysql-test/suite/galera/r/MDEV-22021.result @@ -0,0 +1,57 @@ +connection node_2; +connection node_1; +CREATE TABLE t1 (f1 INTEGER PRIMARY KEY); +START TRANSACTION; +INSERT INTO t1 VALUES (1); +SAVEPOINT sp1; +INSERT INTO t1 VALUES (2); +ROLLBACK TO SAVEPOINT sp1; +COMMIT; +SELECT COUNT(*) = 1 FROM t1; +COUNT(*) = 1 +1 +connection node_2; +SELECT COUNT(*) = 1 FROM t1; +COUNT(*) = 1 +1 +connection node_1; +DELETE FROM t1; +START TRANSACTION; +SAVEPOINT sp1; +INSERT INTO t1 VALUES (1); +SAVEPOINT sp2; +INSERT INTO t1 VALUES (2); +ROLLBACK TO SAVEPOINT sp2; +ROLLBACK TO SAVEPOINT sp1; +COMMIT; +SELECT COUNT(*) = 0 FROM t1; +COUNT(*) = 0 +1 +connection node_2; +SELECT COUNT(*) = 0 FROM t1; +COUNT(*) = 0 +1 +connection node_1; +DELETE FROM t1; +START TRANSACTION; +SAVEPOINT sp1; +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (2); +INSERT INTO t1 VALUES (3); +INSERT INTO t1 VALUES (4); +SAVEPOINT sp2; +INSERT INTO t1 VALUES (5); +ROLLBACK TO SAVEPOINT sp2; +INSERT INTO t1 VALUES (6); +INSERT INTO t1 VALUES (7); +ROLLBACK TO SAVEPOINT sp1; +INSERT INTO t1 VALUES (8); +COMMIT; +SELECT COUNT(*) = 1 FROM t1; +COUNT(*) = 1 +1 +connection node_2; +SELECT COUNT(*) = 1 FROM t1; +COUNT(*) = 1 +1 +DROP TABLE t1; diff --git a/mysql-test/suite/galera/t/MDEV-22021.combinations b/mysql-test/suite/galera/t/MDEV-22021.combinations new file mode 100644 index 00000000000..1eeb8fb4614 --- /dev/null +++ b/mysql-test/suite/galera/t/MDEV-22021.combinations @@ -0,0 +1,4 @@ +[binlogoff] + +[binlogon] +log-bin diff --git a/mysql-test/suite/galera/t/MDEV-22021.test b/mysql-test/suite/galera/t/MDEV-22021.test new file mode 100644 index 00000000000..5e189faf5ea --- /dev/null +++ b/mysql-test/suite/galera/t/MDEV-22021.test @@ -0,0 +1,60 @@ +# +# SAVEPOINT ROLLBACK can introduce inconsistency in cluster. +# + +--source include/galera_cluster.inc + +CREATE TABLE t1 (f1 INTEGER PRIMARY KEY); + +START TRANSACTION; +INSERT INTO t1 VALUES (1); +SAVEPOINT sp1; +INSERT INTO t1 VALUES (2); +ROLLBACK TO SAVEPOINT sp1; +COMMIT; + +SELECT COUNT(*) = 1 FROM t1; + +--connection node_2 +SELECT COUNT(*) = 1 FROM t1; + +--connection node_1 +DELETE FROM t1; + +START TRANSACTION; +SAVEPOINT sp1; +INSERT INTO t1 VALUES (1); +SAVEPOINT sp2; +INSERT INTO t1 VALUES (2); +ROLLBACK TO SAVEPOINT sp2; +ROLLBACK TO SAVEPOINT sp1; +COMMIT; + +SELECT COUNT(*) = 0 FROM t1; +--connection node_2 +SELECT COUNT(*) = 0 FROM t1; + +--connection node_1 +DELETE FROM t1; + +START TRANSACTION; +SAVEPOINT sp1; +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (2); +INSERT INTO t1 VALUES (3); +INSERT INTO t1 VALUES (4); +SAVEPOINT sp2; +INSERT INTO t1 VALUES (5); +ROLLBACK TO SAVEPOINT sp2; +INSERT INTO t1 VALUES (6); +INSERT INTO t1 VALUES (7); +ROLLBACK TO SAVEPOINT sp1; +INSERT INTO t1 VALUES (8); +COMMIT; + +SELECT COUNT(*) = 1 FROM t1; + +--connection node_2 +SELECT COUNT(*) = 1 FROM t1; + +DROP TABLE t1; diff --git a/mysql-test/suite/galera_sr/r/GCF-571.result b/mysql-test/suite/galera_sr/r/GCF-571.result index 4b4f749d910..fd20afd7e6e 100644 --- a/mysql-test/suite/galera_sr/r/GCF-571.result +++ b/mysql-test/suite/galera_sr/r/GCF-571.result @@ -24,7 +24,7 @@ COUNT(*) > 0 1 SELECT COUNT(*) = 1 FROM mysql.wsrep_streaming_log WHERE frag LIKE '%SAVEPOINT `A`%'; COUNT(*) = 1 -0 +1 connection node_1; ROLLBACK TO SAVEPOINT A; connection node_1a; @@ -36,7 +36,7 @@ COUNT(*) > 0 1 SELECT COUNT(*) = 1 FROM mysql.wsrep_streaming_log WHERE frag LIKE '%SAVEPOINT `A`%'; COUNT(*) = 1 -0 +1 SELECT COUNT(*) = 1 FROM mysql.wsrep_streaming_log WHERE frag LIKE '%ROLLBACK TO `A`%'; COUNT(*) = 1 0 @@ -49,7 +49,7 @@ COUNT(*) > 0 1 SELECT COUNT(*) = 1 FROM mysql.wsrep_streaming_log WHERE frag LIKE '%SAVEPOINT `A`%'; COUNT(*) = 1 -0 +1 SELECT COUNT(*) = 1 FROM mysql.wsrep_streaming_log WHERE frag LIKE '%ROLLBACK TO `A`%'; COUNT(*) = 1 0 diff --git a/mysql-test/suite/galera_sr/r/GCF-620.result b/mysql-test/suite/galera_sr/r/GCF-620.result index 33789f82add..02cb163a199 100644 --- a/mysql-test/suite/galera_sr/r/GCF-620.result +++ b/mysql-test/suite/galera_sr/r/GCF-620.result @@ -14,5 +14,5 @@ COMMIT; connection node_2; SELECT COUNT(*) = 2 FROM t1; COUNT(*) = 2 -0 +1 DROP TABLE t1; diff --git a/mysql-test/suite/galera_sr/r/galera_sr_rollback_savepoint.result b/mysql-test/suite/galera_sr/r/galera_sr_rollback_savepoint.result index f2efa20f0d3..9db548c1616 100644 --- a/mysql-test/suite/galera_sr/r/galera_sr_rollback_savepoint.result +++ b/mysql-test/suite/galera_sr/r/galera_sr_rollback_savepoint.result @@ -33,7 +33,7 @@ COUNT(*) = 5 1 SELECT COUNT(*) = 0 FROM t1 WHERE f2 = 'b'; COUNT(*) = 0 -0 +1 SELECT COUNT(*) = 5 FROM t1 WHERE f2 = 'c'; COUNT(*) = 5 1 diff --git a/mysql-test/suite/galera_sr/r/mysql-wsrep-features#22.result b/mysql-test/suite/galera_sr/r/mysql-wsrep-features#22.result index 0053619187c..1215f3341e7 100644 --- a/mysql-test/suite/galera_sr/r/mysql-wsrep-features#22.result +++ b/mysql-test/suite/galera_sr/r/mysql-wsrep-features#22.result @@ -31,5 +31,5 @@ COUNT(*) = 6 connection node_2; SELECT COUNT(*) = 6 FROM t1; COUNT(*) = 6 -0 +1 DROP TABLE t1; -- cgit v1.2.1