MySQL has gone away解决办法

时间：2008-03-16 21:22:26 类别：wordpress 访问：6 viewsRSS 2.0评论

最近写的一个采集系统在wordpress上面老是mysql has gone away
原因是mysql连接丢失

在一些耗时的地方, 使用mysql_ping来重新获得连接

下面给出我改进的wp-db.php类, 加入了

$wpdb->check_conn();

方法检查,如果连接丢失,自动重新连接

我非常讨厌在new一个类的时候还带上参数

$wpdb=newwpdb(DB_USER,DB_PASSWORD,DB_NAME,DB_HOST);

这个写法我是非常讨厌的,也顺便修了它

<?php
// WordPress DB Class
// ========================
// fix by 21Andy.com at 2008-03-16
// Andy Tse ([e-mail])
//http://www.21andy.com
// ========================
// ORIGINAL CODE FROM:
// Justin Vincent ([e-mail])
// http://php.justinvincent.com

define('EZSQL_VERSION','WP1.25');
define('OBJECT','OBJECT',true);
define('ARRAY_A','ARRAY_A',false);
define('ARRAY_N','ARRAY_N',false);

if(!defined('SAVEQUERIES'))
define('SAVEQUERIES',false);

classwpdb{

var$show_errors=false;
var$num_queries=0;
var$last_query;
var$col_info;
var$queries;
var$ready=false;

// Our tables
var$posts;
var$users;
var$categories;
var$post2cat;
var$comments;
var$links;
var$options;
var$optiontypes;
var$optionvalues;
var$optiongroups;
var$optiongroup_options;
var$postmeta;
var$usermeta;
var$terms;
var$term_taxonomy;
var$term_relationships;

var$charset;
var$collate;

/**
* Connects to the database server and selects a database
*@paramstring $dbuser
*@paramstring $dbpassword
*@paramstring $dbname
*@paramstring $dbhost
*/
functionwpdb(){
return$this->__construct();
}

function__construct(){
register_shutdown_function(array(&$this,"__destruct"));

if(defined('WP_DEBUG')andWP_DEBUG==true)
$this->show_errors();

if(defined('DB_CHARSET'))
$this->charset=DB_CHARSET;

if(defined('DB_COLLATE'))
$this->collate=DB_COLLATE;

if(!defined('DB_USER')||
!defined('DB_PASSWORD')||
!defined('DB_NAME')||
!defined('DB_HOST'))
exit('please config DB setting!!!');

$this->dbuser=DB_USER;
$this->dbpassword=DB_PASSWORD;
$this->dbname=DB_NAME;
$this->dbhost=DB_HOST;
$this->connect();
}

functionconnect(){
$this->dbh= @mysql_connect($this->dbhost,$this->dbuser,$this->dbpassword);
if(!$this->dbh){
$this->bail("
<h1>Error establishing a database connection</h1>
<p>This either means that the username and password information in your <code>wp-config.php</code> file is incorrect or we can't contact the database server at <code>$dbhost</code>. This could mean your host's database server is down.</p>
<ul>
<li>Are you sure you have the correct username and password?</li>
<li>Are you sure that you have typed the correct hostname?</li>
<li>Are you sure that the database server is running?</li>
</ul>
<p>If you're unsure what these terms mean you should probably contact your host. If you still need help you can always visit the <a href='http://wordpress.org/support/'>WordPress Support Forums</a>.</p>
");
return;
}

$this->ready=true;

if(!empty($this->charset)&&version_compare(mysql_get_server_info(),'4.1.0','>='))
$this->query("SET NAMES '$this->charset'");

$this->select($this->dbname);
}

functioncheck_conn(){
if(!mysql_ping($this->dbh)){
//here is the major trick, you have to close the connection (even though its not currently working) for it to recreate properly.
mysql_close($this->dbh);
$this->connect();
}
}

function__destruct(){
returntrue;
}

/**
* Selects a database using the current class's $this->dbh
*@paramstring $db name
*/
functionselect($db){
if(!@mysql_select_db($db,$this->dbh)){
$this->ready=false;
$this->bail("
<h1>Can&#8217;t select database</h1>
<p>We were able to connect to the database server (which means your username and password is okay) but not able to select the <code>$db</code> database.</p>
<ul>
<li>Are you sure it exists?</li>
<li>Does the user <code>".DB_USER."</code> have permission to use the <code>$db</code> database?</li>
<li>On some systems the name of your database is prefixed with your username, so it would be like username_wordpress. Could that be the problem?</li>
</ul>
<p>If you don't know how to setup a database you should <strong>contact your host</strong>. If all else fails you may find help at the <a href='http://wordpress.org/support/'>WordPress Support Forums</a>.</p>");
return;
}
}

/**
* Escapes content for insertion into the database, for security
*
*@paramstring $string
*@returnstring query safe string
*/
functionescape($string){
returnaddslashes($string);// Disable rest for now, causing problems
if(!$this->dbh||version_compare(phpversion(),'4.3.0')=='-1')
returnmysql_escape_string($string);
else
returnmysql_real_escape_string($string,$this->dbh);
}

/**
* Escapes content by reference for insertion into the database, for security
*@paramstring $s
*/
functionescape_by_ref(&$s){
$s=$this->escape($s);
}

/**
* Prepares a SQL query for safe use, using sprintf() syntax
*/
functionprepare($args=NULL){
if(NULL===$args)
return;
$args=func_get_args();
$query=array_shift($args);
$query=str_replace("'%s'",'%s',$query);// in case someone mistakenly already singlequoted it
$query=str_replace('"%s"','%s',$query);// doublequote unquoting
$query=str_replace('%s',"'%s'",$query);// quote the strings
array_walk($args,array(&$this,'escape_by_ref'));
return@vsprintf($query,$args);
}

// ==================================================================
// Print SQL/DB error.

functionprint_error($str=''){
global$EZSQL_ERROR;
if(!$str)$str=mysql_error($this->dbh);
$EZSQL_ERROR[]=
array('query'=>$this->last_query,'error_str'=>$str);

$error_str="WordPress database error$strfor query$this->last_query";
error_log($error_str,0);

// Is error output turned on or not..
if(!$this->show_errors)
returnfalse;

$str=htmlspecialchars($str,ENT_QUOTES);
$query=htmlspecialchars($this->last_query,ENT_QUOTES);

// If there is an error then take note of it
print"<div id='error'>
<p class='wpdberror'><strong>WordPress database error:</strong> [$str]<br />
<code>$query</code></p>
</div>";
}

// ==================================================================
// Turn error handling on or off..

functionshow_errors($show=true){
$errors=$this->show_errors;
$this->show_errors=$show;
return$errors;
}

functionhide_errors(){
$show=$this->show_errors;
$this->show_errors=false;
return$show;
}

// ==================================================================
// Kill cached query results

functionflush(){
$this->last_result=array();
$this->col_info=null;
$this->last_query=null;
}

// ==================================================================
// Basic Query - see docs for more detail

functionquery($query){
if(!$this->ready)
returnfalse;

// filter the query, if filters are available
//NOTE:some queries are made before the plugins have been loaded, and thus cannot be filtered with this method
if(function_exists('apply_filters'))
$query=apply_filters('query',$query);

// initialise return
$return_val=0;
$this->flush();

// Log how the function was called
$this->func_call="\$db->query(\"$query\")";

// Keep track of the last query for debug..
$this->last_query=$query;

// Perform the query via std mysql_query function..
if(SAVEQUERIES)
$this->timer_start();

$this->result= @mysql_query($query,$this->dbh);
++$this->num_queries;

if(SAVEQUERIES)
$this->queries[]=array($query,$this->timer_stop());

// If there is an error then take note of it..
if(mysql_error($this->dbh)){
$this->print_error();
returnfalse;
}

if(preg_match("/^\\s*(insert|delete|update|replace) /i",$query)){
$this->rows_affected=mysql_affected_rows($this->dbh);
// Take note of the insert_id
if(preg_match("/^\\s*(insert|replace) /i",$query)){
$this->insert_id=mysql_insert_id($this->dbh);
}
// Return number of rows affected
$return_val=$this->rows_affected;
}else{
$i=0;
while($i< @mysql_num_fields($this->result)){
$this->col_info[$i]= @mysql_fetch_field($this->result);
$i++;
}
$num_rows=0;
while($row= @mysql_fetch_object($this->result)){
$this->last_result[$num_rows]=$row;
$num_rows++;
}

@mysql_free_result($this->result);

// Log number of rows the query returned
$this->num_rows=$num_rows;

// Return number of rows selected
$return_val=$this->num_rows;
}

return$return_val;
}

/**
* Get one variable from the database
*@paramstring $query (can be null as well, for caching, see codex)
*@paramint $x = 0 row num to return
*@paramint $y = 0 col num to return
*@returnmixed results
*/
functionget_var($query=null,$x=0,$y=0){
$this->func_call="\$db->get_var(\"$query\",$x,$y)";
if($query)
$this->query($query);

// Extract var out of cached results based x,y vals
if($this->last_result[$y]){
$values=array_values(get_object_vars($this->last_result[$y]));
}

// If there is a value return it else return null
return(isset($values[$x])&&$values[$x]!=='')?$values[$x]:null;
}

/**
* Get one row from the database
*@paramstring $query
*@paramstring $output ARRAY_A | ARRAY_N | OBJECT
*@paramint $y row num to return
*@returnmixed results
*/
functionget_row($query=null,$output=OBJECT,$y=0){
$this->func_call="\$db->get_row(\"$query\",$output,$y)";
if($query)
$this->query($query);
else
returnnull;

if(!isset($this->last_result[$y]))
returnnull;

if($output==OBJECT){
return$this->last_result[$y]?$this->last_result[$y]:null;
}elseif($output==ARRAY_A){
return$this->last_result[$y]?get_object_vars($this->last_result[$y]):null;
}elseif($output==ARRAY_N){
return$this->last_result[$y]?array_values(get_object_vars($this->last_result[$y])):null;
}else{
$this->print_error("\$db->get_row(string query, output type, int offset) -- Output type must be one of: OBJECT, ARRAY_A, ARRAY_N");
}
}

/**
* Gets one column from the database
*@paramstring $query (can be null as well, for caching, see codex)
*@paramint $x col num to return
*@returnarray results
*/
functionget_col($query=null,$x=0){
if($query)
$this->query($query);

$new_array=array();
// Extract the column values
for($i=0;$i<count($this->last_result);$i++){
$new_array[$i]=$this->get_var(null,$x,$i);
}
return$new_array;
}

/**
* Return an entire result set from the database
*@paramstring $query (can also be null to pull from the cache)
*@paramstring $output ARRAY_A | ARRAY_N | OBJECT
*@returnmixed results
*/
functionget_results($query=null,$output=OBJECT){
$this->func_call="\$db->get_results(\"$query\",$output)";

if($query)
$this->query($query);
else
returnnull;

// Send back array of objects. Each row is an object
if($output==OBJECT){
return$this->last_result;
}elseif($output==ARRAY_A||$output==ARRAY_N){
if($this->last_result){
$i=0;
foreach($this->last_resultas$row){
$new_array[$i]=(array)$row;
if($output==ARRAY_N){
$new_array[$i]=array_values($new_array[$i]);
}
$i++;
}
return$new_array;
}else{
returnnull;
}
}
}

/**
* Grabs column metadata from the last query
*@paramstring $info_type one of name, table, def, max_length, not_null, primary_key, multiple_key, unique_key, numeric, blob, type, unsigned, zerofill
*@paramint $col_offset 0: col name. 1: which table the col's in. 2: col's max length. 3: if the col is numeric. 4: col's type
*@returnmixed results
*/
functionget_col_info($info_type='name',$col_offset= -1){
if($this->col_info){
if($col_offset== -1){
$i=0;
foreach($this->col_infoas$col){
$new_array[$i]=$col->{$info_type};
$i++;
}
return$new_array;
}else{
return$this->col_info[$col_offset]->{$info_type};
}
}
}

/**
* Starts the timer, for debugging purposes
*/
functiontimer_start(){
$mtime=microtime();
$mtime=explode('',$mtime);
$this->time_start=$mtime[1]+$mtime[0];
returntrue;
}

/**
* Stops the debugging timer
*@returnint total time spent on the query, in milliseconds
*/
functiontimer_stop(){
$mtime=microtime();
$mtime=explode('',$mtime);
$time_end=$mtime[1]+$mtime[0];
$time_total=$time_end-$this->time_start;
return$time_total;
}

/**
* Wraps fatal errors in a nice header and footer and dies.
*@paramstring $message
*/
functionbail($message){// Just wraps errors in a nice header and footer
if(!$this->show_errors){
if(class_exists('WP_Error'))
$this->error=newWP_Error('500',$message);
else
$this->error=$message;
returnfalse;
}
wp_die($message);
}
}

if(!isset($wpdb))
$wpdb=newwpdb;
?>

ShareThis