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

Reply via email to