php 解决“MySQL 服务器已消失”错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8689649/
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
Solving "MySQL server has gone away" errors
提问by Rafay
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
我用 PHP 编写了一些代码，用于从 .edu 域返回 html 内容。这里简单介绍一下：关于 PHP 中 Web Crawler 的错误
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.
当要抓取的链接数量很少（大约 40 个 URL）时，抓取工具工作正常，但在此数字之后出现“MySQL 服务器已消失”错误。
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.
我将 html 内容作为长文本存储在 MySQL 表中，但我不明白为什么在至少 40-50 次插入后会出现错误。
Any help in this regard is highly appreciated.
高度赞赏这方面的任何帮助。
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.
请注意，我已经更改了 wait_timeout 和 max_allowed_packet 以适应我的查询和 php 代码，现在我不知道该怎么做。请在这方面帮助我。
采纳答案by rdlowrey
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?
您可能倾向于通过在查询之前“ping”mysql 服务器来处理此问题。这是一个坏主意。有关原因的更多信息，请查看此 SO 帖子：我应该在每次查询之前 ping mysql 服务器吗？
The best way to handle the issue is by wrapping queries inside try/catchblocks 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:
处理该问题的最佳方法是将查询包装在try/catch块内并捕获任何数据库异常，以便您可以适当地处理它们。这在长时间运行和/或守护程序类型的脚本中尤为重要。因此，这是一个非常基本的示例，使用“连接管理器”来控制对数据库连接的访问：
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
}
}
}
}
}
回答by bhamby
I have another answerthat deals with what I think is a similar problem, and it would require a similar answer. Basically, you can use the mysql_ping()function to test the connection before your insert. Before MySQL 5.0.14, mysql_ping()would automatically reconnect the server, but now you have to build your own reconnect logic. Something similar to this should work for you:
我有另一个答案可以解决我认为类似的问题，并且需要类似的答案。基本上，您可以使用该mysql_ping()函数在插入之前测试连接。在 MySQL 5.0.14 之前，mysql_ping()会自动重新连接服务器，但现在您必须构建自己的重新连接逻辑。类似的东西应该适合你：
function check_dbconn($connection) {
if (!mysql_ping($connection)) {
mysql_close($connection);
$connection = mysql_connect('server', 'username', 'password');
mysql_select_db('db',$connection);
}
return $connection;
}
foreach($array as $value) {
$dbconn = check_dbconn($dbconn);
$sql="insert into collected values('".$value."')";
$res=mysql_query($sql, $dbconn);
//then some extra code.
}
回答by Francois
I was facing "Mysql server has gone away"error while using Mysql connector 5.X, replacing dll to the last version solved the problem.
我在使用时遇到了“Mysql 服务器已经消失”错误Mysql connector 5.X，将 dll 替换为最新版本解决了这个问题。
回答by ethrbunny
Are you opening a single DB connection and reusing it? Is it possible that its a simple timeout? You might be better served by opening a new DB connection for each of your read/write operations (IE contact .edu, get text, open DB, write text, close db, repeat).
您是否打开单个数据库连接并重用它？这可能是一个简单的超时吗？为您的每个读/写操作（IE 联系 .edu、获取文本、打开数据库、写入文本、关闭数据库、重复）打开一个新的数据库连接可能会更好地为您服务。
Also how are you using the handle? Is it possible that it has hit an error and has 'gone away' for that reason?
还有你是怎么使用手柄的？它是否有可能因为这个原因遇到错误并“消失”了？
回答by Rafay
Well This is what I am doing now based on rdlowrey's suggestion and I guess this is also right.
嗯，这就是我现在根据 rdlowrey 的建议所做的，我想这也是正确的。
public function url_db_html($sourceLink = NULL, $source) {
$source = mysql_real_escape_string($source);
$query = "INSERT INTO html (id, sourceLink, sourceCode)
VALUES (NULL,('$sourceLink') , ('$source'))";
try {
if(mysql_query($query, $this->connection)==FALSE) {
$msg = mysql_errno($this->connection) . ": " . mysql_error($this->connection);
throw new DbException($msg);
}
} catch (DbException $e) {
echo "<br><br>Catched!!!<br><br>";
if(strstr($e->getMessage(), 'MySQL server has gone away')) {
$this->connection = mysql_connect("localhost", "root", "");
mysql_select_db("crawler1", $this->connection);
}
}
}
So once the query has failed to execute, the script will skip it but will make sure the connection is re-established.
因此，一旦查询执行失败，脚本将跳过它，但会确保重新建立连接。
However, my web crawler is crashing when files such as .jpg, .bmp, .pdf, etc are encountered. Is there a way to skip those urls containing these extensions. I am using preg_match and has given pdf and doc to match. Yet I want the function to skip all links containing extensions such as mp3, pdf, etc. Is this possible??
但是，当遇到 .jpg、.bmp、.pdf 等文件时，我的网络爬虫崩溃了。有没有办法跳过包含这些扩展的那些 url。我正在使用 preg_match 并提供了 pdf 和 doc 来匹配。但我希望该功能跳过所有包含扩展名的链接，例如 mp3、pdf 等。这可能吗？？
