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

Question

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

In my local version everything works fine but after I uploaded my 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?

The answers below don't get to the core of the problem. More answers in [this duplicate](https://stackoverflow.com/questions/29480915/mysql-view-prepared-statement-and-prepared-statement-needs-to-be-re-prepar). — reinierpost, Jun 06 '17 at 15:18
Your comment is no longer true. More answers came over the years, and now the answers below seem to add a lot to the duplicate (which is of little help, indeed) — Kar.ma, Jun 08 '21 at 15:05

score 39 · Accepted Answer · edited Nov 01 '21 at 11:15

39

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.

edited Nov 01 '21 at 11:15

Dharman

30,962
25
85
135

answered Dec 07 '10 at 20:18

docwhat

11,435
6
55
54

4

How do you go about upping the value of table_definition_cache? – warmwhisky Dec 14 '18 at 08:35
3

The error went away for me when I added `table_definition_cache=5000` in my `/etc/my.cnf` file (under the `[mysqld]` section), and restarted `mysqld`. Note that `my.cnf` may be in a different location for you. – Sygmoral Dec 12 '19 at 02:55
3

Also seems to be an equivalent bug in MariaDB 10.0, 10.1, 10.2, 10.3, 10.4, 10.1.34: https://jira.mariadb.org/browse/MDEV-17124 with similar workarounds (increase `table_definition_cache` to be larger than or equal to `table_open_cache`). – Jordan Rieger May 06 '20 at 19:02

Marten Koetsier · Answer 2 · 2018-04-05T18:38:14.447

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

I am also facing this issue and this fix works fine. But for security reasons it is not advised ([citing comment](https://stackoverflow.com/questions/31957441/laravel-general-error-1615-prepared-statement-needs-to-be-re-prepared/34545211#34545211) of [David](https://stackoverflow.com/users/1691446/david)). But if you are willing to take risks or there are no choice, then so be it :D — josevoid, May 23 '18 at 05:37
So far, my search for security issues related to prepared native vs PDO yields no differences. [Here](https://phpdelusions.net/pdo#emulation) is also a nice starting point. What reason do you have to consider emulation a risk? — Marten Koetsier, May 23 '18 at 07:05
I admit I have limited knowledge about this and just depended on some short info I just found. I was believing increasing `table_definition_cache` is the most appropriate solution here. But I'm starting to doubt when I read your link. Actually I'm still facing this issue and I observed this occurs on the server php 7.0 but not on php 5.6 (this is specific on my setup) — josevoid, May 23 '18 at 07:26
Be aware it causes integer types to be fetched as strings. `table_definition_cache` is more reliable. — Parsa_Gholipour, Sep 26 '21 at 15:10

TuralAsgar · Answer 3 · 2020-08-21T21:24:51.537

16

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.

edited Aug 21 '20 at 21:24

answered Jun 27 '20 at 11:02

TuralAsgar

1,275
2
13
26

4

acutally I think this is the correct answer, as it points to the reason for the error and not just handles the effect by changing server configurations! thanks a lot - I'll change my code according to your suggestions and linked resources and hope this will finally solve this weird error! – SaschaP Jul 11 '20 at 14:35
1

This is a really poor answer. Using prepared statements with views *does* work for MySQL/MariaDB databases. (I know from experience with working with TB of data across millions of DB tables, having views for most of them). The better solution would be to determine appropriate values for the table_xxx_cache (allowed for your environment) or limit the SQL concurrency to be within the limits. – J Jorgenson Apr 12 '21 at 21:09
@JJorgenson you didn't specify what "appropriate value" means. How to find it, why it doesn't work. – TuralAsgar Apr 13 '21 at 03:58
(Needs a dedicated article), but for a short answer, the "appropriate values" are likely to be the maximum number of tables in use at any given point in time. My server has >5M tables, however we only 100,000 tables at any given point, and that is where our cache limits are set. Obviously the larger cache requires more server ram/resource, which may impose limits on the cache sizes. – J Jorgenson Apr 15 '21 at 18:31

score 8 · Answer 4 · edited May 26 '21 at 13:26

8

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!

edited May 26 '21 at 13:26

raveren

17,799
12
70
83

answered May 06 '21 at 06:21

Tansy Z

169
2
2

score 5 · Answer 5 · edited Sep 21 '18 at 10:19

5

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;

edited Sep 21 '18 at 10:19

Lajos Arpad

64,414
37
100
175

answered Sep 21 '18 at 10:13

Sanjay Kushwaha

75
1
2

3

Where exactly do you run the above script? – warmwhisky Dec 14 '18 at 08:33

Frank · Answer 6 · 2021-09-28T08:04:37.917

5

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.

edited Sep 28 '21 at 08:04

answered Sep 02 '21 at 13:37

Frank

1,901
20
27

score 3 · Answer 7 · answered Apr 08 '21 at 18:15

3

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

answered Apr 08 '21 at 18:15

rodrigo camargo kopichinski

126
1
5

This worked for me without modifying any project code. Increasing the table_definition_cache to 1024 worked in dev, but not in production. I'm assuming 1024 simply wasn't sufficient in production as we have many more databases and tables on that system. – Nick B Jul 21 '21 at 13:41
This answer needs more up votes honestly. I have scheduled this into a task to run every so often. No issues since. – waltmagic Oct 06 '22 at 21:48

score 1 · Answer 8 · edited Jul 31 '17 at 00:12

1

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

edited Jul 31 '17 at 00:12

Hailwood

89,623
107
270
423

answered Sep 06 '16 at 18:22

Gabriel

41
3

Why do you need that bold and big font? – Alexey Subach Sep 06 '16 at 18:42
1

sorry, font style is not necessary. I hope that my contribution has been useful for someone – Gabriel Oct 08 '16 at 19:35

score 0 · Answer 9 · answered Jan 05 '20 at 23:03

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

score 0 · Answer 10 · edited Jul 28 '21 at 15:18

0

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

edited Jul 28 '21 at 15:18

buddemat

4,552
14
29
49

answered Jul 26 '21 at 01:34

Ivan Sentch

1

score 0 · Answer 11 · answered Sep 21 '21 at 11:16

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.

score 0 · Answer 12 · answered Dec 30 '21 at 17:05

0

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.

answered Dec 30 '21 at 17:05

domaci_a_nas

220
2
11

score -3 · Answer 13 · answered Oct 27 '16 at 12:46

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.

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

13 Answers13

Linked

Related