1# This test should work in embedded server after mysqltest is fixed 2-- source include/not_embedded.inc 3 4--echo Tests of synchronization of stored procedure execution. 5 6--source include/have_debug_sync.inc 7 8# Save the initial number of concurrent sessions. 9--source include/count_sessions.inc 10 11# Clean up resources used in this test case. 12--disable_warnings 13SET DEBUG_SYNC= 'RESET'; 14--enable_warnings 15 16--echo # 17--echo # Bug #30977 Concurrent statement using stored function and 18--echo # DROP FUNCTION breaks SBR 19--echo # 20--echo # A stored routine could change after dispatch_command() 21--echo # but before a MDL lock is taken. This must be noticed and the 22--echo # sp cache flushed so the correct version can be loaded. 23--echo # 24 25connect (con2, localhost, root); 26 27connection default; 28CREATE FUNCTION f1() RETURNS INT RETURN 1; 29--echo # Get f1 cached 30SELECT f1(); 31--echo # Then start executing it again... 32SET DEBUG_SYNC= 'before_execute_sql_command SIGNAL before WAIT_FOR changed'; 33--echo # Sending: 34--send SELECT f1() 35 36connection con2; 37SET DEBUG_SYNC= 'now WAIT_FOR before'; 38--echo # ... but before f1 is locked, change it. 39DROP FUNCTION f1; 40CREATE FUNCTION f1() RETURNS INT RETURN 2; 41SET DEBUG_SYNC= 'now SIGNAL changed'; 42 43--echo # We should now get '2' and not '1'. 44connection default; 45--echo # Reaping: SELECT f1() 46--reap 47 48disconnect con2; 49DROP FUNCTION f1; 50SET DEBUG_SYNC= 'RESET'; 51 52--echo # 53--echo # Field translation items must be cleared in case of back-offs 54--echo # for queries that use Information Schema tables. Otherwise 55--echo # memory allocated in fix_fields() for views may end up referring 56--echo # to freed memory. 57--echo # 58 59--disable_warnings 60DROP FUNCTION IF EXISTS f1; 61--enable_warnings 62 63connect (con2, localhost, root); 64connect (con3, localhost, root); 65 66connection default; 67CREATE FUNCTION f1() RETURNS INT RETURN 0; 68 69connection con2; 70SET DEBUG_SYNC= 'after_wait_locked_pname SIGNAL locked WAIT_FOR issued'; 71--echo # con2 will now have an x-lock on f1 72--echo # Sending: 73--send ALTER FUNCTION f1 COMMENT 'comment' 74 75connection default; 76SET DEBUG_SYNC= 'now WAIT_FOR locked'; 77--disable_result_log 78--echo # This query will block due to the x-lock on f1 and back-off 79--send SHOW OPEN TABLES WHERE f1()=0 80 81connection con3; 82let $wait_condition= SELECT COUNT(*)= 1 FROM information_schema.processlist 83 WHERE state= 'Waiting for stored function metadata lock' 84 AND info='SHOW OPEN TABLES WHERE f1()=0'; 85--source include/wait_condition.inc 86--echo # Check that the IS query is blocked before releasing the x-lock 87SET DEBUG_SYNC= 'now SIGNAL issued'; 88 89connection default; 90--echo # Reaping: ALTER FUNCTION f1 COMMENT 'comment' 91--reap 92--enable_result_log 93DROP FUNCTION f1; 94SET DEBUG_SYNC= 'RESET'; 95disconnect con2; 96disconnect con3; 97 98 99--echo # 100--echo # Bug #48246 assert in close_thread_table 101--echo # 102 103CREATE TABLE t0 (b INTEGER); 104CREATE TABLE t1 (a INTEGER); 105CREATE FUNCTION f1(b INTEGER) RETURNS INTEGER RETURN 1; 106CREATE PROCEDURE p1() SELECT COUNT(f1(a)) FROM t1, t0; 107 108INSERT INTO t0 VALUES(1); 109INSERT INTO t1 VALUES(1), (2); 110 111connect (con2, localhost, root); 112CALL p1(); 113 114SET DEBUG_SYNC= 'after_open_table_mdl_shared SIGNAL locked_t1 WAIT_FOR go_for_t0'; 115--echo # This call used to cause an assertion. MDL deadlock with upcoming 116--echo # LOCK TABLES statement will cause back-off and retry. 117--echo # A variable indicating if a prelocking list exists, used to be not 118--echo # reset properly causing an eventual assert. 119--echo # Sending: 120--send CALL p1() 121 122connection default; 123SET DEBUG_SYNC= 'now WAIT_FOR locked_t1'; 124--echo # Issue LOCK TABLES statement which will enter in MDL deadlock 125--echo # with CALL statement and as result will cause it to perform 126--echo # back-off and retry. 127SET DEBUG_SYNC= 'mdl_acquire_lock_wait SIGNAL go_for_t0'; 128LOCK TABLES t0 WRITE, t1 WRITE; 129UNLOCK TABLES; 130 131connection con2; 132--echo # Reaping: CALL p1() 133--reap; 134 135connection default; 136disconnect con2; 137DROP PROCEDURE p1; 138DROP FUNCTION f1; 139DROP TABLES t0, t1; 140 141 142--echo # 143--echo # test for bug#11756013 144--echo # 145--disable_warnings 146DROP SCHEMA IF EXISTS s1; 147--enable_warnings 148CREATE SCHEMA s1; 149CREATE PROCEDURE s1.p1() BEGIN END; 150 151connect (con3, localhost, root); 152SET DEBUG_SYNC='before_db_dir_check SIGNAL check_db WAIT_FOR dropped_schema'; 153--send CALL s1.p1 154 155connection default; 156SET DEBUG_SYNC='now WAIT_FOR check_db'; 157DROP SCHEMA s1; 158SET DEBUG_SYNC='now SIGNAL dropped_schema'; 159 160connection con3; 161--error ER_BAD_DB_ERROR 162--reap 163connection default; 164disconnect con3; 165 166SET DEBUG_SYNC = 'RESET'; 167 168# Check that all connections opened by test cases in this file are really 169# gone so execution of other tests won't be affected by their presence. 170--source include/wait_until_count_sessions.inc 171