PHP set timeout for MySQL query read data

Source: Internet
Author: User
Tags dsn ini mysql query one more line php code postgresql redis sleep



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

Alibaba Cloud Free Trail

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.