PDO失败，记录过多，缓冲了查询 [英] PDO failing with too many records, buffered queries
问题描述
该脚本昨天运行良好，但是今天，由于我最初从表中选择的表中现在大约有150,000条记录，因此无法说我正在从null()获取.据我所知，这是因为我有太多记录.
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.
因此，我通过在初始查询(1000)和以下行中添加了一个限制来最终纠正了该问题:
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);
这在我运行脚本的前15次左右有效，但是现在失败了，没有插入任何内容.我收到来自buffered_query的错误消息，该消息为假，说它可以在另一个正在进行的查询中运行一个缓冲的查询.
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.
我以前从未使用过此方法，更不用说出现此错误了.我确信我的代码可以为此进行优化(在使用PDO时我也很环保).
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";
}
推荐答案
当您使用无缓冲查询时，这意味着您的结果集正在从MySQL服务器流式传输.因此，运行(无缓冲)查询的连接一直很忙，直到您读取查询的最后一行.在您的情况下，连接为$MysqlConn.
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.
(缓冲查询将整个结果集插入到php程序的RAM中并释放连接.当整个结果集不适合RAM时，您可以使用无缓冲查询).
(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).
使用完无缓冲查询后，应显式关闭它们.因此添加closeCursor()调用.这样.
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.
这篇关于PDO失败，记录过多，缓冲了查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
