Out of memory error

[monk.e.boy]

Hi,

  I run this:

        $sqlxx = <<<EOT
                SELECT
                        FORMAT(seconds_to_download,2) as secs
                FROM page
EOT;

        $db = $this->getAdapter();
        return $db->fetchAll( $sqlxx );

And I get:

Fatal error: Allowed memory size of 67108864 bytes exhausted (tried to allocate 1431655772 bytes) in /usr/local/lib/php/Zend/Db/Statement/Mysqli.php on line 254

Copying this sql into MySQLyog works fine (16 rows returned). No errors.

Tests replacing the SQL::

This WORKS:

  SELECT
    seconds_to_download as secs
  FROM page

This WORKS:

  SELECT
    FORMAT(33.333333,2) as secs
  FROM page

This WORKS:

  SELECT
    FORMAT(NULL,2) as secs
  FROM page

The column 'seconds_to_download' is a FLOAT that can contain NULLs. Anyone got any idea what is going on? I am running MySQL 5.0.45

Thanks,

monk.e.boy

[monk.e.boy]

Hi,

  Has anyone else got this error?

  Does anyone know where I should look to fix it? Is it a bug in the DB Adapter?

Thankx

monk.e.boy

monk.e.boy wrote:

Hi,

  I run this:

        $sqlxx = <<<EOT
                SELECT
                        FORMAT(seconds_to_download,2) as secs
                FROM page
EOT;

        $db = $this->getAdapter();
        return $db->fetchAll( $sqlxx );

And I get:

Fatal error: Allowed memory size of 67108864 bytes exhausted (tried to allocate 1431655772 bytes) in /usr/local/lib/php/Zend/Db/Statement/Mysqli.php on line 254

[Alex Zinchenko]

Why you using getAdapter() method? Try to use $db->fetchAll($sqlxx);
instead.

Alex.

----- Original Message -----
From: "monk.e.boy" <john.glazebrook@...>
To: <fw-db@...>
Sent: Wednesday, August 27, 2008 1:56 AM
Subject: Re: [fw-db] Out of memory error

>
> Hi,
>
>  Has anyone else got this error?
>
>  Does anyone know where I should look to fix it? Is it a bug in the DB
> Adapter?
>
> Thankx
>
> monk.e.boy
>
>
>
> monk.e.boy wrote:
>>
>> Hi,
>>
>>   I run this:
>>
>> $sqlxx = <<<EOT
>> SELECT
>> FORMAT(seconds_to_download,2) as secs
>> FROM page
>> EOT;
>>
>> $db = $this->getAdapter();
>> return $db->fetchAll( $sqlxx );
>>
>> And I get:
>>
>> Fatal error: Allowed memory size of 67108864 bytes exhausted (tried to
>> allocate 1431655772 bytes) in
>> /usr/local/lib/php/Zend/Db/Statement/Mysqli.php on line 254
>>
>>
>
> --
> View this message in context:
> http://www.nabble.com/Out-of-memory-error-tp19108319p19163705.html
> Sent from the Zend DB mailing list archive at Nabble.com.
>
>
> __________ Èíôîðìàöèÿ îò ESET NOD32 Antivirus, âåðñèÿ áàçû äàííûõ ñèãíàòóð
> âèðóñîâ 3389 (20080826) __________
>
> Ñîîáùåíèå ïðîâåðåíî ïðîãðàììîé ESET NOD32 Antivirus.
>
> http://www.esetnod32.ru
>
>

[Bill Karwin]

monk.e.boy wrote:

        $sqlxx = <<<EOT
                SELECT
                        FORMAT(seconds_to_download,2) as secs
                FROM page
EOT;
Fatal error: Allowed memory size of 67108864 bytes exhausted (tried to allocate 1431655772 bytes) in /usr/local/lib/php/Zend/Db/Statement/Mysqli.php on line 254

The Mysqli adapter has a known problem with the LONGTEXT data type (http://framework.zend.com/issues/browse/ZF-1498).  This problem is unresolved, but the workaround is to use TEXT, MEDIUMTEXT or VARCHAR, because the problem does not occur with other data types.

In MySQL, FORMAT() called on a FLOAT column returns a field of type LONGTEXT.  You can verify this by capturing the output in a temporary table and get metadata for the resulting temp table:

CREATE TEMPORARY TABLE tempresult
  SELECT FORMAT(seconds_to_download,2) AS secs FROM page;
DESCRIBE tempresult;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| secs  | longtext | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
1 row in set (0.01 sec)

You can wrap your call to FORMAT() inside CAST() to change its return value to an ordinary string data type:

CREATE TEMPORARY TABLE tempresult
  SELECT CAST(FORMAT(seconds_to_download,2) AS CHAR(20)) AS secs FROM page;
DESCRIBE tempresult;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| secs  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
1 row in set (0.00 sec)

Oddly, even though I specified CHAR(20), it produced a VARCHAR(20).

Regards,
Bill Karwin

[monk.e.boy]

Thanks Bill :-)  that is a strange little bug :-/

I blogged it for google, it may save someone the same frustrations ;-)

http://teethgrinder.co.uk/perm.php?a=Zend-Framework-MySQL-Out-Of-Memory-Error