Getting Error : [Err] 1615 - Prepared statement needs to be re-prepared with MySQL 5.6.30

Question

Problem/Issue : Getting Error : [Err] 1615 - Prepared statement needs to be re-prepared

I have a Stored Procedure which contains a Prepared Statement and a view

DROP PROCEDURE IF EXISTS `sampleProc`;

DELIMITER ;;
CREATE DEFINER = `root`@`localhost` PROCEDURE `sampleProc`()
BEGIN

SET @select = "SELECT * FROM `viewSample` ";

  PREPARE stmt FROM @select ;
  EXECUTE stmt ;
  DEALLOCATE PREPARE stmt ;

END  ;;
DELIMITER ;

Following call sometimes gives error CALL sampleProc();

Possible work around / solutions

It appears that the best solution is to increase the value of table_definition_cache , but it seems that it is not working , as it had already been increased from 1400 (default) to 16384. table_open_cache is also been increased to 32162

Variable_name               Value
table_definition_cache      16384
table_open_cache            32162
table_open_cache_instances  4

Answer 1

This seems to be an on-going issue

Views are messy to handle with Dynamic SQL

Earliest Bug was Cannot create VIEWs in prepared statements from 11 years ago. There was a patch put in to address it.

Another bug report, Prepared-Statement fails when MySQL-Server under load, states that error 1615 is not a bug when the underlying tables are busy. (Really ?)

While there is some merit to increasing the table cache size (See MySql error when working with a mysql view), it does not always work (See General error: 1615 Prepared statement needs to be re-prepared (selecting mysql view))

ALTERNATIVES

Over a year ago, someone mentioned this in the MySQL Forum (MySql “view”, “prepared statement” and “Prepared statement needs to be re-prepared”).

Someone came up with the simple idea of not using the view in the prepared statement but using the SQL of view in a subquery instead. Another idea would be to create the SQL used by the view and execute it in your client code.

These would seems to be better workarounds that just bumping up the table cache size.