PHP mysql_stmt::fetch() 给 PHP 致命错误内存耗尽

2022-01-20 00:00:00 fetch php mysql mysqli

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 致命错误:允许的内存大小为 134217728 字节已用尽(尝试分配 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;

  • 明确地将用户变量转换到更具体的用户变量数据类型:

  • Explicitly casting the user variable to a more specific datatype:

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

  • 使用以显式数据类型声明的局部变量:

  • Using local variables, which are declared with an explicit datatype:

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

  • 通过调用 mysqli_stmt::store_result() before mysqli_stmt::bind_result()，导致结果集存储在 libmysql 中(PHP 内存之外限制)，然后 PHP 将仅在获取记录时分配保存记录所需的实际内存:

  • Working around the issue by calling mysqli_stmt::store_result() before mysqli_stmt::bind_result(), which causes the resultset to be stored in libmysql (outside of PHP's memory limits) and then PHP will only allocate the actual memory required to hold the record upon fetching it:

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

  • 提高 PHP 的 内存限制 所以它可以适应 4GiB 缓冲区的分配(尽管应该意识到这样做对硬件资源的影响)—例如，完全消除内存限制(尽管要注意这样做的潜在负面影响，例如来自真正的内存泄漏):

  • Raising PHP's memory limit so that it can accomodate the allocation of 4GiB buffers (although one should be aware of the implications on hardware resources from doing so)—for example, to remove the memory constraints entirely (although be aware of potential negative side-effects from doing this, e.g. from genuine memory leaks):

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

  • 重新编译 PHP，配置为使用 本机 mysqlnd 驱动 (自 v5.3.0 起包含在 PHP 中，但直到 PHP v5.4.0 才配置为默认值)而不是 libmysql:

  • Recompiling PHP, configured to use the native mysqlnd driver (included with PHP since v5.3.0, but not configured as the default until PHP v5.4.0) instead of libmysql:

    ./configure --with-mysqli=mysqlnd

  • 升级到PHP v5.4.0或更高版本，默认使用mysqlnd.

  • Upgrading to PHP v5.4.0 or later so that mysqlnd is used by default.

    • 相关文章