How to get rid of MySQL error 'Prepared statement needs to be re-prepared'

Solution 1:

This is a possibility: MySQL bug #42041

They suggest upping the value of table_definition_cache.

You can read about statement caching in the MySQL docs.

Solution 2:

@docwhat's answer seems nice, but on a shared hosting server, not everyone is allowed to touch the table_open_cache or table_definition_cache options.

Since this error is related to prepared statements, I have tried to 'emulate' those with PDO by providing the following option:

$dbh = new PDO('mysql:host=localhost;dbname=test', $user, $pass, [
    PDO::ATTR_EMULATE_PREPARES => true
]);

Note: actually this is in a Laravel 5.6 project, and I added the option in config/database.php:

'connections' => [
    'mysql' => [
        'driver' => 'mysql',
        'host' => env('DB_HOST', '127.0.0.1'),
        'port' => env('DB_PORT', '3306'),
        'database' => env('DB_DATABASE', 'forge'),
        'username' => env('DB_USERNAME', 'forge'),
        'password' => env('DB_PASSWORD', ''),
        'unix_socket' => env('DB_SOCKET', ''),
        'charset' => 'utf8mb4',
        'collation' => 'utf8mb4_unicode_ci',
        'prefix' => '',
        'strict' => true,
        'engine' => null,
        'options' => [
            PDO::ATTR_EMULATE_PREPARES => true,
        ],
    ],
    (...)
],

I have not tested the impact of emulating prepared statements on the duration of loading my site, but it works against the error SQLSTATE[HY000]: General error: 1615 Prepared statement needs to be re-prepared I got.

Update on the performance: the emulated version seems to be slightly faster (32.7±1.4ms emulated, 35.0±2.3ms normal, n=10, p-value=0.027 for two-tailed Student's T-test).

Solution 3:

In short: Don't use VIEWS in prepared statements.

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.

Solution 4:

Issue: 'Prepared statement needs to be re-prepared'

This issue generally occurs at the time of calling procedure either by using any Computer Language(like Java) or Calling Procedures from the backend.

Solution: Increase the size of the cache by using (executing) below script.

Script: set global table_definition_cache = 4000;

Solution 5:

First gain access to mysql shell:

mysql 

Check the value of the table_definition_cache:

show global variables like '%table_definition_cache%';

It might be 400 or 1400.

Enlarge it:

set global table_definition_cache = 4000;

Good to go!