catalogrule_product_price and catalogrule_product are empty

Question

I am trying to apply catalog rule for 27% from 2 customer group but it's not applied after indexing and cleaning cache.

So i debug the issue and found that catalogrule_product_price and catalogrule_product are empty. I am using Magento ver. 1.9.2.2

Following query has been generate while apply ruel

INSERT INTO `catalogrule_product_price` SELECT NULL AS `rule_product_price_id`, `dates`.`rule_date`, `t`.`customer_group_id`, `t`.`product_id`, MIN(rule_price) AS `rule_price`, 1 AS `website_id`, `t`.`latest_start_date`, `t`.`earliest_end_date` FROM (SELECT `cppt`.`customer_group_id`, `cppt`.`product_id`, CASE WHEN IFNULL((@group_id), 'N/A') != cppt.grouped_id THEN @price := CASE `cppt`.`action_operator` WHEN 'to_percent' THEN cppt.price * cppt.action_amount/100 WHEN 'by_percent' THEN cppt.price * (1 - cppt.action_amount/100) WHEN 'to_fixed' THEN IF((cppt.action_amount < cppt.price), cppt.action_amount, cppt.price) WHEN 'by_fixed' THEN IF((0 > cppt.price - cppt.action_amount), 0, cppt.price - cppt.action_amount) END WHEN IFNULL((@group_id), 'N/A') = cppt.grouped_id AND IFNULL((@action_stop), 0) = 0 THEN @price := CASE `cppt`.`action_operator` WHEN 'to_percent' THEN @price * cppt.action_amount/100 WHEN 'by_percent' THEN @price * (1 - cppt.action_amount/100) WHEN 'to_fixed' THEN IF((cppt.action_amount < @price), cppt.action_amount, @price) WHEN 'by_fixed' THEN IF((0 > @price - cppt.action_amount), 0, @price - cppt.action_amount) END ELSE @price := @price END AS `rule_price`, `cppt`.`from_date` AS `latest_start_date`, `cppt`.`to_date` AS `earliest_end_date`, CASE WHEN IFNULL((@group_id), 'N/A') != cppt.grouped_id THEN @action_stop := cppt.action_stop WHEN IFNULL((@group_id), 'N/A') = cppt.grouped_id THEN @action_stop := IFNULL((@action_stop), 0) + cppt.action_stop END, @group_id := cppt.grouped_id, `cppt`.`from_time`, `cppt`.`to_time` FROM `catalogrule_product_price_tmp` AS `cppt` ORDER BY `cppt`.`grouped_id` ASC, `cppt`.`sort_order` ASC, `cppt`.`rule_product_id` ASC) AS `t` INNER JOIN (SELECT DATE_ADD(FROM_UNIXTIME(1494181800), INTERVAL -1 DAY) AS rule_date UNION SELECT FROM_UNIXTIME(1494181800) AS rule_date UNION SELECT DATE_ADD(FROM_UNIXTIME(1494181800), INTERVAL 1 DAY) AS rule_date) AS `dates` ON 1=1 WHERE (UNIX_TIMESTAMP(dates.rule_date) >= from_time) AND (IF((to_time = 0), 1, UNIX_TIMESTAMP(dates.rule_date) <= to_time)) GROUP BY `customer_group_id`, `product_id`, `dates`.`rule_date`

Answer 1

If the catalogrule_product_price and catalogrule_product tables are empty, it means that the catalog rules have not been applied or generated yet. This can be due to a number of reasons:

1. The Magento cron job is not set up properly or not running at all. Catalog rules are generated and applied by the Magento cron job, so if it's not running, the rules won't be applied. Make sure the cron job is set up correctly and running as expected.

2. The catalog price rule is not set up properly. Double-check your rule settings to make sure everything is configured correctly. Make sure the rule is active, the customer groups are selected, and the conditions are set up correctly.

3. Indexes are not up-to-date. Catalog rules rely on product price index and catalog product price index to apply the rules. If these indexes are not up-to-date, the rules won't be applied. Try re-indexing your Magento store and see if that resolves the issue.

4. Cache is not cleared properly. After updating the rule, you need to flush the cache and re-index the store to make sure the changes are reflected. Make sure you are clearing the cache properly.

Try the following steps:

1. Make sure the Magento cron job is running properly.

2. Double-check your catalog price rule settings to ensure everything is configured correctly.

3. Re-index your Magento store to make sure the product price and catalog product price indexes are up-to-date.

4. Clear your cache and re-index your store.

Answer 2

This works for magento 2, but I think it works also in 1.9

Try...

Insert in catalogrule

INSERT INTO `catalogrule` (`rule_id`, `name`, `description`, `from_date`, `to_date`, `is_active`, `conditions_serialized`, `actions_serialized`, `stop_rules_processing`, `sort_order`, `simple_action`, `discount_amount`) VALUES (2, '27% from 2 customer group', '', NULL, NULL, 1, '{"type":"Magento\\\\CatalogRule\\\\Model\\\\Rule\\\\Condition\\\\Combine","attribute":null,"operator":null,"value":"1","is_value_processed":null,"aggregator":"all"}', '{"type":"Magento\\\\CatalogRule\\\\Model\\\\Rule\\\\Action\\\\Collection","attribute":null,"operator":"=","value":null}', 0, 0, 'by_percent', 27.0000);

in catalogrule_customer_group

INSERT INTO `catalogrule_customer_group` (`rule_id`, `customer_group_id`) VALUES (2, 1);
INSERT INTO `catalogrule_customer_group` (`rule_id`, `customer_group_id`) VALUES (2, 2);

in catalogrule_group_website

INSERT INTO `catalogrule_group_website` (`rule_id`, `customer_group_id`, `website_id`) VALUES (2, 1, 1);
INSERT INTO `catalogrule_group_website` (`rule_id`, `customer_group_id`, `website_id`) VALUES (2, 2, 1);

in catalogrule_website

INSERT INTO `catalogrule_website` (`rule_id`, `website_id`) VALUES (2, 1);

Dont forget to clean cache and reindex. if it does not work try to enter to the back and in Catalog Rules try to apply rules