PDO在不同服务器中复制行一般错误

所以我正在尝试将选定的行从不同数据库中的一个表"移动"到另一个表。

理论上它是有效的（但如果有人想发表任何意见，请这样做，我对PDO很陌生。但是，我不断收到"SQLSTATE[HY000]：一般错误"错误。

有什么建议吗？

  private function broken() {
    try {
        $sql = "SELECT * FROM `calls` WHERE `calls`.`status`=0 AND `calls`.`stage` < 4 AND `calls`.`answer` < (NOW() + INTERVAL 10 MINUTE)";
        $query = $this->staging->query($sql);
        while($row = $query->fetch(PDO::FETCH_ASSOC)) {
            // Insert in production database:
            $sql = "INSERT INTO `ivr_incomplete` (`id`,`sip_id`,`extension`,`caller_id`,`stage`,`status`,`survey_id`,`start`,`answer`,`hangup`,`end`) VALUES (:id, :sip_id, :extension, :caller_id, :stage, :status, :survey_id, :start, :answer, :hangup, :end)";
            $query = $this->production->prepare($sql);
            $query->execute($row);
            // Delete from staging:
            $sql = "DELETE FROM `calls` WHERE `id`='".$row['id']."'";
            $this->staging->query($sql);
        }
    }
    catch(PDOException $e) {
        $this->informer("FATAL", "Unable to process broken IVR surveys. Error: ".$e->getMessage());
    }
}

两点：

你在每次迭代中都准备INSERT，这在某种程度上消除了使用预准备语句的一半点 - 你使用它的目的只是转义。预准备语句的要点之一是查询只解析一次，因此如果您需要使用不同的值重复执行相同的查询，调用prepare()一次，然后简单地使用不同的数据集调用execute()可以显着提高性能。
~~这整个事情可以通过 2 个查询完成：~~ ^{由于使用两个单独的数据库连接而删除}

编辑

试试这个代码：

您可能需要调整错误处理以满足您的需求，尤其是在INSERT出现错误时如何处理错误，因为我怀疑您是否希望中断整个操作并将已成功处理的行保留在源表中。

private function broken() {
    try {
        // Fetch records to move
        $sql = "
          SELECT *
          FROM `calls`
          WHERE `status` = 0
            AND `stage` < 4
            AND `answer` < (NOW() + INTERVAL 10 MINUTE)
        ";
        $query = $this->staging->query($sql);
        if (!$query) {
            $errorInfo = $this->staging->errorInfo();
            throw new Exception("MySQL error at SELECT: $errorInfo[1] ($errorInfo[0]): $errorInfo[2]");
        }
        // Prepare the INSERT statement
        $sql = "
          INSERT INTO `ivr_incomplete`
            (`id`,`sip_id`,`extension`,`caller_id`,`stage`,`status`,`survey_id`,`start`,`answer`,`hangup`,`end`)
          VALUES
            (:id, :sip_id, :extension, :caller_id, :stage, :status, :survey_id, :start, :answer, :hangup, :end)
        ";
        if (!$stmt = $this->production->prepare($sql)) {
            $errorInfo = $this->production->errorInfo();
            throw new Exception("MySQL error at prepare INSERT: $errorInfo[1] ($errorInfo[0]): $errorInfo[2]");
        }
        // A list of the row IDs we are working with
        $rowIds = array();
        // Loop the results and insert them
        for ($i = 1; $row = $query->fetch(PDO::FETCH_ASSOC); $i++) {
            if (!$stmt->execute($row)) {
                $errorInfo = $stmt->errorInfo();
                throw new Exception("MySQL error at INSERT row $i (id: {$row['id']}): $errorInfo[1] ($errorInfo[0]): $errorInfo[2]");
            }
            $rowIds[] = (int) $row['id'];
        }
        // Delete from staging:
        if ($rowIds) {
            $sql = "
              DELETE FROM `calls`
              WHERE `id` IN (".implode(', ', $rowIds).")
            ";
            if (!$this->staging->query($sql)) {
                $errorInfo = $this->staging->errorInfo();
                throw new Exception("MySQL error at DELETE: $errorInfo[1] ($errorInfo[0]): $errorInfo[2]");
            }
        }
    } catch(PDOException $e) {
        $this->informer("FATAL", "Unable to process broken IVR surveys (PDO). Error: ".$e->getMessage());
    } catch (Exception $e) {
        $this->informer("FATAL", "Unable to process broken IVR surveys (MySQL). Error: ".$e->getMessage());
    }
}

PDO在不同服务器中复制行 一般错误

PDO Replicating rows in different servers General Error