MySQL Bugs: #53216: Unrelated DDL causes repreparation of a prepared statement

Bug #53216	Unrelated DDL causes repreparation of a prepared statement
Submitted:	27 Apr 2010 17:39
Reporter:	Elena Stepanova	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Prepared statements	Severity:	S3 (Non-critical)
Version:	5.5.3-m3, 5.5.4-m3	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	regression
Triage:	Triaged: D3 (Medium) / R4 (High) / E4 (High)

Description:
In the test case below, default connection prepares a statement and executes it; after the statement is prepared and before it is executed, another connection performs a simple DDL in a different schema. It causes automatic repreparation of the prepared statement.

The manual http://dev.mysql.com/doc/refman/5.5/en/statement-repreparation.html mentions several reasons why a prepared statement can be automatically reprepared: 
- metadata changes to tables or views referred to by prepared statements;
- referenced tables or views are flushed from the table definition cache to make room for new entries in the cache;
- FLUSH TABLES is executed.

None of the above seems to be the case.

Reproducible on 5.5.3-m3, 5.5.4-m3, current mysql-trunk, mysql-next-mr.
Not reproducible on 5.5.2-m2, 5.1.46, current mysql-5.1.

The concern is that it might affect performance, and that in highly concurrent environment execution of prepared statements can sometimes fail unreasonably with error ER_NEED_REPREPARE, if automatic repreparation does not work (it is actually happening in our system tests now).

How to repeat:
# MTR test case

--disable_query_log
--disable_warnings
DROP DATABASE IF EXISTS db_for_ddl;
DROP DATABASE IF EXISTS db_for_ps;
--enable_warnings

CREATE DATABASE db_for_ddl;
CREATE DATABASE db_for_ps;

USE db_for_ps;
CREATE TABLE tb_prepared (f1 tinyint);

create function custom_func() returns int
   return 1;

create trigger tb_ps_trg after insert on tb_prepared
        for each row
        set @rows=custom_func();

connect (con1,localhost,root,,db_for_ddl,,);

--enable_query_log
--connection default
prepare stmtInsert FROM
        'insert into tb_prepared (f1) values ( ? )';

--echo # -------- Before DDL statement --------
SHOW GLOBAL STATUS LIKE 'Com_stmt_reprepare';
--echo # ----------------------------

--connection con1
create procedure tmpproc() begin end;

--connection default
set @c1=10;
execute stmtInsert using @c1;

--connection default

--echo # -------- After DDL statement --------
SHOW GLOBAL STATUS LIKE 'Com_stmt_reprepare';
--echo # ----------------------------

deallocate prepare stmtInsert;

# EOF

We also currently reprepare the PS on the stored procedure cache version change.
Creation of the procedure does change the cache version, since if it is referred to a by a some stored function, which is already in the cache (and we don't keep track of it yet), this stored function needs to be invalidated and re-compiled.
The only solution is to keep track of dependencies between objects in the stored routines caches, but we don't do it yet.

The statement is reprepared because the entire stored functions cache is invalidated by CREATE PROCEDURE statement, and the statement is using the stored function, via a trigger.
We must invalidate the entire stored functions cache as currently we do not keep track, in a compiled stored function body, which stored procedures it uses.