Setting a Query Timeout for MySQL中文EN

Yesterday someone asked in the group whether MySQL can be configured with a read/write timeout (not a connection timeout). If so, it could avoid a slow SQL query causing a PHP timeout error. Actually, yes it can — it's just a bit more of a hassle.

First, libmysql does provide a MYSQL_OPT_READ_TIMEOUT setting, and libmysql provides the API mysql_options to set such 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;
   /* abridged */

But unfortunately, at present only the mysqli extension exposes mysql_options fully to PHP:

PHP_FUNCTION(mysqli_options)
{
 /** abridged */
     switch (Z_TYPE_PP(mysql_value)) {
        /* no restriction, passed directly 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);
}

But because Mysqli doesn't export this constant, we need to look at the MySQL code to get the actual value of MYSQL_OPT_READ_TIMEOUT, then call mysql_options directly:

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
};

You can see that MYSQL_OPT_READ_TIMEOUT is 11.
Now we can set the query timeout:

<?php
$mysqli = mysqli_init();
$mysqli->options(11 /*MYSQL_OPT_READ_TIMEOUT*/, 1);
$mysql->real_connect(***);

However, because there's a retry mechanism in libmysql (one try, then two retries), the timeout threshold we actually end up with is three times the value we set.
That is, if we set MYSQL_OPT_READ_TIMEOUT to 1, it will actually time out after 3s. In other words, the shortest timeout we can currently set is 3 seconds...
Though that's a bit large,, it's still better than nothing, haha
PS: Halfway through writing this, I realized heiyeshuwu had already written one. So you can also see this article Handling MySQL query timeout when PHP accesses MySQL