Timeout processing for accessing MySQL using PHP

PHP connects to MySQL mainly by using the libmysqlclient client library provided by Mysql, and also extends the extensions of mysql and mysqli, which is relatively better and more stable than mysql.

PHP connects to MySQL mainly by using the libmysqlclient client library provided by Mysql, and also extends the extensions of mysql and mysqli, which is relatively better and more stable than mysql.

Currently, you can set options to operate the connection timeout of two client extension libraries, such as mysqli:
The Code is as follows:
// Create an object
$ Mysqli = mysqli_init ();
// Set the timeout Option
$ Mysqli-> options (MYSQLI_OPT_CONNECT_TIMEOUT, 5 );
// Connection
$ Mysqli-> real_connect ('localhost', 'My _ user', 'My _ password', 'World ');
// Print the error message if the connection times out or fails.
If (mysqli_connect_errno ()){
Printf ("Connect failed: % s \ n", mysqli_connect_error ());
Exit ();
}
// The connection information is output successfully.
Printf ("Connection: % s \ n.", $ mysqli-> host_info );
$ Mysqli-> close ();
?>

This is the connection timeout, but sometimes we need to query the read/write timeout. For example, if a database is under great pressure or has many connections, the database query will be very slow, however, I hope that some unimportant data, such as the number of clicks in the article, will not be displayed if the query times out, at least to ensure that the subject page is correctly displayed, however, this operation option or function was not found in the PHP manual.

There are only four options in the manual

Tracking the extended source code of mysqli, we found that it calls mysql_options of libmysqlclient at the underlying layer:

In addition, only a few variables are exported in the PHP extension of mysqli:

After reading the libmysqlclient code, I found that it comes with a read/write timeout setting:

Because it defines many operation options, but the php extension does not:

Let's see how mysql achieves read/write Timeout:

The Code is as follows:
// Customize read/write timeout Constants
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 );

// Connect to the database
$ Mysqli-> real_connect ("localhost", "root", "root", "test ");
If (mysqli_connect_errno ()){
Printf ("Connect failed: % s \ n", mysqli_connect_error ());
Exit ();
}

// Execute the query sleep for 1 second without 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 ";
}

// Execution of the query sleep will time out in 9 seconds
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 ";
?>

This basically solves the timeout of PHP read/write query operations on MySQL. I hope it will help you.
Heiyeluren's blog