Allowed memory size of 67108864 bytes exhausted

Question

I must have read all the similar posts here but still cant figure out why this is happening.

My code:

$stmt = $this->db->stmt_init();
        $stmt->prepare("SELECT Table1.id,Name,type,text,fname,lname FROM Table1, Table2 WHERE Table1.email = Table2.email AND type='text' AND Table1.Id=?");
        $stmt->bind_param("i", $id);
        $stmt->bind_result($legTxtId,$legTxtName, $legTxtType, $legTxtText, $legTxtFname, $legTxtLname);
        $stmt->execute();
        $results = array();
        while($stmt->fetch())
        {
            $results[] = array(
                    'legTxtId' => $legTxtId , 'legTxtName' => $legTxtName , 'legTxtType' => $legTxtType , 
                    'legTxtText' => $legTxtText , 'legTxtFname' => $legTxtFname ,
                    'legTxtLname' => $legTxtLname );
        }
        $stmt->close();
        return $results;

Now I am using the exact same code in a different function that is called before this one and it works fine even though it returns one more field.

This one in particular only returns 1 row with just plain short text (no photos or anything) so it should not fail cause its definitely less than 64M.

Can anyone see what the problem is?

Answer 1

Then why you use while loop for one record?

Answer 2

As discussed in the other question it seems the two solutions are:

1) Switch to the mysqlnd connector as this doesn't show the same bug.

If you're using using Yum to install PHP (e.g. on an Amazon ec2 server) then you can achieve that by changing your setup of your LAMP stack from this:

sudo yum install php-mysql php php-xml php-mcrypt php-mbstring php-cli mysql httpd 

to:

sudo yum install php-mysqlnd php php-xml php-mcrypt php-mbstring php-cli mysql httpd 

2) Use either store_result or use_result which also don't show the massive memory allocation issue.

Switching to mysqlnd is probably a better long term solution as it is general better written than the existing php-mysql connector (e.g. results aren't duplicated in MySQL memory before being copied to PHP memory) and is the default connector from PHP 5.4.0 onwards.

Answer 3

Moving the data to a local buffer will maybe help:

// ...
$stmt->prepare(...);
$stmt->bind_param(...);

$stmt->execute();
$stmt->store_result();

$stmt->bind_result(...);
// ...