Home > Backend Development > PHP Problem > How to set the timeout for mysql query to read data in php

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

coldplay.xixi
Release: 2023-03-03 11:40:01
Original
4230 people have browsed it

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 for mysql query to read data in php

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
Copy after login

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";
?>
Copy after login

, an error will be reported:

PHP Warning: PDO::query(): MySQL server has gone away
PHP Warning: PDO::query(): Error reading result set&#39;s header
PHP Fatal error: Call to a member function fetch() on a non-object
Copy after login

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";
?>
Copy after login

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";
?>
Copy after login

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!

Related labels:
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Latest Issues
php data acquisition?
From 1970-01-01 08:00:00
0
0
0
PHP extension intl
From 1970-01-01 08:00:00
0
0
0
How to learn php well
From 1970-01-01 08:00:00
0
0
0
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template