php 发送 QUERY 数据包时出错
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/30753674/
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 QUERY packet
提问by Eric Bana
i was trying to insert some data into the database but i got this error "Error while sending QUERY packet"
我试图向数据库中插入一些数据，但我收到此错误“发送 QUERY 数据包时出错”
$insertDeta = $conPat->prepare("insert into table1(data) VALUES(:data)");
$insertDeta->bindParam(':data',$data);
$conPat->beginTransaction();
$insertDeta->execute();
$conPat->commit();
but what i think the problem is that size of the datais over 16MB.
the data type of the column is set as longtext, which i think can keep data as big as 4GB.
但我认为问题是数据大小超过 16MB。
该列的数据类型设置为longtext，我认为可以将数据保持为 4GB。
I dont know if PDOs is having problems running the query or transfering 16MB of data to the database.
Thats the only guess i can make as mysql might send the data in packetsand the packet cannot hold data as large as 16MB.
我不知道 PDO 是否在运行查询或将 16MB 数据传输到数据库时遇到问题。
这是我唯一能做的猜测，因为 mysql 可能会以数据包的形式发送数据，而数据包不能容纳 16MB 大的数据。
回答by Deepak Nirala
You guessed right MySQL have limitation for size of data, you need to break your query in small group of records or you can Change your max_allowed_packet by using SET GLOBAL max_allowed_packet=524288000;
您猜对了 MySQL 对数据大小有限制，您需要在小记录组中中断查询，或者您可以使用更改 max_allowed_packet SET GLOBAL max_allowed_packet=524288000;
回答by Faisal
You can solve this problem by following few steps:
您可以通过以下几个步骤来解决此问题：
1) open your terminal window
1）打开你的终端窗口
2) please write following command in your terminal
2）请在您的终端中写入以下命令
ssh root@yourIP port
3) Enter root password
3）输入root密码
4) Now edit your server my.cnf file using below command
4）现在使用以下命令编辑您的服务器 my.cnf 文件
nano /etc/my.cnf
if command is not recognized do this first or try vi then repeat: yum install nano.
如果无法识别命令，请先执行此操作或尝试 vi 然后重复：yum install nano。
OR
或者
vi /etc/my.cnf
5) Add the line under the [MYSQLD] section. :
5) 在 [MYSQLD] 部分下添加行。：
max_allowed_packet=524288000 (obviously adjust size for whatever you need)
wait_timeout = 100
6) Control + O (save) then ENTER (confirm) then Control + X (exit file)
6) Control + O (save) 然后 ENTER (confirm) 然后 Control + X (exit file)
7) Then restart your mysql server by following command
7）然后通过以下命令重新启动您的mysql服务器
/etc/init.d/mysql stop
/etc/init.d/mysql start
8) You can verify by going into PHPMyAdmin or opening a SQL command window and executing:
8) 您可以通过进入 PHPMyAdmin 或打开 SQL 命令窗口并执行来验证：
SHOW VARIABLES LIKE 'max_allowed_packet'
This works for me. I hope it should work for you.
这对我有用。我希望它对你有用。
回答by Guillaume
You may also have this error if the variable wait_timeoutis too low.
如果变量wait_timeout太低，您也可能会遇到此错误。
If so, you may set it higher like that:
如果是这样，您可以将其设置得更高：
SET GLOBAL wait_timeout=10;
This was the solution for the same error in my case.
在我的情况下，这是相同错误的解决方案。
回答by Michael Fever
In /etc/my.cnfadd:
另外/etc/my.cnf：
max_allowed_packet=32M
It worked for me. You can verify by going into PHPMyAdmin and opening a SQL command window and executing:
它对我有用。您可以通过进入 PHPMyAdmin 并打开 SQL 命令窗口并执行来验证：
SHOW VARIABLES LIKE 'max_allowed_packet'
回答by Bj?rn Tantau
I encountered a rare edge case in cygwin, where I would get this error when doing exec('rsync');somewhere before the query. Might be a general PHP problem, but I could only reproduce this in cygwin with rsync.
我在 cygwin 中遇到了一个罕见的边缘情况，在exec('rsync');查询之前的某个地方执行时我会得到这个错误。可能是一般的 PHP 问题，但我只能在 cygwin 中使用 rsync 重现此问题。
$pdo = new PDO('mysql:host=127.0.0.1;dbname=mysql', 'root');
var_dump($pdo->query('SELECT * FROM db'));
exec('rsync');
var_dump($pdo->query('SELECT * FROM db'));
produces
产生
object(PDOStatement)#2 (1) {
["queryString"]=>
string(16) "SELECT * FROM db"
}
PHP Warning: Error while sending QUERY packet. PID=15036 in test.php on line 5
bool(false)
Bug reported in https://cygwin.com/ml/cygwin/2017-05/msg00272.html
回答by cjhill
You cannot have the WHEREclause in an INSERTstatement.
您不能在WHERE语句中包含该子句INSERT。
insert into table1(data) VALUES(:data) where sno ='45830'
insert into table1(data) VALUES(:data) where sno ='45830'
Should be
应该
insert into table1(data) VALUES(:data)
insert into table1(data) VALUES(:data)
Update: You have removed that from your code (I assume you copied the code in wrong). You want to increase your allowed packet size:
更新：您已将其从代码中删除（我假设您错误地复制了代码）。您想增加允许的数据包大小：
SET GLOBAL max_allowed_packet=32M
SET GLOBAL max_allowed_packet=32M
Change the 32M(32 megabytes) up/down as required. Here is a link to the MySQL documentation on the subject.
32M根据需要向上/向下更改（32 兆字节）。这是有关该主题的 MySQL 文档的链接。
回答by Piemol
If inserting 'too much data' fails due to the max_allowed_packetsetting of the database server, the following warning is raised:
如果由于max_allowed_packet数据库服务器的设置导致插入“数据过多”失败，则会引发以下警告：
SQLSTATE[08S01]: Communication link failure: 1153 Got a packet bigger than
'max_allowed_packet' bytes
If this warning is catched as exception (due to the set error handler), the database connection is (probably) lost but the application doesn't know about this (failing inserts can have several causes). The next query in line, which can be as simple as:
如果此警告被捕获为异常（由于设置了错误处理程序），则数据库连接（可能）丢失，但应用程序不知道这一点（插入失败可能有多种原因）。行中的下一个查询，可以很简单：
SELECT 1 FROM DUAL
Will then fail with the error this SO-question started:
然后将失败并出现此 SO-question 开始的错误：
Error while sending QUERY packet. PID=18486
Simple test script to reproduce my explanation, try it with and without the error handler to see the difference in impact:
重现我的解释的简单测试脚本，尝试使用和不使用错误处理程序以查看影响的差异：
set_error_handler(function($errno, $errstr, $errfile, $errline, array $errcontext) {
// error was suppressed with the @-operator
if (0 === error_reporting()) {
return false;
}
throw new ErrorException($errstr, 0, $errno, $errfile, $errline);
});
try
{
// $oDb is instance of PDO
var_dump($oDb->query('SELECT 1 FROM DUAL'));
$oStatement = $oDb->prepare('INSERT INTO `test` (`id`, `message`) VALUES (NULL, :message);');
$oStatement->bindParam(':message', $largetext, PDO::PARAM_STR);
var_dump($oStatement->execute());
}
catch(Exception $e)
{
$e->getMessage();
}
var_dump($oDb->query('SELECT 2 FROM DUAL'));
回答by Sanya Snex
Had such a problem when executing forking in php for command line. In my case from time to time the php killed the child process. To fix this, just wait for the process to complete using the command pcntl_wait($status);
在 php 中为命令行执行 fork 时遇到了这样的问题。就我而言，php 不时杀死子进程。要解决此问题，只需使用命令pcntl_wait($status);等待进程完成即可。
here's a piece of code for a visual example:
这是一段可视化示例的代码：
#!/bin/php -n
<?php
error_reporting(E_ALL & ~E_NOTICE);
ini_set("log_errors", 1);
ini_set('error_log', '/media/logs/php/fork.log');
$ski = substr(str_shuffle(str_repeat("0123456789abcdefghijklmnopqrstuvwxyz", 5)), 0, 5);
error_log(getmypid().' '.$ski.' start my php');
$pid = pcntl_fork();
if($pid) {
error_log(getmypid().' '.$ski.' start 2');
// Wait for children to return. Otherwise they
// would turn into "Zombie" processes
// !!!!!! add this !!!!!!
pcntl_wait($status);
// !!!!!! add this !!!!!!
} else {
error_log(getmypid().' '.$ski.' start 3');
//[03-Apr-2020 12:13:47 UTC] PHP Warning: Error while sending QUERY packet. PID=18048 in /speed/sport/fortest.php on line 22457
mysqli_query($con,$query,MYSQLI_ASYNC);
error_log(getmypid().' '.$ski.' sleep child');
sleep(15);
exit;
}
error_log(getmypid().' '.$ski.'end my php');
exit(0);
?>
