問題描述

CentOS 6.4 PHP 5.3.3 MySQL 5.1.69 x86_64

CentOS 6.4 PHP 5.3.3 MySQL 5.1.69 x86_64

mysql_stmt::fetch()

當(dāng)使用準(zhǔn)備好的語句執(zhí)行 fetch 時(shí)，PHP 產(chǎn)生錯(cuò)誤:PHP Fatal error: Allowed memory size of 134217728 bytes expired (試圖分配 4294967296 字節(jié)).

When executing fetch using a prepared statement, PHP yields error: PHP Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 4294967296 bytes).

當(dāng)用于創(chuàng)建臨時(shí)表的 SELECT 語句中包含的變量未設(shè)置時(shí)，無論是否在調(diào)用存儲(chǔ)過程之前在環(huán)境中設(shè)置了該變量，都會(huì)發(fā)生這種情況.該變量必須在存儲(chǔ)過程中設(shè)置.當(dāng)使用SELECT語句將臨時(shí)表中的數(shù)據(jù)返回給PHP，而PHP使用mysql_stmt::fetch()訪問數(shù)據(jù)時(shí)，PHP產(chǎn)生上述致命錯(cuò)誤.

This occurs when a variable included in a SELECT statement used to create a temporary table is unset, whether or not the variable is otherwise set in the environment before the stored procedure is called. The variable must be set within the stored procedure. When a SELECT statement is used to return data in the temporary table to PHP, and PHP uses mysql_stmt::fetch() to access the data, PHP generates the above fatal error.

MySQL 代碼:

DELIMITER $$
CREATE PROCEDURE test_sp()
BEGIN

    # uncomment below line, and PHP call to mysqli_stmt::fetch() works
    # SET @status = 1;

    # remove tmp table
    DROP TABLE IF EXISTS tmp_table;
    # CREATE TEMPORARY TABLE
    CREATE TEMPORARY TABLE tmp_table
        SELECT @status AS status;

    SELECT * FROM tmp_table;

END $$
DELIMITER ;

PHP 代碼:

// obtain MySQL login info
require_once(MYSQLOBJ);

// initialize status
$status = "";


$db = new mysqli(
    DB_HOST,
    DB_USER,
    DB_PASSWORD,
    DB_NAME
    );


$query = "CALL test_sp";

$stmt = $db->prepare($query);

$stmt->execute();

$stmt->bind_result( $status );

$stmt->store_result();

$stmt->fetch(); // PHP FATAL ERROR OCCURS HERE

$stmt->free_result();

$db->close();

print "<p>status = $status</p>
";

推薦答案

你會(huì)發(fā)現(xiàn)這僅在 @status 為 NULL 時(shí)發(fā)生> 或一個(gè)字符串.

You will find that this is occurring only when @status is NULL or a string.

問題是雙重的:

1. 與局部變量不同，MySQL 用戶變量支持非常有限的一組數(shù)據(jù)類型:

1. Unlike local variables, MySQL user variables support a very limited set of datatypes:

可以從一組有限的數(shù)據(jù)類型中為用戶變量分配一個(gè)值:整數(shù)、十進(jìn)制、浮點(diǎn)、二進(jìn)制或非二進(jìn)制字符串，或者 NULL 值.

User variables can be assigned a value from a limited set of data types: integer, decimal, floating-point, binary or nonbinary string, or NULL value.

文檔沒有提到使用的實(shí)際數(shù)據(jù)類型分別是BIGINT、DECIMAL(65,30)、DOUBLE、LONGBLOB、LONGTEXT 和 LONGBLOB.關(guān)于最后一個(gè)，手冊至少做了解釋:

The documentation fails to mention that the actual datatypes used are respectively BIGINT, DECIMAL(65,30), DOUBLE, LONGBLOB, LONGTEXT and LONGBLOB. Regarding the last one, the manual does at least explain:

如果你引用一個(gè)沒有被初始化的變量，它的值是NULL，類型是字符串.

If you refer to a variable that has not been initialized, it has a value of NULL and a type of string.

前三種數(shù)據(jù)類型的存儲(chǔ)(即整數(shù)、十進(jìn)制和浮點(diǎn)值)分別需要 8、30 和 8 個(gè)字節(jié).其他數(shù)據(jù)類型(即字符串和 NULL 值)需要(最多)4 GB 的存儲(chǔ)空間.

Storage of the first three of these datatypes (i.e. for integer, decimal and floating-point values) require 8, 30 and 8 bytes respectively. The other datatypes (i.e. for string and NULL values) require (up to) 4 gigabytes of storage.

由于您使用的是 v5.4.0 之前的 PHP 版本，因此默認(rèn)的 MySQL 驅(qū)動(dòng)程序是 libmysql，在數(shù)據(jù)綁定時(shí)只有列類型元數(shù)據(jù)可從服務(wù)器獲得——因此 MySQLi 嘗試分配足夠的內(nèi)存來保存每個(gè)可能的值(即使最終不需要完整的緩沖區(qū));因此，NULL- 和字符串值的用戶變量(最大可能大小為 4GiB)導(dǎo)致 PHP 超出其默認(rèn)內(nèi)存限制(自 PHP v5.2.0 起為 128MiB).

Since you are using a version of PHP prior to v5.4.0, the default MySQL driver is libmysql, with which only column type metadata is available from the server upon data binding—so MySQLi attempts to allocate sufficient memory to hold every possible value (even if the full buffer is not ultimately required); thus NULL- and string-valued user variables, which have a maximum possible size of 4GiB, cause PHP to exceed its default memory limit (of 128MiB since PHP v5.2.0).

您的選擇包括:

• 覆蓋表定義中的列數(shù)據(jù)類型:

• Overriding the column datatype in the table definition:

DROP TEMPORARY TABLE IF EXISTS tmp_table;
CREATE TEMPORARY TABLE tmp_table (
  status VARCHAR(2)
) SELECT @status AS status;

DROP TEMPORARY TABLE IF EXISTS tmp_table;
CREATE TEMPORARY TABLE tmp_table
  SELECT CAST(@status AS CHAR(2)) AS status;

DECLARE status VARCHAR(2) DEFAULT @status;
DROP TEMPORARY TABLE IF EXISTS tmp_table;
CREATE TEMPORARY TABLE tmp_table
  SELECT status;

$stmt->execute();
$stmt->store_result();
$stmt->bind_result( $status );
$stmt->fetch();

ini_set('memory_limit', '-1');

./configure --with-mysqli=mysqlnd