MySQL very occasional Unable to save result set

Question

This is a production PHP/MySQL system that has been running for months, mostly without issue.

Very occasionally, like once every few weeks, I see "Unable to save result set" errors from a MySQL query.

I'm aware that this can be caused by a large result set, but that's not the case here. In the most recent example, it happened for tiny table that usually has no more than 10 rows (rows represent active games, and they are removed after the games end---lots of turn-over, but always tiny).

The query is very simple:

SELECT player_1_id, player_2_id FROM minuetServer_games 
WHERE  ( player_1_id = '1513399' OR player_2_id = '1513399' )  
AND last_action_time < SUBTIME( CURRENT_TIMESTAMP, '0 0:01:22.000' ) 
FOR UPDATE;

The point of this query is to find any active game for a given user_id (they can be either p1 or p2) that isn't too stale. If one exists, I updated their game's last_action_time in the next query (of course, when the result set isn't saved, that update can't happen).

I ran CHECK TABLE and the result was OK.

What are the causes of spurious, occasional "Unable to save result set" warnings? Is there a way to fix it? Is there a solid work-around?

Edit:

A commenter requested more info about he PHP config. Here's a direct link to the output of phpinfo():

http://cordialminuet.com/info.php

Edit2:

Here's the code where the query is created:

global $tableNamePrefix;
cm_queryDatabase( "SET AUTOCOMMIT=0" );

global $moveTimeLimit;

$query = "SELECT player_1_id, player_2_id ".
    "FROM $tableNamePrefix"."games ".
    "WHERE ( player_1_id = '$user_id' OR player_2_id = '$user_id' ) ".
    " AND last_action_time < ".
    " SUBTIME( CURRENT_TIMESTAMP, '$moveTimeLimit' ) FOR UPDATE;";

// watch for deadlock here and retry
$result = cm_queryDatabase( $query, 0 );

Here's the code for cm_queryDatabase:

function cm_queryDatabase( $inQueryString, $inDeadlockFatal=1 ) {
    global $cm_mysqlLink;
    if( gettype( $cm_mysqlLink ) != "resource" ) {
        // not a valid mysql link?
        cm_connectToDatabase();
        }
    $result = mysql_query( $inQueryString, $cm_mysqlLink );

    // a bunch of error handling here if $result is FALSE
    //  ......

    // then finally

    return $result;
    }

Here's the code for cm_connectToDatabase:

function cm_connectToDatabase( $inTrackStats = true) {
    global $databaseServer,
           $databaseUsername, $databasePassword, $databaseName,
           $cm_mysqlLink;

    $cm_mysqlLink =
        mysql_connect( $databaseServer, 
                       $databaseUsername, $databasePassword );


    // bunch of error handling if $cm_mysqlLink is false
    // ....
    }

Note that "Unable to save result set" is a warning thrown by mysql_query. Also, that this query is run hundreds of times a day without issue. This warning happens spuriously once every few weeks at most. Here's the most recent stack trace, from a few days ago:

Warning:  mysql_query() [<a href='function.mysql-query'>function.mysql-query</a>]: Unable to save result set in /home/jcr14/public_html/gameServer/server.php on line 11248

#0  cm_noticeAndWarningHandler(2, mysql_query() [<a href='function.mysql-query'>function.mysql-query</a>]: Unable to save result set, /home/jcr14/public_html/gameServer/server.php, 11248, Array ([inQueryString] => SELECT player_1_id, player_2_id FROM minuetServer_games WHERE  ( player_1_id = '1513399' OR player_2_id = '1513399' )  AND last_action_time <      SUBTIME( CURRENT_TIMESTAMP, '0 0:01:22.000' ) FOR UPDATE;,[inDeadlockFatal] => 0,[cm_mysqlLink] => Resource id #4))

#1  mysql_query(SELECT player_1_id, player_2_id FROM minuetServer_games WHERE  ( player_1_id = '1513399' OR player_2_id = '1513399' )  AND last_action_time <      SUBTIME( CURRENT_TIMESTAMP, '0 0:01:22.000' ) FOR UPDATE;, Resource id #4) called at [/home/jcr14/public_html/gameServer/server.php:11248]

#2  cm_queryDatabase(SELECT player_1_id, player_2_id FROM minuetServer_games WHERE  ( player_1_id = '1513399' OR player_2_id = '1513399' )  AND last_action_time <      SUBTIME( CURRENT_TIMESTAMP, '0 0:01:22.000' ) FOR UPDATE;, 0) called at [/home/jcr14/public_html/gameServer/server.php:5979]

I'll look in the server logs for other examples from over the past few months.

Edit 3:

In the past 30 days (I'm flushing older log entries), this has happened five times. Four of the occurrences were for the above query. One occurrence happened for a different query:

SELECT next_magic_square_seed % 4294967296 
FROM minuetServer_server_globals FOR UPDATE;

This on is also called 100s of times a day without issue. This is a table with a single row in it.

The surrounding code:

function cm_getNewSquare() {

    global $tableNamePrefix;

    // have it wrap around at the 32-bit unsigned max
    // because getMagicSquare6 takes a 32-bit unsigned seed.
    // we store it as a BIGINT to keep it from getting stuck on the same
    // square after four billion games

    $query = "SELECT next_magic_square_seed % 4294967296 ".
        "FROM $tableNamePrefix".
        "server_globals FOR UPDATE;";

    $result = cm_queryDatabase( $query );

Edit 4:

The table structure:

mysql> describe minuetServer_games;
+-----------------------+---------------------+------+-----+---------+----------------+
| Field                 | Type                | Null | Key | Default | Extra          |
+-----------------------+---------------------+------+-----+---------+----------------+
| game_id               | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| creation_time         | datetime            | NO   |     | NULL    |                |
| last_action_time      | datetime            | NO   |     | NULL    |                |
| player_1_id           | int(10) unsigned    | NO   | MUL | NULL    |                |
| player_2_id           | int(10) unsigned    | NO   | MUL | NULL    |                |
| dollar_amount         | decimal(11,2)       | NO   | MUL | NULL    |                |
| amulet_game           | tinyint(3) unsigned | NO   | MUL | NULL    |                |
| amulet_game_wait_time | datetime            | NO   | MUL | NULL    |                |
| started               | tinyint(3) unsigned | NO   |     | NULL    |                |
| round_number          | int(10) unsigned    | NO   |     | NULL    |                |
| game_square           | char(125)           | NO   |     | NULL    |                |
| player_1_got_start    | tinyint(4)          | NO   |     | NULL    |                |
| player_2_got_start    | tinyint(4)          | NO   |     | NULL    |                |
| player_1_moves        | char(13)            | NO   |     | NULL    |                |
| player_2_moves        | char(13)            | NO   |     | NULL    |                |
| player_1_bet_made     | tinyint(3) unsigned | NO   |     | NULL    |                |
| player_2_bet_made     | tinyint(3) unsigned | NO   |     | NULL    |                |
| player_1_ended_round  | tinyint(3) unsigned | NO   |     | NULL    |                |
| player_2_ended_round  | tinyint(3) unsigned | NO   |     | NULL    |                |
| move_deadline         | datetime            | NO   |     | NULL    |                |
| player_1_coins        | tinyint(3) unsigned | NO   |     | NULL    |                |
| player_2_coins        | tinyint(3) unsigned | NO   |     | NULL    |                |
| player_1_pot_coins    | tinyint(3) unsigned | NO   |     | NULL    |                |
| player_2_pot_coins    | tinyint(3) unsigned | NO   |     | NULL    |                |
| settled_pot_coins     | tinyint(3) unsigned | NO   |     | NULL    |                |
| semaphore_key         | int(10) unsigned    | NO   |     | NULL    |                |
+-----------------------+---------------------+------+-----+---------+----------------+
26 rows in set (0.00 sec)

The CREATE statement:

 "CREATE TABLE $tableName(" .
"game_id BIGINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT," .
"creation_time DATETIME NOT NULL,".
"last_action_time DATETIME NOT NULL,".
"player_1_id INT UNSIGNED NOT NULL," .
"INDEX( player_1_id )," .
"player_2_id INT UNSIGNED NOT NULL," .
"INDEX( player_2_id )," .
"dollar_amount DECIMAL(11, 2) NOT NULL,".
"INDEX( dollar_amount )," .
"amulet_game TINYINT UNSIGNED NOT NULL,".
"INDEX( amulet_game ),".
// wait for a random amount of time before
// settling on an opponent for an amulet game
"amulet_game_wait_time DATETIME NOT NULL,".
"INDEX( amulet_game_wait_time ),".
"started TINYINT UNSIGNED NOT NULL,".
"round_number INT UNSIGNED NOT NULL," .
// 36-cell square, numbers from 1 to 36, separated by #
// character
"game_square CHAR(125) NOT NULL,".
// flag set when each player requests the very first game
// state. This indicates that both are aware that the game
// has started, so leave penalties can be assessed
"player_1_got_start TINYINT NOT NULL,".
"player_2_got_start TINYINT NOT NULL,".
"player_1_moves CHAR(13) NOT NULL,".
"player_2_moves CHAR(13) NOT NULL,".
"player_1_bet_made TINYINT UNSIGNED NOT NULL,".
"player_2_bet_made TINYINT UNSIGNED NOT NULL,".
"player_1_ended_round TINYINT UNSIGNED NOT NULL,".
"player_2_ended_round TINYINT UNSIGNED NOT NULL,".
"move_deadline DATETIME NOT NULL,".
"player_1_coins TINYINT UNSIGNED NOT NULL, ".
"player_2_coins TINYINT UNSIGNED NOT NULL, ".
"player_1_pot_coins TINYINT UNSIGNED NOT NULL, ".
"player_2_pot_coins TINYINT UNSIGNED NOT NULL, ".
// coins in both pots that are common knowledge to both players
// (coins that have been matched by opponent to move on
// to next turn)
"settled_pot_coins TINYINT UNSIGNED NOT NULL, ".
"semaphore_key INT UNSIGNED NOT NULL ) ENGINE = INNODB;"

Answer 1

1. What are the causes of spurious, occasional "Unable to save result set" warnings?

   Sadly this error is not very well documented in the PHP manual. So, let's RTFS!

   Looking through the source code for your version of ext/mysql, the error "Unable to save result set" appears only once:

       if(use_store == MYSQL_USE_RESULT) {
           mysql_result=mysql_use_result(mysql->conn);
       } else {
           mysql_result=mysql_store_result(mysql->conn);
       }
       if (!mysql_result) {
           if (PHP_MYSQL_VALID_RESULT(mysql->conn)) { /* query should have returned rows */
               php_error_docref(NULL TSRMLS_CC, E_WARNING, "Unable to save result set");
   

   Looking at the definition of the PHP_MYSQL_VALID_RESULT macro:

   #define PHP_MYSQL_VALID_RESULT(mysql)       \
       (mysql_field_count(mysql)>0)
   

   It's clear that, for this error to arise, mysql_store_result() must return NULL yet mysql_field_count() must return a positive number. The MySQL C API documentation for the latter function states:

   The normal use of this function is when mysql_store_result() returned NULL (and thus you have no result set pointer). In this case, you can call mysql_field_count() to determine whether mysql_store_result() should have produced a nonempty result. This enables the client program to take proper action without knowing whether the query was a SELECT (or SELECT-like) statement.

   Well, that seems to describe exactly the situation that has arisen in your case. Following the link therein to Section 23.8.15.1, “Why mysql_store_result() Sometimes Returns NULL After mysql_query() Returns Success”:

   It is possible for mysql_store_result() to return NULL following a successful call to mysql_query(). When this happens, it means one of the following conditions occurred:

   • There was a malloc() failure (for example, if the result set was too large).

   • The data could not be read (an error occurred on the connection).

   • The query returned no data (for example, it was an INSERT, UPDATE, or DELETE).

   Since the condition arose whilst you were performing SELECT queries, and there is no indication of an error on the connection (which I imagine would be dropped by the client if that were the case), it would therefore appear that the most likely cause of this error is a malloc() failure. This answer gives a very good explanation of the circumstances in which malloc() can fail.

2. Is there a way to fix it?

   Of course there is, yes! But it could well involve detailed analysis into why MySQL's attempt to allocate memory failed in this instance. My guess would be that your hosting environment limits the memory available to MySQL, and that memory allocation has been reached.

   Perhaps simply increasing the memory available to MySQL would help? Otherwise, you might like to read through How MySQL Uses Memory and start tweaking some server settings…

3. Is there a solid work-around?

   You might simply reattempt the query in the event that this condition arises?

Answer 2

I've got this issue sorted by freeing up my server disk space as my server hit 100% usage.

This issue has been logged in /var/log/mysql/error.log, so I always check the error log first before I do anything.