mysql - DBD::mysql::st fetchrow_array 失败:fetch() 没有执行错误？

Question

运行此 perl 脚本时出现此错误

DBD::mysql::st fetchrow_array 失败:fetch() 未在 ai_light_warehouse.pl 第 174 行执行DBD::mysql::st fetchrow_array 失败:fetch() 未在 ai_light_warehouse.pl 第 187 行执行

我似乎无法弄清楚为什么会发生这种情况，而且我也不熟悉 perl。试图弄清楚，但几个小时后遇到了障碍，我想我应该联系 stackoverflow。

 # reset warehouse table
    my $reset_handle = $dbh->prepare(qq{
      DELETE FROM ai_light_warehouse
    });

my $select_handle = $dbh->prepare(qq{
    SELECT DISTINCT PROD.PRODUCT_ID      AS PRODUCT_ID,
          PROD.PRODUCT_NAME            AS PRODUCT_NAME,
          PROD.BRAND_NAME              AS BRAND_NAME,
          PC.DESCRIPTION               AS COLLECTION,
          PFA.DESCRIPTION        AS PRODUCT_FEATURE_ID,
          PROD.DEFAULT_SEQUENCE_NUM    AS POPULARITY,
          PA.PRODUCT_ID                AS VIRTUAL_PRODUCT_ID,
          PROD.IS_VIRTUAL              AS IS_VIRTUAL,
          "N"                          AS ON_SALE,
          CASE 
            WHEN PROD.TOTAL_TIMES_VIEWED IS NULL THEN 0
            ELSE PROD.TOTAL_TIMES_VIEWED
          END   AS TOTAL_TIMES_VIEWED,
          CASE 
            WHEN PROD.IS_CLEARANCE IS NULL THEN "N"
            ELSE PROD.IS_CLEARANCE
          END   AS IS_CLEARANCE,
          PROD.internal_Name
  FROM    PRODUCT PROD
  INNER JOIN Ai_Light_category AS PC
  ON      (PROD.PRODUCT_ID = PC.PRODUCT_ID)
  LEFT OUTER JOIN PRODUCT_ASSOC PA
  ON      PROD.PRODUCT_ID = PA.PRODUCT_ID_TO
  INNER JOIN AI_LIGHT_FEATURES PFA
  ON      PA.PRODUCT_ID_TO = PFA.PRODUCT_ID
  WHERE (PA.PRODUCT_ASSOC_TYPE_ID    = "PRODUCT_VARIANT"
      AND PFA.PRODUCT_FEATURE_TYPE_ID = "COLOR"
      AND PROD.PRODUCT_TYPE_ID IN ("LIGHTING")
      AND (PROD.IS_LIVE = "Y"
      OR PROD.IS_LIVE IS NULL)
      AND ((PROD.SALES_DISCONTINUATION_DATE > NOW()
       OR PROD.SALES_DISCONTINUATION_DATE IS NULL)))
  GROUP BY PA.PRODUCT_ID,
          PFA.PRODUCT_FEATURE_ID
  ORDER BY PROD.DEFAULT_SEQUENCE_NUM DESC,
          UPPER(PROD.PRODUCT_NAME) ASC
});
# retrieve basic product information
my $insert_handle = $dbh->prepare(qq{
  INSERT INTO ai_light_warehouse
          (
                  PRODUCT_ID,
                  PRODUCT_NAME,
                  BRAND_NAME,
                  COLLECTION,
                  PRODUCT_FEATURE_ID,
                  MIN_PRICE,
                  MAX_PRICE,
                  POPULARITY,
                  VIRTUAL_PRODUCT_ID,
                  IS_VIRTUAL,
                  TOTAL_TIMES_VIEWED,
                  IS_CLEARANCE,
                  INTERNAL_NAME,
                  ON_SALE,
                  SELECTOR
          )
          VALUES
          (
                  ?,
                  ?,
                  ?,
                  ?,
                  ?,
                  ?,
                  ?,
                  ?,
                  ?,
                  ?,
                  ?,
                  ?,
                  ?,
                  ?,
                  ?
          )

});

# save default and list prices
my $price_handle = $dbh->prepare(qq{
 UPDATE AI_LIGHT_WAREHOUSE W,
        PRODUCT_PRICE PP1,
        PRODUCT_PRICE PP2
 SET    W.PRICE = PP1.PRICE,
        W.LIST_PRICE    = PP2.PRICE
 WHERE  W.PRODUCT_ID    = PP1.PRODUCT_ID
    AND W.PRODUCT_ID    = PP2.PRODUCT_ID
    AND PP1.PRODUCT_PRICE_TYPE_ID = "DEFAULT_PRICE"
    AND PP2.PRODUCT_PRICE_TYPE_ID = "LIST_PRICE"
});

my $selectWatt = $dbh->prepare(qq{
    SELECT PF.PRODUCT_FEATURE_ID, PF.DESCRIPTION FROM AI_LIGHT_FEATURES PF WHERE PF.PRODUCT_FEATURE_TYPE_ID = "WATT_CAT" AND PF.PRODUCT_ID = ? limit 1
});

my $watt_handle = $dbh->prepare(qq{
    UPDATE AI_LIGHT_WAREHOUSE SET WATT_CAT = ?, WATT_CAT_DESCRIPTION = ? WHERE PRODUCT_ID = ?
});

my $selectHEIGHT = $dbh->prepare(qq{
    SELECT PF.PRODUCT_FEATURE_ID, PF.DESCRIPTION FROM AI_LIGHT_FEATURES PF WHERE PF.PRODUCT_FEATURE_TYPE_ID = "HEIGHT_CAT" AND PF.PRODUCT_ID = ? limit 1
});

my $HEIGHT_handle = $dbh->prepare(qq{
    UPDATE AI_LIGHT_WAREHOUSE SET HEIGHT_CAT = ?, HEIGHT_CAT_DESCRIPTION = ? WHERE PRODUCT_ID = ?
});


my $selectType = $dbh->prepare(qq{
    SELECT PF.PRODUCT_FEATURE_ID, PF.DESCRIPTION FROM AI_LIGHT_FEATURES PF WHERE PF.PRODUCT_FEATURE_TYPE_ID = "TYPE" AND PF.PRODUCT_ID = ? LIMIT 1
});

my $type_handle = $dbh->prepare(qq{
    UPDATE AI_LIGHT_WAREHOUSE SET TYPE_CAT = ?, TYPE_CAT_DESCRIPTION = ? WHERE PRODUCT_ID = ?
});

my $selectMat = $dbh->prepare(qq{
    SELECT PF.PRODUCT_FEATURE_ID, PF.DESCRIPTION FROM AI_LIGHT_FEATURES PF WHERE PF.PRODUCT_FEATURE_TYPE_ID = "MATERIAL_CAT" AND PF.PRODUCT_ID = ? LIMIT 1
});

my $mat_handle = $dbh->prepare(qq{
    UPDATE AI_LIGHT_WAREHOUSE SET MAT_CAT = ?, MAT_CAT_DESCRIPTION = ? WHERE PRODUCT_ID = ?
});

my $select_Price = $dbh->prepare(qq{
    SELECT PRICE FROM PRODUCT_PRICE WHERE PRODUCT_ID = ? AND PRODUCT_PRICE_TYPE_ID = "DEFAULT_PRICE"
});

my $variants_handle = $dbh->prepare(qq{
    SELECT PA.PRODUCT_ID_TO FROM PRODUCT_ASSOC PA INNER JOIN PRODUCT PROD ON PROD.PRODUCT_ID = PA.PRODUCT_ID_TO WHERE PA.PRODUCT_ID = ? AND (PROD.SALES_DISCONTINUATION_DATE IS NULL OR PROD.SALES_DISCONTINUATION_DATE > NOW())
});

my $rating_handle = $dbh->prepare(qq{
        SELECT PRODUCT_RATING FROM PRODUCT WHERE PRODUCT_ID = ?
    }); 

die "Couldn't prepare queries; aborting"
  unless defined $reset_handle
    && defined $insert_handle
    && defined $price_handle;

#------------------------------------------------------------
my %virtualMinPriceMap = ();
my %virtualMaxPriceMap = ();

my $success = 1;
$success &&= $reset_handle->execute();
$success &&= $select_handle->execute();


while (my($PRODUCT_ID, $PRODUCT_NAME, $BRAND_NAME, $COLLECTION, $PRODUCT_FEATURE_ID, $POPULARITY, $VIRTUAL_PRODUCT_ID, $IS_VIRTUAL, $ON_SALE, $TOTAL_TIMES_VIEWED, $IS_CLEARANCE, $internalName) = $select_handle->fetchrow_array())
{
    $success &&= $rating_handle->execute($VIRTUAL_PRODUCT_ID);
    my $rating = $rating_handle->fetchrow_array();
    $PRODUCT_FEATURE_ID =~ s/^\s+//;
    $PRODUCT_FEATURE_ID =~ s/\s+$//;
    $PRODUCT_FEATURE_ID =~ s/ /-/g;
    $PRODUCT_FEATURE_ID =~ s/--/-/g;
    $PRODUCT_FEATURE_ID =~ s/[^0-9a-zA-Z_-]//g;
    $PRODUCT_FEATURE_ID =~ tr/A-Z/a-z/;
    my $selector = $VIRTUAL_PRODUCT_ID . $PRODUCT_FEATURE_ID;
    my $MAX_PRICE = -1;
    my $MIN_PRICE = -1;
    if (!exists($virtualMinPriceMap{$VIRTUAL_PRODUCT_ID}))
    {
        $success &&= $variants_handle->execute($VIRTUAL_PRODUCT_ID);
        while (my($variantId) = $variants_handle->fetchrow_array())
        {
            $success &&= $select_Price->execute($variantId);
            while (my($priceValue) = $select_Price->fetchrow_array())
            {
                if($MIN_PRICE == -1 || $MIN_PRICE > $priceValue)
                {
                    $MIN_PRICE = $priceValue;
                }
                if($MAX_PRICE < $priceValue)
                {
                    $MAX_PRICE = $priceValue;
                }
            }
        }
        $virtualMaxPriceMap{$VIRTUAL_PRODUCT_ID} = $MAX_PRICE;
        $virtualMinPriceMap{$VIRTUAL_PRODUCT_ID} = $MIN_PRICE;
    }
    else
    {
        $MAX_PRICE = $virtualMaxPriceMap{$VIRTUAL_PRODUCT_ID};
        $MIN_PRICE = $virtualMinPriceMap{$VIRTUAL_PRODUCT_ID};
    }
    $success &&= $insert_handle->execute($PRODUCT_ID, $PRODUCT_NAME, $BRAND_NAME, $COLLECTION, $PRODUCT_FEATURE_ID, $MIN_PRICE, $MAX_PRICE, $POPULARITY, $VIRTUAL_PRODUCT_ID, $IS_VIRTUAL, $TOTAL_TIMES_VIEWED, $IS_CLEARANCE, $internalName, $ON_SALE, $selector);

    $selectWatt->execute($PRODUCT_ID);
    my ($watt_ID, $watt_description) = $selectWatt->fetchrow_array();
    if(defined ($watt_ID))
    {
        $watt_handle->execute($watt_ID, $watt_description, $PRODUCT_ID);
    }

    $selectHEIGHT->execute($PRODUCT_ID);
    my ($HEIGHT_ID, $height_description) = $selectHEIGHT->fetchrow_array();
    if(defined ($HEIGHT_ID))
    {
        $HEIGHT_handle->execute($HEIGHT_ID, $height_description, $PRODUCT_ID);
    }

    $selectType->execute($PRODUCT_ID);
    my ($type_ID, $type_description) = $selectType->fetchrow_array();
    if(defined ($type_ID))
    {
        $type_handle->execute($type_ID, $type_description, $PRODUCT_ID);
    }

    $selectMat->execute($PRODUCT_ID);
    my ($Mat_ID, $Mat_description) = $selectMat->fetchrow_array();
    if(defined ($Mat_ID))
    {
        $mat_handle->execute($Mat_ID, $Mat_description, $PRODUCT_ID);
    }
}

$success &&= $price_handle->execute();
$selectWatt->finish();
$selectHEIGHT->finish();
$rating_handle->finish();
$selectMat->finish();
$selectType->finish();

my $result = ($success ? $dbh->commit : $dbh->rollback);

unless($result) {
  die "Couldn't finish transaction: " . $dbh->errstr;
}

$dbh->disconnect();

my $end_run = time();
my $run_time = $end_run - $start_run;
$run_time = $run_time/60;
print "Job took $run_time minut

es\n";

Answer 1

以下有两个错误:

$success &&= $rating_handle->execute($VIRTUAL_PRODUCT_ID);
my $rating = $rating_handle->fetchrow_array();

• 如果$success为假，则调用fetchrow_array而不调用execute。

• 如果 $success 为 true 并且 execute 失败，则会调用 fetchrow_array 而不会成功调用 execute.