MySQL Bugs: #12093: SP not found on second PS execution if another thread drops other SP in between

Bug #12093	SP not found on second PS execution if another thread drops other SP in between
Submitted:	21 Jul 2005 20:42	Modified:	23 Jul 2008 18:02
Reporter:	Sergey Petrunya	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Prepared statements	Severity:	S3 (Non-critical)
Version:	5.0-bk	OS:	Linux (Linux)
Assigned to:	Konstantin Osipov	CPU Architecture:	Any
Tags:	prepared statement, rt_q1_2007, stored procedure

Description:
One gets a "PROCEDURE does not exist" when one tries the following:

-- Run the following from client#1: --- 
create table t1 (a int);
insert into t1 values(1);
create table t2 as select * from t1;

delimiter //
create procedure p_just_select (a int )
begin
  select * from t2 limit 2;
end //

create function f_t1_max() returns int 
begin
  return (select max(a) from t1);
end //

prepare s1 from  'call p_just_select (f_t1_max())' //

execute s1 //
-- Now connect with client#2 and run this: --
create procedure dummy() select 'dummy';
drop procedure dummy;

-- Now switch back client#1 and run this: --
execute s1 //

And get this:
ERROR 1305 (42000): PROCEDURE test6.p_just_select does not exist

How to repeat:
Get 5.0-bk, last changeset:
ChangeSet@1.1882, 2005-07-21 18:43:55+05:00, gluh@eagle.intranet.mysql.r18.ru
  merge fix
compile it with compile-pentium-debug, and run the sequence of commands mentioned above.

tested against 5.0 from bk. And at least 5.0.7 also has this bug

I did some experiments on this testcase and got the following results:
1. Actions of the second session like
    CREATE/DROP STORED PROCEDURE/FUNCTION/VIEW (!!)
    cause that the problem appears.
    It does not matter if the parallel Created/Dropped PROCEDURE/..
    uses a table used within the Stored Procedure/function of the first
    session.
2. If session 1 calls the procedure with a constant like 
    CALL test_proc1 (888) -- that means the function
    is not used -- the problem disappears.
3. If the function of session 1 does not contain a table
    the problem disappears.
4. The procedure is not needed.
    Session 1 will see the same bug if it executes
         "SELECT test_func1() FROM t1 LIMIT 1"
    instead of
         "CALL test_proc1 (test_func1())"
5. Even the second session is not needed.
Please have a look into the attached testcases.

t/ml055.test  simplified testcase (two session variant)
t/ml056.test  simplified testcase (single session variant)
            + r/ml056.reject (my "bad" results)

t/ml050.test+include/ml050a.inc+include/ml0501.inc+
r/ml050.result (The correct results I expect.)
            many configurable variations on the testcase of the bug
            two sessions

IMHO the Priority and Severity should be set to P2/S2.

testcase (ESSENTIALS) single session

Attachment: ml056.test (application/test, text), 1.72 KiB.

my "bad" results of testcase (ESSENTIALS) single session

Attachment: ml056.reject (application/octet-stream, text), 1.45 KiB.

testcase (ESSENTIALS)  two sessions

Attachment: ml055.test (application/test, text), 2.46 KiB.

configurable variations on the testcase of the bug

Attachment: ml050.test (application/test, text), 13.00 KiB.

script sourced by t/ml050.test

Attachment: ml050a.inc (application/octet-stream, text), 1.74 KiB.

script sourced by include/ml050a.inc

Attachment: ml0501.inc (application/octet-stream, text), 1.17 KiB.

File with the expected results

Attachment: ml050.result (application/octet-stream, text), 46.87 KiB.

Bug #24572 "FUNCTION does not exist error with prepared statements" was marked as duplicate of this bug.

Bug #26445 was closed as a duplicate of this bug.
Please add the test case from Bug#26445 to the test suite when this problem is solved.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/36209

ChangeSet@1.2543, 2007-10-23 17:16:54-06:00, malff@lambda.hsd1.co.comcast.net. +3 -0
  Bug#12093 (SP not found on second PS execution if another thread drops other
  SP in between)
  
  TEMPORARY PATCH FOR INTERNAL DISCUSION
  
  NOT READY FOR PRODUCTION
  
  In particular, while fix patch fixes the issue reported,
  this patch also expose the following existing problem:
  - PREPARE
  - DDL on Stored Functions or Stored Procedures,
    affecting the SF/SP used in the prepared statement
  - EXECUTE
    executes code with an invalid pre-locked set.
  
  See related bug 27430

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/36210

ChangeSet@1.2543, 2007-10-23 17:23:18-06:00, malff@lambda.hsd1.co.comcast.net. +5 -0
  Bug#12093 (SP not found on second PS execution if another thread drops other
  SP in between)
  
  TEMPORARY PATCH FOR INTERNAL DISCUSION
  
  NOT READY FOR PRODUCTION
  
  (reposted, with test cases)
  
  In particular, while fix patch fixes the issue reported,
  this patch also expose the following existing problem:
  - PREPARE
  - DDL on Stored Functions or Stored Procedures,
    affecting the SF/SP used in the prepared statement
  - EXECUTE
    executes code with an invalid pre-locked set.
  
  See related bug 27430

The bug#21294 bears obvious resemblance to this one.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/37009

ChangeSet@1.2603, 2007-11-02 11:20:03-06:00, malff@lambda.hsd1.co.comcast.net. +31 -0
  Bug#12093 (SP not found on second PS execution if another thread drops other
  SP in between)
  
  TEMPORARY PATCH FOR INTERNAL DISCUSION
  
  NOT READY FOR PRODUCTION
  
  This patch is only a prototype, for review.
  The following bugs are fixed and tested:
  - Bug 12093 (SP not found on second PS execution if another thread drops other
    SP in between)
  - Bug 21294 (executing a prepared statement that executes a stored function
    which was recreat)
  - Bug 27420 (A combination of PS and view operations cause error + assertion
    on shutdown)
  - Bug 27430 Crash in subquery code when in PS and table DDL changed after
    PREPARE()
  - Bug 27690 (Re-execution of prepared statement after table was replaced with
    a view crashes)

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/37922

ChangeSet@1.2608, 2007-11-16 00:10:19-07:00, malff@lambda.hsd1.co.comcast.net. +36 -0
  Bug#12093 (SP not found on second PS execution if another thread drops other
  SP in between)
  
  TEMPORARY PATCH FOR INTERNAL DISCUSSION
  
  This patch is the second iteration for this fix.
  Remaining issues:
  - Minor code cleanup (open_table_impl),
  - Minor test cleanup (add marc_*.test to ps.test),
  - Improve test coverage for more complex DDL.
  
  The following bugs are fixed and tested:
  - Bug 12093 (SP not found on second PS execution if another thread drops other
    SP in between)
  - Bug 21294 (executing a prepared statement that executes a stored function
    which was recreat)
  - Bug 27420 (A combination of PS and view operations cause error + assertion
    on shutdown)
  - Bug 27430 Crash in subquery code when in PS and table DDL changed after
    PREPARE()
  - Bug 27690 (Re-execution of prepared statement after table was replaced with
    a view crashes)

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/38534

ChangeSet@1.2616, 2007-11-26 09:15:03-07:00, malff@lambda.hsd1.co.comcast.net. +29 -0
  Bug#12093 (SP not found on second PS execution if another thread drops other
  SP in between)
  
  TEMPORARY PATCH FOR INTERNAL DISCUSSION
  
  This patch is the 3rd iteration for this fix.
  Remaining issues:
  - Using TABLE_SHARE::table_map_id as version does not resist FLUSH TABLES
  - Dependency on Bug#26379 (to be merged) in table.cc
  - Re validation of views under LOCK_open / view definition cache
  
  The following bugs are fixed and tested:
  - Bug 12093 (SP not found on second PS execution if another thread drops other
    SP in between)
  - Bug 21294 (executing a prepared statement that executes a stored function
    which was recreat)
  - Bug 27420 (A combination of PS and view operations cause error + assertion
    on shutdown)
  - Bug 27430 Crash in subquery code when in PS and table DDL changed after
    PREPARE()
  - Bug 27690 (Re-execution of prepared statement after table was replaced with
    a view crashes)

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/39172

ChangeSet@1.2671, 2007-12-03 20:06:04-07:00, malff@lambda.hsd1.co.comcast.net. +34 -0
  Bug#12093 (SP not found on second PS execution if another thread drops other
  SP in between)
  
  TEMPORARY PATCH FOR INTERNAL DISCUSSION
  
  This patch is the 4th iteration for this fix.
  Remaining issues:
  - Re validation of views under LOCK_open / view definition cache
  
  The following bugs are fixed and tested:
  - Bug 12093 (SP not found on second PS execution if another thread drops other
    SP in between)
  - Bug 21294 (executing a prepared statement that executes a stored function
    which was recreat)
  - Bug 27420 (A combination of PS and view operations cause error + assertion
    on shutdown)
  - Bug 27430 Crash in subquery code when in PS and table DDL changed after
    PREPARE()
  - Bug 27690 (Re-execution of prepared statement after table was replaced with
    a view crashes)

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/41534

ChangeSet@1.2661, 2008-01-31 16:13:10-07:00, malff@lambda.hsd1.co.comcast.net. +30 -0
  WL#4166 (Prepared statements: automatic re-prepare)
  Bug#12093 (SP not found on second PS execution if another thread drops other
  SP in between)
  
  Temporary patch, aggregating WL#4165 and WL#4166.
  NOT READY FOR REVIEW, NOT READY FOR PRODUCTION

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/41741

ChangeSet@1.2661, 2008-02-05 16:44:14-07:00, malff@lambda.hsd1.co.comcast.net. +30 -0
  WL#4166 (Prepared statements: automatic re-prepare)
  Bug#12093 (SP not found on second PS execution if another thread drops other
  SP in between)
  
  Temporary patch, aggregating WL#4165 and WL#4166.
  NOT READY FOR REVIEW, NOT READY FOR PRODUCTION
  
  Misc cleanup

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/41880

ChangeSet@1.2661, 2008-02-07 11:12:05-07:00, malff@lambda.hsd1.co.comcast.net. +30 -0
  WL#4166 (Prepared statements: automatic re-prepare)
  Bug#12093 (SP not found on second PS execution if another thread drops other
  SP in between)
  
  Temporary patch, aggregating WL#4165 and WL#4166.
  NOT READY FOR REVIEW, NOT READY FOR PRODUCTION
  
  Changes from the previous patch:
  - remember parameters type code in setup_conversion_function,
  which impacts the binary protocol for re-prepare

The previous patch is un-usable, it has been truncated by the email chain
during posting.

smaller patch (without ps_ddl.test, ps_ddl.result) to be posted soon

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/41890

ChangeSet@1.2661, 2008-02-07 12:04:55-07:00, malff@lambda.hsd1.co.comcast.net. +28 -0
  WL#4166 (Prepared statements: automatic re-prepare)
  Bug#12093 (SP not found on second PS execution if another thread drops other
  SP in between)
  
  Temporary patch, aggregating WL#4165 and WL#4166.
  NOT READY FOR REVIEW, NOT READY FOR PRODUCTION
  
  Changes from the previous patch:
  - remember parameters type code in setup_conversion_function,
  which impacts the binary protocol for re-prepare
  - ps_ddl.test / ps_ddl.result posted separately (patch size)

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/42730

ChangeSet@1.2563, 2008-02-20 20:56:57-07:00, malff@lambda.hsd1.co.comcast.net. +28 -0
  WL#4166 (Prepared statements: automatic re-prepare)
  Bug#12093 (SP not found on second PS execution if another thread drops other
  SP in between)
  
  Full solution implementing:
  - Prepared statements revalidation
  - automatic re-prepare
  Temporary patch, for internal review.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/42731

ChangeSet@1.2565, 2008-02-20 22:11:23-07:00, malff@lambda.hsd1.co.comcast.net. +17 -0
  Bug#12093 (SP not found on second PS execution if another thread drops other
  SP in between)
  
  Partial solution implementing:
  - Prepared statements revalidation
  - raises ER_NEED_REPREPARE errors, without re-prepare
  This patch isolate all the validation code in a separate commit,
  to facilitate code review.
  *DOES NOT PASS THE TEST SUITE*, returns errors during EXECUTE

For 6.0

Will be solved by WL#4299 "Stored Routines: use the new metadata cache and metadata locks"

Set risk and effort to "low" since came to a workaround solution:

- when preparing a prepared statement, remember the version of the stored procedure cache in the statement. 
- when executing a statement, verify that the version of the stored procedure cache hasn't changed. Invalidate the statement otherwise.

Expected to take 10-20 lines of code to do this very local and low-risk fix.

Suggest to triage this back to be fixed in 5.1.

http://lists.mysql.com/commits/48957

 2686 Konstantin Osipov	2008-07-03
      A fix for 
      Bug#12093 "SP not found on second PS execution if another thread 
      drops other SP in between" and
      Bug#21294 "executing a prepared statement that executes a stored 
      function which was recreat"
      
      Stored functions are resolved at prepared statement prepare only.
      If someone flushes the stored functions cache between prepare and
      execute, execution fails.
      
      The fix is to detect the situation of the cache flush and automatically
      reprepare the prepared statement after it.

Matthias, could you please selectively add the attached tests to ps_ddl1.test?
I volunteer to review this patch.

Queued in 5.1-bugteam.

Pushed into 5.1.28 and 6.0.7-alpha

Noted in 5.1.28, 6.0.7 changelogs.

After executing a prepared statement that accesses a stored function,
the next execution would fail to find the function if the stored
function cache was flushed in the meantime.

Pushed into 5.1.28  (revid:joerg@mysql.com-20080714105031-88hmr2baz5di9xej) (version source revid:joerg@mysql.com-20080714105031-88hmr2baz5di9xej) (pib:3)

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/51124

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/51216

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/51345

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/51561