解决“MySQL服务器已经走了”错误 [英] Solving "MySQL server has gone away" errors

问题描述

我在PHP中编写了一些代码，它们返回.edu域的html内容。简要介绍如下： PHP中的Web爬网程序错误

I have written some code in PHP that returns the html content from .edu domains. A brief introduction is given here: Errors regarding Web Crawler in PHP

当抓取的链接数量很少（大约40个URLS）时，抓取工具正常工作，但是我在这个数字后收到MySQL server has gone away错误。

The crawler works fine when the number of links to crawl are small (something around 40 URLS) but I am getting "MySQL server has gone away" error after this number.

我在MySQL表中存储html内容作为长文本，我没有得到为什么在至少40-50次插入后错误到达。

I am storing html content as longtext in MySQL tables and I am not getting why the error arrives after at least 40-50 insertions.

在这方面，任何帮助都非常感激。

Any help in this regard is highly appreciated.

请注意，我已经改变了wait_timeout和max_allowed_pa​​cket来适应我的查询和php代码，现在我不知道该怎么办。

Please note that I have already altered the wait_timeout and max_allowed_packet to accomodate my queries and the php code and now I don't know what to do. Please help me in this regard.

推荐答案

您可能倾向于通过在查询前pingmysql服务器来处理这个问题。这是一个坏主意。有关更多信息，请查看此SO帖子：我应该先ping mysql服务器每个查询？

You might be inclined to handle this problem by "pinging" the mysql server before a query. This is a bad idea. For more on why, check this SO post: Should I ping mysql server before each query?

处理问题的最好方法是在中包装查询try / catch 阻止和捕获任何数据库异常，以便您可以正确处理它们。这在长期运行和/或守护进程类型脚本中尤为重要。所以，这里有一个非常基本的例子，使用连接管理器来控制对DB连接的访问​​：

The best way to handle the issue is by wrapping queries inside try/catch blocks and catching any database exceptions so that you can handle them appropriately. This is especially important in long running and/or daemon type scripts. So, here's a very basic example using a "connection manager" to control access to DB connections:

class DbPool {

    private $connections = array();

    function addConnection($id, $dsn) {
        $this->connections[$id] = array(
            'dsn' => $dsn,
            'conn' => null
        );
    }

    function getConnection($id) {
        if (!isset($this->connections[$id])) {
            throw new Exception('Invalid DB connection requested');
        } elseif (isset($this->connections[$id]['conn'])) {
            return $this->connections[$id]['conn'];
        } else {
            try {
                // for mysql you need to supply user/pass as well
                $conn = new PDO($dsn);

                // Tell PDO to throw an exception on error
                // (like "MySQL server has gone away")
                $conn->setAttribute(
                    PDO::ATTR_ERRMODE,
                    PDO::ERRMODE_EXCEPTION
                );
                $this->connections[$id]['conn'] = $conn;

                return $conn;
            } catch (PDOException $e) {
                return false;
            }
        }
    }

    function close($id) {
        if (!isset($this->connections[$id])) {
            throw new Exception('Invalid DB connection requested');
        }
        $this->connections[$id]['conn'] = null;
    }


}


class Crawler {

    private $dbPool;

    function __construct(DbPool $dbPool) {
        $this->dbPool = $dbPool;
    }

    function crawl() {
        // craw and store data in $crawledData variable
        $this->save($crawledData);
    }

    function saveData($crawledData) {
        if (!$conn = $this->dbPool->getConnection('write_conn') {
            // doh! couldn't retrieve DB connection ... handle it
        } else {
            try {
                // perform query on the $conn database connection
            } catch (Exception $e) {
                $msg = $e->getMessage();
                if (strstr($msg, 'MySQL server has gone away') {
                    $this->dbPool->close('write_conn');
                    $this->saveData($val);
                } else {
                    // some other error occurred
                }
            }
        }
    }
}

这篇关于解决“MySQL服务器已经走了”错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！