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

I've rewritten my site php-code and added MySQL Stored Procedures.

In my local version everything works fine but after I uploaded m开发者_运维技巧y site to hosting server I'm constantly getting fatal error 'Prepared statement needs to be re-prepared'.

Sometimes page loads, sometimes loading fails and I see this error. What's that?

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.

@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).

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.

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!

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;

just do this:

SET GLOBAL table_definition_cache = 4096;
SET GLOBAL table_open_cache = 4096;

4096 can be to less, so set it to a higher value. But make sure that both values ​​are the same.

FLUSH TABLES; comand on database solved for me, i was using doctrine orm.

my solutions is to create a routine like this:

DELIMITER $$
--
-- Procedimientos
--
DROP PROCEDURE IF EXISTS `dch_content_class_content`$$

CREATE DEFINER=`renuecod`@`localhost` PROCEDURE `dch_content_class_content`(IN $classId INTEGER)
BEGIN
-- vw_content_class_contents is a VIEW (UNIONS)
  select * from vw_content_class_contents;
END$$

I hope this help someone

This is a workaround for people who are on shared hosting and don't want to risk it with sql injection. According to this post: Laravel Fluent Query Builder Join with subquery you could store the definition of your view in a function

private function myView(){
    return DB::raw('(**definition of the view**) my_view_name)');
}

and then use it like this:

public function scopeMyTable(Builder $query)
{
    return $query->join($this->myView(),'my_view_name.id','=','some_table.id');
}

This is a laravel approach, but I'm sure it could be applied in most cases and doesn't need huge code refactoring or architecture change. Plus, it's relatively secure as your statements stay prepared

I had this error being caused by a large group_concat_max_len statement SET SESSION group_concat_max_len = 1000000000000000000; removed it and the error went away

Okay, we were stuck on a laptop that would not allow Tableau to update or retrieve data from views on our MariaDB 10.3.31 databases. We did the usual, google and stack overflow, lots of solutions that just would not work. However, we have another laptop that did connect and ran just fine. So after many head scratches, a eureka moment came. The offending laptop had both V5.3.14 and V8.0.26 ODBC connectors installed. We removed the V8.0.26 ODBC connector and Bang, all the view issues disappeared. Hope someone finds this solution useful.

I tried to run an UPDATE query which was joining view and a table, and also got the same error. Since I had no user input, I decided to run DB::unprepared which fixed the problem.

You can read more about it in the Laravel Documentation.

I was getting this same error in Ruby on Rails in a shared hosting environment. It may not be the most secure solution, but disabling prepared statements got rid of the error message for me.

This can be done by adding the "prepared_statements: false" setting to your database.yml file:

production:
  prepared_statements: false

This seems like a reasonable solution when you don't have control over the configuration settings on the MySQL server.