How to set the timeout for mysql query to read data in php

How to set the timeout time for mysql query to read data in php: 1. Use mysqlnd to set the timeout time for mysql query, the code is [mysqlnd.net_read_timeout =3]; 2. Use mysqli to limit the timeout time for read.

How to set the timeout time for mysql query to read data in php:

The first setting The method for mysql query timeout is to use mysqlnd.

After enabling the mysqlnd extension in php, just set mysqlnd.net_read_timeout in the php.ini file.

The unit of parameter value is seconds. For example:

mysqlnd.net_read_timeout = 3

means that the timeout for each mysql query is 3 seconds. If it times out, an error will be reported.

For example, if the following code is used:

<?php
$dsn = &#39;mysql:dbname=demo;host=127.0.0.1;port=3306&#39;;
$user = &#39;demo&#39;;
$password = &#39;demo&#39;;
$dbh = new PDO($dsn, $user, $password);
$dbh->query("set names utf8");
$sql = "select sleep(5)";
$sth = $dbh->query($sql);
$row = $sth->fetch();
echo "over";
?>

, an error will be reported:

PHP Warning: PDO::query(): MySQL server has gone away
PHP Warning: PDO::query(): Error reading result set's header
PHP Fatal error: Call to a member function fetch() on a non-object

Due to a PHP Fatal error, the code after fetch() will not be executed.

Therefore, the code needs to make a judgment on the return value of query. The modified code is as follows:

<?php
$dsn = &#39;mysql:dbname=demo;host=127.0.0.1;port=3306&#39;;
$user = &#39;demo&#39;;
$password = &#39;demo&#39;;
$dbh = new PDO($dsn, $user, $password);
$dbh->query("set names utf8");
$sql = "select sleep(5)";
$sth = $dbh->query($sql);
if(is_object($sth)){
    $row = $sth->fetch();
}
echo "over";
?>

Note: The level of the setting item mysqlnd.net_read_timeout is PHP_INI_SYSTEM. Therefore, the timeout of mysql query cannot be modified in the php code.

Another way is to use mysqli.

If mysqlnd is not enabled in PHP, you can use mysqli to limit the read timeout.

The sample code is as follows:

<?php
//自己定义读写超时常量
if (!defined(&#39;MYSQL_OPT_READ_TIMEOUT&#39;)) {
        define(&#39;MYSQL_OPT_READ_TIMEOUT&#39;,  11);
}
if (!defined(&#39;MYSQL_OPT_WRITE_TIMEOUT&#39;)) {
        define(&#39;MYSQL_OPT_WRITE_TIMEOUT&#39;, 12);
}
//设置超时
$mysqli = mysqli_init();
$mysqli->options(MYSQL_OPT_READ_TIMEOUT, 3);
$mysqli->options(MYSQL_OPT_WRITE_TIMEOUT, 1);
//连接数据库
$mysqli->real_connect("localhost", "root", "root", "test");
if (mysqli_connect_errno()) {
   printf("Connect failed: %s/n", mysqli_connect_error());
   exit();
}
//执行查询 sleep 1秒不超时
printf("Host information: %s/n", $mysqli->host_info);
if (!($res=$mysqli->query(&#39;select sleep(1)&#39;))) {
    echo "query1 error: ". $mysqli->error ."/n";
} else {
    echo "Query1: query success/n";
}
//执行查询 sleep 9秒会超时
if (!($res=$mysqli->query(&#39;select sleep(9)&#39;))) {
    echo "query2 error: ". $mysqli->error ."/n";
} else {
    echo "Query2: query success/n";
}
$mysqli->close();
echo "close mysql connection/n";
?>

Note:

1. The timeout setting unit is seconds, and the minimum configuration is 1 second

2. But the underlying read of mysql It will be retried twice, so the actual time will be 3 seconds.

Retries itself twice = 3 times the timeout.

That means the minimum timeout is 3 seconds, which will not be lower than this value. It is acceptable for most applications, but it needs to be optimized for a small number of applications.

Related learning recommendations: PHP programming from entry to proficiency

The above is the detailed content of How to set the timeout for mysql query to read data in php. For more information, please follow other related articles on the PHP Chinese website!