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

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

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

$wpdb->check_conn();
方法检查,如果连接丢失,自动重新连接

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

$wpdb = new wpdb(DB_USER, DB_PASSWORD, DB_NAME, DB_HOST);
这个写法我是非常讨厌的,也顺便修了它

< ?php // WordPress DB Class // ======================== // fix by 21Andy.com at 2008-03-16 // Andy Tse (andy@21andy.com) // http://www.21andy.com // ======================== // ORIGINAL CODE FROM: // Justin Vincent (justin@visunet.ie) // 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); class wpdb { 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 * @param string $dbuser * @param string $dbpassword * @param string $dbname * @param string $dbhost */ function wpdb() { return $this->__construct();
}

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

if ( defined('WP_DEBUG') and WP_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();
}

function connect() {
$this->dbh = @mysql_connect($this->dbhost, $this->dbuser, $this->dbpassword);
if (!$this->dbh) {
$this->bail("

Error establishing a database connection

This either means that the username and password information in your wp-config.php file is incorrect or we can't contact the database server at $dbhost. This could mean your host's database server is down.

• Are you sure you have the correct username and password?
• Are you sure that you have typed the correct hostname?
• Are you sure that the database server is running?

If you're unsure what these terms mean you should probably contact your host. If you still need help you can always visit the WordPress Support Forums.

");
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);
}

function check_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() {
return true;
}

/**
* Selects a database using the current class's $this->dbh
* @param string $db name
*/
function select($db) {
if (!@mysql_select_db($db, $this->dbh)) {
$this->ready = false;
$this->bail("

Can’t select database

We were able to connect to the database server (which means your username and password is okay) but not able to select the $db database.

• Are you sure it exists?
• Does the user ".DB_USER." have permission to use the $db database?
• 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?

If you don't know how to setup a database you should contact your host. If all else fails you may find help at the WordPress Support Forums.

");
return;
}
}

/**
* Escapes content for insertion into the database, for security
*
* @param string $string
* @return string query safe string
*/
function escape($string) {
return addslashes( $string ); // Disable rest for now, causing problems
if( !$this->dbh || version_compare( phpversion(), '4.3.0' ) == '-1' )
return mysql_escape_string( $string );
else
return mysql_real_escape_string( $string, $this->dbh );
}

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

/**
* Prepares a SQL query for safe use, using sprintf() syntax
*/
function prepare($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.

function print_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 $str for query $this->last_query";
error_log($error_str, 0);

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

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

// If there is an error then take note of it
print "

";
}

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

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

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

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

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

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

function query($query) {
if ( ! $this->ready )
return false;

// 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();
return false;
}

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
* @param string $query (can be null as well, for caching, see codex)
* @param int $x = 0 row num to return
* @param int $y = 0 col num to return
* @return mixed results
*/
function get_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
* @param string $query
* @param string $output ARRAY_A | ARRAY_N | OBJECT
* @param int $y row num to return
* @return mixed results
*/
function get_row($query = null, $output = OBJECT, $y = 0) {
$this->func_call = "$db->get_row("$query",$output,$y)";
if ( $query )
$this->query($query);
else
return null;

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

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
* @param string $query (can be null as well, for caching, see codex)
* @param int $x col num to return
* @return array results
*/
function get_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
* @param string $query (can also be null to pull from the cache)
* @param string $output ARRAY_A | ARRAY_N | OBJECT
* @return mixed results
*/
function get_results($query = null, $output = OBJECT) {
$this->func_call = "$db->get_results("$query", $output)";

if ( $query )
$this->query($query);
else
return null;

// 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_result as $row ) {
$new_array[$i] = (array) $row;
if ( $output == ARRAY_N ) {
$new_array[$i] = array_values($new_array[$i]);
}
$i++;
}
return $new_array;
} else {
return null;
}
}
}

/**
* Grabs column metadata from the last query
* @param string $info_type one of name, table, def, max_length, not_null, primary_key, multiple_key, unique_key, numeric, blob, type, unsigned, zerofill
* @param int $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
* @return mixed results
*/
function get_col_info($info_type = 'name', $col_offset = -1) {
if ( $this->col_info ) {
if ( $col_offset == -1 ) {
$i = 0;
foreach($this->col_info as $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
*/
function timer_start() {
$mtime = microtime();
$mtime = explode(' ', $mtime);
$this->time_start = $mtime[1] + $mtime[0];
return true;
}

/**
* Stops the debugging timer
* @return int total time spent on the query, in milliseconds
*/
function timer_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.
* @param string $message
*/
function bail($message) { // Just wraps errors in a nice header and footer
if ( !$this->show_errors ) {
if ( class_exists('WP_Error') )
$this->error = new WP_Error('500', $message);
else
$this->error = $message;
return false;
}
wp_die($message);
}
}

if ( ! isset($wpdb) )
$wpdb = new wpdb;
?>
ShareThis