Magento fix catalog price rules promotion

One of the magento based website had strange issue. All rules in the admin > Promtions > Catalog Price Rules were inactive. But still some products were displaying as sale items. So ran mysql code listed below to find out what was happening.

select * from catalogrule_product group by rule_id where product_id = 12058;

Then found out it had 3 rules attached which were deleted. Client confirmed they deleted active rules. As they deleted active rules, rule applied to product was still active.

Again I ran below mysql statement to find all the rules that are active in that table.

select * from catalogrule_product group by rule_id;

Then I got list of all the rule_ids and compared with rules currently existed. I found rule_id 11-20 and 34 was deleted. As all the rules in the system were inactive, I just updated their rule_id to match deleted rule_id.

They have around 60k products, so 'Apply Rules' to apply all rules was not working from admin. So created new file in shell as apply_rules.php with content as below.

<?php
require_once '../app/Mage.php';
Mage::app('default');
$catalogRule = Mage::getModel('catalogrule/rule');
$catalogRule->applyAll();
Mage::app()->removeCache('catalog_rules_dirty');

After I ran this file from command line as, "php apply_rules.php", I found table catalogrule_product was empty which was expected as all the rules were inactive.

When I checked frontend, the product was still listed as sale but when I add to cart, it was displaying correct price. So I knew this was the case of page cache. So I cleared magento page cache and checked the product page again.

Wohooo! sale was gone from the product and everything was working as they should. If this helped you, please like and +1 us.