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:
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

[24 Nov 2006 12:29] Imre Palik
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.
[24 Nov 2006 14:11] Valeriy Kravchuk
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.
[24 Nov 2006 15:56] Dmitry Lenev
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!