Jump to content

Problem with specific price table in database growing too big.


Recommended Posts

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

  • 1 month later...

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

Share this post


Link to post
Share on other sites

  • 4 weeks later...

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

  • 2 months later...

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

Share this post


Link to post
Share on other sites

  • 2 weeks later...
  • 2 weeks later...

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

Share this post


Link to post
Share on other sites

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 by tatoucompris (see edit history)

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

  • 1 month later...

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 by bagriydn (see edit history)

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

  • 3 weeks later...

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.

Share this post


Link to post
Share on other sites

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".

Share this post


Link to post
Share on other sites

  • 1 month later...

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 by cheekymang (see edit history)

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

  • 4 weeks later...
  • 2 months later...

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 [email protected]!!!!!! 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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

  • 2 weeks later...

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

post-88855-0-99588000-1390423129_thumb.jpg

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

  • 1 month later...

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

Share this post


Link to post
Share on other sites

  • 4 months later...

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;

 

 

;)

Share this post


Link to post
Share on other sites

  • 2 weeks later...

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.

Share this post


Link to post
Share on other sites

  • 11 months later...
  • 3 months later...

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

Share this post


Link to post
Share on other sites

  • 2 weeks later...
  • 4 months later...

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?

Share this post


Link to post
Share on other sites

  • 1 month later...

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

Share this post


Link to post
Share on other sites

 Share

×
×
  • Create New...

Important Information

Cookies ensure the smooth running of our services. Using these, you accept the use of cookies. Learn More