From 4ae05129dc51f87f3ff3528640465d2122db96f3 Mon Sep 17 00:00:00 2001 From: Konstantin Osipov Date: Thu, 3 Dec 2009 18:47:20 +0300 Subject: Backport of: ------------------------------------------------------------ revno: 2630.13.16 committer: Davi Arnaut branch nick: WL#4284 timestamp: Sat 2008-07-26 13:38:20 -0300 message: WL#4284: Transactional DDL locking SQL statements' effect on transactions. Currently the MySQL server and its storage engines are not capable of rolling back operations that define or modify data structures (also known as DDL statements) or operations that alter any of the system tables (the mysql database). Allowing these group of statements to participate in transactions is unfeasible at this time (since rollback has no effect whatsoever on them) and goes against the design of our metadata locking subsystem. The solution is to issue implicit commits before and after those statements execution. This effectively confines each of those statements to its own special transaction and ensures that metadata locks taken during this special transaction are not leaked into posterior statements/transactions. --- tests/mysql_client_test.c | 54 +++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 54 insertions(+) (limited to 'tests') diff --git a/tests/mysql_client_test.c b/tests/mysql_client_test.c index 02241dd92f6..c9edd210c32 100644 --- a/tests/mysql_client_test.c +++ b/tests/mysql_client_test.c @@ -18436,6 +18436,59 @@ static void test_bug36004() DBUG_VOID_RETURN; } +/** + Test that COM_REFRESH issues a implicit commit. +*/ + +static void test_wl4284_1() +{ + int rc; + MYSQL_ROW row; + MYSQL_RES *result; + + DBUG_ENTER("test_wl4284_1"); + myheader("test_wl4284_1"); + + /* set AUTOCOMMIT to OFF */ + rc= mysql_autocommit(mysql, FALSE); + myquery(rc); + + rc= mysql_query(mysql, "DROP TABLE IF EXISTS trans"); + myquery(rc); + + rc= mysql_query(mysql, "CREATE TABLE trans (a INT) ENGINE= InnoDB"); + myquery(rc); + + rc= mysql_query(mysql, "INSERT INTO trans VALUES(1)"); + myquery(rc); + + rc= mysql_refresh(mysql, REFRESH_GRANT | REFRESH_TABLES); + myquery(rc); + + rc= mysql_rollback(mysql); + myquery(rc); + + rc= mysql_query(mysql, "SELECT * FROM trans"); + myquery(rc); + + result= mysql_use_result(mysql); + mytest(result); + + row= mysql_fetch_row(result); + mytest(row); + + mysql_free_result(result); + + /* set AUTOCOMMIT to OFF */ + rc= mysql_autocommit(mysql, FALSE); + myquery(rc); + + rc= mysql_query(mysql, "DROP TABLE trans"); + myquery(rc); + + DBUG_VOID_RETURN; +} + static void test_bug38486(void) { @@ -19197,6 +19250,7 @@ static struct my_tests_st my_tests[]= { { "test_wl4166_3", test_wl4166_3 }, { "test_wl4166_4", test_wl4166_4 }, { "test_bug36004", test_bug36004 }, + { "test_wl4284_1", test_wl4284_1 }, { "test_wl4435", test_wl4435 }, { "test_wl4435_2", test_wl4435_2 }, { "test_bug38486", test_bug38486 }, -- cgit v1.2.1 From 0b39c189baf40a9f0f9bc1588a31947cb941bb1a Mon Sep 17 00:00:00 2001 From: Konstantin Osipov Date: Sat, 5 Dec 2009 02:02:48 +0300 Subject: Backport of revno ## 2617.31.1, 2617.31.3, 2617.31.4, 2617.31.5, 2617.31.12, 2617.31.15, 2617.31.15, 2617.31.16, 2617.43.1 - initial changeset that introduced the fix for Bug#989 and follow up fixes for all test suite failures introduced in the initial changeset. ------------------------------------------------------------ revno: 2617.31.1 committer: Davi Arnaut branch nick: 4284-6.0 timestamp: Fri 2009-03-06 19:17:00 -0300 message: Bug#989: If DROP TABLE while there's an active transaction, wrong binlog order WL#4284: Transactional DDL locking Currently the MySQL server does not keep metadata locks on schema objects for the duration of a transaction, thus failing to guarantee the integrity of the schema objects being used during the transaction and to protect then from concurrent DDL operations. This also poses a problem for replication as a DDL operation might be replicated even thought there are active transactions using the object being modified. The solution is to defer the release of metadata locks until a active transaction is either committed or rolled back. This prevents other statements from modifying the table for the entire duration of the transaction. This provides commitment ordering for guaranteeing serializability across multiple transactions. - Incompatible change: If MySQL's metadata locking system encounters a lock conflict, the usual schema is to use the try and back-off technique to avoid deadlocks -- this schema consists in releasing all locks and trying to acquire them all in one go. But in a transactional context this algorithm can't be utilized as its not possible to release locks acquired during the course of the transaction without breaking the transaction commitments. To avoid deadlocks in this case, the ER_LOCK_DEADLOCK will be returned if a lock conflict is encountered during a transaction. Let's consider an example: A transaction has two statements that modify table t1, then table t2, and then commits. The first statement of the transaction will acquire a shared metadata lock on table t1, and it will be kept utill COMMIT to ensure serializability. At the moment when the second statement attempts to acquire a shared metadata lock on t2, a concurrent ALTER or DROP statement might have locked t2 exclusively. The prescription of the current locking protocol is that the acquirer of the shared lock backs off -- gives up all his current locks and retries. This implies that the entire multi-statement transaction has to be rolled back. - Incompatible change: FLUSH commands such as FLUSH PRIVILEGES and FLUSH TABLES WITH READ LOCK won't cause locked tables to be implicitly unlocked anymore. --- tests/mysql_client_test.c | 6 ++++-- 1 file changed, 4 insertions(+), 2 deletions(-) (limited to 'tests') diff --git a/tests/mysql_client_test.c b/tests/mysql_client_test.c index c9edd210c32..56e1d609754 100644 --- a/tests/mysql_client_test.c +++ b/tests/mysql_client_test.c @@ -18479,8 +18479,8 @@ static void test_wl4284_1() mysql_free_result(result); - /* set AUTOCOMMIT to OFF */ - rc= mysql_autocommit(mysql, FALSE); + /* set AUTOCOMMIT to ON */ + rc= mysql_autocommit(mysql, TRUE); myquery(rc); rc= mysql_query(mysql, "DROP TABLE trans"); @@ -18637,6 +18637,8 @@ static void test_bug40365(void) DIE_UNLESS(tm[i].day == 0); } mysql_stmt_close(stmt); + rc= mysql_commit(mysql); + myquery(rc); DBUG_VOID_RETURN; } -- cgit v1.2.1 From 93613d4e07b6e6bd42942eeceec577794c5050b1 Mon Sep 17 00:00:00 2001 From: Alexander Nozdrin Date: Tue, 29 Dec 2009 18:50:01 +0300 Subject: A test case for Bug#49972 (Crash in prepared statements). --- tests/mysql_client_test.c | 110 ++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 110 insertions(+) (limited to 'tests') diff --git a/tests/mysql_client_test.c b/tests/mysql_client_test.c index 866c63ae1d3..cce5910311f 100644 --- a/tests/mysql_client_test.c +++ b/tests/mysql_client_test.c @@ -18935,6 +18935,115 @@ static void test_bug44495() DBUG_VOID_RETURN; } +/* + Bug#49972: Crash in prepared statements. + + The following case lead to a server crash: + - Use binary protocol; + - Prepare a statement with OUT-parameter; + - Execute the statement; + - Cause re-prepare of the statement (change dependencies); + - Execute the statement again -- crash here. +*/ + +static void test_bug49972() +{ + int rc; + MYSQL_STMT *stmt; + + MYSQL_BIND in_param_bind; + MYSQL_BIND out_param_bind; + int int_data; + my_bool is_null; + + DBUG_ENTER("test_bug49972"); + myheader("test_49972"); + + rc= mysql_query(mysql, "DROP FUNCTION IF EXISTS f1"); + myquery(rc); + + rc= mysql_query(mysql, "DROP PROCEDURE IF EXISTS p1"); + myquery(rc); + + rc= mysql_query(mysql, "CREATE FUNCTION f1() RETURNS INT RETURN 1"); + myquery(rc); + + rc= mysql_query(mysql, "CREATE PROCEDURE p1(IN a INT, OUT b INT) SET b = a"); + myquery(rc); + + stmt= mysql_simple_prepare(mysql, "CALL p1((SELECT f1()), ?)"); + check_stmt(stmt); + + bzero((char *) &in_param_bind, sizeof (in_param_bind)); + + in_param_bind.buffer_type= MYSQL_TYPE_LONG; + in_param_bind.buffer= (char *) &int_data; + in_param_bind.length= 0; + in_param_bind.is_null= 0; + + rc= mysql_stmt_bind_param(stmt, &in_param_bind); + + rc= mysql_stmt_execute(stmt); + check_execute(stmt, rc); + + { + bzero(&out_param_bind, sizeof (out_param_bind)); + + out_param_bind.buffer_type= MYSQL_TYPE_LONG; + out_param_bind.is_null= &is_null; + out_param_bind.buffer= &int_data; + out_param_bind.buffer_length= sizeof (int_data); + + rc= mysql_stmt_bind_result(stmt, &out_param_bind); + check_execute(stmt, rc); + + rc= mysql_stmt_fetch(stmt); + rc= mysql_stmt_fetch(stmt); + DBUG_ASSERT(rc == MYSQL_NO_DATA); + + mysql_stmt_next_result(stmt); + mysql_stmt_fetch(stmt); + } + + rc= mysql_query(mysql, "DROP FUNCTION f1"); + myquery(rc); + + rc= mysql_query(mysql, "CREATE FUNCTION f1() RETURNS INT RETURN 1"); + myquery(rc); + + rc= mysql_stmt_execute(stmt); + check_execute(stmt, rc); + + { + bzero(&out_param_bind, sizeof (out_param_bind)); + + out_param_bind.buffer_type= MYSQL_TYPE_LONG; + out_param_bind.is_null= &is_null; + out_param_bind.buffer= &int_data; + out_param_bind.buffer_length= sizeof (int_data); + + rc= mysql_stmt_bind_result(stmt, &out_param_bind); + check_execute(stmt, rc); + + rc= mysql_stmt_fetch(stmt); + rc= mysql_stmt_fetch(stmt); + DBUG_ASSERT(rc == MYSQL_NO_DATA); + + mysql_stmt_next_result(stmt); + mysql_stmt_fetch(stmt); + } + + mysql_stmt_close(stmt); + + rc= mysql_query(mysql, "DROP PROCEDURE p1"); + myquery(rc); + + rc= mysql_query(mysql, "DROP FUNCTION f1"); + myquery(rc); + + DBUG_VOID_RETURN; +} + /* Read and parse arguments and MySQL options from my.cnf */ @@ -19264,6 +19373,7 @@ static struct my_tests_st my_tests[]= { #endif { "test_bug41078", test_bug41078 }, { "test_bug44495", test_bug44495 }, + { "test_bug49972", test_bug49972 }, { 0, 0 } }; -- cgit v1.2.1 From cd6fbffc38bb1c90a81e23c00f0a0fd042d39030 Mon Sep 17 00:00:00 2001 From: Alexander Nozdrin Date: Tue, 29 Dec 2009 21:12:06 +0300 Subject: Disable test case for Bug#49972. --- tests/mysql_client_test.c | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'tests') diff --git a/tests/mysql_client_test.c b/tests/mysql_client_test.c index cce5910311f..1b580f2412b 100644 --- a/tests/mysql_client_test.c +++ b/tests/mysql_client_test.c @@ -19373,7 +19373,7 @@ static struct my_tests_st my_tests[]= { #endif { "test_bug41078", test_bug41078 }, { "test_bug44495", test_bug44495 }, - { "test_bug49972", test_bug49972 }, + /* XXX { "test_bug49972", test_bug49972 }, */ { 0, 0 } }; -- cgit v1.2.1