HyperDB 1.8: db_connect() is unable to reconnect after ping failures

[pypt]

HyperDB 1.8: db_connect() is unable to reconnect after ping failures

HyperDB version 1.8's db_connect() is unable to gracefully reconnect on ping failures when there's only a single possible host to connect to. This is most likely because the newly introduced $this->unused_servers doesn't get "refilled" with the same sole host that was given up on previously.

Ping failures might (and do) happen when using HyperDB for long running scripts. When a ping failure occurs (i.e. ex_mysql_ping() returns false), db_connect() doesn't decide to try to reconnect to the same host again (which it should).

HyperDB 1.7 managed to do graceful reconnections fine, so this was most likely introduced in #2 PR and c7344ec commit.

This is probably why Christopher in #40 saw those undefined $host, $port, ... notices too - if db_connect() never manages to decide on which host to reconnect after failures, $host, $port and such never get set.

How to reproduce

Set up

Start a test MariaDB instance:

docker run \
    --name hyperdb_mariadb \
    --publish 127.0.0.1:33060:3306 \
    --env MYSQL_USER=a8ctest \
    --env MYSQL_PASSWORD=a8ctest \
    --env MYSQL_ROOT_PASSWORD=a8ctest \
    mariadb:10.1.48

Set it up with a database, table and a single row:

mysql -u root -pa8ctest -h 127.0.0.1 -P 33060 -e "CREATE DATABASE a8ctest"
mysql -u root -pa8ctest -h 127.0.0.1 -P 33060 -e "GRANT ALL PRIVILEGES ON a8ctest.* TO a8ctest@'%'"
mysql -u a8ctest -pa8ctest -h 127.0.0.1 -P 33060 -D a8ctest -e "CREATE TABLE test (value TEXT NOT NULL)"
mysql -u a8ctest -pa8ctest -h 127.0.0.1 -P 33060 -D a8ctest -e "INSERT INTO test VALUES ('Works')"

Set up HyperDB:

wget https://raw.githubusercontent.com/WordPress/WordPress/6.0.1/wp-includes/wp-db.php
git clone https://github.com/Automattic/HyperDB.git

Save the following script to timeouts.php:

<?php

define( 'ABSPATH', __DIR__ . '/' );
define( 'WP_DEBUG', '' );
define( 'WP_CONTENT_DIR', '' );
function wp_load_translations_early() { }
function __( $x ) { return $x; }
function do_action($tag, $arg = '') { }

define( 'DB_HOST', '127.0.0.1:33060' );
define( 'DB_USER', 'a8ctest' );
define( 'DB_PASSWORD', 'a8ctest' );
define( 'DB_NAME', 'a8ctest' );

define( 'WPDB_PATH', __DIR__ . '/wp-db.php' );
define( 'DB_CONFIG_FILE', __DIR__ . '/HyperDB/db-config.php' );
require_once __DIR__ . '/HyperDB/db.php';

global $wpdb;

$start = microtime( true );

while ( true ) {
    $value = $wpdb->get_var( 'SELECT value FROM test' );

    $now = microtime( true );
    printf( '%2.6f s: ', $now - $start );

    if ( $wpdb->last_error ) {
        echo "Database error: " . $wpdb->last_error . "\n";
    } else {
        echo "$value\n";
    }
    sleep( 1 );
}

Buggy behavior on HyperDB 1.8

Check out HyperDB 1.8:

cd HyperDB/
git checkout e24a61cb4db3c32d0f006ba4d00e45a02b19ff84

Make every 20th ping fail:

patch -p1 << 'EOF'
diff --git a/db.php b/db.php
index 182d405..2a60b1f 100644
--- a/db.php
+++ b/db.php
@@ -64,6 +64,10 @@ define( 'HYPERDB_CONNNECTION_ERROR', 2002 ); // Can't connect to local MySQL ser
 define( 'HYPERDB_CONN_HOST_ERROR', 2003 ); // Can't connect to MySQL server on '%s' (%d)
 define( 'HYPERDB_SERVER_GONE_ERROR', 2006 ); // MySQL server has gone away
 
+
+$ping_counter = 0;
+
+
 // phpcs:ignore PEAR.NamingConventions.ValidClassName.StartWithCapital
 class hyperdb extends wpdb {
 	/**
@@ -1528,6 +1532,15 @@ class hyperdb extends wpdb {
 	}
 
 	public function ex_mysql_ping( $dbh ) {
+
+		global $ping_counter;
+		if ( 0 === $ping_counter % 20 ) {
+			// Every 20th ping should fail
+			return false;
+		} else {
+			++$ping_counter;
+		}
+
 		if ( ! $this->use_mysqli ) {
 			return @mysql_ping( $dbh );
 		}
EOF

Run timeouts.php:

cd ../
php timeouts.php

After a few seconds, the script will lose connection to MySQL and never manage to reconnect:

0.009290 s: Works
1.026420 s: Works
2.043919 s: Works
3.061042 s: Works
4.068179 s: Database error: Database connection failed
5.070985 s: Database error: Database connection failed
<...>

Correct behavior on HyperDB 1.7

Check out HyperDB 1.7:

cd HyperDB/
git checkout db.php
git checkout 068448a48a1cb4e9cab235ef1fef5bdf251a5d83

Make every 20th ping fail:

patch -p1 << 'EOF'
diff --git a/db.php b/db.php
index 9b74522..4f2c55c 100644
--- a/db.php
+++ b/db.php
@@ -50,6 +50,8 @@ define( 'HYPERDB_LAG_UNKNOWN', 3 );
 define( 'HYPERDB_CONN_HOST_ERROR', 2003 );   // Can't connect to MySQL server on '%s' (%d)
 define( 'HYPERDB_SERVER_GONE_ERROR', 2006 ); // MySQL server has gone away
 
+$ping_count = 0;
+
 class hyperdb extends wpdb {
 	/**
 	 * The last table that was queried
@@ -1293,6 +1295,16 @@ class hyperdb extends wpdb {
 	}
 
 	function ex_mysql_ping( $dbh ) {
+
+		global $ping_count;
+
+		if ( 0 === $ping_count % 20 ) {
+			// Every 20th ping fails
+			return false;
+		} else {
+			++$ping_count;
+		}
+
 		if ( ! $this->use_mysqli )
 			return @mysql_ping( $dbh );
 
EOF

Run timeouts.php:

cd ../
php timeouts.php

Even through every 20th or so MySQL ping fails, the older HyperDB connection manages to reconnect to the database every time just fine:

0.009290 s: Works
1.026420 s: Works
2.043919 s: Works
3.061042 s: Works
<...>
71.382320 s: Works
<...>