How to Set query timeout in mysql

This article introduces the time-out period of custom mysql query. Let's take a look at the detailed setting method.

This article introduces the time-out period of custom mysql query. Let's take a look at the detailed setting method.

First, in libmysql, The MYSQL_OPT_READ_TIMEOUT setting item is provided, and libmysql provides the API for setting related setting items,

The Code is as follows:

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:

The Code is as follows:

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:

The Code is as follows:

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:

The Code is as follows:
$ 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.