laravel 发送 STMT_PREPARE 数据包时出错。PID=2
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22932723/
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
Error while sending STMT_PREPARE packet. PID=2
提问by clod986
I have no idea why I'm experiencing this bug.
我不知道为什么我会遇到这个错误。
I have the following script:
我有以下脚本：
foreach($brands as $brand){ // about 600items for this loop
....
....
DB::table('mailing_list')->insert(array(
'email' => $email,
'source' => $source,
'source_url'=> $brand->external_url,
'facebook_url' => $facebook_url,
'instagram_id' => $instagram_username,
'instagram_url' => $instagram_url,
'instagram_followers' => $instagram_followers
));
}
which breaks always at the same item
总是在同一项目上中断
Error while sending STMT_PREPARE packet. PID=2 (SQL: insert into `mailing_list` (`email`, `source`, `source_url`, `facebook_url`, `instagram_id`, `instagram_url`, `instagram_followers`) values ([email protected], source, www.url.com, https://www.facebook.com/url, some_username, http://instagram.com/url, 501))
Before I was getting 29 queries executed, now 34.
在我执行 29 个查询之前，现在是 34 个。
I would like to understand this error better: it might be the single entry breaking it, but even if the data I posted are gibberish the actual ones look fine to me.
我想更好地理解这个错误：它可能是单个条目破坏了它，但即使我发布的数据是乱码，实际的数据对我来说也很好。
WHAT I'VE TRIED:
我试过的：
set_time_limit(3600);
DB::connection()->disableQueryLog();
DB::reconnect(Config::get('database.default')); // this one after each foreach loop
采纳答案by clod986
I've solved it by reducing the items passed to the foreach loop.
我通过减少传递给 foreach 循环的项目来解决它。
$all_brands = Brand::all();
$padding = 0;
$batch = 100;
while($all_brands->count() > $padding){
$brands = Brand::orderBy('id', 'asc')->skip($padding)->take($batch)->get();
foreach($brands as $brand){
....
....
DB::table('mailing_list')->insert(array(
'email' => $email,
'source' => $source,
'source_url'=> $brand->external_url,
'facebook_url' => $facebook_url,
'instagram_id' => $instagram_username,
'instagram_url' => $instagram_url,
'instagram_followers' => $instagram_followers
));
}
$padding = $padding + $batch;
}
回答by Armen Markossyan
The real reason and solution of the problem can be found in my blog post:
问题的真正原因和解决方法可以在我的博文中找到：
Laravel 4 and "Error while sending STMT_PREPARE packet"
Laravel 4 和“发送 STMT_PREPARE 数据包时出错”
It's impossible to describe the whole answer here but, long story short, there was a bug in Laravel 4's \Illuminate\Database\MySqlConnectionclass. Specifically, in its causedByLostConnectionmethod which looked like this:
在这里无法描述整个答案，但是，长话短说，Laravel 4 的\Illuminate\Database\MySqlConnection课程中存在一个错误。具体来说，它的causedByLostConnection方法如下所示：
/**
* Determine if the given exception was caused by a lost connection.
*
* @param \Illuminate\Database\QueryException
* @return bool
*/
protected function causedByLostConnection(QueryException $e)
{
return str_contains($e->getPrevious()->getMessage(), 'server has gone away');
}
Unfortunately, "server has gone away" is not the only message indicating that a connection with MySQL was lost.
不幸的是，“服务器已消失”并不是表明与 MySQL 的连接丢失的唯一消息。
The same method in Laravel 5 checks for 6 additional messages and this solves the problem:
Laravel 5 中的相同方法检查 6 个附加消息，这解决了问题：
/**
* Determine if the given exception was caused by a lost connection.
*
* @param \Exception $e
* @return bool
*/
protected function causedByLostConnection(Exception $e)
{
$message = $e->getMessage();
return Str::contains($message, [
'server has gone away',
'no connection to the server',
'Lost connection',
'is dead or not enabled',
'Error while sending',
'decryption failed or bad record mac',
'SSL connection has been closed unexpectedly',
]);
}
回答by ban-geoengineering
I had this problem in a script that did the following operations in this order:
我在按以下顺序执行以下操作的脚本中遇到了这个问题：
- Connect to local database
- Connect to a remote API via cURL
- Update local database
- 连接到本地数据库
- 通过 cURL 连接到远程 API
- 更新本地数据库
It seems to be that step 2 was sometimes taking too long to complete, which ended up with a timed out / broken connection to the database.
似乎步骤 2 有时需要很长时间才能完成，最终导致与数据库的连接超时/断开。
To fix, I simply changed the order of operations, so I connect to the database afterthe long-running operation, just before I do the work on the database:
为了解决这个问题，我只是改变了操作顺序，所以我在长时间运行的操作之后连接到数据库，就在我对数据库进行工作之前：
- Connect to a remote API via cURL
- Connect to local database
- Update local database
- 通过 cURL 连接到远程 API
- 连接到本地数据库
- 更新本地数据库
回答by Arman Ara Arojan
I had the same issue few days ago, so the simplest solution to this problem was to make some changes to the database (mysql in my case) .cnf config file. The variable that required to be changed was wait_timeout. By default it is 30seconds, but if you increase it, then you won't get this error. In my case I have set it to 10 minutes, and now my script works like charm.
几天前我遇到了同样的问题，所以解决这个问题的最简单的方法是对数据库（在我的例子中是 mysql）.cnf 配置文件进行一些更改。需要更改的变量是wait_timeout。默认情况下它是30 seconds，但如果你增加它，那么你不会得到这个错误。就我而言，我已将其设置为 10 分钟，现在我的脚本就像魅力一样。
What was happening is that the script would initialize, and sit and wait until all my API calls are finished before starting to parse each of them, in a for each loop also by the way. So until it got to the point when it required to do database query, it had already lost connection. Hope it helps!
发生的事情是脚本将初始化，并等待我的所有 API 调用完成，然后再开始解析它们中的每一个，顺便说一下，在 for each 循环中也是如此。所以直到它需要进行数据库查询时，它已经失去了连接。希望能帮助到你！
