diff options
| -rw-r--r-- | mysql-test/r/sp-lock.result | 42 | ||||
| -rw-r--r-- | mysql-test/r/sp_sync.result | 107 | ||||
| -rw-r--r-- | mysql-test/t/disabled.def | 1 | ||||
| -rw-r--r-- | mysql-test/t/sp-lock.test | 62 | ||||
| -rw-r--r-- | mysql-test/t/sp_sync.test | 164 | ||||
| -rw-r--r-- | sql/sql_parse.cc | 6 |
6 files changed, 332 insertions, 50 deletions
diff --git a/mysql-test/r/sp-lock.result b/mysql-test/r/sp-lock.result index e9087f61807..a7823175b3c 100644 --- a/mysql-test/r/sp-lock.result +++ b/mysql-test/r/sp-lock.result @@ -693,5 +693,47 @@ set @@session.max_sp_recursion_depth=default; # --> connection con3 # --> connection default # +# SHOW CREATE PROCEDURE p1 called from p1, after p1 was altered +# +# We are just covering the existing behaviour with tests. The +# results are not necessarily correct." +# +CREATE PROCEDURE p1() +BEGIN +SELECT get_lock("test", 10); +SHOW CREATE PROCEDURE p1; +END| +# Connection default +SELECT get_lock("test", 10); +get_lock("test", 10) +1 +# Connection 2 +# Will halt before executing SHOW CREATE PROCEDURE p1 +# Sending: +CALL p1(); +# Connection 3 +# Alter p1 +DROP PROCEDURE p1; +CREATE PROCEDURE p1() BEGIN END; +# Connection default +# Resume CALL p1, now with new p1 +SELECT release_lock("test"); +release_lock("test") +1 +# Connection 2 +# Reaping: CALL p1() +get_lock("test", 10) +1 +Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation +p1 CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`() +BEGIN +SELECT get_lock("test", 10); +SHOW CREATE PROCEDURE p1; +END latin1 latin1_swedish_ci latin1_swedish_ci +# Connection 3 +# Connection 2 +# Connection default; +DROP PROCEDURE p1; +# # End of 5.5 tests # diff --git a/mysql-test/r/sp_sync.result b/mysql-test/r/sp_sync.result index afa37e70531..a16babaef67 100644 --- a/mysql-test/r/sp_sync.result +++ b/mysql-test/r/sp_sync.result @@ -1,23 +1,94 @@ Tests of syncronization of stored procedure execution. +SET DEBUG_SYNC= 'RESET'; # -# Bug#48157: crash in Item_field::used_tables +# Bug #30977 Concurrent statement using stored function and +# DROP FUNCTION breaks SBR # -CREATE TABLE t1 AS SELECT 1 AS a, 1 AS b; -CREATE TABLE t2 AS SELECT 1 AS a, 1 AS b; -CREATE PROCEDURE p1() -BEGIN -UPDATE t1 JOIN t2 USING( a, b ) SET t1.b = 1, t2.b = 1; -END| -LOCK TABLES t1 WRITE, t2 WRITE; -SET DEBUG_SYNC = 'multi_update_reopen_tables SIGNAL parked WAIT_FOR go'; +# A stored routine could change after dispatch_command() +# but before a MDL lock is taken. This must be noticed and the +# sp cache flushed so the correct version can be loaded. +# +# Connection default +CREATE FUNCTION f1() RETURNS INT RETURN 1; +# Get f1 cached +SELECT f1(); +f1() +1 +# Then start executing it again... +SET DEBUG_SYNC= 'before_execute_sql_command SIGNAL before WAIT_FOR changed'; +# Sending: +SELECT f1(); +# Connection 2 +SET DEBUG_SYNC= 'now WAIT_FOR before'; +# ... but before f1 is locked, change it. +DROP FUNCTION f1; +CREATE FUNCTION f1() RETURNS INT RETURN 2; +SET DEBUG_SYNC= 'now SIGNAL changed'; +# Connection default +# We should now get '2' and not '1'. +# Reaping: SELECT f1() +f1() +2 +DROP FUNCTION f1; +SET DEBUG_SYNC= 'RESET'; +# +# Field translation items must be cleared in case of back-offs +# for queries that use Information Schema tables. Otherwise +# memory allocated in fix_fields() for views may end up referring +# to freed memory. +# +DROP FUNCTION IF EXISTS f1; +# Connection default +CREATE FUNCTION f1() RETURNS INT RETURN 0; +# Connection con2 +SET DEBUG_SYNC= 'after_wait_locked_pname SIGNAL locked WAIT_FOR issued'; +# con2 will now have an x-lock on f1 +# Sending: +ALTER FUNCTION f1 COMMENT 'comment'; +# Connection default +SET DEBUG_SYNC= 'now WAIT_FOR locked'; +# This query will block due to the x-lock on f1 and back-off +SHOW OPEN TABLES WHERE f1()=0; +# Connection con3 +# Check that the IS query is blocked before releasing the x-lock +SET DEBUG_SYNC= 'now SIGNAL issued'; +# Connection default +# Reaping: ALTER FUNCTION f1 COMMENT 'comment' +DROP FUNCTION f1; +SET DEBUG_SYNC= 'RESET'; +# +# Bug #48246 assert in close_thread_table +# +CREATE TABLE t1 (a INTEGER); +CREATE FUNCTION f1(b INTEGER) RETURNS INTEGER RETURN 1; +CREATE PROCEDURE p1() SELECT COUNT(f1(a)) FROM t1; +INSERT INTO t1 VALUES(1), (2); +# Connection 2 +CALL p1(); +COUNT(f1(a)) +2 +# Connection default +SET DEBUG_SYNC= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR called'; +# Sending: +CREATE TABLE t1 (a INTEGER); +# Connection 2 +SET DEBUG_SYNC= 'now WAIT_FOR locked'; +SET DEBUG_SYNC= 'before_open_table_wait_refresh SIGNAL called WAIT_FOR created'; +# This call used to cause an assertion. MDL locking conflict will +# cause back-off and retry. A variable indicating if a prelocking list +# exists, used to be not reset properly causing an eventual assert. +# Sending: CALL p1(); -DROP TABLE t1, t2; -SET DEBUG_SYNC = 'now WAIT_FOR parked'; -CREATE TABLE t1 AS SELECT 1 AS a, 1 AS b; -CREATE TABLE t2 AS SELECT 1 AS a, 1 AS b; -SET DEBUG_SYNC = 'now SIGNAL go'; -# Without the DEBUG_SYNC supplied in the same patch as this test in the -# code, this test statement will hang. -DROP TABLE t1, t2; +# Connection default +# Reaping: CREATE TABLE t1 (a INTEGER) +ERROR 42S01: Table 't1' already exists +SET DEBUG_SYNC= 'now SIGNAL created'; +# Connection 2 +# Reaping: CALL p1() +COUNT(f1(a)) +2 +# Connection default DROP PROCEDURE p1; -SET DEBUG_SYNC = 'RESET'; +DROP FUNCTION f1; +DROP TABLE t1; +SET DEBUG_SYNC= 'RESET'; diff --git a/mysql-test/t/disabled.def b/mysql-test/t/disabled.def index 3b34ef368e1..de7956eb500 100644 --- a/mysql-test/t/disabled.def +++ b/mysql-test/t/disabled.def @@ -11,7 +11,6 @@ ############################################################################## kill : Bug#37780 2008-12-03 HHunger need some changes to be robust enough for pushbuild. query_cache_28249 : Bug#43861 2009-03-25 main.query_cache_28249 fails sporadically -sp_sync : Bug#48157 2010-02-06 5.5-m3 demands a differnt solution innodb-autoinc : Bug#49267 2009-12-02 test fails on windows because of different case mode innodb : Bug#49396 2009-12-03 test fails in embedded mode plugin_load : Bug#42144 2009-12-21 alik plugin_load fails diff --git a/mysql-test/t/sp-lock.test b/mysql-test/t/sp-lock.test index 0b31eabb0f1..7297790a886 100644 --- a/mysql-test/t/sp-lock.test +++ b/mysql-test/t/sp-lock.test @@ -893,6 +893,68 @@ disconnect con3; --source include/wait_until_disconnected.inc --echo # --> connection default connection default; + + +--echo # +--echo # SHOW CREATE PROCEDURE p1 called from p1, after p1 was altered +--echo # +--echo # We are just covering the existing behaviour with tests. The +--echo # results are not necessarily correct." +--echo # + +delimiter |; +CREATE PROCEDURE p1() +BEGIN + SELECT get_lock("test", 10); + SHOW CREATE PROCEDURE p1; +END| +delimiter ;| + +connect (con2, localhost, root); +connect (con3, localhost, root); + +--echo # Connection default +connection default; +SELECT get_lock("test", 10); + +--echo # Connection 2 +connection con2; +--echo # Will halt before executing SHOW CREATE PROCEDURE p1 +--echo # Sending: +--send CALL p1() + +--echo # Connection 3 +connection con3; +let $wait_condition=SELECT COUNT(*)=1 FROM information_schema.processlist + WHERE state='User lock' and info='SELECT get_lock("test", 10)'; +--source include/wait_condition.inc +--echo # Alter p1 +DROP PROCEDURE p1; +CREATE PROCEDURE p1() BEGIN END; + +--echo # Connection default +connection default; +--echo # Resume CALL p1, now with new p1 +SELECT release_lock("test"); + +--echo # Connection 2 +connection con2; +--echo # Reaping: CALL p1() +--reap + +--echo # Connection 3 +connection con3; +disconnect con3; +--source include/wait_until_disconnected.inc +--echo # Connection 2 +connection con2; +disconnect con2; +--source include/wait_until_disconnected.inc +--echo # Connection default; +connection default; +DROP PROCEDURE p1; + + --echo # --echo # End of 5.5 tests --echo # diff --git a/mysql-test/t/sp_sync.test b/mysql-test/t/sp_sync.test index 519a9211206..391298b604a 100644 --- a/mysql-test/t/sp_sync.test +++ b/mysql-test/t/sp_sync.test @@ -5,54 +5,156 @@ --source include/have_debug_sync.inc +# Save the initial number of concurrent sessions. +--source include/count_sessions.inc + +# Clean up resources used in this test case. +--disable_warnings +SET DEBUG_SYNC= 'RESET'; +--enable_warnings + +--echo # +--echo # Bug #30977 Concurrent statement using stored function and +--echo # DROP FUNCTION breaks SBR --echo # ---echo # Bug#48157: crash in Item_field::used_tables +--echo # A stored routine could change after dispatch_command() +--echo # but before a MDL lock is taken. This must be noticed and the +--echo # sp cache flushed so the correct version can be loaded. --echo # -CREATE TABLE t1 AS SELECT 1 AS a, 1 AS b; -CREATE TABLE t2 AS SELECT 1 AS a, 1 AS b; +connect (con2, localhost, root); -DELIMITER |; +--echo # Connection default +connection default; +CREATE FUNCTION f1() RETURNS INT RETURN 1; +--echo # Get f1 cached +SELECT f1(); +--echo # Then start executing it again... +SET DEBUG_SYNC= 'before_execute_sql_command SIGNAL before WAIT_FOR changed'; +--echo # Sending: +--send SELECT f1() + +--echo # Connection 2 +connection con2; +SET DEBUG_SYNC= 'now WAIT_FOR before'; +--echo # ... but before f1 is locked, change it. +DROP FUNCTION f1; +CREATE FUNCTION f1() RETURNS INT RETURN 2; +SET DEBUG_SYNC= 'now SIGNAL changed'; + +--echo # Connection default +--echo # We should now get '2' and not '1'. +connection default; +--echo # Reaping: SELECT f1() +--reap + +disconnect con2; +DROP FUNCTION f1; +SET DEBUG_SYNC= 'RESET'; -CREATE PROCEDURE p1() -BEGIN - UPDATE t1 JOIN t2 USING( a, b ) SET t1.b = 1, t2.b = 1; -END| +--echo # +--echo # Field translation items must be cleared in case of back-offs +--echo # for queries that use Information Schema tables. Otherwise +--echo # memory allocated in fix_fields() for views may end up referring +--echo # to freed memory. +--echo # -DELIMITER ;| +--disable_warnings +DROP FUNCTION IF EXISTS f1; +--enable_warnings -connect (con1,localhost,root,,); -connect (con2,localhost,root,,); +connect (con2, localhost, root); +connect (con3, localhost, root); -connection con1; -LOCK TABLES t1 WRITE, t2 WRITE; +--echo # Connection default +connection default; +CREATE FUNCTION f1() RETURNS INT RETURN 0; +--echo # Connection con2 connection con2; -LET $ID= `select connection_id()`; -SET DEBUG_SYNC = 'multi_update_reopen_tables SIGNAL parked WAIT_FOR go'; ---send CALL p1() +SET DEBUG_SYNC= 'after_wait_locked_pname SIGNAL locked WAIT_FOR issued'; +--echo # con2 will now have an x-lock on f1 +--echo # Sending: +--send ALTER FUNCTION f1 COMMENT 'comment' -connection con1; -let $wait_condition= SELECT 1 FROM information_schema.processlist WHERE ID = $ID AND -state = "Waiting for table"; +--echo # Connection default +connection default; +SET DEBUG_SYNC= 'now WAIT_FOR locked'; +--disable_result_log +--echo # This query will block due to the x-lock on f1 and back-off +--send SHOW OPEN TABLES WHERE f1()=0 + +--echo # Connection con3 +connection con3; +let $wait_condition= SELECT COUNT(*)= 1 FROM information_schema.processlist + WHERE state= 'Waiting for table' + AND info='SHOW OPEN TABLES WHERE f1()=0'; --source include/wait_condition.inc -DROP TABLE t1, t2; -SET DEBUG_SYNC = 'now WAIT_FOR parked'; -CREATE TABLE t1 AS SELECT 1 AS a, 1 AS b; -CREATE TABLE t2 AS SELECT 1 AS a, 1 AS b; -SET DEBUG_SYNC = 'now SIGNAL go'; +--echo # Check that the IS query is blocked before releasing the x-lock +SET DEBUG_SYNC= 'now SIGNAL issued'; -connection con2; +--echo # Connection default +connection default; +--echo # Reaping: ALTER FUNCTION f1 COMMENT 'comment' --reap - -disconnect con1; +--enable_result_log +DROP FUNCTION f1; +SET DEBUG_SYNC= 'RESET'; disconnect con2; +disconnect con3; + + +--echo # +--echo # Bug #48246 assert in close_thread_table +--echo # + +CREATE TABLE t1 (a INTEGER); +CREATE FUNCTION f1(b INTEGER) RETURNS INTEGER RETURN 1; +CREATE PROCEDURE p1() SELECT COUNT(f1(a)) FROM t1; + +INSERT INTO t1 VALUES(1), (2); + +--echo # Connection 2 +connect (con2, localhost, root); +CALL p1(); + +--echo # Connection default connection default; +SET DEBUG_SYNC= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR called'; +--echo # Sending: +--send CREATE TABLE t1 (a INTEGER) ---echo # Without the DEBUG_SYNC supplied in the same patch as this test in the ---echo # code, this test statement will hang. -DROP TABLE t1, t2; +--echo # Connection 2 +connection con2; +SET DEBUG_SYNC= 'now WAIT_FOR locked'; +SET DEBUG_SYNC= 'before_open_table_wait_refresh SIGNAL called WAIT_FOR created'; +--echo # This call used to cause an assertion. MDL locking conflict will +--echo # cause back-off and retry. A variable indicating if a prelocking list +--echo # exists, used to be not reset properly causing an eventual assert. +--echo # Sending: +--send CALL p1() + +--echo # Connection default +connection default; +--echo # Reaping: CREATE TABLE t1 (a INTEGER) +--error ER_TABLE_EXISTS_ERROR +--reap +SET DEBUG_SYNC= 'now SIGNAL created'; + +--echo # Connection 2 +connection con2; +--echo # Reaping: CALL p1() +--reap; + +--echo # Connection default +connection default; +disconnect con2; DROP PROCEDURE p1; +DROP FUNCTION f1; +DROP TABLE t1; +SET DEBUG_SYNC= 'RESET'; -SET DEBUG_SYNC = 'RESET'; +# Check that all connections opened by test cases in this file are really +# gone so execution of other tests won't be affected by their presence. +--source include/wait_until_count_sessions.inc diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index 23e0d8c0d70..06acfab0537 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -31,6 +31,7 @@ #include "transaction.h" #include "sql_audit.h" #include "sql_prepare.h" +#include "debug_sync.h" #include "probes_mysql.h" #include "set_var.h" @@ -2115,6 +2116,11 @@ mysql_execute_command(THD *thd) if (thd->global_read_lock.wait_if_global_read_lock(thd, FALSE, TRUE)) goto error; +#ifndef DBUG_OFF + if (lex->sql_command != SQLCOM_SET_OPTION) + DEBUG_SYNC(thd,"before_execute_sql_command"); +#endif + switch (lex->sql_command) { case SQLCOM_SHOW_EVENTS: |