Bug #21294 executing a prepared statement that executes a stored function which was recreat
Submitted: 26 Jul 2006 10:32 Modified: 12 Sep 2008 15:23
Reporter: Magnus Blåudd Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Prepared statements Severity:S2 (Serious)
Version:5.1 OS:Any
Assigned to: Konstantin Osipov
Tags: prepared statement, rt_q1_2007, stored procedure
Triage: D3 (Medium)

[26 Jul 2006 10:32] Magnus Blåudd
Description:
Execution of a prepared statement which executes a stored procedure which has been recreated will fail with error message: "ERROR 42000: Function aaa.bbb does not exist"

This is caused by the stored procedure cache being invalidated when the stored procedure was dropped or altered.

Workaround:
Close and prepare the statement again.

How to repeat:
drop function if exists bug11834_1;
drop function if exists bug11834_2;
--enable_warnings
create function bug11834_1() returns int return 10;
create function bug11834_2() returns int return bug11834_1();
prepare stmt from "select bug11834_2()";
execute stmt;
# Re-execution of statement should not crash server.
execute stmt;
drop function bug11834_1;
# Attempt to execute statement should return proper error and 
# should not crash server.
--error ER_SP_DOES_NOT_EXIST
execute stmt;
  ^^^^^^^^^^ Here it will return error that procedure does not exist
deallocate prepare stmt;
drop function bug11834_2;
[26 Jul 2006 10:43] Magnus Blåudd
Better test case:

create function sp_1() returns int return 10;
prepare stmt from "select sp_1()";
execute stmt;

drop function sp_1;
create function sp_1() returns int return 10;

--error ER_SP_DOES_NOT_EXIST
execute stmt;
[26 Jul 2006 10:45] Magnus Blåudd
Suggested first fix:
Detect that sp cache has changed and return error message indicating that prepared statement need to be re prepared.
[24 Mar 2007 7:02] Vladimir Shebordaev
As of now the test results are as follows.

--------------
select version()
--------------

version()
5.0.40-debug
--------------
drop function if exists sp_1
--------------

--------------
create function sp_1() returns int return 10
--------------

--------------
prepare stmt from "select sp_1()"
--------------

--------------
execute stmt
--------------

sp_1()
10
--------------
drop function sp_1
--------------

--------------
create function sp_1() returns int return 20
--------------

--------------
execute stmt
--------------

sp_1()
20

EOF

But the same thing doesn't yet work for mysql-5.1.18BK
--------------
select version()
--------------

version()
5.1.18-beta-debug
--------------
drop function if exists sp_1
--------------

--------------
create function sp_1() returns int return 10
--------------

--------------
prepare stmt from "select sp_1()"
--------------

--------------
execute stmt
--------------

sp_1()
10
--------------
drop function sp_1
--------------

--------------
create function sp_1() returns int return 20
--------------

--------------
execute stmt
--------------

ERROR 1305 (42000) at line 11: FUNCTION test.sp_1 does not exist
[18 Apr 2007 19:42] Vladimir Shebordaev
This issue seems to tightly correlate with the bug #12093
[26 Feb 2008 9:25] Konstantin Osipov
Will be fixed in 6.0
[3 Jul 2008 11:45] 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/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:40] 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/48982

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:41] 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/48983

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:45] Konstantin Osipov
Queued in 5.1-bugteam
[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)
[28 Jul 2008 17:05] Paul Dubois
Noted in 5.1.28 changelogs (same entry as for Bug#12093).

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.

Setting report to Patch Queued pending push of fix into 6.0.x.
[6 Sep 2008 1:02] Lig NOT_FOUND
bug also effects 5.0.56sp1 on Mac OS and 5.0.60sp1 on Linux.  

If any stored procedure (even an unrelated one) is modified while another stored procedure is in a prepared statement, you get a "ERROR 1305 (42000): PROCEDURE xxxx.sp does not exist" for the stored procedure in the prepared statement.

See uploaded file.
[6 Sep 2008 2:23] Vladimir Shebordaev
I guess it is exactly the bug #12093 that is expected to be fixed since 5.1.28
[12 Sep 2008 10:03] Konstantin Osipov
Pushed into 5.1.29, 6.0.8
[12 Sep 2008 15:23] Paul Dubois
It's pushed into 5.1.28, 6.0.8, and noted in those changelogs.
[12 Sep 2008 16:00] Paul Dubois
Sorry, noted in 5.1.28 and 6.0.7.