PHP mysql_stmt :: fetch () gives obsolete PHP error memory

Question

CentOS 6.4 PHP 5.3.3 MySQL 5.1.69 x86_64

mysql_stmt::fetch() 

When fetching using a prepared statement, PHP gives an error: PHP Fatal error: allowable memory size of 134217728 bytes has been exhausted (tried to allocate 4294967296 bytes).

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

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

Answer 1

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

The problem is twofold:

• Unlike local variables , MySQL user variables support a very limited set of data types:

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

  The documentation does not mention that the actual data types used are BIGINT , DECIMAL(65,30) , DOUBLE , LONGBLOB , LONGTEXT and LONGBLOB . Regarding the latter, the manual at least explains:

  If you are referring to a variable that has not been initialized, it is NULL and the type of the string.

  The storage of the first three of these data types (that is, for integer, decimal, and floating values) requires 8, 30, and 8 bytes, respectively. Other data types (i.e., for string and NULL values) require (up to) 4 gigabytes of storage.

• Since you are using a version of PHP prior to version v5.4.0, the default MySQL driver libmysql is used , with which only a column of type metadata is available from the server when binding data & mdash, therefore MySQLi tries to allocate sufficient memory to store all possible values ​​(even if the full buffer is in the final account not required); thus, NULL and user variables with variable string lengths that have the maximum possible 4GiB size will cause PHP to exceed the default memory limit (128MiB with PHP v5.2.0).

Your options include:

• Overriding a column data type in a table definition:

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

• Casting a user variable explicitly into a more specific data type:

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

• Using local variables declared with an explicit data type:

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

• Work on the problem by calling mysqli_stmt::store_result() before mysqli_stmt::bind_result() , which causes the result set to be stored in libmysql (outside of the PHP memory limits), and then PHP will allocate the actual memory needed for storage of the record, after receiving it:

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

• Raise the PHP memory limit so that it can accommodate the distribution of 4GiB buffers (although you need to be aware of the hardware implications of resources from this). For example, to completely remove memory limits (although keep in mind potential negative side effects from this, such as a genuine memory leak):

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

• A recompilation of PHP configured to use the native mysqlnd driver (part of PHP since v5.3.0, but not configured as the default value before PHP v5.4.0) instead of libmysql:

   ./configure --with-mysqli=mysqlnd 

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