Edit report at http://bugs.php.net/bug.php?id=32013&edit=1
ID: 32013
Comment by: riankruger at gmail dot com
Reported by: mhe at ltcgroup dot de
Summary: MySQLi bind_result causes fatal error: memory limit
reached
Status: Closed
Type: Bug
Package: MySQLi related
Operating System: *
PHP Version: 5CVS-2005-02-28
Assigned To: georg
Block user comment: N
Private report: N
New Comment:
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
Previous Comments:
------------------------------------------------------------------------
[2005-04-27 20:07:35] [email protected]
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.
------------------------------------------------------------------------
[2005-02-25 15:52:51] 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-02-25 14:35:17] [email protected]
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-17 20:17:12] 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-17 20:10:27] 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
------------------------------------------------------------------------
The remainder of the comments for this report are too long. To view
the rest of the comments, please view the bug report online at
http://bugs.php.net/bug.php?id=32013
--
Edit this bug report at http://bugs.php.net/bug.php?id=32013&edit=1
