Timeout processing for accessing MySQL using PHP

Source: Internet
Author: User
Tags mysql query

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:

<? Php
// 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", mysqli_connect_error ());
Exit ();
// The connection information is output successfully.
Printf ("Connection: % s.", $ 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:

Php-5.2.8/ext/mysqli. c


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

Mysql-5.1.30/SQL-common/client. c


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

Mysql-5.1.30/include/mysql. h


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

Mysql-5.1.30/SQL-common/client. c



The real operation of read/write timeout. The timeout is retried twice, or the write is dead:







Now I have come to the conclusion:

According to the code shown above, currently, PHP has the following restrictions on MySQL query Timeout:

1. The unit of timeout setting is seconds. The minimum value is 1 second.

2. However, the underlying read of mysql will be retried twice, so it will actually be 3 seconds.


Retry twice + itself at one time = 3 times the timeout time.


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




Now let's take a look at it. If we want to set the timeout, we can press MYSQL_OPT_READ_TIMEOUT to achieve the read/write timeout effect. Write a piece of code to test it:


<? Php
// Customize read/write timeout Constants

// 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", mysqli_connect_error ());
Exit ();

// Execute the query sleep for 1 second without timeout
Printf ("Host information: % s", $ mysqli-> host_info );
If (! ($ Res = $ mysqli-> query (select sleep (1 )))){
Echo "query1 error:". $ mysqli-> error ."";
} Else {
Echo "Query1: query success ";

// Execution of the query sleep will time out in 9 seconds
If (! ($ Res = $ mysqli-> query (select sleep (9 )))){
Echo "query2 error:". $ mysqli-> error ."";
} Else {
Echo "Query2: query success ";

$ Mysqli-> close ();
Echo "close mysql connection ";

Check the execution result of the code above and verify the above view. The first query is successful, and the second query connection is disconnected:



If you need to modify the timeout value in seconds, for example, to change the timeout value in milliseconds, you can only modify it in two places:


1. Modify the client, such as the query code of mysqli, and add the timer. If the timeout time is exceeded, return

2. Modify the vio code in Mysql because the underlying network processing of mysql is performed by vio.


MySQL-related vio code:

Poll Timeout:


Setsockopt Timeout:



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


Aspx ">

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.