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)

[27 Apr 2010 17:39] Elena Stepanova
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
[27 Apr 2010 18:56] Konstantin Osipov
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.
[28 Sep 2010 11:29] Konstantin Osipov
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.