MySQL Bugs: #12331: A thread may end up using stale stored routine definition indefinitely

Bug #12331	A thread may end up using stale stored routine definition indefinitely
Submitted:	3 Aug 2005 1:57	Modified:	12 Aug 2005 9:19
Reporter:	Sergey Petrunya	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0	OS:
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
A thread may end up using stale stored routine definition indefinitely.
We have per-thread SP cache, and a thread may end up in a situation where its SP cache contains stale cache SPs while it thinks its cache is up to date.

How to repeat:
Apply the attached patch to 5.0 (I'll attach a file that allows to make mysqld to manually pause a query)
Start the patched server
Start 2 clients, C1 and C2.
C1: create procedure p1() select 'sp1-version1';
C2: call p1();
    (here the client will freeze)
C1: drop procedure p1;
C1: create procedure p1() select 'sp1-version2';
Then run "touch /tmp/wait_for_me" from a separate xterm
C2 will unfreeze and print "sp1-version1"
C2: call p1(); -- prints "sp1-version1"
C1: call p1(); -- prints "sp1-version2"

At this point C2 has obsolete SP in the cache but it thinks it is up to date.

Suggested fix:
This particular example doesn't seem very dangerous, but sloppy SP cache implementation  may actually contain difficult-to-repeat race-conditions bugs. We need to fix SP cache code.

Helper patch to repeat the bug.

Attachment: bug12331-test.diff (text/plain), 666 bytes.

This bug will be fixed by fix for BUG#12228

Setting to duplicate as fix for BUG#12228 has been pushed.