Magento 2.2.1 Error(Item (Magento\SalesRule\Model\Rule) with the same ID "33" already exists)

Question

I got an error in log like

main.CRITICAL: Item (Magento\SalesRule\Model\Rule) with the same ID "33" already exists. {"exception":"[object] (Exception(code: 0): Item (Magento\SalesRule\Model\Rule) with the same ID \"33\" already exists. at vendor/magento/framework/Data/Collection.php:404)"} []

How can I resolve this? Any quick solution

Answer 1

This issue occurs if you have duplicate rows in "salesrule" database table with the same "created_in" and "update_in" range.

select * from salesrule where rule_id = 71;

You should use 33 instead of 71

The result should look like (note: row_id 198 and 388 has the same date range):

To fix, you will need to split the values in updated_in for row_id 198 and the created_id for row_id 388

update salesrule set updated_in = 2147480000 where row_id = 198;
update salesrule set created_in = 2147480000 where row_id = 388;

Answer 2

Please see Renon's answer above.

To further elaborate on the issue, you can search your database for other affected rules. First, get the current staging version for your site:

SELECT * FROM `flag` WHERE `flag_code` = 'staging';

You will see something like this:

{"current_version":"1595433660"}

Next, take that timestamp, and inject it into this query:

SELECT rule_id, GROUP_CONCAT(`row_id`)
FROM`salesrule`
WHERE(created_in <= '1595433660')
AND (updated_in > '1595433660')
GROUP BY `rule_id` HAVING COUNT(*) > 1

This will return the rule_id and row_id of affected Sales Rules. Update those rows using Renon's method so that only one row for each rule matches the above date conditions, and you should no longer have this problem.

Note: I observed this issue on Magento 2.3.5-p1