Set query timeout for MySQL _ MySQL

Set query timeout for MySQL bitsCN.com

Whether MySQL can set read/write timeout (non-connection timeout). if so, you can avoid the PHP timeout error caused by slow execution of an SQL statement. In fact, this can be a bit complicated.

First, in libmysql, the MYSQL_OPT_READ_TIMEOUT setting item is provided, and libmysql provides the API for setting related settings, mysql_options:

Int STDCALL

Mysql_options (MYSQL * mysql, enum mysql_option option, const void * arg)

{

DBUG_ENTER ("mysql_option ");

DBUG_PRINT ("enter", ("option: % d", (int) option ));

Switch (option ){

Case MYSQL_OPT_CONNECT_TIMEOUT:

Mysql-> options. connect_timeout = * (uint *) arg;

Break;

/** Read timeout */

Case MYSQL_OPT_READ_TIMEOUT:

Mysql-> options. read_timeout = * (uint *) arg;

Break;

Case MYSQL_OPT_WRITE_TIMEOUT:

Mysql-> options. write_timeout = * (uint *) arg;

Break;

Case MYSQL_OPT_COMPRESS:

Mysql-> options. compress = 1;

/* The following is Omitted */

However, unfortunately, currently only the mysqli extension is available, and mysql_options is completely exposed to PHP:

PHP_FUNCTION (mysqli_options)

{

/** Omitted */

Switch (Z_TYPE_PP (mysql_value )){

/** Without any restrictions, directly pass to mysql_options */

Case IS_STRING:

Ret = mysql_options (mysql-> mysql, mysql_option, Z_STRVAL_PP (mysql_value ));

Break;

Default:

Convert_to_long_ex (mysql_value );

L_value = Z_LVAL_PP (mysql_value );

Ret = mysql_options (mysql-> mysql, mysql_option, (char *) & l_value );

Break;

}

RETURN_BOOL (! Ret );

}

However, because Mysqli does not export this constant, we need to view the MySQL code to obtain the actual value of MYSQL_OPT_READ_TIMEOUT, and then directly call mysql_options:

Enum mysql_option

{

MYSQL_OPT_CONNECT_TIMEOUT, MYSQL_OPT_COMPRESS, MYSQL_OPT_NAMED_PIPE,

MYSQL_INIT_COMMAND, MYSQL_READ_DEFAULT_FILE, MYSQL_READ_DEFAULT_GROUP,

MYSQL_SET_CHARSET_DIR, MYSQL_SET_CHARSET_NAME, MYSQL_OPT_LOCAL_INFILE,

MYSQL_OPT_PROTOCOL, MYSQL_SHARED_MEMORY_BASE_NAME, MYSQL_OPT_READ_TIMEOUT,

MYSQL_OPT_WRITE_TIMEOUT, MYSQL_OPT_USE_RESULT,

MYSQL_OPT_USE_REMOTE_CONNECTION, MYSQL_OPT_USE_EMBEDDED_CONNECTION,

MYSQL_OPT_GUESS_CONNECTION, MYSQL_SET_CLIENT_IP, MYSQL_SECURE_AUTH,

MYSQL_REPORT_DATA_TRUNCATION, MYSQL_OPT_RECONNECT,

MYSQL_OPT_SSL_VERIFY_SERVER_CERT

};

As you can see, MYSQL_OPT_READ_TIMEOUT is 11.

Now, we can set query timeout:

　　

$ Mysqli = mysqli_init ();

$ Mysqli-> options (11/* MYSQL_OPT_READ_TIMEOUT */, 1 );

$ Mysql-> real_connect (***);

However, because there is a retry mechanism in libmysql (retry once or twice), the timeout threshold we set will be three times the value we set.

That is to say, if we set MYSQL_OPT_READ_TIMEOUT to 1, the timeout will end after 3 S. that is to say, the minimum timeout value that we can set now is 3 seconds...

Although it is a little bigger, it is better than nothing.

BitsCN.com