All products disappeared after using backend selection box and drop down disable 500 error

[sparxx]

2.2.2 version

pedicurespasuperstore.com

I had 10 tabs open revising products SEO and titles and descriptions. I noticed 2 products had the same name. I checked the box drop down disable. All loading tabs 500 error. There are now no products in the backend product section but the categories show how many products there are. The database has all the products. I ssh mass cache flush and mass reindexed. Obviously the product section is bugged with a processing product. There are 2000 products in this store. This was a site that had been migrated before a year ago. Thoughts? Help appreciated.

[Tarandeep Singh]

@sparxxSeems like instead of selecting the particular product to disable/delete you did select all. I am not sure you changed the status or delete. I doubt you deleted as you are saying that you can see the product in database.

In this case you may have bulk disabled them. From the product list screen you must be able to Enable them again.

Let know if you still face the issue. Some screenshots will help in resolving the issue quicker.

Problem solved? Please give 'Kudos' and accept 'Answer as Solution'.

[sparxx]

sorry even if i did there are 2000 products and only 200 to a page. No products are shown in the products category. At this point it will only be able to corrected in the db i believe as the backend is kindof useless.

[sparxx]

so i guess at the db level what should i be looking for to correct this and which section of the db would it be in. OR... is there something i am missing in this troubleshooting thought process.

[Tarandeep Singh]

If those product are still in database then try the below query to see if they are there. If you do see them then you can easily update them from Disabled to Enabled.

SELECT entity_id, store_id FROM catalog_product_entity_int
WHERE value = 2 AND attribute_id = (
  SELECT attribute_id FROM eav_attribute
  WHERE entity_type_id=4 AND attribute_code='status'
)

Value =1 (Enabled)

Value = 2 (Disabled)

More attribute can be seens like this:

SELECT t_int.entity_id, t_int.store_id, t_varchar.value FROM catalog_product_entity_int AS t_int
JOIN catalog_product_entity_varchar as t_varchar ON t_varchar.entity_id = t_int.entity_id 
WHERE t_int.value = 2 AND t_int.attribute_id = (
    SELECT attribute_id FROM eav_attribute
    WHERE entity_type_id=4 AND attribute_code='status'
) AND t_varchar.attribute_id = (
    SELECT attribute_id FROM eav_attribute
    WHERE entity_type_id=4 AND attribute_code='name'
)

Problem solved? Please give 'Kudos' and accept 'Answer as Solution'.

[sparxx]

Both returned

MySQL returned an empty result set i.e. zero rows. Query took 0.0010 seconds.

[sparxx]

ran

SELECT entity_id, store_id FROM db_catalog_product_entity_int WHERE value = 2 

20486 results

SELECT entity_id, store_id FROM db_catalog_product_entity_int WHERE value = 1 

19948 results

[Tarandeep Singh]

@sparxxYes table has records for many other attributes as well. You can find the attribute ID from Magento admin. Login to admin -> Stores -> Attributes -> Product.

In Attribute Code column search for status. Click on the row for status. In the IRL you'll be able to see the ID for that attribute. Use the same in your query to see what all products are disabled. You can enabled them by setting the value to 1.

Problem solved? Please give 'Kudos' and accept 'Answer as Solution'.