1

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?

1
  • salesrule is generally related to a promotion applied to the order. Is the database correct before you are trying to cancel? Sep 14, 2018 at 1:15

4 Answers 4

0

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

0

We've faced a similar issue, and thanks to your comments I figured out what was happening.

We had a custom payment method that is canceling the order and sometimes when a coupon is applied to the order, the error was thrown (This only happens when the customer didn't have used the coupon yet, this is when there is not an entry in the database for that customer and that rule).

After debugging it I found that the problem was our custom payment method, it was canceling the order before the place order method is completed.

The problem re is a plugin that adds the coupon usage to the DB, and then when is canceled it is decreased, but since this method is canceling the order before that, it throws the foreign key exception.

The plugin is Magento\SalesRule\Plugin\CouponUsagesIncrement, it must be executed before the Magento\SalesRule\Plugin\CouponUsagesDecrement.

To solve our problem I moved the $order->cancel() execution from our custom payment method to be executed after the CouponUsagesIncrement plugin

Hope this helps anyone else :)

Regards

0

I have the same issue with Magento version 2.4.1 Steps to reproduce:

Create a cart price rule without a coupon(With coupon didn't try, but I think will be the same result). Create order as a guest user. After placing the order, register an account with user data which were used in step 2. Go to the admin panel to this order. Try to cancel it. The main problem is it. When we placed an order as a guest user method updateCustomerRuleUsages isn't executed and isn't created a new record in the salesrule_customer table. When we create a user and try to cancel the order method updateCustomerRuleUsages is executed and try to get a row from a table. The row isn't retrieved and as result, we have the next consequences.

if ($ruleCustomer->getId()) { //1. CONDITION NOT MET
   if ($increment || $ruleCustomer->getTimesUsed() > 0) {
     $ruleCustomer->setTimesUsed($ruleCustomer->getTimesUsed() + ($increment ? 1 : -1))
   }
} elseif ($increment) { //2. CONDITION NOT MET
  $ruleCustomer->setCustomerId($customerId)->setRuleId($ruleId)->setTimesUsed(1);
}
$ruleCustomer->save(); //3. EMPTY OBJECT CAN'T BE SAVED

I have a solution. Need to add plugin for \Magento\SalesRule\Model\Coupon\Usage\Processor::process

<?php
namespace Vendor\Module\Plugin\Model\Coupon\Usage;
use Magento\SalesRule\Model\Coupon\Usage\Processor as Subject;
use Magento\SalesRule\Model\Coupon\Usage\UpdateInfo;
use Magento\SalesRule\Model\Rule\CustomerFactory;
use Magento\SalesRule\Model\ResourceModel\Rule\Customer as RuleCustomerResource; 
use Psr\Log\LoggerInterface;

/**
 * @var CustomerFactory
 */
protected CustomerFactory $ruleCustomerFactory;

/**
 * @var RuleCustomerResource
 */
protected RuleCustomerResource $ruleCustomerResource;

/**
 * @var LoggerInterface
 */
protected LoggerInterface $logger;

class Processor
{

/**
 * Processor constructor.
 * @param CustomerFactory $ruleCustomerFactory
 * @param RuleCustomerResource $ruleCustomerResource
 * @param LoggerInterface $logger
 */
public function __construct(
    CustomerFactory $ruleCustomerFactory,
    RuleCustomerResource $ruleCustomerResource,
    LoggerInterface $logger
) {
    $this->ruleCustomerFactory = $ruleCustomerFactory;
    $this->ruleCustomerResource = $ruleCustomerResource;
    $this->logger = $logger;
}

/**
 * @param Subject $subject
 * @param UpdateInfo $updateInfo
 * @return null
 */
public function beforeProcess(Subject $subject, UpdateInfo $updateInfo)
{
    $isIncrement = $updateInfo->isIncrement();
    $customerId = $updateInfo->getCustomerId();
    if ($isIncrement || !$customerId) {
        return null;
    }
    foreach (array_unique($updateInfo->getAppliedRuleIds()) as $ruleId) {
        if (!(int) $ruleId) {
            continue;
        }
        $ruleCustomer = $this->ruleCustomerFactory->create();
        $this->ruleCustomerResource->loadByCustomerRule($ruleCustomer, $customerId, $ruleId);
        if (!$ruleCustomer->getId()) {
            $ruleCustomer->setCustomerId($customerId)->setRuleId($ruleId)->setTimesUsed(1);
            try {
                $this->ruleCustomerResource->save($ruleCustomer);
            } catch (\Exception $e) {
                $this->logger->error($e->getMessage());
            }
        }
    }
    return null;
}

}

0

This seems to be fixed in the current develop branch. So this fix can be used instead of custom solutions.

The easiest way to implement this is trough a composer patch until it will be released.

https://github.com/magento/magento2/blob/7d80b877d7057e62555d3376f2be7e7be68838f0/app/code/Magento/SalesRule/Model/Coupon/Usage/Processor.php#L149

private function updateCustomerRuleUsages(bool $isIncrement, int $ruleId, int $customerId): void
{
    $ruleCustomer = $this->ruleCustomerFactory->create();
    $ruleCustomer->loadByCustomerRule($customerId, $ruleId);
    if ($ruleCustomer->getId()) {
        if ($isIncrement || $ruleCustomer->getTimesUsed() > 0) {
            $ruleCustomer->setTimesUsed($ruleCustomer->getTimesUsed() + ($isIncrement ? 1 : -1));
        }
    } elseif ($isIncrement) {
        $ruleCustomer->setCustomerId($customerId)->setRuleId($ruleId)->setTimesUsed(1);
    }

    if ($ruleCustomer->hasData()) { // <-- This conditional statedment is added as fix.
        $ruleCustomer->save();
    }
}

EDIT:

I made a pacth, I may as well share it. Place content of this file in <magento root>/patches

diff --git a/Model/Coupon/Usage/Processor.php b/Model/Coupon/Usage/Processor.php
index 90a456d..2ca2b64 100644
--- a/Model/Coupon/Usage/Processor.php
+++ b/Model/Coupon/Usage/Processor.php
@@ -144,6 +144,8 @@ class Processor
         } elseif ($isIncrement) {
             $ruleCustomer->setCustomerId($customerId)->setRuleId($ruleId)->setTimesUsed(1);
         }
-        $ruleCustomer->save();
+        if ($ruleCustomer->hasData()) {
+            $ruleCustomer->save();
+        }
     }
 }

Add following in composer.json

"magento/module-sales-rule": {
    "fix for integrity constraint violation for cronjob sales_clean_orders": "patches/magento-sales-rule-cron-sales-clean-orders-fix.patch"
 }

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.