問題描述

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

當使用準備好的語句執行 fetch 時，PHP 產生錯誤:PHP Fatal error: Allowed memory size of 134217728 bytes expired (試圖分配 4294967296 字節).

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

當用于創建臨時表的 SELECT 語句中包含的變量未設置時，無論是否在調用存儲過程之前在環境中設置了該變量，都會發生這種情況.該變量必須在存儲過程中設置.當使用SELECT語句將臨時表中的數據返回給PHP，而PHP使用mysql_stmt::fetch()訪問數據時，PHP產生上述致命錯誤.

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

推薦答案

你會發現這僅在 @status 為 NULL 時發生> 或一個字符串.

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

問題是雙重的:

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

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

可以從一組有限的數據類型中為用戶變量分配一個值:整數、十進制、浮點、二進制或非二進制字符串，或者 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.

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

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:

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

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

前三種數據類型的存儲(即整數、十進制和浮點值)分別需要 8、30 和 8 個字節.其他數據類型(即字符串和 NULL 值)需要(最多)4 GB 的存儲空間.

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 版本，因此默認的 MySQL 驅動程序是 libmysql，在數據綁定時只有列類型元數據可從服務器獲得——因此 MySQLi 嘗試分配足夠的內存來保存每個可能的值(即使最終不需要完整的緩沖區);因此，NULL- 和字符串值的用戶變量(最大可能大小為 4GiB)導致 PHP 超出其默認內存限制(自 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).

您的選擇包括:

• 覆蓋表定義中的列數據類型:

• 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