如何摆脱MySQL错误'准备好的声明需要重新准备'

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

我重写了我的网站php-code并添加了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服务器后问题已经消失。感谢帮助。

5 个解决方案

#1

This is a possibility: MySQL bug #42041

这是一种可能性:MySQL bug#42041

They suggest upping the value of table_definition_cache.

他们建议增加table_definition_cache的值。

You can read about re-preparation in the MySQL docs.

您可以在MySQL文档中阅读有关重新准备的内容。

#2

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

@ 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: actually this 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-prepared I got.

我没有测试模拟预处理语句对加载我的网站的持续时间的影响,但它可以解决错误SQLSTATE [HY000]:一般错误:1615准备好的语句需要重新准备我得到。

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

性能更新:模拟版本似乎稍快(模拟32.7±1.4ms,正常35.0±2.3ms,n = 10,双尾学生T检验p值= 0.027)。

#3

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

我希望这有助于某人

#4

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;

#5

-1

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服务器上的配置设置时,这似乎是一个合理的解决方案。

#1