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] and...@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. ------------------------------------------------------------------------ [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] sni...@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-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