SOLVED - All products vanished - Prestashop 1.6.13

[Colin Earle]

I've had my shop up and running successfully for a few months now without any problems, till now.

Something happened, not sure what, perhaps I clicked on something, anyway I've spend a few weeks trying to resolve this problem but got nowhere.

 

On the Catalogue ->Products screen, all my products vanished while I was entering a few new products. I can't find any filter that I may have accidentally clicked.

 

What I have found is:

Viewing the SQL via ">_" button, I see in the WHERE clause WHERE 1 AND sa.`active` = 0    

So I can only see inactive products. I've changed some products to inactive, and then they appear.

 

I am not using Multi Store mode (some discussions have indicated this may be the cause).

I've looked through the code and can't find where this filter is set.

 

How do I clear this filter?

 

 

Thanks,

Colin.

Edited August 31, 2017 by Colin Earle (see edit history)

[Scully]

You should post the FULL sql not only a little part. The alias "sa" in the sa.active might not be related to products but some other table.

Or simply run these two SQL and post the results here:
 

select count(id_product) from ps_product;

select count(id_product) from ps_product_shop;

 

This will give us the number of rows in the two most imortant tables.

[Colin Earle]

My bad, I meant to paste this at the end.

 

SELECT a.`id_product`, b.`name` AS `name`, `reference`, a.`price` AS `price`, sa.`active` AS `active` , shop.`name` AS `shopname`, a.`id_shop_default`, image_shop.`id_image` AS `id_image`, cl.`name` AS `name_category`, sa.`price`, 0 AS `price_final`, a.`is_virtual`, pd.`nb_downloadable`, sav.`quantity` AS `sav_quantity`, sa.`active`, IF(sav.`quantity`<=0, 1, 0) AS `badge_danger` FROM `ps_product` a LEFT JOIN `ps_product_lang` b ON (b.`id_product` = a.`id_product` AND b.`id_lang` = 1 AND b.`id_shop` = 1) LEFT JOIN `ps_stock_available` sav ON (sav.`id_product` = a.`id_product` AND sav.`id_product_attribute` = 0 AND sav.id_shop_group = 1 AND sav.id_shop = 0 ) JOIN `ps_product_shop` sa ON (a.`id_product` = sa.`id_product` AND sa.id_shop = a.id_shop_default) LEFT JOIN `ps_category_lang` cl ON (sa.`id_category_default` = cl.`id_category` AND b.`id_lang` = cl.`id_lang` AND cl.id_shop = a.id_shop_default) LEFT JOIN `ps_shop` shop ON (shop.id_shop = a.id_shop_default) LEFT JOIN `ps_image_shop` image_shop ON (image_shop.`id_product` = a.`id_product` AND image_shop.`cover` = 1 AND image_shop.id_shop = a.id_shop_default) LEFT JOIN `ps_image` i ON (i.`id_image` = image_shop.`id_image`) LEFT JOIN `ps_product_download` pd ON (pd.`id_product` = a.`id_product` AND pd.`active` = 1) WHERE 1 AND sa.`active` = 0 ORDER BY a.`id_product` asc LIMIT 0, 50

 

Quote

select count(id_product) from ps_product;

124

select count(id_product) from ps_product_shop;

124

124

[Scully]

sa.`active` = 0

 

this would normally triggered by the active flag (green or red symbol) in the products list. Try to actively switching this value to active (green) or not active (red).

[Colin Earle]

This is filtering out all active products. My question is where and how is it set?

 

I have the same version of Prestashop installed in a VM for evaluation where the filtering clause

AND sa.`active` = 0 

does not appear. 

[Scully]

Please set intentionally the active selector to inactive (red) and back to active (green) and check the total counts of products. Report the counters.

[Colin Earle]

As no products are listed, I have to update the status directly in the database. Once inactive, the products appear as inactive. This does not change the products count.

Changing them back to active via clicking on the status makes them disappear again, this does not change the products count.

[Scully]

I spoke from the backoffice product list filter, not from database. If you current setup only shows active = 0 and you switch the filters to active, it show a other SQL statement. In other words: does toggle the list active yes / no filter change the corresponding SQL?

 

At this moment I am only refering to the SQL generated - not to the result it brings back.

Edited August 31, 2017 by Scully (see edit history)

[Colin Earle]

Where is the backoffice product list filter?  I can only find 'Filter by Category'

[Scully]

See my screenshot. It's the line right before the first product. There you can select specific products. One of the last ones is status which is a pull down menu.

 

Screenshot:

 

[Colin Earle]

I see. Quite clearly this is where the problem is then. My filter is gone!

Just need to work out how to activate it again

[Scully]

Check you error logfile. It will most likely give you the hit which file is missing or has a bad syntax.

[Colin Earle]

Checked the error_log in the main directory and in the admin directory, they are both empty

 

Here's what mine looks like

[Scully]

The webservers (apache or nginx) error log is usually not in the shop directory. And I haven't seen empty error logs so far.

[Colin Earle]

Solved. 

I created a new admin super user and logged in, the filter appeared.

Logged out, logged in as the original user, and the filter is back.

 

I'm still not sure what caused it, but it's possible that logging out and logging back in might have fixed it, or it could be cache or a cookie.

 

Thanks so much for your help Scully

[Scully]

You're welcome. And please add 'SOLVED' to the topic title.

Have a good day!