Bug #71526 prepared statement fail on table_open_cache even when is autosize
Submitted: 30 Jan 2014 13:10 Modified: 28 Feb 2014 18:18
Reporter: martin fuxa Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: Prepared statements Severity:S3 (Non-critical)
Version:5.6 OS:Any
Assigned to: CPU Architecture:Any

[30 Jan 2014 13:10] martin fuxa
Description:
prepared statements are affected by table_%_cache, ok set it to autosized.

set global table_definition_cache=DEFAULT;
set global table_open_cache      =DEFAULT;

and than run ONE prepared statement with more execution, under server with any wokrload (see below),
in this test case one INSERT executed 3145 times (attached).

PREPARE stmt1 FROM 'INSERT t1 (a, b, c, d, e, f, g, h, i, j)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)';
SET ...
EXECUTE stmt1 USING @a,@b,@c,@d,@e,@f,@g,@h,@i,@j;

But immediately fail!
mysql -B test < dis-test.sql 
ERROR 1615 (HY000) at line 15: Prepared statement needs to be re-prepared

This error is caused by table_open_cache, with higher value works without error. tested with
set global table_open_cache =4000;

As you see, autosize doesn't work for this case. With autosized cache I expect than prepared statement complete without error.

Ok, test server is little bit busy, with mix OLTP and OLAP workload, but it's commodity HW, with RAID1 over two SSD. usual case in these days.
Threads: 121  Questions: 3865532646  Slow queries: 41435  Opens: 839687  Flush tables: 1  Open tables: 2000  Queries per second avg: 2420.353
Similar load can be achieved for test with sysbench.

How to repeat:
1/ set cache to autosized and create test table
set global table_definition_cache=DEFAULT;
set global table_open_cache      =DEFAULT;
CREATE TABLE `t1` (
  `a` int(10) unsigned DEFAULT NULL,
  `b` varchar(40) DEFAULT NULL,
  `c` varchar(40) DEFAULT NULL,
  `d` int(10) unsigned NOT NULL,
  `e` varchar(10) NOT NULL,
  `f` decimal(13,2) NOT NULL,
  `g` smallint(4) unsigned NOT NULL,
  `h` smallint(3) unsigned NOT NULL,
  `i` varchar(10) NOT NULL,
  `j` varchar(10) NOT NULL,
  PRIMARY KEY (`rok_mesic`,`produkty_id`,`ic`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2/ make some load for mysql test server, use sysbench or whatever you like ...

3/ run prepared statement
mysql -B test < dis-test.sql 
# in my case end up with ERROR 1615 (HY000) Prepared statement needs to be re-prepared

Suggested fix:
when table_%_cache is set to autosized, I expect prepared statement complete without error, caused by too small table_open_cache.
[30 Jan 2014 13:32] martin fuxa
same problem had Mysql users long time ago, see #42041. But past times autosized conf doesn't exists and everyone who is using prepared statement must 
oversize table_open_cache. And within 5 years this is alive topic.
[30 Jan 2014 18:18] Sveta Smirnova
Thank you for the report.

I can not repeat described behavior. Please indicate exact version and package of MySQL server you use: file name you downloaded
[1 Mar 2014 1:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".