From b93cc42623ba99237c1c625e19e34a1f4419e899 Mon Sep 17 00:00:00 2001 From: Vasil Dimov Date: Tue, 5 Apr 2011 11:20:20 +0300 Subject: Add the testcase for Bug#59410 to 5.1/InnoDB Plugin Bug#59410 read uncommitted: unlock row could not find a 3 mode lock on the record This bug is present only in 5.6 but I am adding the test case to earlier versions to ensure it never appears in earlier versions too. --- .../suite/innodb_plugin/r/innodb_bug59410.result | 17 +++++++++++++++ .../suite/innodb_plugin/t/innodb_bug59410.test | 24 ++++++++++++++++++++++ 2 files changed, 41 insertions(+) create mode 100644 mysql-test/suite/innodb_plugin/r/innodb_bug59410.result create mode 100644 mysql-test/suite/innodb_plugin/t/innodb_bug59410.test (limited to 'mysql-test/suite/innodb_plugin') diff --git a/mysql-test/suite/innodb_plugin/r/innodb_bug59410.result b/mysql-test/suite/innodb_plugin/r/innodb_bug59410.result new file mode 100644 index 00000000000..494d601ba4f --- /dev/null +++ b/mysql-test/suite/innodb_plugin/r/innodb_bug59410.result @@ -0,0 +1,17 @@ +create table `bug59410_1`(`a` int)engine=innodb; +insert into `bug59410_1` values (1),(2),(3); +select 1 from `bug59410_1` where `a` <> any ( +select 1 from `bug59410_1` where `a` <> 1 for update) +for update; +1 +1 +1 +drop table bug59410_1; +create table bug59410_2(`a` char(1),`b` int)engine=innodb; +insert into bug59410_2 values('0',0); +set transaction isolation level read uncommitted; +start transaction; +set @a=(select b from bug59410_2 where +(select 1 from bug59410_2 where a group by @a=b) +group by @a:=b); +drop table bug59410_2; diff --git a/mysql-test/suite/innodb_plugin/t/innodb_bug59410.test b/mysql-test/suite/innodb_plugin/t/innodb_bug59410.test new file mode 100644 index 00000000000..30bb0642679 --- /dev/null +++ b/mysql-test/suite/innodb_plugin/t/innodb_bug59410.test @@ -0,0 +1,24 @@ +# +# Bug#59410 read uncommitted: unlock row could not find a 3 mode lock on the record +# +-- source include/have_innodb.inc + +# only interested that the following do not produce something like +# InnoDB: Error: unlock row could not find a 2 mode lock on the record +# in the error log + +create table `bug59410_1`(`a` int)engine=innodb; +insert into `bug59410_1` values (1),(2),(3); +select 1 from `bug59410_1` where `a` <> any ( +select 1 from `bug59410_1` where `a` <> 1 for update) +for update; +drop table bug59410_1; + +create table bug59410_2(`a` char(1),`b` int)engine=innodb; +insert into bug59410_2 values('0',0); +set transaction isolation level read uncommitted; +start transaction; +set @a=(select b from bug59410_2 where +(select 1 from bug59410_2 where a group by @a=b) +group by @a:=b); +drop table bug59410_2; -- cgit v1.2.1 From acedd7a4a5542288b30969ff5a6f11566458e9d4 Mon Sep 17 00:00:00 2001 From: Vasil Dimov Date: Wed, 6 Apr 2011 14:38:24 +0300 Subject: Load the innodb plugin instead of builtin in innodb_plugin.innodb_bug59410 Spotted by: Marko --- mysql-test/suite/innodb_plugin/t/innodb_bug59410.test | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'mysql-test/suite/innodb_plugin') diff --git a/mysql-test/suite/innodb_plugin/t/innodb_bug59410.test b/mysql-test/suite/innodb_plugin/t/innodb_bug59410.test index 30bb0642679..6eabe0a8403 100644 --- a/mysql-test/suite/innodb_plugin/t/innodb_bug59410.test +++ b/mysql-test/suite/innodb_plugin/t/innodb_bug59410.test @@ -1,7 +1,7 @@ # # Bug#59410 read uncommitted: unlock row could not find a 3 mode lock on the record # --- source include/have_innodb.inc +-- source include/have_innodb_plugin.inc # only interested that the following do not produce something like # InnoDB: Error: unlock row could not find a 2 mode lock on the record -- cgit v1.2.1 From 0ff2a182b67ea433d9070628696e648dff170fdd Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Marko=20M=C3=A4kel=C3=A4?= Date: Thu, 7 Apr 2011 21:12:54 +0300 Subject: Bug #11766513 - 59641: Prepared XA transaction in system after hard crash causes future shutdown hang InnoDB would hang on shutdown if any XA transactions exist in the system in the PREPARED state. This has been masked by the fact that MySQL would roll back any PREPARED transaction on shutdown, in the spirit of Bug #12161 Xa recovery and client disconnection. [mysql-test-run] do_shutdown_server: Interpret --shutdown_server 0 as a request to kill the server immediately without initiating a shutdown procedure. xid_cache_insert(): Initialize XID_STATE::rm_error in order to avoid a bogus error message on XA ROLLBACK of a recovered PREPARED transaction. innobase_commit_by_xid(), innobase_rollback_by_xid(): Free the InnoDB transaction object after rolling back a PREPARED transaction. trx_get_trx_by_xid(): Only consider transactions whose trx->is_prepared flag is set. The MySQL layer seems to prevent attempts to roll back connected transactions that are in the PREPARED state from another connection, but it is better to play it safe. The is_prepared flag was introduced in the InnoDB Plugin. trx_n_prepared: A new counter, counting the number of InnoDB transactions in the PREPARED state. logs_empty_and_mark_files_at_shutdown(): On shutdown, allow trx_n_prepared transactions to exist in the system. trx_undo_free_prepared(), trx_free_prepared(): New functions, to free the memory objects of PREPARED transactions on shutdown. This is not needed in the built-in InnoDB, because it would collect all allocated memory on shutdown. The InnoDB Plugin needs this because of innodb_use_sys_malloc. trx_sys_close(): Invoke trx_free_prepared() on all remaining transactions. --- .../suite/innodb_plugin/r/innodb_bug59641.result | 57 ++++++++++++++++++ .../suite/innodb_plugin/t/innodb_bug59641.test | 70 ++++++++++++++++++++++ 2 files changed, 127 insertions(+) create mode 100644 mysql-test/suite/innodb_plugin/r/innodb_bug59641.result create mode 100644 mysql-test/suite/innodb_plugin/t/innodb_bug59641.test (limited to 'mysql-test/suite/innodb_plugin') diff --git a/mysql-test/suite/innodb_plugin/r/innodb_bug59641.result b/mysql-test/suite/innodb_plugin/r/innodb_bug59641.result new file mode 100644 index 00000000000..361172aa82b --- /dev/null +++ b/mysql-test/suite/innodb_plugin/r/innodb_bug59641.result @@ -0,0 +1,57 @@ +CREATE TABLE t(a INT PRIMARY KEY, b INT)ENGINE=InnoDB; +INSERT INTO t VALUES(2,2),(4,4),(8,8),(16,16),(32,32); +COMMIT; +XA START '123'; +INSERT INTO t VALUES(1,1); +XA END '123'; +XA PREPARE '123'; +XA START '456'; +INSERT INTO t VALUES(3,47),(5,67); +UPDATE t SET b=2*b WHERE a BETWEEN 5 AND 8; +XA END '456'; +XA PREPARE '456'; +XA START '789'; +UPDATE t SET b=4*a WHERE a=32; +XA END '789'; +XA PREPARE '789'; +call mtr.add_suppression("Found 3 prepared XA transactions"); +SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; +SELECT * FROM t; +a b +1 1 +2 2 +3 47 +4 4 +5 134 +8 16 +16 16 +32 128 +COMMIT; +SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; +SELECT * FROM t; +a b +1 1 +2 2 +3 47 +4 4 +5 134 +8 16 +16 16 +32 128 +COMMIT; +XA RECOVER; +formatID gtrid_length bqual_length data +1 3 0 789 +1 3 0 456 +1 3 0 123 +XA ROLLBACK '123'; +XA ROLLBACK '456'; +XA COMMIT '789'; +SELECT * FROM t; +a b +2 2 +4 4 +8 8 +16 16 +32 128 +DROP TABLE t; diff --git a/mysql-test/suite/innodb_plugin/t/innodb_bug59641.test b/mysql-test/suite/innodb_plugin/t/innodb_bug59641.test new file mode 100644 index 00000000000..0fb24e47a54 --- /dev/null +++ b/mysql-test/suite/innodb_plugin/t/innodb_bug59641.test @@ -0,0 +1,70 @@ +# Bug #59641 Prepared XA transaction causes shutdown hang after a crash + +-- source include/not_embedded.inc +-- source include/have_innodb_plugin.inc + +let $innodb_file_format_check_orig=`select @@innodb_file_format_check`; + +CREATE TABLE t(a INT PRIMARY KEY, b INT)ENGINE=InnoDB; +INSERT INTO t VALUES(2,2),(4,4),(8,8),(16,16),(32,32); +COMMIT; +XA START '123'; +INSERT INTO t VALUES(1,1); +XA END '123'; +XA PREPARE '123'; + +CONNECT (con1,localhost,root,,); +CONNECTION con1; + +XA START '456'; +INSERT INTO t VALUES(3,47),(5,67); +UPDATE t SET b=2*b WHERE a BETWEEN 5 AND 8; +XA END '456'; +XA PREPARE '456'; + +CONNECT (con2,localhost,root,,); +CONNECTION con2; + +XA START '789'; +UPDATE t SET b=4*a WHERE a=32; +XA END '789'; +XA PREPARE '789'; + +# The server would issue this warning on restart. +call mtr.add_suppression("Found 3 prepared XA transactions"); + +# Kill the server without sending a shutdown command +-- exec echo "wait" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect +-- shutdown_server 0 +-- source include/wait_until_disconnected.inc + +# Restart the server. +-- exec echo "restart" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect +-- enable_reconnect +-- source include/wait_until_connected_again.inc +SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; +SELECT * FROM t; +COMMIT; + +# Shut down the server. This would hang because of the bug. +-- exec echo "wait" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect +-- shutdown_server +-- source include/wait_until_disconnected.inc + +# Restart the server. +-- exec echo "restart" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect +-- enable_reconnect +-- source include/wait_until_connected_again.inc + +SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; +SELECT * FROM t; +COMMIT; +XA RECOVER; +XA ROLLBACK '123'; +XA ROLLBACK '456'; +XA COMMIT '789'; +SELECT * FROM t; + +DROP TABLE t; +--disable_query_log +eval set global innodb_file_format_check=$innodb_file_format_check_orig; -- cgit v1.2.1