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
Tags: prepared statement, rt_q1_2007, stored procedure
Triage: D2 (Serious) / R2 (Low) / E2 (Low)

[21 Jul 2005 20:42] Sergey Petrunya
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.
[22 Jul 2005 4:25] Aleksey Kishkin
tested against 5.0 from bk. And at least 5.0.7 also has this bug
[30 Nov 2005 19:43] Matthias Leich
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.
[30 Nov 2005 19:46] Matthias Leich
testcase (ESSENTIALS) single session

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

[30 Nov 2005 19:48] Matthias Leich
my "bad" results of testcase (ESSENTIALS) single session

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

[30 Nov 2005 19:49] Matthias Leich
testcase (ESSENTIALS)  two sessions

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

[30 Nov 2005 19:50] Matthias Leich
configurable variations on the testcase of the bug

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

[30 Nov 2005 19:52] Matthias Leich
script sourced by t/ml050.test

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

[30 Nov 2005 19:53] Matthias Leich
script sourced by include/ml050a.inc

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

[30 Nov 2005 19:54] Matthias Leich
File with the expected results

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

[24 Nov 2006 15:58] Dmitry Lenev
Bug #24572 "FUNCTION does not exist error with prepared statements" was marked as duplicate of this bug.
[4 Jun 2007 21:32] Konstantin Osipov
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.
[23 Oct 2007 23:17] Bugs System
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
[23 Oct 2007 23:23] Bugs System
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
[24 Oct 2007 3:00] Vladimir Shebordaev
The bug#21294 bears obvious resemblance to this one.
[2 Nov 2007 17:20] Bugs System
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)
[16 Nov 2007 7:10] Bugs System
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)
[26 Nov 2007 16:15] Bugs System
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)
[4 Dec 2007 3:06] Bugs System
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)
[31 Jan 2008 23:13] Bugs System
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
[5 Feb 2008 23:44] Bugs System
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
[7 Feb 2008 18:13] Bugs System
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
[7 Feb 2008 18:53] Marc Alff
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
[7 Feb 2008 19:05] Bugs System
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)
[21 Feb 2008 3:57] Bugs System
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.
[21 Feb 2008 5:11] Bugs System
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
[26 Feb 2008 9:27] Konstantin Osipov
For 6.0
[7 Mar 2008 17:20] Konstantin Osipov
Will be solved by WL#4299 "Stored Routines: use the new metadata cache and metadata locks"
[26 Jun 2008 9:25] Konstantin Osipov
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.
[3 Jul 2008 13:23] Konstantin Osipov
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.
[3 Jul 2008 19:05] Konstantin Osipov
Matthias, could you please selectively add the attached tests to ps_ddl1.test?
I volunteer to review this patch.
[3 Jul 2008 19:44] Konstantin Osipov
Queued in 5.1-bugteam.
[23 Jul 2008 10:59] Georgi Kodinov
Pushed into 5.1.28 and 6.0.7-alpha
[23 Jul 2008 18:02] Paul Dubois
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.
[28 Jul 2008 16:46] Bugs System
Pushed into 5.1.28  (revid:joerg@mysql.com-20080714105031-88hmr2baz5di9xej) (version source revid:joerg@mysql.com-20080714105031-88hmr2baz5di9xej) (pib:3)
[7 Aug 2008 16:19] Bugs System
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
[8 Aug 2008 17:59] Bugs System
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
[11 Aug 2008 19:38] Bugs System
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
[13 Aug 2008 19:42] Bugs System
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