MySQL Bugs: #21294: executing a prepared statement that executes a stored function which was recreat

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	CPU Architecture:	Any
Tags:	prepared statement, rt_q1_2007, stored procedure

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;

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;

Suggested first fix:
Detect that sp cache has changed and return error message indicating that prepared statement need to be re prepared.

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

This issue seems to tightly correlate with the bug #12093

Will be fixed in 6.0

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.

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.

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.

Queued in 5.1-bugteam

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

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.

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.

I guess it is exactly the bug #12093 that is expected to be fixed since 5.1.28

Pushed into 5.1.29, 6.0.8

It's pushed into 5.1.28, 6.0.8, and noted in those changelogs.

Sorry, noted in 5.1.28 and 6.0.7.