integrity constraint violation in sales_clean_orders cron job

[ktruehl]

Preconditions (*)

1. Magento 2.3.4 (but also Magento 2.3.5)

Steps to reproduce (*)

1. Create a catalog price rule with coupon.
2. Add product to cart and apply the coupon.
3. Start placing an order. Choose a credit card payment method, or any payment method that allows transitioning the order to the pending_payment status works.
4. Do not finish the payment, so that the order stays with the pending_payment status.
5. Wait for the sales_clean_orders cron job to process this order.

Expected result (*)

1. The cron job sales_clean_orders runs smoothly and the unfinished order should now be cancelled.

Actual result (*)

1. The cron job sales_clean_orders runs into an exception:

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 ()

Observations and Fix

The problem lies in the class Magento\SalesRule\Model\Coupon\UpdateCouponUsages in method updateCustomerRuleUsages(). The problem occurs if no sales rule/customer relation ($ruleCustomer) exists ($ruleCustomer->getId() == false) and if $increment == 0. In that case the method will try to save the sales rule/customer relation $ruleCustomer without any data being set on it. In particular the field rule_id will be null, which than causes the foreign key constraint violation.

The problem can be easily fixed by adding an else clause to the if/elseif instruction:

        else {
            return;
        }

This will cause the sales rule/customer relation to not be saved.

[m2-assistant]

Hi @ktruehl. Thank you for your report.
To help us process this issue please make sure that you provided the following information:

• Summary of the issue
• Information on your environment
• Steps to reproduce
• Expected and actual results

Please make sure that the issue is reproducible on the vanilla Magento instance following Steps to reproduce. To deploy vanilla Magento instance on our environment, please, add a comment to the issue:

@magento give me 2.4-develop instance - upcoming 2.4.x release

For more details, please, review the Magento Contributor Assistant documentation.

@ktruehl do you confirm that you were able to reproduce the issue on vanilla Magento instance following steps to reproduce?

• yes
• no

---

• Join Magento Community Engineering Slack and ask your questions in #github channel.

[ktruehl]

The yes checkbox in the bot questionaire doesn't work. I cannot check it. But yes, I do confirm that it can be reproduced on a vanilla Magento instance.

[CarmineDamore78]

@magento give me 2.4-develop instance

[magento-engcom-team]

Hi @CarmineDamore78. Thank you for your request. I'm working on Magento 2.4-develop instance for you

[magento-engcom-team]

Hi @CarmineDamore78, here is your Magento instance.
Admin access: https://i-27918-2-4-develop.instances.magento-community.engineering/admin_3eeb
Login: b5c6dfa8 Password: 0a949c0e9b1d
Instance will be terminated in up to 3 hours.

[CarmineDamore78]

Hi @ktruehl I wasn't enable to reproduce the issue on M2.4 develop and M.2.3.4
Can you give me more details on how reproduce the issue?

Thanks

[ktruehl]

Hi @CarmineDamore78,

You won't be able to reproduce the issue on a Magento instance that lasts only 3 hours. The default setting for the delay for cleaning out orders is 8 hours. So you would have to create an order, wait for at least 8 hours without touching the order, then check the result of the sales_clean_orders cron job in the cron_schedule table.

[ivanweiler]

Similar problems here, I think this is related to:

#16779
#19230

It seems this is really old issue that was never properly fixed.

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed after 14 days if no further activity occurs. Is this issue still relevant? If so, what is blocking it? Is there anything you can do to help move it forward? Thank you for your contributions!

[ivanenkoaleksei]

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

1. Create a cart price rule without coupon(With coupon didn't try, but I think will be the same result).
2. Create order as guest user.
3. After placing the order, register an account with user data witch were used in step 2.
4. Go to admin panel to this order.
5. Try to cancel it.

The main problem in it. When we placed an order as a guest user method updateCustomerRuleUsages doesn't execute and doesn't create new record in salesrule_customer table.
When we create an user and try to cancel order method updateCustomerRuleUsages executes and tries to get a row from table. Row doesn't retrieve and in result we have the next consiquenses.
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