PHP set timeout for MySQL query read data

Phenomenon: PHP can connect to MySQL through the normal agent. However, after you execute query, you wait and no data returns.
As a result, the PHP-FPM process is all blocked at the place where the data is being read. Cannot process other normal requests. Workaround:
You can resolve this problem by setting the timeout time for MySQL killing.
The first way to set the MySQL query timeout is to use MYSQLND.

Once PHP has enabled MYSQLND extensions, just set mysqlnd.net_read_timeout in the php.ini file.

The unit of the parameter value is seconds. Such as:

Mysqlnd.net_read_timeout = 3 indicates that the MySQL query timeout is 3 seconds each time. If the timeout occurs, an error is.

As in the following code:
<!--? php
$dsn = ' mysql:dbname=demo;host=127.0.0.1;port=3306 ';
$user = ' demo ';
$password = ' demo ';
$DBH = new PDO ($DSN, $user, $password);
$DBH----->query ("Set names UTF8");
$sql = "Select Sleep (5)";
$sth = $dbh->query ($sql);
$row = $sth->fetch ();
echo "Over";
?>

Baseline; Word-break:break-all; Color:rgb (68, 68, 68); font-family: "microsoft=" "yahei", = "" Helvetica= "neue", = "helvetica,=" "arial,=" "sans-serif;=" "line-height:=" " 21px;= "" background-color:= "" RGB (255,= "" 255,= "" 255); " = "" > will report an error:
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

Because of a PHP Fatal error error, the code after fetch () will not execute.
So the code needs to judge the return value of query, and the modified code is as follows:

<!--? php
$dsn = ' mysql:dbname=demo;host=127.0.0.1;port=3306 ';
$user = ' demo ';
$password = ' demo ';
$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 set item Mysqlnd.net_read_timeout is Php_ini_system. So in the PHP code can not modify the MySQL query timeout time.

Another way is to use mysqli.
If PHP does not enable MYSQLND, then you can use MYSQLI to limit the read timeout.
The sample code is as follows:

<!--? php
Define your own read and write Hyper-constant
if (!defined (' mysql_opt_read_timeout ')) {
Define (' Mysql_opt_read_timeout ', 11);
}
if (!defined (' mysql_opt_write_timeout ')) {
Define (' Mysql_opt_write_timeout ', 12);
}
Set timeout
$mysqli = Mysqli_init ();
$mysqli----->options (Mysql_opt_read_timeout, 3);
$mysqli->options (mysql_opt_write_timeout, 1);
Connecting to a database
$mysqli->real_connect ("localhost", "root", "root", "test");
if (Mysqli_connect_errno ()) {
printf ("Connect failed:%s/n", Mysqli_connect_error ());
Exit ();
}
Run Query sleep 1 seconds does not timeout
printf ("Host Information:%s/n", $mysqli->host_info);
if (!) ( $res = $mysqli->query (' Select Sleep (1) ')) {
echo "Query1 error:". $mysqli->error. " /n ";
} else {
echo "Query1:query success/n";
}
Run Query sleep 9 seconds will timeout
if (!) ( $res = $mysqli->query (' Select Sleep (9) ')) {
echo "Query2 error:". $mysqli->error. " /n ";
} else {
echo "Query2:query success/n";
}
$mysqli->close ();
echo "Close MySQL connection/n";
?>
Attention:

1. Timeout set to seconds, minimum configuration 1 seconds
2. But the MySQL bottom of read will retry two times, so the actual will be 3 seconds
Retry two times + self once = 3 times times timeout time.
That means the minimum timeout is 3 seconds, not below this value, acceptable for most applications, but optimized for a small number of applications.

Of course there's MySQL. Configuration permissions can refer to the following method to modify the timeout time

Mysql> Show variables like '%timeout ';
mysql> set wait_timeout = 28800000;
mysql> set interactive_timeout = 28800000;

The modifications are as follows: Open/etc/my.cnf and add the following parameters to the property group Mysqld:

[Mysqld]
interactive_timeout=28800000
wait_timeout=28800000
Add in My.ini under Windows:
interactive_timeout=28800000
wait_timeout=28800000

or use the command

To view the MySQL server timeout period:

Msyql> show global variables like '%timeout% ';

To set the MySQL server timeout (in seconds):

msyql> set global wait_timeout=10;

msyql> set global interactive_timeout=10;

The maximum value for Wait_timeout is 24 days/365 days respectively (Windows/linux). Take Windows for example, assuming that we want to set it to 21 days, we simply modify the MYSQL5 profile "My.ini" (MySQL5 installation dir) and add one more line: wait_timeout=18144006
A reboot of the MYSQL5 is required.

Alibaba Cloud Hot Products
Elastic Compute Service (ECS)	Dedicated Host (DDH)	ApsaraDB RDS for MySQL (RDS)	ApsaraDB for PolarDB(PolarDB)	AnalyticDB for PostgreSQL (ADB for PG)
AnalyticDB for MySQL(ADB for MySQL)	Data Transmission Service (DTS)	Server Load Balancer (SLB)	Global Accelerator (GA)	Cloud Enterprise Network (CEN)
Object Storage Service (OSS)	Content Delivery Network (CDN)	Short Message Service (SMS)	Container Service for Kubernetes (ACK)	Data Lake Analytics (DLA)
ApsaraDB for Redis (Redis)	ApsaraDB for MongoDB (MongoDB)	NAT Gateway	VPN Gateway	Cloud Firewall
Anti-DDoS	Web Application Firewall (WAF)	Log Service	DataWorks	MaxCompute
Elastic MapReduce (EMR)	Elasticsearch