PDO failing with too many records, buffered queries

Question

This script was running fine yesterday, but today, since there are now around 150,000 records in the table which I initially select from, it failed saying I was fetching from null(). As far as I could tell it's because I had too many records.

So, I rectified it finally by adding a limit to the initial query (1000) and this line:

$MysqlConn->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);

This worked the first 15 or so times I ran the script but now it's failing and no insertions are being made. I'm getting an error from the buffered_query being false saying that it can run a buffered query while another is in progress.

I've never even used this before, let alone gotten this error. I'm sure my code could be optimmized for this (I'm also very green when it comes to using PDO).

Maybe someone can offer some insight:

$MysqlConn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$MysqlConn->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);



/*Delete records that were made placements more than 5 days ago*/
$deleteOld = '
        DELETE 
        FROM Order_status 
        WHERE date_updated < current_date() - INTERVAL 5 DAY';
try{
$delete = $MysqlConn->prepare($deleteOld);
$result = $delete->execute();
$count = $delete->rowcount();
echo "Records Deleted: " . $count . "\n";
}
catch(PDOException $ex)
{
    echo "QUERY FAILED!: " .$ex->getMessage();
}

/*Placement process for orders already marked as Shipped*/
//PDO statement to select from order_status
$ordStatSql = 'SELECT order_id, order_status, is_placement, date_updated 
                FROM order_status
                WHERE order_status = "S"
                AND date_updated IS NULL
                order by order_id desc
                LIMIT 1000';
try{
$ordStat = $MysqlConn->prepare($ordStatSql);
$result = $ordStat->execute();
}
catch(PDOException $ex)
{
    echo "QUERY FAILED!: " .$ex->getMessage();
}

$order_ids = [];
while ($row = $ordStat->fetch(PDO::FETCH_ASSOC)) {
    $order_ids[] = $row['order_id'];
}

if (count($order_ids) > 0) {

    $placeholders = implode(',', array_fill(0, count($order_ids), '?'));
    $detailStatCheck = "
        SELECT 
             invnoc as INVOICE,
             fstatc as STATUS,
             cstnoc AS DEALER,
             framec AS FRAME,
             covr1c AS COVER,
             colr1c AS COLOR ,
             extd2d AS SHIPDATE,
             orqtyc AS QUANTITY
        FROM GPORPCFL
        WHERE invnoc IN ($placeholders)
    ";

    try {
        $detailCheck = $DB2conn->prepare($detailStatCheck);
        $detailRslt = $detailCheck->execute($order_ids);
        $count2 = $detailCheck->fetch();
        print_r($order_ids);
        print_r($count2);
    } catch(PDOException $ex) {
        echo "QUERY FAILED!: " .$ex->getMessage();
    }

    //Create prepared INSERT statement
    $insertPlacement = "
        INSERT ignore INTO placements_new (sku_id, group_id, dealer_id, start_date, expire_date, locations, order_num)
        SELECT 
            id, 
            sku_group_id, 
            :DEALER, 
            DATE_ADD(DATE_FORMAT(CONVERT(:SHIPDATE, CHAR(20)), '%Y-%m-%d'),INTERVAL 7 DAY) as start_date,
            DATE_ADD(DATE_FORMAT(CONVERT(:SHIPDATE, CHAR(20)), '%Y-%m-%d'),INTERVAL 127 DAY) as expire_date, 
            :QUANTITY,
            :INVOICE  
        FROM skus s  
        WHERE  s.frame=:FRAME AND s.cover1=:COVER AND s.color1=:COLOR
    ";

    //create update statement for necessary constraints
    $updatePlacement = "
        UPDATE placements_new 
        SET expire_date = DATE_ADD(DATE_FORMAT(CONVERT(current_date(), CHAR(20)), '%Y-%m-%d'),INTERVAL 127 DAY)
    ";

    //perpare query to check for existing records that are expired
    $expiredCheck = "
        SELECT 
            sku_id,
            dealer_id,
            expire_date
        FROM placements_new p
            INNER JOIN skus s
                ON p.sku_id = s.id
        WHERE p.dealer_id = :DEALER
            AND   s.frame = :FRAME
            AND   s.cover1 = :COVER
            AND   s.color1 = :COLOR
            AND   p.order_num = :INVOICE
            AND   p.expire_date <= current_date()
    ";

    //perpare query to check for existing records that are expired
    $validCheck = "
        SELECT 
            sku_id,
            dealer_id,
            expire_date
        FROM placements_new p
            INNER JOIN skus s
                ON p.sku_id = s.id
        WHERE p.dealer_id = :DEALER
            AND   s.frame = :FRAME
            AND   s.cover1 = :COVER
            AND   s.color1 = :COLOR
            AND   p.order_num = :INVOICE
            AND   p.expire_date > current_date()
    ";

    $updateShipped = '
        UPDATE order_status S
        INNER JOIN placements_new N 
        ON S.order_id = N.order_num
        set S.date_updated = current_date();
    ';


    while ($row2 = $detailCheck->fetch(PDO::FETCH_ASSOC)) {

        $values = [
            ":DEALER" => $row2["DEALER"],
            ":SHIPDATE" => $row2["SHIPDATE"],
            ":QUANTITY" => $row2["QUANTITY"],
            ":INVOICE" => $row2["INVOICE"],
            ":FRAME" => $row2["FRAME"],
            ":COVER" => $row2["COVER"],
            ":COLOR" => $row2["COLOR"],
        ];

        $values2 = [
            ":DEALER" => $row2["DEALER"],
            ":FRAME" => $row2["FRAME"],
            ":COVER" => $row2["COVER"],
            ":COLOR" => $row2["COLOR"],
            ":INVOICE" => $row2["INVOICE"],

        ];

        try{
            //Array will contain records that are expired
            $checkExisting = $MysqlConn->prepare($expiredCheck);
            $existingRslt = $checkExisting->execute($values2);
            $count3 = $checkExisting->fetch(PDO::FETCH_ASSOC);

            //Array will contain records that are valid
            $checkExistingValid = $MysqlConn->prepare($validCheck);
            $existingVldRslt = $checkExistingValid->execute($values2);
            $count4 = $checkExistingValid->fetch(PDO::FETCH_ASSOC);

            //print_r($count3);

        }catch(PDOException $ex){
                echo "QUERY FAILED!!!: " . $ex->getMessage();
        }



            // IF records do not exist, or records exist and today is after expiration date
            if(empty($count3) && empty($count4)){
                for($i=0; $i<$row2["QUANTITY"]; $i++) {  
                    try{
                        $insert = $MysqlConn->prepare($insertPlacement);
                        $insertRslt = $insert->execute($values);
                    }catch(PDOException $ex){
                        echo "QUERY FAILED!!!: " . $ex->getMessage();
                    }

                }
            }elseif(!empty($count3)){
                for($i=0; $i<$row2['QUANTITY']; $i++){
                    try{
                        $insert = $MysqlConn->prepare($insertPlacement);
                        $insertRslt = $insert->execute($values);
                    }catch(PDOException $ex){
                        echo "QUERY FAILED!!!: " . $ex->getMessage();
                    }
                }
            }elseif(!empty($count4)){
                for($i=0; $i<$row2['QUANTITY']; $i++){
                    try{
                        $update = $MysqlConn->prepare($updatePlacement);
                        $updateRslt = $update->execute($values);
                    }catch(PDOException $ex){
                        echo "QUERY FAILED!!!: " . $ex->getMessage();
                    }
                }
            }else{
                die("No action taken");
            }
         }


                try{
                $updateStatus = $MysqlConn->prepare($updateShipped);
                $statUpdateRslt = $updateStatus->execute();
                $count = $updateStatus->rowcount();
                }
                catch(PDOException $ex)
                {
                    echo "QUERY FAILED!: " .$ex->getMessage();
                }
            echo "Records Updated: " . $count . "\n";


}

Answer 1

When you use an unbuffered query, that means your result set is being streamed in from the MySQL server. So, the connection on which the (unbuffered) query runs is busy until you read the last row of the query. In your case the connection is $MysqlConn.

(A buffered query slurps the entire resultset into your php program's RAM and frees up the connection. You use unbuffered queries when your whole result set doesn't fit in RAM).

Unbuffered queries should be closed explicitly when you're done with them. So add a closeCursor() call. Like this.

while ($row = $ordStat->fetch(PDO::FETCH_ASSOC)) {
    $order_ids[] = $row['order_id'];
}
$ordStat->closeCursor();

There's no harm in closing buffered queries too. It's a good habit.