DB Connection Check Issue

7 posts by 3 authors in: Forums > CMS Builder
Last Post: September 23 (RSS)

I have some nightly scripts that are very important to be executed, but sometimes the DB connection fails for a random reason causing the entire script to die. So I created a wrapper that runs the connectToMySQL() function in a loop that sleeps if the connection fails and continues to attempt to connect. This used to work very well before the update to using the DB class.

I couldn't figure out why it wasn't retrying on fail recently until I just noticed that the DB::isConnected() function only checks that the $mysqli variable has been instantiated. When DB::connect() throws an exception, it doesn't reset the $mysqli variable. So when I check DB::isConnected() it always returns true after the first attempt. I created a workaround to call DB::disconnect() after a failed attempt, but it would seem that DB::isConnected() should be refactored to actually check that we have a connection, not that it was just attempted.

By Tim - September 17

Hi tbcshifter,

You make a valid point. Anything that causes a failure during a connection should really update that variable to reflect the true status of the connection and allow isConnected() to reflect that. It should also allow a second connect() to start from a fresh new state. That way you can connect, throw an error, catch it and then initiate a new connect() (be it after a sliding delay I imagine, you wouldn't want to hit your server repeatedly within milliseconds).

I will let Dave take a look at it and see if perhaps there is even a more comprehensive way.

Thanks for the comment!

Tim Hurd
Senior Web Programmer
Interactivetools.com

By Dave - September 17

Hi tbcshifter,

Thanks for the detailed report and background info. Here's an updated isConnected() method that checks for an active connection.

/**
 * Checks if the MySQLi connection is active.
 *
 * @return bool True if connected, false otherwise.
 */
public static function isConnected(): bool {
    return self::$mysqli instanceof mysqli && self::$mysqli->ping();
}

Can you give that a try and let me know if it works for your use case?

Thanks!

Dave Edis - Senior Developer
interactivetools.com

By tbcshifter - September 17

Dave, I think that should work nicely. Thanks for the quick response!

By tbcshifter - September 21

Dave, found an issue with this new code. The ping() takes about 0.2 seconds. This function is called all over the place in functions like mysql_escape() where you might run that dozens of times on the page. Insert scripts that used to take a couple of seconds to run are now taking 30 or 45 seconds. So I have to remove the ping(). It might make sense to have a parameter that runs the ping() when you want a "real" check, but not run it every time by default.

By Dave - September 23

Hi tbcshifter,

Yea, we just ran into that as well. We've updated the mysql_escape() code for the upcoming beta as follows as it just needs to know that a connection was established, not that it is active. $mysqli->real_escape_string continues to work even if a connection is dropped.

/**
 * Escapes input for use in MySQL queries, preventing SQL injection.
 *
 * @param mixed $input The input to escape
 * @param bool $escapeLikeWildcards Whether to escape LIKE wildcards % and _ (default: false)
 * @return string The escaped string
 * @throws DBException If called before database connection is established
 */
function mysql_escape(string|int|float|bool|null $input, bool $escapeLikeWildcards = false): string
{
    if (!DB::$mysqli) {
        throw new DBException("mysql_escape() called before DB connection established");
    }

    $processed = $escapeLikeWildcards ? addcslashes((string)$input, '%_\\') : (string)$input;
    return DB::$mysqli->real_escape_string($processed);
}

That should speed it up a lot. Let me know if you run into any other issues with it.

Also, here's the code code we used to simulate a disconnection for testing, in case it might be helpful:

echo "\$mysqli->ping() = " . (DB::$mysqli->ping() ? 'connected' : 'disconnected') . "\n";
echo "Simulate disconnection with SET SESSION wait_timeout = 1;\n";
$r = DB::$mysqli->query("SET SESSION wait_timeout = 1;");
time_sleep_until(microtime(true) + 1.5);
echo "\$mysqli->ping() = " . (DB::$mysqli->ping() ? 'connected' : 'disconnected') . "\n";

Thanks!

Dave Edis - Senior Developer
interactivetools.com

By tbcshifter - September 23

Dave, that looks great, thanks.

While I was in there, I thought I'd mention a few other enhancements that I added over the years in case you want to incorporate any of them.

https://www.interactivetools.com/forum/forum-posts.php?A-Few-Potential-Enhancements-82871