MySQL Bugs: #42041: Prepared-Statement fails when MySQL-Server under load

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

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

mysqldump maybe flushing/locking tables.  looks similar to bug #41119

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?

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.

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".

I still need feedback and idea how to repeat this problem on our side.

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".

I am facing the same issue in 5.1.52.

I also tried adding continue handler for 1615.

But the problem still persists

the error comes at the time of execution of the prepared statement.

just to add my OS is debian etch

Saurabh,

Does it also happen while mysqldump is executed concurrently?

Yes.

But now we have found a solution.

We increased the value of table_definition_cache.

All reporters,

please try to increased the value of table_definition_cache and let us know if this will help.

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".

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

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.

Anders,

thank you for the feedback. Do you issue parallel FLUSH TABLES statements in time when see the problem.

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".

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

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

Please see prior request from Sveta. Thanks.

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".

Doesn't http://dev.mysql.com/doc/refman/5.1/en/statement-repreparation.html explain the problem ?

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?

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"

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.

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..?

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

Thanks for that Derek, it does appear to have helped. Not seeing the error at all now.

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

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???

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.

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.

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.

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));

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

We are facing the same issue without running a mysqldump. Suggestions to change the two cache parameters have not helped.

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.

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?

The solution is to update to 5.7, the issue seems to be limited to specific old MySQL versions.

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.

This problem is caused by the group_ concat_ max_ Len is too long. You can try shortening it.