DB Connection Check Issue

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!

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, 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, 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