这是一个已经运行了几个月的PHP/MMySQL生产系统,基本上没有问题。
偶尔,比如每隔几周,我会看到MySQL查询中出现"无法保存结果集"错误。
我知道这可能是由大的结果集引起的,但这里的情况并非如此。在最近的例子中,这种情况发生在通常不超过10行的小桌子上(行代表活动游戏,在游戏结束后会被删除——很多翻转,但总是很小)。
查询非常简单:
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语句:
"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;"
-
虚假的、偶尔出现的"无法保存结果集"警告的原因是什么?
遗憾的是,PHP手册中并没有很好地记录这个错误。所以,让我们使用RTF!
查看
ext/mysql
版本的源代码,错误"Unable to save result set"
只出现一次: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");
查看
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文档说明:此函数的正常使用是当
mysql_store_result()
返回NULL
时(因此您没有结果集指针)。在这种情况下,可以调用mysql_field_count()
来确定mysql_store_result()
是否应该产生非空结果。这使得客户端程序能够在不知道查询是SELECT
(还是类似SELECT
)语句的情况下采取适当的操作。好吧,这似乎正好描述了你案件中出现的情况。以下链接到第23.8.15.1节,"为什么mysql_store_result()有时在mysql_query()返回成功后返回NULL":
mysql_store_result()
可以在成功调用mysql_query()
之后返回NULL
。当这种情况发生时,意味着发生了以下情况之一:存在
malloc()
故障(例如,如果结果集太大)。无法读取数据(连接上出现错误)。
查询没有返回任何数据(例如,它是
INSERT
、UPDATE
或DELETE
)。
由于这种情况是在执行
SELECT
查询时出现的,并且没有迹象表明连接出现错误(如果是这种情况,我想会被客户端删除),因此,出现这种错误的最可能原因是malloc()
故障。这个答案很好地解释了malloc()
可能失败的情况。 -
有办法解决它吗?
当然有,是的!但它很可能涉及到MySQL在这种情况下分配内存失败的原因的详细分析。我的猜测是,您的托管环境限制了MySQL可用的内存,并且已经达到了内存分配。
也许简单地增加MySQL可用的内存会有所帮助?否则,您可能需要阅读MySQL如何使用内存,并开始调整一些服务器设置…
-
周围有扎实的工作吗?
如果出现这种情况,您可以简单地重新尝试查询吗?
我已经通过在服务器达到100%使用率时释放服务器磁盘空间来解决这个问题。
这个问题已经记录在/var/log/mysql/error.log中,所以在做任何事情之前,我总是先检查错误日志。