Magento systematically deleting all products one by one, on its own?

[RossCC]

We have a client with a new Magento 2.2.1 store with approx 35,000 products.

We are trying to identify the cause of a very odd problem.  For weeks now, they would notice at some point during the day, that several thousand products had disappeared from the store, and were disappearing at a rate of approx 1 per second.  When this behaviour is happening, I can run SELECT COUNT(*) FROM o4e_mage.catalog_product_entity every second, and watch the products disappearing.

Client has blamed suppliers of modules (MagePlaza, M2ePro) who have said it's impossible.  Another Magento 'expert' concluded that it was only possible if someone had hacked the site and was doing this from the backend.  We disagreed, and maintained that it felt like a Magento (or module) bug.

Today, I enabled the MySQL query log for all queries, and we identified the trigger for the problem, and can watch the problem happen in real time...

This problem is easily repeatable on the client's store, and they demonstrated it today, with shocking results...

To start, the client navigates to Catalog -> Products.  They select a bunch of products, eg. 10 from any page, and click Actions -> Delete.  If you're lucky, it will delete your selected products.

If you're unlucky, and 75% of the time this is what happens, the back end will become unresponsive and the user thinks it's crashed, and will restart their browser and carry on about their business.

In the background though, Magento is one by one deleting every trace of every single product from every product-related table.  Go away and make a coffee, and a few hundred products are gone.  Leave it a few hours and thousands have gone.  It seems to start from the last ID that you actually wanted to deleted, then increments the product ID by 1, and runs again, and again...

We've lost count of the times the client has had to restore her database and lose order history to fix this now, but at least we have linked it to the action of deleting a product, and have logged all the related queries.

Here is a snippet of the delete queries that we see on MySQL, note none of these products were selected for deletion:

15:31:34	363994	Query	DELETE FROM `catalog_product_entity` WHERE (entity_id = '17554')
15:31:39	363994	Query	DELETE FROM `catalog_product_entity` WHERE (entity_id = '17555')
15:31:43	363994	Query	DELETE FROM `catalog_product_entity` WHERE (entity_id = '17592')
15:31:48	363994	Query	DELETE FROM `catalog_product_entity` WHERE (entity_id = '17593')
15:31:52	363994	Query	DELETE FROM `catalog_product_entity` WHERE (entity_id = '17594')
15:31:57	363994	Query	DELETE FROM `catalog_product_entity` WHERE (entity_id = '17608')
15:32:01	363994	Query	DELETE FROM `catalog_product_entity` WHERE (entity_id = '17644')
15:32:06	363994	Query	DELETE FROM `catalog_product_entity` WHERE (entity_id = '17646')
15:32:10	363994	Query	DELETE FROM `catalog_product_entity` WHERE (entity_id = '17807')
15:32:11	363994	Query	DELETE FROM `catalog_product_entity` WHERE (entity_id = '17808')
15:32:11	363994	Query	DELETE FROM `catalog_product_entity` WHERE (entity_id = '17809')
15:32:11	363994	Query	DELETE FROM `catalog_product_entity` WHERE (entity_id = '17810')
15:32:12	363994	Query	DELETE FROM `catalog_product_entity` WHERE (entity_id = '17811')
15:32:12	363994	Query	DELETE FROM `catalog_product_entity` WHERE (entity_id = '17812')
15:32:12	363994	Query	DELETE FROM `catalog_product_entity` WHERE (entity_id = '17813')
15:32:12	363994	Query	DELETE FROM `catalog_product_entity` WHERE (entity_id = '17814')

For anyone interested, the full query log for that MySQL thread is available here, it's 2.8MB uncompressed CSV file, with approx 7000 rows:

https://computercentric.co.uk/resources/query_log.zip

Note that the activity was stopped after a minute or so (by restarting MySQL or killing the thread) in order to stop every product disappearing.  Within the 7000 rows I can identify 50 products being deleted, along with all associated data on the same product ID.   Only 5 were selected for deletion.  (Aside from the obvious problem of deleting products we want to keep, why the flip does Magento need to run 7000 queries to delete 50 products??!  That's a question for another day...)

It is unbelievable to me (software developer hat on), that code can exist in either Magento core or a module, that can execute such a process without a failsafe in place.

Can anyone take on the challenge of identifying the cause of this bug?  If not, can anyone recommend where we should take this?  Disclaimer:  I am not a Magento expert, but I am pretty sure this ain't right.

Thanks in advance to anyone brave enough.

Ross

[GauravMehta04]

@RossCC Very bad to hear this, hoping that you'll get a solution of this very soon. However, there are few things which I would like to include:

Possible ways to delete products are from the backend (Product grid) MySql (delete query or trigger), PHP script or CRON script.

Until you sure please stop access of admin (if shared with multiple users), second disabled all CRONs and analysis / compare Magento document root files with Vanilla Magento and your project files.  the last thing, to check is there any trigger set on MySQL.

Hope this helps, cheers!

P.S - Mostly, none of the extension merchants adds such scripts to delete products as per your behavior; but it might be subjective. 

[RossCC]

Hi there - yes these queries are being individually executed by a script, presumably Magento core, they are not the results of a MySQL trigger.

All crons are disabled, and the problem only occurs when you attempt to delete a handful of products.

For anyone reading this may be related to this bug:

https://github.com/magento/magento2/pull/15985

[RossCC]

Further to above, it appears a fix is available for this in the form of a patch, as this has been identified as a critical issue.  Just trying to figure out how to implement this...

[Meles1]

This is NOT a bug.

Magento have a VERY big problem with their testing methodolgy.

The future maxima is greater than the past maxima.

You do NOT test/calibrate new releases of established software against a PAST benchmark, you test against a HIGHER benchmark thereby revealving hidden problems. 

This has caused me SERIOUS issues.

[Sunil Patel]

Hello @RossCC

diff --git a/vendor/magento/module-catalog/etc/di.xml b/vendor/magento/module-catalog/etc/di.xml
index 9f1fb02..b38d31b 100644
--- a/vendor/magento/module-catalog/etc/di.xml
+++ b/vendor/magento/module-catalog/etc/di.xml
@@ -1057,4 +1057,10 @@
             <argument name="productRepository" xsi:type="object">Magento\Catalog\Api\ProductRepositoryInterface\Proxy</argument>
         </arguments>
     </type>
+
+       <type name="Magento\Catalog\Controller\Adminhtml\Product\MassDelete">
+        <arguments>
+            <argument name="filter" xsi:type="object">Magento\Ui\Component\MassAction\DeleteFilter</argument>
+        </arguments>
+    </type>
 </config>
diff --git a/vendor/magento/module-cms/etc/adminhtml/di.xml b/vendor/magento/module-cms/etc/adminhtml/di.xml
index 98a8ff6..9623cf1 100644
--- a/vendor/magento/module-cms/etc/adminhtml/di.xml
+++ b/vendor/magento/module-cms/etc/adminhtml/di.xml
@@ -12,4 +12,16 @@
             <argument name="frontendUrlBuilder" xsi:type="object">Magento\Framework\Url</argument>
         </arguments>
     </type>
+
+    <type name="Magento\Cms\Controller\Adminhtml\Block\MassDelete">
+        <arguments>
+            <argument name="filter" xsi:type="object">Magento\Ui\Component\MassAction\DeleteFilter</argument>
+        </arguments>
+    </type>
+
+    <type name="Magento\Cms\Controller\Adminhtml\Page\MassDelete">
+        <arguments>
+            <argument name="filter" xsi:type="object">Magento\Ui\Component\MassAction\DeleteFilter</argument>
+        </arguments>
+    </type>
 </config>
diff --git a/vendor/magento/module-customer/etc/adminhtml/di.xml b/vendor/magento/module-customer/etc/adminhtml/di.xml
index 575fd63..e5a17ed 100644
--- a/vendor/magento/module-customer/etc/adminhtml/di.xml
+++ b/vendor/magento/module-customer/etc/adminhtml/di.xml
@@ -23,4 +23,10 @@
             </argument>
         </arguments>
     </type>
+
+    <type name="Magento\Customer\Controller\Adminhtml\Index\MassDelete">
+        <arguments>
+            <argument name="filter" xsi:type="object">Magento\Ui\Component\MassAction\DeleteFilter</argument>
+        </arguments>
+    </type>
 </config>
diff --git a/vendor/magento/module-ui/Component/MassAction/Filter.php b/vendor/magento/module-ui/Component/MassAction/Filter.php
index 6877303..918e772 100644
--- a/vendor/magento/module-ui/Component/MassAction/Filter.php
+++ b/vendor/magento/module-ui/Component/MassAction/Filter.php
@@ -47,20 +47,27 @@ class Filter
      * @var DataProviderInterface
      */
     private $dataProvider;
+    /**
+     * @var bool
+     */
+    private $avoidEmptyFilter;

     /**
      * @param UiComponentFactory $factory
      * @param RequestInterface $request
      * @param FilterBuilder $filterBuilder
+     * @param bool $avoidEmptyFilter
      */
     public function __construct(
         UiComponentFactory $factory,
         RequestInterface $request,
-        FilterBuilder $filterBuilder
+        FilterBuilder $filterBuilder,
+        $avoidEmptyFilter = false
     ) {
         $this->factory = $factory;
         $this->request = $request;
         $this->filterBuilder = $filterBuilder;
+        $this->avoidEmptyFilter = $avoidEmptyFilter;
     }

     /**
@@ -100,10 +107,10 @@ class Filter
         }
         /** @var \Magento\Customer\Model\ResourceModel\Customer\Collection $collection */
         $idsArray = $this->getFilterIds();
-        if (!empty($idsArray)) {
+        if (!empty($idsArray) || $this->avoidEmptyFilter) {
             $collection->addFieldToFilter(
                 $collection->getIdFieldName(),
-                ['in' => $idsArray]
+                ['in' => (array) $idsArray]
             );
         }
         return $collection;
diff --git a/vendor/magento/module-ui/etc/adminhtml/di.xml b/vendor/magento/module-ui/etc/adminhtml/di.xml
index e220581..82acb54 100644
--- a/vendor/magento/module-ui/etc/adminhtml/di.xml
+++ b/vendor/magento/module-ui/etc/adminhtml/di.xml
@@ -45,4 +45,10 @@
             </argument>
         </arguments>
     </type>
+
+    <virtualType name="Magento\Ui\Component\MassAction\DeleteFilter" type="Magento\Ui\Component\MassAction\Filter">
+        <arguments>
+            <argument name="avoidEmptyFilter" xsi:type="boolean">true</argument>
+        </arguments>
+    </virtualType>
 </config>
diff --git a/vendor/magento/module-search/etc/adminhtml/di.xml b/vendor/magento/module-search/etc/adminhtml/di.xml
new file mode 100644
index 0000000..117cac6
--- /dev/null
+++ b/vendor/magento/module-search/etc/adminhtml/di.xml
@@ -0,0 +1,14 @@
+<?xml version="1.0"?>
+<!--
+/**
+* Copyright © Magento, Inc. All rights reserved.
+* See COPYING.txt for license details.
+*/
+-->
+<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:ObjectManager/etc/config.xsd">
+    <type name="Magento\Search\Controller\Adminhtml\Synonyms\MassDelete">
+        <arguments>
+            <argument name="filter" xsi:type="object">Magento\Ui\Component\MassAction\DeleteFilter</argument>
+        </arguments>
+    </type>
+</config>
\ No newline at end of file

create patch.sh file into your root Magento

and add below thing into composer json

 "scripts": {
        "post-install-cmd": [
            "git apply patch.sh"
        ],
        "post-update-cmd": [
            "git apply patch.sh"
    }

so

when you run composer update or install it automatically apply that patch

hope it will help you

if works then mark as solution

[mhawks]

I am the owner of a Magento site that we just upgraded to Magento 2 on 1/2/2019 - we had this mass delete happen last night.  It is just awful.  I selected about 20 products to delete - it looked like it hadn't "taken" so I selected them again and hit delete a second time - and this started a cascade that deleted my entire database.  From 5pm to 7pm last night as I was returning back from the office to home, my team called to say that all of the products were disappearing from the backend until there were 0 products left.  We had over 12,000 products -as we are a large home decor website.   This is a total and complete nightmare.  We have had nothing but problems with Magento 2 - literally daily putting out fires. Is there an official Magento fix for this.  It's outrageous. 

[Sunil Patel]

@mhawks 

please check below link for same

https://github.com/magento/magento2/pull/15985

if works then mark as solution

[mhawks]

Where is the fix?
We lost 12,000 products last night

[Sunil Patel]

Hello @mhawks 

you need to ask to your hosting provider and need to setup previous db.

whatever last comment i did it is fixed issue so it will not happen again