Problem with specific price table in database growing too big.

[Kildar]

Recently I was told by my hosting provider that my freshly installed stock 1.5.2 was using too much memory. I asked the hosting provider to see if they could find what was using the memory and here is their reply:

 

It appears the problem is with your database table for the specific price has become too large. (1.4 million rows)

The error is coming about on this line (368) in your file /home6/fetbidsc/public_html/store/classes/SpecificPrice.php:

 

foreach ($results as $row)

$ids_product[] = $with_combination_id ? array('id_product' => (int)$row['id_product'], 'id_product_attribute' => (int)$row['id_product_attribute']) : (int)$row['id_product'];

return $ids_product;

 

The reason this is failing is because this loop is running a database query to check every single one of those 1.4 million rows, causing your php memory to be extinguished.

The easiest fix for this is to disable the specific price class from running, or simply empty the ps_specific_price table in your database.

 

My questions are this:

 

1. I only have about 3000 items in the store. How did I get 1.4 Million rows of data in this table?

2. What does this table do?

3. What will happen if I do remove the data from this table? Will the item prices disappear?

4. If I do empty the table, how do I keep this from happening again?

5. Is this a known Prestashop issue?

 

Thank you for any and all help you can provide.

[tomerg3]

Did you add many specific prices to your products?

Can you attach a dump of that table?

[Kildar]

As far as I know I haven't added any specific prices to my products. I enter the wholesale and retail prices on the price screen and that is it.

[olea]

Are you in a multi-shop context ?

 

Do you use the Catalog Rules feature ?

[chris2407]

Olea why don't you answer to PM's ? I wrote you many PM but you never answer... please answer to me. thanks

[Kildar]

Olea,

 

No, I'm not running a multi - shop. Yes, I've set up one Catalog Rules for a sale on Cyber Monday. I've deleted that since then. Does that set that table? If so, wouldn't it have cleared it out when I deleted the rule?

 

Thank you for your help.

[ffmartinet]

Hi,

I'm on Prestashop 1.5.2 and I've the same problem, when I save a product, a lot of rows are added in the table specific_price. I have found a temporary solution which is to save my rule and the rows becomes like before in the datatable but i want to find an issue.

 

Have you found a solution ?

 

Sorry for my english and thank you for your help

[angel2003]

Hi,

I am having that problem too, and I am trying to figure it out since I don't have specific prices, only the prices set up in the product itself, I I can clear the table without having problems in the prices of the products.

 

I assume that those lines were created but some bug when the specific cart rules and prices where set up.

 

But I am not 100% sure if nothing else will be affected. Does anyone knows the answer for this?

 

thanks

[Neurona Dreams]

Hi,

I'm on Prestashop 1.5.2 and I've the same problem, when I save a product, a lot of rows are added in the table specific_price. I have found a temporary solution which is to save my rule and the rows becomes like before in the datatable but i want to find an issue.

 

Have you found a solution ?

 

Sorry for my english and thank you for your help

 

Same problem here, i've now about 2000 products in my catalog, when i use import csv (last test with 8 more products), my specific_price table grows from 2000 lines to 20000 or more. If you continue importing more csv, database grows exponentially.

 

My solution was truncate the specific_prices table and after that, in the back office edit and save the cart rules again, no need to create again, just enter to edit the rule and click save.

[pacoenri]

I have the same problem. The specific_price table grow and grow and the import process is eternal.

I have many specific_price rules.

[sworld]

I also had specific price TABLE growing in Size in 1.5.2 and I have upgraded 1.5.4 with HOPE that the problem will be solved

[benjamin utterback]

Hi sworld, please keep us posted if the database problem is still present in 1.5.4. Thank you!

[sworld]

Specific Price - Table Size is today 664 KB, I am not going to add any new product or Specific Price for NEXT 1 week and see if the Table size grow in SIZE or Not

[benjamin utterback]

Hi sworld, thank you very much!

[sworld]

The Size of the TABLE - Specific Price is 1.0 MB Now from 664 KB in 2 Days

 

I have EMPTY and applied the Discount once again

[djbiod]

Hi all,

same problem for me (multi store, about 6000 products and 1000 categories).

This one is the store with the problem : http://www.bailly-loisirs.fr/pieces/

as u can see, it s showing a beautiful error

[benjamin utterback]

hi djbiod, do you have specific prices set up?

[djbiod]

Hi Benjamin,

I have 12 card rules and 5 catalog rules

[djbiod]

I forgot to tell I m in 1.5.3

[djbiod]

Hi,

I truncated the specific_prices table (1 200 000 rows) and now it s ok

[fitri musa]

i'm using prestashop 1.5.3.1 and i have problems with specific price. when i set specific price a product, the price doesnt change. it used to be ok, now it failed to change the price based on my setting. please help. Ive been trying to solve this problem since months ago.. and now i 'm stuck. i cant run promotion due to that problem.. pls help. tq

[tatoucompris]

Hi all

 

This table is growing too fast due to duplicate entries. I've noticed each time ONE product is updated, all reduction prices for ALL products are inserted into database.

 

The solution I found is to add a constraint :

 

'ALTER TABLE ' . _DB_PREFIX_ . 'specific_price ADD CONSTRAINT Reduction_Per_Product_Unique UNIQUE (id_specific_price_rule, id_product, reduction, reduction_type);'

 

You should probably add more fields in the constraint regarding the duplicate entries found.

And Please, backup your database first !

 

Enjoy

 

Hope that the Prestashop Team will take care of this major bug

Edited May 29, 2013 by tatoucompris (see edit history)

[sworld]

Benjamin Utterback, does the above solution can be applied without risk to database.

 

If yes Step by Step procedure on doing the same.

[mixlab]

I'm using Prestashop v 1.5.3.1. and also have a problem with this one.

 

I've got blank page when this happened, the error was in db.php

I've tried to set ini_set('memory_limit','512M'); the page was/loaded ok, but gave a problem with mysql server.

 

But since i truncate the specific price and set back the memory_limit to 48M, The page loads faster.

 

When you truncate the specific_price and you have some catalog price rules (for example sale price) on your shop, you have to save it once again.

 

I think the problem is that the catalog price rules (for example sale price) insert new prices into the specific price table, event the product is not in the category of sale price (catalog price rules).

If you have many combination price on the shop or products with combination price this could be a problem during saving a product price, price combination, customization.

[bagriydn]

I have prestashop 1.5.4.1

I also have this problem too.

Each time ONE product is updated, all reduction prices from table "ps_specific_price_rule" are inserted and duplicated into database.

I have 4000 product with reduction from table "ps_specific_price_rule" so when I update for example 10 product my table "ps_specific_price" is grow on 40000. Now I truncate table "ps_specific_price_rule" and check for solution...

Edited July 18, 2013 by bagriydn (see edit history)

[benjamin utterback]

I have prestashop 1.5.4.1

I also have this problem too.

Each time ONE product is updated, all reduction prices from table "ps_specific_price_rule" are inserted and duplicated into database.

I have 4000 product with reduction from table "ps_specific_price_rule" so when I update for example 10 product my table "ps_specific_price" is grow on 40000. Now I truncate table "ps_specific_price_rule" and check for solution...

 

When you say you have this problem, can you be more specific. Is your store moving slow?

[bagriydn]

When you say you have this problem, can you be more specific. Is your store moving slow?

My store is slow only when I save or update the product and it is sad for me... not critically but...

And sometime when I save-update product I got error and MySql is reboot (as I understand it).

 

Now when I truncate "ps_specific_price_rule" saving and updating product little faster. I truncate "ps_specific_price_rule" only today so I need some time to looking on my store to say for sure.

 

Other parts of the store loadin fast usaly 0,5-1,5 second. Saving-updating product now around 6-10 second. Before I truncate "ps_specific_price_rule" I have 15-25+ second usually 20+ second.

 

"Catalog price rules" is very comfortably but I cant use it while this issue is not fix.

PS: sorry for my english

[bagriydn]

I update me prestashop store many times so today I find in my database this:

UNIQUE KEY `id_product_2` (`id_product`,`id_shop`,`id_shop_group`,`id_currency`,`id_country`,`id_group`,`id_customer`,`id_product_attribute`,`from_quantity`,`from`,`to`),

And when I update-save_new product I have mesql error "duplicate KEY `id_product_2` ...." (only when active debug mode).

In fresh install prestashop 1.5.4.1 we dont have UNIQUE KEY `id_product_2` so we dont have error but we have duplicating product in "ps_specific_price" who have specific price from "ps_specific_price_rule".

When you say you have this problem, can you be more specific. Is your store moving slow?

Now I am sure store slow only when update-save_new product. Now when I truncate "ps_specific_price_rule" saving-new and updating product little faster.

[sworld]

I am using PS 1.5.4 and many of us have this Specific Price Growing when a product is added or even updated.

 

Yes the Site is running Slow, Now when I truncate "ps_specific_price" saving-new and updating product little faster.

[FredCD]

I just can't believe it !!!

I hope Benjamin Utterback will read this comment.

So far i have considered that Prestashop is not perfect and minor bugs are acceptable as the solution is freely provided.

 

THAT is a major huge bug known for months (well at least first posts and comments about it are pretty old).

But no fix has been released and last major version keeps making specific price table growing to 200 Mo because Prestshop team think adding "Prestashop Expertise" bs is more important than providing a solution to a problem that can make websites really slow.

 

Now what for me ? I have a database i cannot export and correct in 1 time because it is way too big.

I have a table i cannot truncate as it contains "good" data and PS-created garbage...

 

I think all i have to do is to laugh when i read that Benjamin answer bagriydn on post #26 "When you say you have this problem, can you be more specific. Is your store moving slow? " .

 

Well so this this guy explains EXACTLY what is going wrong and the answer is "Oh yeah your database is corrupted due to one of our bugs but... is that OK however ?" I think he should has added "Do you like our new useless and invading Prestashop Expertise ? Hum see it tells you that your are a great user because you created 2 shipping methods and 3 groups of customers".

[[email protected]]

The Size of the TABLE - Specific Price is 1.0 MB Now from 664 KB in 2 Days

 

I have EMPTY and applied the Discount once again

 

Hi Sworld,

Did the size increase without you making updates on the products? .i.e. it increased by itself?

Thanks

Ian

[dfajardor]

I have the same problem. My specific_price table is growing exponentially when I insert new articles.

 

Has anyone found a solution or can give me a clue about how to solve it?

[cheekymang]

I have the problem too on 1.5.3.1

From what I can see the only way out is to truncate ps_specific_price and ps_specific_price_rule

 

If I reimport my csv it never overwrites the old discount (I kinda get that not working to be fair) but the big problem I reckon is that does not make the new discount you put in the current one you want to use.

If you put the same discount amount figure I'm reading it still makes a record.

 

Everything on the store I work on has a discounted price so this is hard.

Looks like for now I must truncate ps_specific_price and ps_specific_price_rule every time before importing my csv daily. 

 

Or has this been fixed?

Edited October 7, 2013 by cheekymang (see edit history)

[FredCD]

I'm back to this one for 2 reasons :

1. As we all can see PS Team doesn't care at all, they never gave any explanations nor solutions and many people keep "garbaging" their databases because of this huge bug

2. Give my feedback on what i think is the problem and how tyo solve it

 

Here is a little review of what i discovered and how i stopped the exponential growth.

 

In my experience the problem was due to "mutli-conditional" prices rules.

For example when i created a specific price rule saying that if a product is in category 1 OR category 2 OR category 3 OR ... category 9 the discount was duplicated x times for same product.

Ex : Create a rule that give 20% discount to product which are part of category 1 or .... and save.

Go to the datasheet of a "concerned" product and check the "Prices" tab.

And see that for no reason the same rule is duplicated many times (for instance you have way to many lines saying "x quantity  give 20% discount").

May be you are thinking that is because the product is associated with more than one category but that is not even the explanation !!!.

 

In conclusion : DO NOT use "mutli-conditional"  rules.

The "and" parameter works fine (give a discount if the product is this brand AND this category) but the "or" paramater is totally bugged.

[sworld]

Same Problem, Benjamin Please Help !

[Gate404]

hello, i have the same problem.

 

can we make a cron to clean the ps_specific_price table ? 

 

Fred

[wouterb038]

I have the same problem!!!! the tabel ps_specific_price is growing en growing everday.... after 2 days the webshop give a blank page en database is stop working because its to big@!!!!!! PRESTASHOP PLEASE HELP!!!!!

[tatoucompris]

I have the same problem!!!! the tabel ps_specific_price is growing en growing everday.... after 2 days the webshop give a blank page en database is stop working because its to big@!!!!!! PRESTASHOP PLEASE HELP!!!!!

Hi wouterb038,

 

Change all occurence of MyISam to InnoDB in your mysql dump. Then drop your database and restore it, then apply the constraint I gave :

'ALTER TABLE ' . _DB_PREFIX_ . 'specific_price ADD CONSTRAINT Reduction_Per_Product_Unique UNIQUE (id_specific_price_rule, id_product, reduction, reduction_type);'

 

This method prevent prestashop code to duplicate entries in ps_specific_price.

 

Regards,

 

Tatoucompris

[vekia]

tatoucompris - isn't this field by default unique ?

[tatoucompris]

tatoucompris - isn't this field by default unique ?

 

Hi Vekia,

 

Which field ? We are talking about indexes...

Just make a show indexes from ps_specific_price

 

There is no index on id_specific_price_rule, reduction, reduction_type to prevent duplicate entries, the problem which is encountered here.

 

So, the suggested method fixed this issue using MySQL but the fix should be provided ideally by the code...

 

Regards

[LBPC]

Hi, same problem here, v1.5.5.0.  

 

If the above works, could someone explain the fix it step by step pls?

Edited January 22, 2014 by LBPC (see edit history)

[bretweb]

Hello

 

I have the same problem for our prestashop 1.5.4.1 

 

We have  a sale condition on quite all  website till february !

 

the specific-price table is now  23MO !!!!  for arroud 3000 product on line... and it's  going down !

 

It's increadible  nobody at Prestashop found a code solution !

 

How to resolve ?

Please men !!!!

thank you

[vekia]

you've got probably a lot of price reductions (or you've got these reductions before)

it's normal.

If you don't use these reductions anymore, why not to just... remove them?

[LBPC]

you've got probably a lot of price reductions (or you've got these reductions before)

it's normal.

If you don't use these reductions anymore, why not to just... remove them?

No, that's the error - I only have 2.  All the others listed are duplicates.

 

As someone else said, if you open the rule, then save it (without making any changes), then return to the product price page, you'll see that all the duplicate reductions are gone.

[bretweb]

hello

 

personnaly we are on Sale period and  have made a 10% on quite all categories product  just summer categories  are not reducting

www.caillou-flacoti.com

 

thank you

Edited January 23, 2014 by bretweb (see edit history)

[manel10_12]

Hi guys, I had to fix right now this issue from a customer who upgraded his prestashop 1.4 to 1.5.0.1.

 

here is the (dirty) solution:

 

go to overrides/classes/SpecificPriceRule.php

 

you'll see this:

 

<?php

 

class SpecificPriceRule extends SpecificPriceRuleCore

{

 

}

 

just modify it and make it looks like this:

 

class SpecificPriceRule extends SpecificPriceRuleCore

{

   public static function applyAllRules($products = false)

   {

 

   }

}

 

Hope it will help you guys. But anyways, that's just a dirty patch. But for now, if you want to add lots of attributes and other stuff, without having the duplicate key or waiting for long, do this.

 

Manel

 

Hostienda.com

[infinityl]

DELETE p
FROM ps_specific_price p JOIN
(
SELECT id_product, MAX(id_specific_price) id_specific_price
FROM ps_specific_price
GROUP BY id_product
) d
ON p.id_product = d.id_product
AND p.id_specific_price <> d.id_specific_price;

 

 

[vekia]

hello

this sql query removes unused special prices definitions?

[infinityl]

hello

this sql query removes unused special prices definitions?

That query removes all the specific prices for a product BUT the last (max id)

 

If you have a price rule and a specific price for a product you have to take care.

[Alex-on2]

The problem happens if you have price rules with several conditions
 
I have more 700000 rows in specific_price with 3 price rules and 3000 products in the store.

 

 

This problem was solved in the version 1.6 ???

Edited July 24, 2015 by Alex-on2 (see edit history)

[afshop]

The problem happens if you have price rules with several conditions

 

I have more 700000 rows in specific_price with 3 price rules and 3000 products in the store.

 

 

This problem was solved in the version 1.6 ???

 

 

Hi, 

 

Unfortunately the problem is not solved in 1.6

 

Someone found a solution?

 

thanks

Angela

[biello]

Hi, 

 

Unfortunately the problem is not solved in 1.6

 

Someone found a solution?

 

thanks

Angela

 

 

I have the same problem.

[gabba]

Same problem here on presta 1.5, ps_specific_prices grows uncontrollably to the point of completely maxing out any server the site is placed on up to 13254219 rows currently and then lookups max out literally any CPU power, or the table corrupts.

 

Seems a simple enough bug so not sure why this thread hasn't been responded to by prestashop, or why its not been fixed in 1.6, or has it?

[Arnaud Drieux]

Hello. 

 

I post here like you all because one of our shop has + 6 000 000 rows in table and  + 16 000 products.

We have disable the block-cart module to improve the global speed of website.  if one customer have more 50 products in his cart the shopping-cart page will get more than 20seconds to show.

We have no idea what to do more  to make the website fastest...

Like Gabba, maybe Prestashop can respond because this is a severe problem