[BOOM-2595] Conception problem form cart rule restrictions

[prestonBot]

This issue has been migrated from the Forge. Read the original ticket here.

• Reporter: djbuch
• Created at: Tue, 7 Mar 2017 10:29:38 +0100

Hello folks,

There is a huge conception problem for cart rule restrictions.

In case a website has just 5 000 (for example) unrestricted cart rules, and the user adds a restricted cart rules, the lines starting at line 338 of file controllers/admin/AdminCartRulesController.php will start inserting 25 millions of rows in the table "ps_cart_rule_combination", just making the whole site hang when using a cart rule.

We should consider to rethink this part. Maybe adding cart rule types and only enabling cart rule types restrictions which would lead to much less rows (because most of the time those 5000 cart rules could be split in 20 or 30 cart rule types).

Regards,
David

[prestonBot]

This comment has been migrated from the Forge. Read the original comment here.

• Author: vincentbz
• Created at: Wed, 8 Mar 2017 16:37:14 +0100

Hi David-Julian BUCH,

This part will be revamped but we don't know yet when. We will improve the code and the UX so we take your ticket into consideration
We will come back to you when we start working on it.

Thanks for your feedback !

[alevals]

any update on this?
It's a major problem for our store (we have +20k cart rules )

[roadster31]

Critical problem for us :

Yes, nearly 70Gb !

[gennaris]

@roadster31 looks like I am stealing your gold medal here 🥇 :-)

This website have 119k cart rules, most of them are disabled and 1.7 billion entries in ps_cart_rule_combination

While checking AdminCartRuleController.php it looks like ps_cart_rule_combination is getting filled also with disabled id_cart_rules so this could be a first optimization but yes, this really need a "core" redesign.

[matks]

Hi, I just got a quick look at this.

First, it seems the code responsible for this has been there for many years (at least 2011) 5547835

Reworking such an old piece of the software is very hard, especially due to lack of documentation on the inner working of this stuff. It does not mean it will not be done, it means it will be hard and slow 😅

Second, would it be possible to elaborate on the usecase ? the code is old and not the easiest to read, it seems you have explored what's going on. Why so many lines are put into ps_cart_rule_combination ? why current implementation is putting them there ? 😊

[gennaris]

@matks , thank you for your time !

Issue is happening when there are a lot of cart rules and a cart rule is being set as restricted (use the "Compatibility with other cart rules" flag) among other existing cart rules (that are being dinamically set as restricted with cart_rule_restriction = 1 too).

Basically ps_cart_rule_combination store all the incompatible couple of rules expanding exponentially as soon as you have restricted rules.

There are a lot of Addons modules that generates a lot of cart rules "on the fly" so it's not too uncommon to encounter this problem (I have seen many sites with huge ps_cart_rule_combination table ...)

It's easy to reproduce yourself on a clean install.

1. Create 5000 random unrestricted rules with this php script to be placed on the root :

require_once __dir__.'/config/config.inc.php';
require_once __dir__.'/init.php';

define ('NUMBER_OF_RULES', 5000);
$languages = Language::getLanguages(false);

for ($i = 0; $i < NUMBER_OF_RULES; $i++) {
    $cartRuleObj = new CartRule();
    $cartRuleObj->quantity = rand(0,10);
    $cartRuleObj->code = substr(md5(microtime()),rand(0,26),8);
    foreach ($languages as $lang) {
        $cartRuleObj->name[(int)$lang['id_lang']] = 'My rule number '.$i;
    }
    $cartRuleObj->active = 1;
    $cartRuleObj->date_from = 0;
    $cartRuleObj->date_to = 0;
    $cartRuleObj->id_customer = 0;
    $cartRuleObj->add();

}

2. Create a new cart rule from BO with all the other rules set as restricted :

3. Save the rule and see the magic happens in ps_cart_rule_combination , if your PHP will not timeout
   you'll end up with a 25 million rows ps_cart_rule_combination table (500+MB).

[sijafu]

Hi! @matks @gennaris
Sorry for digging up the old topic, but maybe you have information about this bug? How can it be avoided?

[TheoAlloin]

hi @matks @gennaris ,

I have the same issue....
Have we an update about this post ? This is an real issue for our e-merchants...

Thanks,
Regards

[sijafu]

@TheoAlloin maybe we could share some thoughts on priv about that issue? I suspect that some modules could be involved so we could compare some things :)

[TheoAlloin]

@sijafu yes why not :) But i think it's a real problem of conception, it doesn't necessarily come from an external module.

[gennaris]

I think I've well explained the reason behind the problem in July 31st post , how to reproduce it and why it happens.

Unfortunately, this function has been designed into the core without taking into account the fact that you can have numerous restricted rules. No modules are involved, unless they are creating restricted rules as-per-my previous explanation.

It seems that a total redesign of this method and also of the database structure would be required to fix this.

In my case, the solution was to stop using restricted rules in the involved e-commerce
and truncate the ps_cart_rule_combination table.

[Ditwo]

Hi,
Is there any news, since 2021 ?
Any improvment would be appreciated ;)

[ravi2448]

Hi,
Is there any Update ?
Any thing change in code to resolve this problem ?

[crishnakh]

Hi, we had this problem, this table was about 180GB and we needed to take action because it was taking our server.

My advice now is:

1. Clean that table if you don't care about combination of cart roules (discounts)
2. If you have a lot discounts coupons (our number was 70.000) this table increase with the option cart_rule_combination activated (our rows in that table was about 2.800 Millions). Delete all the coupons you don't use or the validation date is over. We delete about 65000 records on cart_rule table and drop the content on cart_rule_combination.

We have this problem since 2018... this problem is not a problem if you don't have a lot of discounts.

[Lionel-dev]

Hi @crishnakh,

Thank you for sharing, however, I wonder if by deleting the old cart_rules, we will lose some of the history in the orders not?

For example, some customers look at old orders and see a discount code without knowing where it comes from. So we have to look at the cart_rules to see its description, ....

In this case, the cleaning would not help the customer in the long run. Do you have any news about PretaShop?

[crishnakh]

Hi @Lionel-dev we are aware of this problem. In our case, we have the code of the cart_rule or the name to know about the discount. We had about 4-5 years of cart rules, and about the 80% where used or expired. We don't have any news from PrestaShop... I think the problem is still there when you have a lot of discounts.

[NetVicious]

Any SQL queries to do a little cleaning? Something like:

1. Delete used or expired discounts codes
2. Delete related cart_rules related to deleted discount codes

[kpodemski]

Hello everyone. Following our new approach to feature requests, I converted this Issue to the GitHub Discussion so that the community can discuss the best possible ways to handle this problem and maybe, I hope, organize together to work towards fixing it in the core. If you are motivated to do so, we will be happy to help.

[simbus82]

But is this crazy bug also present in PrestaShop 8.x?

[ebuildy]

Sure, the code is quite the same than .. 13 years ago ^^

https://github.com/PrestaShop/PrestaShop/blob/8.1.x/controllers/admin/AdminCartRulesController.php#L422

[rcatedra]

I think is not a real issue or bug. Just simply this system is done for ecommerces who brings from 5 to 10 different discounts, so you can choose wich discounts or cart rules are compatibles with the rest of rules. The problem comes when we uses an external rules as mine. I used a gift cart module to bring the customers some free items in the cart page. Well, the module creates a discount (cart rule) for each gift the order has inside. Even if the customer doubt about the gift to choose, a new cart rule is created on every gift change. After all, if you have choosen to combinate all cart rules, the table rows grows exponencially. I'm researching about this to avoid it, but I'm affraid is not an easy thing...

[ebuildy]

When the voucher is used for an order, all cart_combination entries should removed isnit? Thats the bug I think.

Could we delete safely rows:

SELECT crc.* 
FROM psds17_cart_rule_combination crc
LEFT JOIN psds17_cart_rule cr1 ON cr1.id_cart_rule = crc.id_cart_rule_1
LEFT JOIN psds17_cart_rule cr2 ON cr2.id_cart_rule = crc.id_cart_rule_2
WHERE cr1.id_cart_rule IS NULL OR cr2.id_cart_rule IS NULL OR cr1.date_to < NOW() OR cr2.date_to < NOW();

?

[gennaris]

There isn't a direct connection between the tables ps_cart_rule_* and the orders.
When specific cart rules are used in orders, this information is stored in the ps_order_cart_rule table.

The purpose of the ps_cart_rule_combination table is to maintain the relationship between the restrictions imposed by individual cart rules.

If you're relying on single-use vouchers, you can set up a crontask to delete expired rules and you'll be fine
(delete() method of the CartRule class already handles the ps_cart_combination cleanup)

However, this isn't applicable for rules that can be used multiple times..

[ebuildy]

Thanks you for details! What about voucher for single customer and single usage (such as refund) ? We use it a lot and this is killing our MySQL :-/ Le mar. 26 mars 2024, 07:20, Simone Gennari ***@***.***> a écrit :

…

There isn't a direct connection between the tables *ps_cart_rule_** and the orders. When specific cart rules are used in orders, this information is stored in the *ps_order_cart_rule* table. The purpose of the *ps_cart_rule_combination* table is to maintain the relationship between the restrictions imposed by individual cart rules. If you're relying on single-use vouchers, you can set up a crontask to delete expired rules and you'll be fine (*delete()* method of the CartRule class already handles the *ps_cart_combination* cleanup) However, this isn't applicable for rules that can be used multiple times.. — Reply to this email directly, view it on GitHub <#33442 (reply in thread)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAJJZ2JS43LLAQWZQN3MRVDY2FDVJAVCNFSM6AAAAAA64E3ICKVHI2DSMVQWIX3LMV43SRDJONRXK43TNFXW4Q3PNVWWK3TUHM4DSMJTHE4TA> . You are receiving this because you commented.Message ID: ***@***.***>

[djbuch]

This problem is still very common. And a simple fix would to add a notion of "Cart rule category" and to add the restriction at the category level instead of the Cart rule level.

The code is not so hard to understand.

[jimNutrix]

We had a 20go ps_cart_rule_combination slowing our server
Because many of our cart_rule are for a single customer, we asked ourselves why cart_rule_combination table link cart_rule that are for a different customer.
So we limited the creation to cart_rule without a specified customer or with same customer to the currently created cart_rule.
And we also stopped creating cart_rule_combination for cart_rule expired or consumed.

Technically, in controllers\admin\AdminCartRulesController.php , function afterAdd (I did this for version 1.7.8.7) :
Add this part before "// If the new rule has no cart rule restriction, then it must be added to the white list of the other cart rules that have restrictions"

 $limit_to_customer = '';
        if (isset($currentObject->id_customer) &&  $currentObject->id_customer != '0'){
            // Cart restricted on a specific customer
            $limit_to_customer = ' AND (id_customer = 0 OR id_customer = '.(int)$currentObject->id_customer.')';
        }

Then modify this part, changes are in lines that contains "$limit_to_customer"

// If the new rule has no cart rule restriction, then it must be added to the white list of the other cart rules that have restrictions
        if (!Tools::getValue('cart_rule_restriction')) {
            Db::getInstance()->execute('
			INSERT INTO `' . _DB_PREFIX_ . 'cart_rule_combination` (`id_cart_rule_1`, `id_cart_rule_2`) (
				SELECT id_cart_rule, ' . (int) $currentObject->id . ' FROM `' . _DB_PREFIX_ . 'cart_rule` WHERE cart_rule_restriction = 1
				    AND quantity != 0 AND date_to > NOW() AND active = 1 '.$limit_to_customer.'
			)');
        } else {
            // And if the new cart rule has restrictions, previously unrestricted cart rules may now be restricted (a mug of coffee is strongly advised to understand this sentence)
            $ruleCombinations = Db::getInstance()->executeS('
			SELECT cr.id_cart_rule
			FROM ' . _DB_PREFIX_ . 'cart_rule cr
			WHERE cr.id_cart_rule != ' . (int) $currentObject->id . '
			AND cr.cart_rule_restriction = 0
			AND NOT EXISTS (
				SELECT 1
				FROM ' . _DB_PREFIX_ . 'cart_rule_combination
				WHERE cr.id_cart_rule = ' . _DB_PREFIX_ . 'cart_rule_combination.id_cart_rule_2 AND ' . (int) $currentObject->id . ' = id_cart_rule_1 
				    AND quantity != 0 AND date_to > NOW() AND active = 1 '.$limit_to_customer.'
			)
			AND NOT EXISTS (
				SELECT 1
				FROM ' . _DB_PREFIX_ . 'cart_rule_combination
				WHERE cr.id_cart_rule = ' . _DB_PREFIX_ . 'cart_rule_combination.id_cart_rule_1 AND ' . (int) $currentObject->id . ' = id_cart_rule_2 
				    AND quantity != 0 AND date_to > NOW() AND active = 1 '.$limit_to_customer.'
			)
			');
            foreach ($ruleCombinations as $incompatibleRule) {
                Db::getInstance()->execute('UPDATE `' . _DB_PREFIX_ . 'cart_rule` SET cart_rule_restriction = 1 WHERE id_cart_rule = ' . (int) $incompatibleRule['id_cart_rule'] . ' LIMIT 1');
                Db::getInstance()->execute('
				INSERT IGNORE INTO `' . _DB_PREFIX_ . 'cart_rule_combination` (`id_cart_rule_1`, `id_cart_rule_2`) (
					SELECT id_cart_rule, ' . (int) $incompatibleRule['id_cart_rule'] . ' FROM `' . _DB_PREFIX_ . 'cart_rule`
					WHERE active = 1 AND quantity != 0 AND date_to > NOW() '.$limit_to_customer.'
					AND id_cart_rule != ' . (int) $currentObject->id . '
					AND id_cart_rule != ' . (int) $incompatibleRule['id_cart_rule'] . '
				)');
            }
        }

We also deleted cart_rule_combination for cart_rule expired or consumed with this (could be long) :

<?php

// Chargement de la configuration de Prestashop
include(dirname(__FILE__) . '/../config/config.inc.php');
include(dirname(__FILE__) . '/../init.php');


// Connexion à la base de données via le connecteur Prestashop
$db = Db::getInstance();

$sql = 'SELECT cr.id_cart_rule FROM '._DB_PREFIX_.'cart_rule cr WHERE cr.quantity = 0 OR cr.date_to < NOW() OR cr.active = 0';
$results = $db->executeS($sql);

print_r(count($results));

echo '<br>';
echo '<br>';

if ($results) {

    $i=1;

    foreach ($results as $result) {

        $sql = 'DELETE crc
            FROM '._DB_PREFIX_.'cart_rule_combination crc
            WHERE crc.id_cart_rule_1 = '.$result['id_cart_rule'].' OR crc.id_cart_rule_2 = '.$result['id_cart_rule'];

        // Exécution de la requête
        $result_delete = $db->execute($sql);

        if ($result_delete) {
            echo ' ' . $result['id_cart_rule'] . ' => Unused cart rule combinations have been successfully deleted. ( loop ' . $i . ' )<br>';
        } else {
            echo ' ' . $result['id_cart_rule']. ' => Failed to delete unused cart rule combinations. ( loop ' . $i . ' )<br>';
        }

        $i++;

    }

    echo '<br>';

} else {

    echo 'No results found.';

}

You need to optimize the sql table after that. That shrinked our table from 20go to 9mo...
Now we hope it will be faster.