PHP test whether remote MySQL server is available

Question

I have some websites that uses MySQL. I'm not expert in SQL, so I use simple connection, querys, etc.. Sometimes (is rare but happens) only the database server hangs, or I forgot to turn on my home testing mysql. Until it operational again, the server hangs trying to connect and finally a timeout error happens.

I'm trying to add some previous test of database server like this "ping" function:

function pingDomain($domain){
    $starttime = microtime(true);
    $file = fsockopen ($domain, 80, $errno, $errstr, 10);
    $stoptime = microtime(true);
    $status = 0;

    if (!$file) {
        $status = -1; // Site is down
    } else {
        fclose($file);
        $status = ($stoptime - $starttime) * 1000;
        $status = floor($status);
    }

    return $status;
}

But didn't work either, php hangs anyway. Any ideas?

What behavior are you expecting? What behavior are you experiencing? It looks like this script should block (hang) for 10 seconds. If your server timeout is 10 seconds or less than the whole script will timeout. — David W
– David W, Commented Sep 26, 2012 at 19:09

GTodorov · Accepted Answer · 2012-09-26 19:34:25Z

1

Simple use mysql_ping:

            <?php
            set_time_limit(0);

            $conn = mysql_connect('localhost', 'mysqluser', 'mypass');
            $db   = mysql_select_db('mydb');

            /* Assuming this query will take a long time */
            $result = mysql_query($sql);
            if (!$result) {
                echo 'Query #1 failed, exiting.';
                exit;
            }

            /* Make sure the connection is still alive, if not, try to reconnect */
            if (!mysql_ping($conn)) {
                echo 'Lost connection, exiting after query #1';
                exit;
            }
            mysql_free_result($result);

            /* So the connection is still alive, let's run another query */
            $result2 = mysql_query($sql2);
            ?>

You may want to take a look at this:

mysql_ping

answered Sep 26, 2012 at 19:34

GTodorov

2,14222 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Orbling Over a year ago

+1 Though preferably mysqli::ping() these days, mysql extension is soon to be deprecated.

Gustavo Over a year ago

It doesn't work. As I read, set_time_limit complements the timeout set in php.ini (usually set as 30s), so it still hang for a looong time. But I thinking about this kind of approach: a quick error to detect that there is no connection.

GTodorov Over a year ago

@Orbling Soon? Tomorrow? Next week? Even in PHP 6 mysql_connect will If it doesn't I'll write you myself the functions as wrapper over the mysqli ;)

Orbling Over a year ago

@GTodorov: Provision of a facility, does not mean it is not frowned upon. There are reasons for the change being pushed. I think the mysql extension functions are to issue E_DEPRECATED warnings by version 6, earlier possibly.

GTodorov Over a year ago

The update is after 5.4+ but the extension will be still available.

Gustavo · Accepted Answer · 2013-10-17 23:01:50Z

0

Can't find anywhere a documentation about a reliable and simple test about mySQL server. I'm using now the following technique:

@$this->Base = mysqli_init();
@$this->Base->options(MYSQLI_OPT_CONNECT_TIMEOUT, 1);
@$this->Base->real_connect($host, $usuario, $senha, $bd);

Than you catch the error with $this->Base->connect_error or $this->Base->connect_errno, it will not be written because of "@" symbols.

You may use procedural form as well:

$Link = mysqli_init();
mysqli_options($Link, MYSQLI_OPT_CONNECT_TIMEOUT, 1);
$res = @mysqli_real_connect($host, $usuario, $senha, $bd);
if($res) {
    $E->echop("Connected!!!");
} else {
    $E->echop("Huston, we got a problem...");
}

Comments and other answers are welcome!

answered Oct 17, 2013 at 23:01

Gustavo

1,6834 gold badges24 silver badges41 bronze badges

Collectives™ on Stack Overflow

PHP test whether remote MySQL server is available

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related