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.

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

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

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.

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

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