php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #32013 MySQLi bind_result causes fatal error: memory limit reached
Submitted: 2005-02-17 19:32 UTC Modified: 2005-04-27 20:07 UTC
From: mhe at ltcgroup dot de Assigned: georg
Status: Closed Package: MySQLi related
PHP Version: 5CVS-2005-02-28 OS: *
Private report: No CVE-ID:
 [2005-02-17 19:32 UTC] mhe at ltcgroup dot de
Description:
------------
submit a prepared query to mysqli ext using method prepare().

if i use bind_result to bind a column to a variable php crashes with fatal error: memory limit, if the column in database is mediumtext. if you alter this to text, everything works fine.

using mysqld 4.1.10

--output: php5 -v
micronium:~/phpn# php5 -v
PHP 5.0.3-1.dotdeb.0 (cli) (built: Dec 16 2004 13:08:20)
Copyright (c) 1997-2004 The PHP Group
Zend Engine v2.0.3, Copyright (c) 1998-2004 Zend Technologies

-- mysql:
CREATE TABLE tt (
  ID_ITEM INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  TITLE VARCHAR(255) NULL,
  DESCRIPTION MEDIUMTEXT NULL,
  PRIMARY KEY(ID_ITEM)
);

-- php:
$query = "
 select
  DESCRIPTION
 from
  tt
";
$stmt = $db->prepare($query);
$stmt->execute();

$stmt->bind_result($DESCRIPTION); //-- this is line 63

-- output:

Fatal error: Allowed memory size of 8388608 bytes exhausted (tried to allocate 16777216 bytes) in /root/phpn/run.php on line 63
Allowed memory size of 8388608 bytes exhausted (tried to allocate 256 bytes)

-- mysql:

ALTER TABLE `tt` CHANGE `DESCRIPTION` `DESCRIPTION` TEXT

-- php:
$query = "
 select
  DESCRIPTION
 from
  tt
";
$stmt = $db->prepare($query);
$stmt->execute();

$stmt->bind_result($ID_ITEM); //-- this is line 63

-- output:
:::  everything is ok, no crahs, can go on


Reproduce code:
---------------
try to bind a MEDIUM text column to a variable, and php will crash ..



Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2005-02-17 20:10 UTC] mhe at ltcgroup dot de
MEDIUMTEXT ~ 2^24 bytes = 16777216 bytes, which php tries to  allocate

php dies, memory limit reached ..

mh, lets play with this. so what happens, if i use TEXT, which seems to work ..

so i use memory_get_usage() befor and after ->bind_result($ID_ITEM)

before: 48120
after: 113848

dif: 65728

2^16+2 = 65538, which is the size of TEXT .. *ouch :-?

..

what happens, if i bind 2 variables to a TEXT ..

"select DESCRIPTION as DD, DESCRIPTION as DD2, from tt"

$stmt->bind_result($ID_ITEM, $IDD);

before: 48424
after: 179640

dif: (179640 - 48424) / 2 = 65608 ~ 2 * 2 ^16

! notice: table was empty
 [2005-02-17 20:17 UTC] mhe at ltcgroup dot de
perhaps it is a mysql client lib issue, i downt know ..

php side: increasing memory limit isnt a solution
mysql side: alter table isnt a solution
 [2005-02-25 14:35 UTC] sniper@php.net
Please try using this CVS snapshot:

  http://snaps.php.net/php5-latest.tar.gz
 
For Windows:
 
  http://snaps.php.net/win32/php5-win32-latest.zip


 [2005-02-25 15:52 UTC] mhe at ltcgroup dot de
i wasnt able to test this under debian woody, but i downloaded the windows latest snapshot.

now the memory limit is reached using fetch(),
I added a "normal" query, so that you see, that the select using mysqli is possible on mediumtext,

::
	$query = "
	 select
	  DESCRIPTION,
	  DESCRIPTION AS DD
	 from
	  tt
	 order by ID_ITEM
	";

	echo "init	" . getMemUsage() . "\n";

	$result = $db->query($query);
	echo "query	" . getMemUsage() . "\n";

	while($row = $result->fetch_assoc())
	{
		echo " fetch	" . getMemUsage() . "\n";
	}

	$stmt = $db->prepare($query);
	echo "prepare	" . getMemUsage() . "\n";

	$stmt->execute();
	echo " exec	" . getMemUsage() . "\n";

	$stmt->bind_result($DESCRIPTION, $DD);
	echo " bind	" . getMemUsage() . "\n";

	$stmt->fetch();
	echo " fetch	" . getMemUsage() . "\n";

	$stmt->fetch();
	echo " fetch	" . getMemUsage() . "\n";

::

using function getMemUsage() from http://php.net/memory_get_usage

these are the results:

debain woody 5.0.3:
init    48744
query   48864
 fetch  49120
 fetch  49296
prepare 49392
 exec   49392

Fatal error: Allowed memory size of 8388608 bytes exhausted (tried to allocate 16777216 bytes) in /root/mysqli_prepared.php on line 73
Allowed memory size of 8388608 bytes exhausted (tried to allocate 256 bytes)

line 73 ~ bind_result(), this is how 5.0.3 currently works,

::

these are the results, using version "latest win32:
(increased memory limit, for testing)

init	 7.772 K
query	 7.812 K
 fetch	 7.812 K
 fetch	 7.812 K
prepare	 7.824 K
 exec	 7.832 K
 bind	 7.844 K
 fetch	 40.672 K
 fetch	 40.672 K

you can see, that the memory usage explode after the first fetch(), bin_result() seems to work.

::

there were 2 items in database,

		DROP TABLE tt;
		CREATE TABLE tt (
		  ID_ITEM INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
		  TITLE VARCHAR(255) NULL,
		  DESCRIPTION MEDIUMTEXT NULL,
		  PRIMARY KEY(ID_ITEM)
		);
		INSERT INTO tt (ID_ITEM, TITLE, DESCRIPTION) VALUES (1, 'fghfjkgfd', 'first description');
		INSERT INTO tt (ID_ITEM, TITLE, DESCRIPTION) VALUES (2, 'second', 'second description');

::
 [2005-04-27 20:07 UTC] andrey@php.net
This bug has been fixed in CVS.

Snapshots of the sources are packaged every three hours; this change
will be in the next snapshot. You can grab the snapshot at
http://snaps.php.net/.
 
Thank you for the report, and for helping us make PHP better.

Now should behave a lot better but a bit slower because the whole result set has to be inspected by the MySQL server to find the max length. To lower the usage you have to call store_result() otherwise ext/mysqli will still try to allocated 16Megs for a column that does not have more than 30 chars (for example). For big result sets better don't use binding but the simple API.
 [2010-11-30 07:53 UTC] riankruger at gmail dot com
WORKAROUND: 

SELECT SUBSTRING(LONG_COLUMN, 1, 512) FROM RUBBISH_TABLE;

where the table looks like:

CREATE RUBBISH_TABLE (
LONG_COLUMN TEXT
);

It seems that due to the substring, mysqli will actually check the size of the string before allocating memory. 512 can be any arbitrary length and does not complain or pad the string if the actual value is shorter.

I know this bug is resolved but sometimes one is forced to work on old systems.

Good Luck
 
PHP Copyright © 2001-2014 The PHP Group
All rights reserved.
Last updated: Wed Apr 23 18:01:55 2014 UTC