时间戳

首页 > 解决方案 > 在触发错误的情况下 PDO 不会抛出错误

php - 在触发错误的情况下 PDO 不会抛出错误

问题描述

DELETE在带有事件的表上触发。并且在这方面特别有和错误。当手动删除行时收到错误，但是尝试通过PDO删除时，收到成功语句，并且行成功删除。不明白哪里出了问题。

触发代码

CREATE DEFINER=`user` TRIGGER `TR_VM_users_AD` AFTER DELETE ON `VM_users` FOR EACH ROW BEGIN
    /* Archive profile */   
    INSERT INTO VM_users_archive (date_time_created, date_time_modified, imsi, msisdn, `password`, skip_password, email, send_email, is_header_enabled, ivr_lang, sms_lang, ussd_lang, status_id)
    VALUES (OLD.date_time_created, OLD.date_time_modified, OLD.imsi, OLD.msisdn, OLD.`password`, OLD.skip_password, OLD.email, OLD.send_email, OLD.is_header_enabled, OLD.ivr_lang, OLD.sms_lang, OLD.ussd_lang, OLD.status_id);
    
        /* Delete personal blacklist */
        DELETE FROM VM_personal_blacklist WHERE callednumber = OLD.msisdn;
        
        /*Delete greetings */
        DELETE FROM VM_greetings WHERE called_number = OLD.msisdn;
END;

尝试手动删除时出现MYSQL错误

mysql> Delete from VM_users where msisdn = 810044168;
ERROR 1054 (42S22): Unknown column 'callednumber' in 'where clause'
mysql>

php代码

try
{
    $transactionStartTime = microtime(true);
    
    $this->db->pdo->beginTransaction();
    
    $queryDeleteProfile = 'DELETE FROM VM_users WHERE msisdn = :msisdn';
    
    if (!$this->db->preparedQuery($queryDeleteProfile, ['msisdn' => $msisdn]))
    {
        $this->db->pdo->rollBack();
        
        return false;
    }
    
    $this->db->pdo->commit();
    
    $transactionEndTime = round((microtime(true) - $transactionStartTime), 2);
    
    $this->utility->loggerAll($this->logPrefix, __FUNCTION__, $this->logLevel, 3, 'Profile deleted. msisdn: ' . $msisdn . ', transactionEndTime: ' . $transactionEndTime);  
}
catch (\PDOException $ex)
{
    $this->utility->loggerAll($this->logPrefix, __FUNCTION__, $this->logLevel, 1, 'Failed to delete profile!. msisdn: ' . $msisdn . ', errorMessage: ' . $ex->getMessage());
    
    $this->db->pdo->rollBack();
    
    return false;
}

public function preparedQuery($query, $params)
{
    try
    {
        $this->checkConnection();
        $stmt = $this->pdo->prepare($query);
        
        if (!$stmt->execute($params))
        {
            $this->utility->loggerAll($this->logPrefix, __FUNCTION__, $this->logLevel, 1, 'Failed to execute prepared query! ' . $this->pdo->errorInfo()[2] . ', query: ' . $query . ', params: ' . json_encode($params));
            
            return false;
        }
    }
    catch (PDOException $e)
    {
        $this->utility->loggerAll($this->logPrefix, __FUNCTION__, $this->logLevel, 1, 'Failed to execute prepared query! ' . $e->getMessage() . ', query: ' . $query . ', params: ' . json_encode($params));
        
        return false;
    }
    
    return $stmt;
}

标签： phppdo

解决方案

哦，这是我的大错误，PDO 中的错误一切正常。问题是因为，从 php 脚本试图删除已经从数据库中删除的行，结果没有错误。

$query = 'DELETE FROM VM_users WHERE msisdn = :msisdn';

var_dump($db->preparedQuery($query, ['msisdn' => $msisdn]));

DBModule: preparedQuery(): CRITICAL: Failed to execute prepared query! SQLSTATE[42S22]: Column not found: 1054 Unknown column 'callednumber' in 'where clause', query: DELETE FROM VM_users WHERE msisdn = :msisdn, params: {"msisdn":810044172}

推荐阅读