Asked  9 Months ago    Answers:  5   Viewed   75 times

CentOS 6.4 PHP 5.3.3 MySQL 5.1.69 x86_64

mysql_stmt::fetch()

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

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 Code:

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 Code:

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

 Answers

60

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

The problem is twofold:

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

    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.

    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:

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

    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.

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

Your options include:

  • 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;
    
  • 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();
    
  • 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');
    
  • 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
    
  • Upgrading to PHP v5.4.0 or later so that mysqlnd is used by default.

Wednesday, March 31, 2021
 
CMOS
answered 9 Months ago
93

You're getting the error because $mail at the bottom of your code is not an object. You have this snippet of code:

$mail=New_Mail($email,$firstname,$surname,'This is the body','Welcome','this is the alternate body',100);

However, New_Mail() doesn't actually return anything, so $mail is an empty variable.

One way to fix this is to return the $mail object in New_Mail().

Another note: be careful when choosing variable names. You're using the varname $mail within the New_Mail() function and outside of the function. This is perfectly fine, but just remember that the $mail object from within New_Mail() is no longer in scope by the time you call Send(). That's why PHP is throwing up.

Wednesday, March 31, 2021
 
inVader
answered 9 Months ago
33

I've solved this in the past by using simply:

php -d memory_limit=1G /usr/local/bin/composer update --prefer-dist --no-dev

Where the no-dev may help reduce the memory footprint too - certainly speed.

Saturday, May 29, 2021
 
DilbertDave
answered 7 Months ago
71

you can't print the result from mysqli_query, it is mysqli_resource and for dumping the error you need to change mysql_error() to mysqli_error()

$username = "bob";
$db = mysqli_connect("localhost", "username", "password", "user_data");
$sql1 = "select id from user_information where username='$username'";
$result = mysqli_query($db, $sql1) or die(mysqli_error());
while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) { 
    echo $row['id'].'<br>'; 
} 
Saturday, May 29, 2021
 
pamelus
answered 7 Months ago
24

I don't think it will work this way. When you close the statement (e.g. $menu_stmt->close();) you also deallocate the statement handle. So the second time through the loop you don't have the prepared statements available to work with anymore.

Try closing the statements after the loop has finished executing.

Friday, August 6, 2021
 
NIKHIL
answered 4 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :
 
Share