mysql 无法保存 n_PHP-MySQL偶尔无法保存结果集

这是一个运行了几个月的生产PHP / MySQL系统,大多数情况下都没有问题.

偶尔,像每隔几周一次,我从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()输出的直接链接：

编辑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() [function.mysql-query]: Unable to save result set in /home/jcr14/public_html/gameServer/server.php on line 11248

#0 cm_noticeAndWarningHandler(2, mysql_query() [function.mysql-query]: 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;

每天也被称为无数次这种情况.这是一个只有一行的表.

周围的代码：

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