Setting a query timeout for MySQL

Author: Laruence Yesterday someone asked in a group chat whether MySQL can set a read/write timeout (not a connection timeout), because if it could, you could avoid a single slow SQL query causing a PHP timeout error. Well, it turns out you can. It’s just a bit more trouble. First of all, libmysql does provide the MYSQL_OPT_READ_TIMEOUT setting, and libmysql provides an API for setting the relevant options, 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;

     /\* omitted below */

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

PHP_FUNCTION(mysqli_options)
{
 /\*\* omitted */
     switch (Z\_TYPE\_PP(mysql_value)) {
        /\*\* no restriction at all, 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 source code to get the actual value of MYSQL_OPT_READ_TIMEOUT, and 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
};

As you can see, 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 libmysql has a retry mechanism (try once, retry twice), the timeout threshold we finally set ends up being three times the value we set. In other words, if we set MYSQL_OPT_READ_TIMEOUT to 1, it will finally time out after 3s. In other words, the shortest timeout we can currently set is 3 seconds… a bit long, sure, but better than nothing, heh. PS: Halfway through writing this I found that Xiaohei had already written an article about it, so you can also refer to this one: Handling PHP MySQL query timeouts