[Solved] Upgrading From 1.6.1.0 To 1.6.1.4 - Error While Saving Product, Duplicate Entry For Key

[Nati Mask]

Hi!
 
Please help, I'm very frustrated with this:
 
We have decent shop (hundreds of products) and we lately upgraded from 1.6.1.0 to 1.6.1.4
 
When saving now (within the BO edit product) many products, not all, but those who gets a lot of Specific Prices, we're getting this error:

Duplicate entry '39-0-0-0-2015-11-29 00:00:00-2015-12-02 00:00:00-1-0-0-0-0-1-47' for key 'id_product_2'

Which is indeed an UNIQUE KEY in the specific_price table.
I tried digging in the code, and I found that the process of saving the product tirggers this function call: 

SpecificPriceRule::applyAllRules(array((int)$this->id));

Which indeed inserting the specific prices coming from Catalog Price Rules for that product, to the specific_price table in database!
 
Plus, Looking into this sql error it seems that prestashop just try to insert the same specific price rule for the same product, where prestashop try at all to prevent this duplicate for unique key?
 
I'm well aware of that topic:
https://www.prestashop.com/forums/topic/467748-solved-specific-price-error-after-update-from-1609-to-1611/
And of that issues:
http://forge.prestashop.com/browse/PSCSX-6535
http://forge.prestashop.com/browse/PSCSX-6346
 
None of the solutions mentioned there solved my error, none of it should, if prestashop indeed tries to insert duplicate values when unique key restriction exists.

My issue is different though, my specific prices are working, only the process of Saving changes to my products is cancelled because of that error.

 

Thank you for any help,

Nati, Web developer

Edited February 23, 2016 by Nati Mask (see edit history)

[Nati Mask]

Well, thank you! But how am I supposed to do that? `id_product_2` is a mysql index (key), mysql itself is adding the values into it, as much as I know...

 

I can DROP the key entirely, but it looks like not a good practice to me, because I guess prestashop uses this key as sort of defense against duplicates, isn't it?

[Nati Mask]

What we did to get out of this problem is that:

 

• Delete many (A LOT, in fact the majority of ours) Catalog Price Rules (via the Back Office)
• Truncate the entire specific_price table.
• Back at the Back Office, save again, one by one, any of the remaining Catalog Price Rules

 

Then we could save our products again.

 

It was costly enough, because we have had a lot of rules which took nice time to build (many conditions) and for that I think it's not such a solution (after all we are talking about upgrade here, not about deleting and re-inserting data) so I'm not marking this as SOLVED for now, and I'd like somebody's opinion or ideas...

 

Thank you,

Nati

 

(Sorry for my English mistakes, not my native)

[Nati Mask]

Ok, after some decent time the problem appear again, then my above solution proved as not good.
 
We made a deeper dig in prestashop core code, to find that there is certainly and undoubtedly a bug, I'm providing the details in short:
 
When you save a product prestashop indeed deletes all specfic_rules (Catalog Cart Rules) applies to the product. It's done in a function called SpecificPriceRule::resetApplication
BUT, after deleting those rules prestashop checks for every specfic_rules if this now-saved product is still affected by them.
Prestashop doing this check with a function called SpecificPriceRule::getAffectedProducts. Sometimes, not every time, the results of this getAffectedProducts are duplicate for some reason I don't have time to find, so you just get the same affected product twice.
Immediately after that prestashop tries to insert the real rules for each of the affected, but the key (index) named id_product_2 prevents this duplicate for the same product, which is right to prevent (We don't need to hold the fact that our green sport socks are at 30% discount TWICE in our database, are we?)
 
We wrote very simple WORKAROUND style fix if you want you can use it yourself, no warranty at all (but looks stable to me)
 
At the file prestashop/classes/SpecificPriceRule.php
 
Find the function named apply. Inside that function, immediately after the line: 

$products = $this->getAffectedProducts($products);

Add those lines:

$existAffected = [];
foreach ($products as $key => $affectedProduct) {
    if(in_array($affectedProduct['id_product'], $existAffected)) unset($products[$key]);
    $existAffected[] = $affectedProduct['id_product'];
}

The whole function will now look something like that:    

public function apply($products = false)
{
    if (!SpecificPriceRule::$rules_application_enable) {
        return;
    }

    $this->resetApplication($products);        
    $products = $this->getAffectedProducts($products);
    $existAffected = [];
    foreach ($products as $key => $affectedProduct) {
        if(in_array($affectedProduct['id_product'], $existAffected)) unset($products[$key]);
        $existAffected[] = $affectedProduct['id_product'];
    }

    foreach ($products as $product) {
        SpecificPriceRule::applyRuleToProduct((int)$this->id, (int)$product['id_product'], (int)$product['id_product_attribute']);
    }
}

Solve the problem for me.
 
Clearly that's not the way this bug have to get fixed. Sure thing there is a need to create nice bug-report, pull request or something, the real fix in my opinion has to be in the getAffectedProducts function, something like GROUP BY there, this function is a bit long and I still don't have enough time to look there seriously.

 

If anybody might volunteer to do that, the whole Prestashop Community will be happier.

 

Sorry again for my weak English, not my native $id_lang 

[TiaNex Shopping]

i have the same problem,

maybe it's bug,

i found out,the error will happen on the products with a "Catalog Price Rules" applied,

 

it will apply this rule twice,so try to avoid apply again,

 

 

you can see it in

 

https://github.com/PrestaShop/PrestaShop/pull/6469

Edited October 13, 2016 by TiaNex. (see edit history)

[Destiny84]

I know this issue is marked solved, but I stumbled upon this problem and found a one-liner solution. 

this is the error I received : 

Fatal error: Uncaught Duplicate entry '82-0-0-0-0000-00-00 00:00:00-0000-00-00 00:00:00-1-0-0-0-0-1-2' for key 'id_product_2'<br /><br /><pre>INSERT INTO `ps_specific_price` (`id_shop_group`, `id_shop`, `id_cart`, `id_product`, `id_product_attribute`, `id_currency`, `id_specific_price_rule`, `id_country`, `id_group`, `id_customer`, `price`, `from_quantity`, `reduction`, `reduction_tax`, `reduction_type`, `from`, `to`) VALUES ('0', '1', '0', '82', '0', '0', '2', '0', '0', '0', '-1', '1', '0', '0', 'amount', '0000-00-00 00:00:00', '0000-00-00 00:00:00')</pre> thrown in C:\web\presta_beautiful\classes\db\Db.php on line 791

As it turned out, the function getAffectedProducts in classes/SpecificPriceRule.php could return results like this: 
 

Array
(
    [0] => Array
        (
            [id_product] => 82
            [id_product_attribute] => 
        )

    [1] => Array
        (
            [id_product] => 82
            [id_product_attribute] => 
        )

)

The solution is to change SpecificPriceRule.php around line 285

return $result;

to

$result = array_unique($result);
return $result;

 

[TiaNex Shopping]

 

 

I know this issue is marked solved, but I stumbled upon this problem and found a one-liner solution. 

this is the error I received : 

Fatal error: Uncaught Duplicate entry '82-0-0-0-0000-00-00 00:00:00-0000-00-00 00:00:00-1-0-0-0-0-1-2' for key 'id_product_2'<br /><br /><pre>INSERT INTO `ps_specific_price` (`id_shop_group`, `id_shop`, `id_cart`, `id_product`, `id_product_attribute`, `id_currency`, `id_specific_price_rule`, `id_country`, `id_group`, `id_customer`, `price`, `from_quantity`, `reduction`, `reduction_tax`, `reduction_type`, `from`, `to`) VALUES ('0', '1', '0', '82', '0', '0', '2', '0', '0', '0', '-1', '1', '0', '0', 'amount', '0000-00-00 00:00:00', '0000-00-00 00:00:00')</pre> thrown in C:\web\presta_beautiful\classes\db\Db.php on line 791

As it turned out, the function getAffectedProducts in classes/SpecificPriceRule.php could return results like this: 

 

Array
(
    [0] => Array
        (
            [id_product] => 82
            [id_product_attribute] => 
        )

    [1] => Array
        (
            [id_product] => 82
            [id_product_attribute] => 
        )

)

The solution is to change SpecificPriceRule.php around line 285

 

return $result;

to

 

 

$result = array_unique($result);
return $result;

hi,@Destiny84 please try this change and test,thanks

 

https://github.com/PrestaShop/PrestaShop/pull/6469/files

Edited October 13, 2016 by TiaNex. (see edit history)