Problem with ps_product_download table

[a.nakos]

Hello everyone,

I just started a website with 2000 products. In the database there is a table called ps_product_download that is enormous. As far as i can tell it must contain downloadable products. All though i have no products that are downloadable the size of this table is really large (approx. 180MB) and all the database is 220MB. Can somenone tell me if this table is required or i can truncate it with no problems.

 

Thanx in advance.

[NemoPS]

This is so weird. You have no downloadable products? Are yours set as virtual perhaps? Can you check the contents of that table and see what's in there?

[a.nakos]

I fixed the problem. At the beginning i accidently created a few products that where downloadable. I created them with the copy option. Although i deleted them the table ps_product_download still had records. 4 products had over 2 million records in the table. I deleted them through MySql query and that fixed the problem. Thanks a lot Nemo1 for the fast reply.

[ilovekutchi.com]

Same problem here. There might be some weird bug in a php that makes ps 1.5.6 to constantly add rows to this table...

 

Mine has 600mb and I cannot make a backup before deleting most of the lines (I pretend to leave only the "active" ones but I'm afraid of deleting them without backup).

[Logical IT]

Had the same issue today with one of my client 1.5.6 sites. Got a warning email from my web host company.  The ps_product_download table was over 150 Mb in size. Really odd.

 

These are the steps I did to purge the table:

1) did a full database backup

2) exported all product_download records where active = 1

3) truncated the product_download table

4) imported all active records back into product_download table

 

Hopefully issue was once off and it doesn't come back! I will investigate further if issue comes back.

[vekia]

what is the size of your table after this clean up ?

[Natan Verm]

my ps_product_download table contains 2800000 records

all records have field "active"=0

 

allthought their are "downloadable" files.

 

Can this table be truncated without corrupting running/working prestashop ?

 

Thanks,

Natan

Edited August 25, 2014 by Natan Verm (see edit history)

[NemoPS]

Well I guess you can give it a try, make a backup of the table and truncate all inactive records, then check if you have any issue

[Natan Verm]

Thanks, before I give it a try. Can you tell me if I can also empty the folder /downloads/ ?

 

This folder contains 4455 files.

 

Regards,

Natan

[vekia]

hello

you have some files associated with products? as a virtual product, as an attachment for product etc. ?

[Natan Verm]

no files associated. (the associations didn't exsist anymore)

 

I deleted all records and all files.

 

And uploaded the file associations again.

 

Everything okay for me.

 

 

How could this (loop?) happen ???

 

 

Thanks for the help.

[vekia]

i ahve no idea, perhaps it was related to some permission issues and prestashop script couldn't remove the files when you removed associations / files in back office

[RobS]

I am hoping this is the right place to post this issue. Upon monitoring our account's mysql usage, I noticed multiple queries of the following form being invoked when the script, /home3/hgeem/public_html/index.php was being executed:

 

SELECT `display_filename` FROM `ps_product_download` WHERE `filename` = 'a80863632311e78081ad5ebdd352eb099b568bdc' LIMIT 1

 

Each query of this form was traversing over 5 million rows in the ps_product_download table of our hgeem_prst1 database, thereby taking a massive amount of time to complete its execution. To help with this, I added an index to the ps_product_download table. Observe the before and after.

 

Before:

 

mysql> EXPLAIN SELECT `display_filename`

-> FROM `ps_product_download`

-> WHERE `filename` = 'a80863632311e78081ad5ebdd352eb099b568bdc' LIMIT 1 ;

+----+-------------+---------------------+------+---------------+------+---------+------+---------+-------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+---------------------+------+---------------+------+---------+------+---------+-------------+

| 1 | SIMPLE | ps_product_download | ALL | NULL | NULL | NULL | NULL | 5188032 | Using where |

+----+-------------+---------------------+------+---------------+------+---------+------+---------+-------------+

1 row in set (0.01 sec)

 

After:

 

mysql> EXPLAIN SELECT `display_filename` FROM `ps_product_download` WHERE `filename` = 'a80863632311e78081ad5ebdd352eb099b568bdc' LIMIT 1;

+----+-------------+---------------------+------+---------------+------+---------+-------+------+-------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+---------------------+------+---------------+------+---------+-------+------+-------------+

| 1 | SIMPLE | ps_product_download | ref | i1 | i1 | 768 | const | 1 | Using where |

+----+-------------+---------------------+------+---------------+------+---------+-------+------+-------------+

1 row in set (0.00 sec)

 

Notice that the total rows traversed by the sample query dropped from 5188032 to just 1, indicating a marked improvement in its overall efficiency.

 

My problem now, however, is something else. Looking over the structure of the ps_product_download table, I observed the following fields:

 

+---------------------+---------------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+---------------------+---------------------+------+-----+---------+----------------+

| id_product_download | int(10) unsigned | NO | PRI | NULL | auto_increment |

| id_product | int(10) unsigned | NO | MUL | NULL | |

| display_filename | varchar(255) | YES | | NULL | |

| filename | varchar(255) | YES | MUL | NULL | |

| date_add | datetime | NO | | NULL | |

| date_expiration | datetime | YES | | NULL | |

| nb_days_accessible | int(10) unsigned | YES | | NULL | |

| nb_downloadable | int(10) unsigned | YES | | 1 | |

| active | tinyint(1) unsigned | NO | | 1 | |

| is_shareable | tinyint(1) unsigned | NO | | 0 | |

+---------------------+---------------------+------+-----+---------+----------------+

10 rows in set (0.00 sec)

 

I'm not 100% on this but it looks like this table is tracking statistical information relating to our site's product downloads. Likely any time a download is initiated on our site, one or more rows are being added to this database table resulting in the heavy accumulation of data. The following prestashop forum thread also seems to agree with this: http://www.prestashop.com/forums/topic/310139-problem-with-ps-product-download-table/

 

I tried to empty this database table and immediately noticed a problem. I really hoped that emptying the ps_product_download would resolve this issue, but unfortunately it looks like this table also affects our visitors' ability to download their purchases from within their own user accounts on our shop. It also prevented emails from being sent to our customers with their attached download links. I had to restore from backup and now everything is back to the way it was. 

 

Does anyone know how to empty the ps_product_download table without this problem occurring? Thanks

 

[musicmaster]

I'm not 100% on this but it looks like this table is tracking statistical information relating to our site's product downloads. Likely any time a download is initiated on our site, one or more rows are being added to this database table resulting in the heavy accumulation of data. The following prestashop forum thread also seems to agree with this: http://www.prestashop.com/forums/topic/310139-problem-with-ps-product-download-table/

Did you read the previous posts?

 

They suggest that you should try a mysql command like "delete from ps_product_download where active=0". Nobody seems to be bothered about statistics...

[RobS]

May I ask if you are referring to this below? Also I think I got lost by your meaning behind nobody seems to be bothered about statistics? I did try this but I will try it again maybe I missed something? Thanks

 

These are the steps I did to purge the table:

1) did a full database backup

2) exported all product_download records where active = 1

3) truncated the product_download table

4) imported all active records back into product_download table

[musicmaster]

Posts #3, #4 and #5 suggest that there is some bug in 1.5 that multiplies records. It also suggests that most of these are inactive and can be deleted. 

 

As you seem to have done that (your and my method have the same effect) you should now have only as much records as you have products with downloads. Please correct me if I am wrong.

[RobS]

We just finished running the query in which all records in the ps_product_download table whose "active" field was set to 0 were purged as instructed.

 

This eliminated 5148033 database rows resulting in the ps_product_download table becoming much more manageable in size (currently contains 459 rows). Thanks a lot