php 如何摆脱 MySQL 错误“准备好的语句需要重新准备”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4380813/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to get rid of MySQL error 'Prepared statement needs to be re-prepared'
提问by Roman
I've rewritten my site php-code and added MySQL Stored Procedures.
我已经重写了我的站点 php 代码并添加了 MySQL 存储过程。
In my local version everything works fine but after I uploaded my site to hosting server I'm constatly getting fatal error 'Prepared statement needs to be re-prepared'.
在我的本地版本中，一切正常，但在我将网站上传到托管服务器后，我不断收到致命错误“需要重新准备准备好的语句”。
Sometimes page loads, sometimes loading fails and I see this error. What's that?
有时页面加载，有时加载失败，我看到这个错误。那是什么？
UPDATE:The problem has gone after moving to VPS server. Thanks for help.
更新：移至 VPS 服务器后问题已解决。感谢帮助。
回答by docwhat
This is a possibility: MySQL bug #42041
这是一种可能性：MySQL 错误 #42041
They suggest upping the value of table_definition_cache.
他们建议提高table_definition_cache.
You can read about re-preparation in the MySQL docs.
您可以在 MySQL 文档 中阅读有关重新准备的信息。
回答by Marten Koetsier
@docwhat'sanswer seems nice, but on a shared hosting server, not everyone is allowed to touch the table_open_cacheor table_definition_cacheoptions.
@docwhat 的答案似乎不错，但在共享托管服务器上，并非每个人都可以触摸table_open_cache或table_definition_cache选项。
Since this error is related to prepared statements, I have tried to 'emulate' those with PDO by providing the following option:
由于此错误与准备好的语句有关，因此我尝试通过提供以下选项来“模拟”那些带有 PDO 的错误：
$dbh = new PDO('mysql:host=localhost;dbname=test', $user, $pass, [
PDO::ATTR_EMULATE_PREPARES => true
]);
Note: actuallythis is in a Laravel 5.6 project, and I added the option in config/database.php:
注意：实际上这是在 Laravel 5.6 项目中，我添加了选项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-preparedI got.
我没有测试模拟准备好的语句对加载我的网站的持续时间的影响，但它可以解决SQLSTATE[HY000]: General error: 1615 Prepared statement needs to be re-prepared我遇到的错误。
Updateon the performance: the emulated version seems to be slightlyfaster (32.7±1.4ms emulated, 35.0±2.3ms normal, n=10, p-value=0.027 for two-tailed Student's T-test).
性能更新：模拟版本似乎稍快（模拟 32.7±1.4ms，正常为 35.0±2.3ms，n=10，双尾学生 T 检验的 p 值=0.027）。
回答by Sanjay Kushwaha
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.
此问题通常发生在通过使用任何计算机语言（如 Java）或从后端调用过程调用过程时。
Solution: Increase the size of the cache by using (executing) below script.
解决方案：通过使用（执行）下面的脚本来增加缓存的大小。
Script: set global table_definition_cache = 4000;
脚本：设置全局 table_definition_cache = 4000；
回答by Gabriel
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
我希望这有助于某人
回答by Charles Brocchiero
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 subqueryyou could store the definition of your view in a function
对于共享主机上的人来说，这是一种解决方法，并且不想冒 sql 注入的风险。根据这篇文章： Laravel Fluent Query Builder Join with subquery您可以将视图的定义存储在函数中
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
这是一种 Laravel 方法，但我确信它可以应用于大多数情况，并且不需要大量的代码重构或架构更改。此外，它相对安全，因为您的报表随时准备就绪
回答by John Langford
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.
我在共享托管环境中的 Ruby on Rails 中遇到了同样的错误。它可能不是最安全的解决方案，但禁用准备好的语句为我摆脱了错误消息。
This can be done by adding the "prepared_statements: false" setting to your database.yml file:
这可以通过将“prepared_statements：false”设置添加到您的 database.yml 文件来完成：
production:
prepared_statements: false
This seems like a reasonable solution when you don't have control over the configuration settings on the MySQL server.
当您无法控制 MySQL 服务器上的配置设置时，这似乎是一个合理的解决方案。
