PHP Mysql PDO:一般错误:2006 MySQL 服务器已消失 [英] PHP Mysql PDO: General error: 2006 MySQL server has gone away

问题描述

我目前正在运行一个自定义 php 脚本，该脚本在我的本地主机上运行时可以正常工作，但是在共享主机上运行时，我收到以下错误:

Im currently running a custom php script which when running on my localhost works fine however when running on shared hosting, i receive the following error:

致命错误:未捕获的异常PDOException"，消息为SQLSTATE[HY000]:一般错误:2006

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 2006

MySQL 服务器已经消失'在...... 205 堆栈跟踪:

MySQL server has gone away' in ...... 205 Stack trace:

0 ........ PDO->prepare('SELECT * FROM o...') #1 {main} 在 .. 205 行中抛出 ..

0 ........ PDO->prepare('SELECT * FROM o...') #1 {main} thrown in .......... on line 205

(我已经用......替换了文件路径)

(I've replaced the file paths with .......)

我尝试过的事情:

1. 添加PDO::ATTR_TIMEOUT =>999999999999999999999"，延长任何超时时间.

检查了max_allowed_pa​​cket(我找到的每个答案都说这是原因)我的本地机器当前设置为 1048576 (1mb) 但是我的主机当前设置为 268435456 (268mb)

Checked the max_allowed_packet (every answer ive found says this is the cause) My local machine is currently set to 1048576 (1mb) however my hosting is currently set to 268435456 (268mb)

如果我的本地机器是 268mb 而我的主机是 1mb，我会理解，但这并不能说明这是问题的原因，因为事实并非如此.

I would understand if my local machine was 268mb and my hosting was 1mb but this doesn't make any sense that it is the cause of the problem, as this is not the case.

我确实尝试在我的主机上增加它，但由于它是共享的，我无权更改此全局变量.

I did try to increase it on my hosting but as its shared I don't have the permissions to change this global variable.

关于我可以尝试的任何其他想法?

Any other ideas of what I could try?

仅供参考，这是脚本:

//MYSQL PREPARE STATEMENTS
//check to see if a product is in database
$mysql['productcheck'] = $mysql['pdo']->prepare('SELECT * FROM oc_product WHERE sku = ?');
$newproductcount=0;
$x = 0;
while(($maintable[$x]['status']=="active") || ($maintable[$x]['status']=="new") || ($maintable[$x]['status']=="discontinued") || ($maintable[$x]['status']=="archive")) {
    if(($maintable[$x]['status']=="active") || ($maintable[$x]['status']=="new")){
        //check to see if product exsists
        $params[1]=$maintable[$x]['euid'];
        $mysql['productcheck']->execute([$params[1]]);
        if($mysql['productcheck']->rowCount()==0){
            //PRODUCT DOESN'T EXSIST
            echo "Product doesnt exsists!";
            $newproductcount++;
        }
    }
$x++;
}

推荐答案

导致 MySQL 服务器消失错误的一些其他常见原因是:

Some other common reasons for the MySQL server has gone away error are:

• 您(或数据库管理员)已使用 KILL 语句或 mysqladmin kill 命令终止了正在运行的线程.

• You (or the db administrator) has killed the running thread with a KILL statement or a mysqladmin kill command.

您在关闭与服务器的连接后尝试运行查询.这表示应更正应用程序中的逻辑错误.

You tried to run a query after closing the connection to the server. This indicates a logic error in the application that should be corrected.

在不同主机上运行的客户端应用程序没有必要的权限从该主机连接到 MySQL 服务器.

A client application running on a different host does not have the necessary privileges to connect to the MySQL server from that host.

客户端的 TCP/IP 连接超时.如果您一直在使用以下命令，则可能会发生这种情况:mysql_options(..., MYSQL_OPT_READ_TIMEOUT,...) 或 mysql_options(..., MYSQL_OPT_WRITE_TIMEOUT,...).在这种情况下，增加超时可能有助于解决问题.

You got a timeout from the TCP/IP connection on the client side. This may happen if you have been using the commands: mysql_options(..., MYSQL_OPT_READ_TIMEOUT,...) or mysql_options(..., MYSQL_OPT_WRITE_TIMEOUT,...). In this case increasing the timeout may help solve the problem.

你遇到了服务器端超时，客户端自动重连被禁用(MYSQL结构中的重连标志等于0).

You have encountered a timeout on the server side and the automatic reconnection in the client is disabled (the reconnect flag in the MYSQL structure is equal to 0).

您使用的是 Windows 客户端并且服务器在发出命令之前断开了连接(可能是因为 wait_timeout 已过期).

You are using a Windows client and the server had dropped the connection (probably because wait_timeout expired) before the command was issued.

Windows 上的问题是，在某些情况下，MySQL 在写入到服务器的 TCP/IP 连接时不会从操作系统收到错误，而是在尝试从连接中读取答案时收到错误.

The problem on Windows is that in some cases MySQL does not get an error from the OS when writing to the TCP/IP connection to the server, but instead gets the error when trying to read the answer from the connection.

如果自上次查询以来已经很长时间(这是连接器/ODBC所做的)或在mysqld服务器上设置wait_timeout如此之高，则解决此问题的方法是在连接上执行mysql_ping()它在实践中永远不会超时.

The solution to this is to either do a mysql_ping() on the connection if there has been a long time since the last query (this is what Connector/ODBC does) or set wait_timeout on the mysqld server so high that it in practice never times out.

如果向服务器发送不正确或过大的查询，也会出现这些错误.如果 mysqld 收到一个太大或乱序的数据包，它会假设客户端出现问题并关闭连接.如果您需要大查询(例如，如果您正在处理大 BLOB 列)，您可以通过设置服务器的 max_allowed_pa​​cket 变量来增加查询限制，该变量的默认值为 4MB.您可能还需要增加客户端的最大数据包大小.有关设置数据包大小的更多信息，请参见第 B.5.2.10 节数据包太大".

You can also get these errors if you send a query to the server that is incorrect or too large. If mysqld receives a packet that is too large or out of order, it assumes that something has gone wrong with the client and closes the connection. If you need big queries (for example, if you are working with big BLOB columns), you can increase the query limit by setting the server's max_allowed_packet variable, which has a default value of 4MB. You may also need to increase the maximum packet size on the client end. More information on setting the packet size is given in Section B.5.2.10, "Packet Too Large".

插入大量行的 INSERT 或 REPLACE 语句也会导致此类错误.无论要插入的行数如何，这些语句中的任何一个都会向服务器发送单个请求；因此，您通常可以通过减少每次 INSERT 或 REPLACE 发送的行数来避免该错误.

An INSERT or REPLACE statement that inserts a great many rows can also cause these sorts of errors. Either one of these statements sends a single request to the server irrespective of the number of rows to be inserted; thus, you can often avoid the error by reducing the number of rows sent per INSERT or REPLACE.

如果您的客户端版本早于 4.0.8 而您的服务器版本为 4.0.8 及更高版本，或者相反的情况，您发送 16MB 或更大的数据包也会导致连接丢失.

You also get a lost connection if you are sending a packet 16MB or larger if your client is older than 4.0.8 and your server is 4.0.8 and above, or the other way around.

如果主机名查找失败(例如，如果您的服务器或网络所依赖的 DNS 服务器出现故障)，也可能会看到此错误.这是因为 MySQL 依赖于主机系统进行名称解析，但无法知道它是否正常工作——从 MySQL 的角度来看，这个问题与任何其他网络超时没有区别.

It is also possible to see this error if host name lookups fail (for example, if the DNS server on which your server or network relies goes down). This is because MySQL is dependent on the host system for name resolution, but has no way of knowing whether it is working—from MySQL's point of view the problem is indistinguishable from any other network timeout.

如果使用 --skip-networking 选项启动 MySQL，您可能还会看到 MySQL 服务器已消失错误.

You may also see the MySQL server has gone away error if MySQL is started with the --skip-networking option.

如果 MySQL 端口(默认 3306)被防火墙阻止，从而阻止与 MySQL 服务器的任何连接，则会发生另一个可能导致此错误的网络问题.

Another networking issue that can cause this error occurs if the MySQL port (default 3306) is blocked by your firewall, thus preventing any connections at all to the MySQL server.

对于分叉子进程的应用程序，您也可能会遇到此错误，所有这些子进程都尝试使用与 MySQL 服务器相同的连接.这可以通过为每个子进程使用单独的连接来避免.

You can also encounter this error with applications that fork child processes, all of which try to use the same connection to the MySQL server. This can be avoided by using a separate connection for each child process.

您遇到了在执行查询时服务器死机的错误.

You have encountered a bug where the server died while executing the query.

检查此链接:走开

这篇关于PHP Mysql PDO:一般错误:2006 MySQL 服务器已消失的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！