MySQL Bugs: #24572: FUNCTION does not exist error with prepared statements

Bug #24572	FUNCTION does not exist error with prepared statements
Submitted:	24 Nov 2006 12:29	Modified:	24 Nov 2006 15:56
Reporter:	Imre Palik	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: C API (client library)	Severity:	S1 (Critical)
Version:	5.0.27	OS:	Windows (Windows)
Assigned to:		CPU Architecture:	Any
Tags:	mysqld, prepared statement, stored procedure

Description:
I have two clients connecting to the server through shared memory.  Both clients are extensively using the prepared statements C API.  I start one client, do some operations with it, then I am switching to the other, doing some more operations.  Then I am switching back to the first, to do something else.  At this point execution of the previously prepared statements are starting to fail with FUNCTION does not exist errors.

After debugging into mysqld, it seems that the reason for this is the following (as far as I understand the code):

In mysql_stmt_execute() sp_cache_flush_obsolete() is called to flush the statement cache (or something similar).  But the proper sp_cache_routines_and_add_tables() functions won't be called from open_tables(), because thd->lex->query_tables_own_last is other than NULL.

How to repeat:
I guess setting up two clients that does this, would do it.

Suggested fix:
Inserting the line thd->lex->mark_as_requiring_prelocking(NULL); into Prepared_statement::execute(), after the call to thd->set_n_backup_statement() (sql_prepare.cpp: 2895 in the release) seems to fix the problem.

Thank you for a problem report. Please, send the exact client(s) (C API code) that demonstrates the behaviour described, and all SQL statements that are needed to run them. We need a repeatable test case for this bug.

Hi, Imre!

Judging from the description and your fine analysis I think that problem which you are experiencing is already reported as bug#12093 "SP not found on second PS execution if another thread drops other SP in between" (or at least your problem has the same solution as this bug).

So I will mark this bug-report as duplicate.

Thank you!