Magento – Magento 2 Order Cancel foreign key constraint error

databasemagento2PHP

I'm currently receiving this error when cancelling an authorize.net direct post order (authorized only, not captured yet, the shipping method is up).

[2018-09-13 13:14:49] report.CRITICAL: SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`magento`.`salesrule_customer`, CONSTRAINT `SALESRULE_CUSTOMER_RULE_ID_SEQUENCE_SALESRULE_SEQUENCE_VALUE` FOREIGN KEY (`rule_id`) REFERENCES `sequence_salesrule` (`sequence_value`) ON DELETE C), query was: INSERT INTO `salesrule_customer` () VALUES () {"exception":"[object] (Zend_Db_Statement_Exception(code: 23000): SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`magento`.`salesrule_customer`, CONSTRAINT `SALESRULE_CUSTOMER_RULE_ID_SEQUENCE_SALESRULE_SEQUENCE_VALUE` FOREIGN KEY (`rule_id`) REFERENCES `sequence_salesrule` (`sequence_value`) ON DELETE C), query was: INSERT INTO `salesrule_customer` () VALUES () at /var/www/html/magento2/vendor/magento/zendframework1/library/Zend/Db/Statement/Pdo.php:235, PDOException(code: 23000): SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`magento`.`salesrule_customer`, CONSTRAINT `SALESRULE_CUSTOMER_RULE_ID_SEQUENCE_SALESRULE_SEQUENCE_VALUE` FOREIGN KEY (`rule_id`) REFERENCES `sequence_salesrule` (`sequence_value`) ON DELETE C) at /var/www/html/magento2/vendor/magento/zendframework1/library/Zend/Db/Statement/Pdo.php:228)"} []

It threw this same error twice in a row (in the exception log), voided the transaction on authorize.net correctly, but then failed to move the order into the canceled state (I'm assuming because of the exception).

Looking at the related tables, I have

mysql> select * from salesrule_customer;
+------------------+---------+-------------+------------+
| rule_customer_id | rule_id | customer_id | times_used |
+------------------+---------+-------------+------------+
|                1 |      18 |       12085 |          1 |
|                2 |      27 |       12099 |          1 |
|                3 |      27 |       12118 |          1 |
|                4 |      27 |       12143 |          1 |
|                5 |      27 |       12139 |          2 |
|                6 |      27 |       12135 |          1 |
|                7 |      27 |        5697 |          1 |
|                8 |      27 |        9223 |          1 |
|                9 |      27 |       12175 |          1 |
|               10 |      27 |       12198 |          1 |
|               11 |      28 |       12182 |          1 |     
+------------------+---------+-------------+------------+

And

+----------------+
| sequence_value |
+----------------+
|              1 |
|              2 |
|              3 |
|              4 |
|              5 |
|              6 |
|              7 |
|              8 |
|              9 |
|             10 |
|             11 |
|             12 |
|             13 |
|             14 |
|             15 |
|             16 |
|             17 |
|             18 |
|             19 |
|             20 |
|             21 |
|             22 |
|             23 |
|             24 |
|             25 |
|             26 |
|             27 |
|             28 |
|             29 |
|             30 |
|             31 |
|             32 |
|             33 |
|             34 |
+----------------+

Can anyone help me figure out what's going wrong?

Best Answer

Seems like there is a bug in Magento\SalesRule\Model\Coupon\UpdateCouponUsages, method updateCustomerRuleUsages(). Change it to:

private function updateCustomerRuleUsages(bool $increment, int $ruleId, int $customerId)
{
    /** @var \Magento\SalesRule\Model\Rule\Customer $ruleCustomer */
    $ruleCustomer = $this->ruleCustomerFactory->create();
    $ruleCustomer->loadByCustomerRule($customerId, $ruleId);
    if ($ruleCustomer->getId()) {
        if ($increment || $ruleCustomer->getTimesUsed() > 0) {
            $ruleCustomer->setTimesUsed($ruleCustomer->getTimesUsed() + ($increment ? 1 : -1));
        }
    } elseif ($increment) {
        $ruleCustomer->setCustomerId($customerId)->setRuleId($ruleId)->setTimesUsed(1);
    } else {
        return false;
    }
    $ruleCustomer->save();
}

this is a quick fix

Related Topic