php - MySQL 非常偶然 Unable save result set

Question

这是一个生产 PHP/MySQL 系统，已经运行了几个月，大部分时间都没有问题。

偶尔，比如每隔几周一次，我会看到来自 MySQL 查询的“无法保存结果集”错误。

我知道这可能是由大型结果集引起的，但这里不是这种情况。在最近的示例中，它发生在通常不超过 10 行的小 table 上(行代表正在进行的游戏，并且在游戏结束后它们会被删除——很多翻转，但总是很小)。

查询很简单:

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;

此查询的目的是为给定的 user_id(它们可以是 p1 或 p2)找到任何不太陈旧的事件游戏。如果存在，我会在下一个查询中更新他们游戏的 last_action_time(当然，当结果集未保存时，更新不会发生)。

我运行了 CHECK TABLE，结果正常。

虚假的、偶尔出现的“无法保存结果集”警告的原因是什么？有办法解决吗？有可靠的解决方法吗？

编辑:

评论者要求提供有关 PHP 配置的更多信息。这是 phpinfo() 输出的直接链接:

http://cordialminuet.com/info.php

编辑2:

这是创建查询的代码:

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

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

这是 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
    // ....
    }

请注意，“无法保存结果集”是 mysql_query 抛出的警告。此外，此查询每天运行数百次而没有问题。这个警告最多每隔几周就会虚假地发生一次。这是几天前的最新堆栈跟踪:

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]

我会在服务器日志中查找过去几个月的其他示例。

编辑 3:

在过去 30 天内(我正在刷新较旧的日志条目)，这种情况发生了五次。其中四次是针对上述查询。不同的查询发生了一次:

SELECT next_magic_square_seed % 4294967296 
FROM minuetServer_server_globals FOR UPDATE;

这也是一天调用100次没有问题。这是一个只有一行的表格。

周边代码:

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

编辑 4:

表结构:

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)

创建语句:

 "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?

   遗憾的是，这个错误在 PHP 手册中没有得到很好的记录。那么，让我们使用 RTFS!

   查看您的ext/mysql 版本的源代码，错误“无法保存结果集” 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");
   

   查看the definition PHP_MYSQL_VALID_RESULT 宏:

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

   很明显，要出现此错误，mysql_store_result() 必须返回 NULL 而 mysql_field_count() 必须返回正数。 MySQL C API documentation对于后一个函数状态:

   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.

   好吧，这似乎准确描述了您的案例中出现的情况。点击其中的链接到 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).

   由于在您执行 SELECT 查询时出现了这种情况，并且没有连接错误的迹象(我想 如果情况确实如此)，因此看来导致此错误的最可能原因是 malloc() 失败。 This answer对 malloc() 可能失败的情况给出了很好的解释。

2. Is there a way to fix it?

   当然有，是的!但它很可能涉及详分割析为什么 MySQL 在这种情况下尝试分配内存失败。我的猜测是您的托管环境限制了 MySQL 可用的内存，并且已经达到了内存分配。

   也许简单地增加 MySQL 可用的内存会有所帮助？否则，您可能想通读 How MySQL Uses Memory并开始调整一些服务器设置……

3. Is there a solid work-around?

   如果出现这种情况，您可以简单地重新尝试查询吗？