PHP memory limit with SQL result

Question

I'm facing a problem that shouldn't be hard at all to solve, but somehow I can't. Here is scenario:

I want to fetch results from SQL statement into PHP array, seems simple right?

The problem is that it gives me error about memory_limit (Fatal error: Out of memory (allocated 408944640) (tried to allocate 805306376 bytes)).

Here is my code that is working with statements that gives less results (statement that I need to use contains +/- 4000 records).

$connect = @new mysqli($host, $db_user, $db_password, $db_name);

if($connect->connect_errno!=0)
{
    echo "Error: ".$connect->connect_errno."Why: ". $connect->connect_error;
}
else{
    $query = "SELECT meta_value, post_date 
FROM 
  wp_postmeta
  INNER JOIN 
  wp_posts
  ON wp_posts.ID=wp_postmeta.post_id 
where wp_posts.post_type ='shop_order' AND wp_postmeta.meta_key = '_order_total'";
    $sql_query=mysqli_query($connect,$query);
    if ($sql_query->num_rows > 0){
    while(($row = mysqli_fetch_assoc($sql_query))!== false){
        $result[] = $row;
    }} else {
        echo "0 results";
    }
}

echo json_encode($result);

Not really sure why it's uses 0.8 GB of memory (seems pretty much for a simple statement. However to give it a try I have changed memory_limit in php.ini file to: memory_limit=8192M. But it didn't help at all (checked if memory_limit appiled calling php_info() function).

Thank you guys!

Answer 1

Do not hold full result tables in memory or your server will be vulnerable to DOS attacks easily. The server has to be able to reply to many requests simultaneously. Process only one row at once and release the allocated memory.

You should output the JSON chunks comma-seperated directly.

echo '[';

$sep = '';
while(($row = mysqli_fetch_assoc($sql_query))!== false){
    echo $sep . json_encode($row);
    $sep = ',';
}

echo ']';

Alternatively you could cache it in a temporary file.

On newer MySql versions you could try:

SELECT JSON_OBJECT('meta_value', meta_value, 'post_date', post_date)
FROM wp_postmeta 
INNER JOIN wp_posts ON wp_posts.ID=wp_postmeta.post_id 
WHERE wp_posts.post_type ='shop_order' 
AND wp_postmeta.meta_key = '_order_total' 
INTO OUTFILE '/tmp/mysql-temp-GENERATED_UUID';

This generates a file with JSON lines, but not comma-seperated. As documentated on mysqlserverteam.com the aggretage function JSON_ARRAYAGG should fix that. (My installation unfortunately has third party repository conflicts, thus I cannot test it.)

SELECT JSON_ARRAGG(JSON_OBJECT('meta_value', meta_value, 'post_date', post_date))
FROM wp_postmeta 
INNER JOIN wp_posts ON wp_posts.ID=wp_postmeta.post_id 
WHERE wp_posts.post_type ='shop_order' 
AND wp_postmeta.meta_key = '_order_total' 
INTO OUTFILE '/tmp/mysql-temp-GENERATED_UUID';

If JSON_ARRAYAGG causes problems for some reason, a workaround (at least on unix-like systems) would be:

<?php
  $guid = bin2hex(openssl_random_pseudo_bytes(16));
  $tmp_filename = "/tmp/mysql-json-{$guid}.json";
  /* let MySql generate the tempfile, then */

  passthru('sed \'1 s/^/[/; $! s/$/,/ ; $ s/$/]/\' ' . $tmp_filename);
?>