Re: Out of memory error

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