Bug #42041 Prepared-Statement fails when MySQL-Server under load
Submitted: 12 Jan 2009 9:33 Modified: 19 Sep 2011 23:07
Reporter: Raphael Thoma Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Prepared statements Severity:S3 (Non-critical)
Version:5.1.30, 5.1.52 OS:Any (FreeBSD 7.0-RELEASE-p4, Debian)
Assigned to: CPU Architecture:Any

[12 Jan 2009 9:33] Raphael Thoma
Description:
Hello

As backup-procedure we use 4 concurrent mysql-dump processes. When these
processes are running, MySQLi-Prepared-Statements (PHP) can't be executed and
the following error appears:

Errno: 1615
Message: Prepared statement needs to be re-prepared

The problem only appears while using Prepared-Statements in MySQLi-PHP. Prepared-Statements in PDO-PHP work and "normal" queries as well. According to php.net this is due to an emulation used in PDO why it seems to be a problem in mysql itself.

The problem does not occur if NO mysql-dump-processes are running. It doesn't matter which database the process is dumping. The problem comes up if even another database is dumped, not the one actually used in my php-application.

We reduced the amount of concurrent dumps already from 8 to 4 which reduced the problems. But its still coming up every day when backup processes are running.

We dump our databases with the following options: mysqldump --opt -Q. The server is a dedicated mysql-server without any other services. It serves around 4500 databases.

How to repeat:
1) run around 5-10 concurrent mysql-dump processes
2) reproduce code: http://tmp.raphis.ch/mysqli_problem.phps - use a database which is not dumped at the moment. mysqli-prepared statements fails. (modify server parameters)
3) mysqli-prepared-statement should return the requested values, but in my case it returns: 
Errno: 1615
Message: Prepared statement needs to be re-prepared
[12 Jan 2009 9:37] MySQL Verification Team
mysqldump maybe flushing/locking tables.  looks similar to bug #41119
[12 Jan 2009 11:36] Raphael Thoma
it looks a bit similar to the bug mentioned. But one thing I don't understand: why does a lock/flush on another database affect the database which i actually use?
[18 May 2009 6:15] Sveta Smirnova
Thank you for the report.

I can not repeat described behavior: mysqldump some database and run your program on another.

Please upgrade to current version 5.1.34, try with it and if problem still repeatable for you provide more information about your environment: PHP version, client version.
[18 Jun 2009 23: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".
[9 Oct 2010 5:34] Sveta Smirnova
I still need feedback and idea how to repeat this problem on our side.
[10 Nov 2010 0: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".
[10 Nov 2010 16:09] Saurabh Deshpande
I am facing the same issue in 5.1.52.

I also tried adding continue handler for 1615.

But the problem still persists
[10 Nov 2010 16:10] Saurabh Deshpande
the error comes at the time of execution of the prepared statement.
[10 Nov 2010 16:12] Saurabh Deshpande
just to add my OS is debian etch
[14 Nov 2010 0:29] Valeriy Kravchuk
Saurabh,

Does it also happen while mysqldump is executed concurrently?
[15 Nov 2010 6:36] Saurabh Deshpande
Yes.

But now we have found a solution.

We increased the value of table_definition_cache.
[29 Nov 2010 12:58] Susanne Ebrecht
All reporters,

please try to increased the value of table_definition_cache and let us know if this will help.
[30 Dec 2010 0: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".
[25 Feb 2011 11:11] Ondřej Kunc
Hi,
i've found that increasing table_definition_cache doesn't help. I'had also created this bug in debian:
http://bugs.debian.org/cgi-bin/bugreport.cgi?bug=615011

Thanks
[25 Feb 2011 13:32] Anders Skar
We experience the same error on Fedora after upgrading to MySQL Server version: 5.1.55.

The error occurs at random times during normal load, and is not linked to backup.
[17 Mar 2011 20:10] Sveta Smirnova
Anders,

thank you for the feedback. Do you issue parallel FLUSH TABLES statements in time when see the problem.
[17 Apr 2011 23: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".
[7 Jul 2011 17:52] Saurya Das
I am getting this error periodically. I have never seen this before.

Caught exception (246): Query failed: (HY000:1615) : Prepared statement needs to be re-prepared
[7 Jul 2011 17:55] Saurya Das
I am getting this error periodically. I have never seen this before.

Caught exception (246): Query failed: (HY000:1615) : Prepared statement needs to be re-prepared
[7 Jul 2011 18:00] MySQL Verification Team
Please see prior request from Sveta. Thanks.
[7 Aug 2011 23: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".
[8 Aug 2011 5:44] MySQL Verification Team
Doesn't http://dev.mysql.com/doc/refman/5.1/en/statement-repreparation.html explain the problem ?
[13 Sep 2011 21:14] Derek Hemmes
We are hitting this error every time we run a database dump with our servers running MySQL 5.1.52, PHP 5.3 and RHEL 6.  We do not see this error on servers running MySQL 5.0.86, PHP 5.2.17, and RHEL 5.7. 

Error message: Could not execute statement
Native code: 1615
Native message: Prepared statement needs to be re-prepared

The database dump can be running on a different database than the one throwing the error.

Has anyone heard of any plans to address this?
[19 Sep 2011 23:07] Sveta Smirnova
Derek,

thank you for the feedback. Please read URL which Shane provided. This clearly explains why you get this error in 5.1 and don't in 5.0 Closing as "Not a Bug"
[8 Feb 2012 20:16] Matt Inglot
Upping the value of table_definition_cache does not seem to solve the problem entirely, it just makes it less frequently. Basically if you do mysql_dump, you start getting these errors in 5.1. 

If you google the problem you will see a lot of people are experiencing this:
https://www.google.com/search?q=Prepared+statement+needs+to+be+re-prepared&ie=utf-8&oe=utf...

I am not sure I understand why this is being listed as Not a Bug? Surely mySQL could do something to recover from this error better? Especially since previous versions of mySQL do not have this issue.
[24 Feb 2012 14:09] Pete Walker
Hi, I'm also experiencing this problem.

MySQL 5.1.58-1ubuntu1
PHP 5.3.6-13ubuntu3.6
I use MySQLi (but without mysqlnd)

When I run a single mysqldump process on my database server, it affects all databases intermittently. The man page previously mentioned says:

> The server attempts repreparation up to three times. An error occurs if all attempts fail.

Presumably the server is being prevented from repreparing the statement itself, what in mysqldump is causing it? And are there any parameters we can alter to prevent this? e.g. Is it a memory issue..?
[27 Feb 2012 23:49] Derek Hemmes
Peter,

Not sure if this helps, but we seemed to solve the issue on our servers by changing some db settings.

table_open_cache 128=>16384
table_definition_cache 1024=>16384
tmp_table_size 32M=>64M
join_buffer_size 256k=>512k
[28 Feb 2012 10:30] Pete Walker
Thanks for that Derek, it does appear to have helped. Not seeing the error at all now.
[17 Oct 2013 12:15] Martin Burrow
I am experiencing the same issue with my website.  I switched to use MySQLi and prepared statements and went live with my website and now, each morning between 1:00 and 1:30am I get these errors "Prepared statement needs to be re-prepared" each time a page is viewed.  It effectively causes my entire website to go down for 30 minutes each day.

Also, I can confirm this happens while the webhost is performing a backup using mysqldump.  They changed the backup cron to 4am and sure enough, the errors started being logged between 4:00 and 4:30am.

Searching Google for "Prepared statement needs to be re-prepared" mysqldump returns many folk experiencing the same issue.

My webhost also tried changing the server settings to those that Derek recommended but the error is still occurring.

This really needs to be resolved...
[19 Oct 2013 22:29] Martin Burrow
I'm absolutely amazed more people aren't complaining about this; it effectively means that if a prominent website uses MySQLi/Prepared statements and a mysqldump is executed it will effectively bring the website to a standstill until the dump is complete.  That is absolutely crazy!

On a shared hosting solution, anyone else performing a mysqldump can bring all other websites down.

How on earth has this not become a major priority in over 4 1/2 years???
[22 Oct 2013 9:47] Martin Burrow
My web host moved my account to another server, and voila, it's working. Interestingly enough, it's the same version of MySQL on both servers. After the migration I began getting these errors:

[22-Oct-2013 06:39:02 UTC] PHP Warning: PHP Startup: Unable to load dynamic library \'/usr/local/lib/php/extensions/no-debug-non-zts-20060613/sqlite.so\' - /usr/local/lib/php/extensions/no-debug-non-zts-20060613/sqlite.so: cannot open shared object file: No such file or directory in Unknown on line 0

[22-Oct-2013 06:39:02 UTC] PHP Warning: PHP Startup: Unable to load dynamic library \'/usr/local/lib/php/extensions/no-debug-non-zts-20060613/pdo_mysql.so\' - /usr/local/lib/php/extensions/no-debug-non-zts-20060613/pdo_mysql.so: cannot open shared object file: No such file or directory in Unknown on line 0

So, it appears the webhost had enabled sqlite and pdo_mysql in my php.ini file.  These libraries evidently don't exist on the new server and that's why I'm not having the problem that's being described in this log.

So, anyone else gets this problem, check whether you have pdo_mysql and/or sqlite enabled in your PHP config, as I suspect disabling it will resolve the issue.
[1 Nov 2013 15:16] Doug Harple
I disagree with the status of "Not a Bug".  One feature, database dumps, prevents another feature, prepared statements, from functioning correctly.  That sounds like a bug to me.  I understand that I can re-tune the database to reduce the prevalence of the issue, but it's still an absurdly high risk to assume for such a silly issue.  Our solution to this will be to stop using prepared statements, at least until this issue is taken seriously and addressed.
[10 Apr 2014 12:27] Rik Vanachterberg
This affects me too, running MySQL 5.6.15 (Percona flavor). 
Increasing the table_definition_cache to 16384 works for me.
(Additionally, my table_open_cache is 2000; tmp_table_size and join_buffer_size aren't set).
I can reproduce this with a PHP script attempting to run a prepared SELECT statement. There are no (concurrent) mysqldump processes running on our servers; we can reproduce this bug on fairly idle servers. I'd be happy to supply more information.
[24 Aug 2014 0:36] A B
This can easily be reproduced with PHP PDO extension as well as mysqli.
Yo just have to disable prepared statements emulation…

$_pdo = new PDO($DSN, $SQLUSER, $SQLPWD, array(PDO::ATTR_EMULATE_PREPARES => 0));
[6 Jan 2015 20:39] Dan Fournier
On MySQL 5.1.73, I was able to reproduce this bug by only specifying the database in question and omitting the table. 

I was able to do a backup of the database using DBForge and MySQL Workbench without any issues; sites using mysqli libraries were still functional. I remembered that in MySQL Workbench, I could see individual statements go by with a table name specified. 

I then went into the CLI and executed a similar statement without specifying the table name, and websites using mysqli libraries stopped being functional.

Scenario 1, that works:

C:\wamp\bin\mysql\mysql5.1.41\bin>mysqldump.exe --user=root --max_allowed_packet=1G --host=db_host_here  --port=3306 --default-character-set=utf8 "database_here" "table_here" –p

Scenario 2, that doesn’t work (notice omitted table name):

C:\wamp\bin\mysql\mysql5.1.41\bin>mysqldump.exe --user=root --max_allowed_packet=1G --host=db_host_here  --port=3306 --default-character-set=utf8 "database_here"  -p

TLDR: mysqldump without a table name will crash sites using mysqli
[23 Jan 2017 15:43] Nikolaos Gaitanis
We are facing the same issue without running a mysqldump. Suggestions to change the two cache parameters have not helped.
[6 Apr 2017 20:31] Ray Malanga
Just moved a website to GoDaddy hosting and this is happening now.  The site is riddled with prepared statements.  All others work fine except one which is producing this error:

           Mysqli statement execute error : Prepared statement needs to be re-prepared

I can't tell GoDaddy to reboot or change their MySql parameters, but I can provide you any info you want, including the whole phpinfo() dump.
The db server is 5.6.33, as is the mysqli client API library version (as reported by phpinfo).

The old site ran fine for eight years through various database upgrades and apparently an (unbekownst to me) switch to Percona and is presently reported as: 

           Server version: 5.6.32-78.1-log - Percona Server (GPL), Release 78.1, Revision 8bb53b6

with php reporting:
           Database client version: libmysql - 5.1.73
           PHP extension: mysqli

I believe it may be relevant that, in my case, the only failing prepared statement references a view rather than a table.
[26 Jul 2017 23:05] Andrei Dexter
Hi, 2017!

I'm also getting this error message called: Prepared statement needs to be re-prepared...

So, where is the fix? It depend on what?

I see that some of users said that changing settings like table_open_cache doesn't seems to work.

So, again, 2017, how can I get rid of this error?
[27 Jul 2017 19:03] A B
The solution is to update to 5.7, the issue seems to be limited to specific old MySQL versions.
[2 Sep 2017 0:34] Michael Baldwin
I can confirm this is also happening on MySQL version 5.6.34. This is still a supported version, so updating to 5.7 shouldn't be required to fix the problem. I'm waiting on my client's shared host to get back to me as they had to escalate the ticket to see if there was anything they could remedy on their end.