magento2: Clean Expired Orders Issue

Preconditions

When you have pending orders, from time to time magento cancel this orders. It depends on the option Pending Payment Order Lifetime (minutes) (https://prnt.sc/k5zs2m). Magento has a cronjob sales_clean_orders to do this

Magento 2.2.4 PHP 7.0.27

Steps to reproduce

Create a catalog price rule without a coupon
Add product to cart, and make sure that the rule is applied
Plase an order (use payment method without invoice), e.g. credit memo

Expected result

Once Pending Payment Order Lifetime expired, magento run cron job sales_clean_orders without the issue

Actual result

Cron job status = Error Message SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (salesrule_customer, CONSTRAINT SALESRULE_CUSTOMER_RULE_ID_SALESRULE_RULE_ID FOREIGN KEY (rule_id) REFERENCES salesrule (rule_id) ON DELETE CASCADE), query was: INSERT INTO salesrule_customer () VALUES ()

More info

We have made an investigation, and the problem is in method

\Magento\SalesRule\Model\Coupon\UpdateCouponUsages::updateCustomerRuleUsages When order has been placed using cart rule without coupon it does not do eny set actions, so the object data before $ruleCustomer->save(); is empty array.

Our temporary sollution was to chage code like this.

Old code

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);
        }
        $ruleCustomer->save();
    }

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));
                $ruleCustomer->save();
            }
        } elseif ($increment) {
            $ruleCustomer->setCustomerId($customerId)->setRuleId($ruleId)->setTimesUsed(1);
            $ruleCustomer->save();
        }
    }

About this issue

Original URL
State: closed
Created 6 years ago
Reactions: 8
Comments: 22 (3 by maintainers)

Links to this issue

Magento 2 Error while canceling the order - Magento Forums

Most upvoted comments

Hi,

Magento Open Source v2.2.5

We encountered a very similar issue when trying to cancel an order with a coupon code applied that had been created by a Guest that later created an account to which the order had been assigned.

In our case, the issue has been triggered when manually cancelling the order from the backend.

As a customer ID is now assigned to this order, it is passed as a parameter to this function:

Magento\SalesRule\Model\Coupon\UpdateCouponUsages::updateRuleUsages()

The $customerId variable then satisfies the condition below:

...
if ($customerId) {
    $this->updateCustomerRuleUsages($increment, $ruleId, $customerId);
}
...

Then within the below function, as 1. there is no existing rule associated to the current customer ID due to the fact that the order had been placed by a Guest and that 2. the $increment variable is false due to the fact that the current order cancellation action is intended to subtract 1 use, 3. the result is that there is no data set to the object before executing the save() function:

Magento\SalesRule\Model\Coupon\UpdateCouponUsages::updateCustomerRuleUsages()

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

To reproduce the issue very simply, one might place an order as a guest and then manually add a customer_id for the order in the sales_order database table. Then when trying to cancel the order from the backend, this error will be triggered.

The solution proposed by @magefan which consist of executing the save() method within the conditions directly is valid as it would make sure that there will be no attempt to save an empty object under any circumstances.

Solution:

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));
            $ruleCustomer->save(); //ADD SAVE METHOD WITHIN THE CONDITION
        }
    } elseif ($increment) {
        $ruleCustomer->setCustomerId($customerId)->setRuleId($ruleId)->setTimesUsed(1);
        $ruleCustomer->save(); //ADD SAVE METHOD WITHIN THE CONDITION
    }
    //$ruleCustomer->save(); //REMOVE SAVE METHOD
}

+10

nikoelgatito on Sep 2, 2018

@magefan, we are closing this issue due to inactivity. If you’d like to update it, please reopen the issue.

ghost on Aug 6, 2018

Still an issue 2.4.3 Code moved to another class https://github.com/magento/magento2/blob/2.4-develop/app/code/Magento/SalesRule/Model/Coupon/Usage/Processor.php#L147 but issue still exists

MyroslavDobra on Sep 29, 2021