I am running Magento ver. 2.1.11 with over 36000 products in total, Max 2000 products per category. Caching enabled and using Varnish. Randomly and inconsistently I find that products completely disappear from categories. It could be any category at any time. This is my setup:

  • Use Flat Catalog Category = No
  • Use Flat Catalog Product = No
  • All indexers set to "Update on save"
  • I have noticed a custom extension that integrates products from external source and also reindexes the site every 5 mins.

Running php bin/magento indexer:reindex -vvv sometimes results in:

main.CRITICAL: PDOException: SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded

I've investigated the logs and there are some errors have occur regularly that could be indirectly related:

PDOException: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction 

and on product save (could be via admin or programmatically, not sure which):

main.CRITICAL: Exception: Asymmetric transaction rollback

I saw this issue which I thought could be the cause: https://github.com/magento/magento2/issues/8681 - where:

There is a constant variable called RANGE_CATEGORY_STEP, and it is set to 500. This variable is used when the function isRangingNeeded() returns true for an index (which is the default unless overridden). This effectively limits what gets put in the product category index table which in turn shows a limited result set of products on category pages.

I implemented this fix, but the problem still persists.

Regarding this custom extension, I have looked through the code, here is a snippet where products are being programmatically assigned to categories:

     * Associates a product to a category, and/or update's the product's
     * position within the category.
     * @param int $productId
     * @param int $categoryId
     * @param int $position
     * @return Api
    public function associateToCategory($productId, $categoryId, $position = null)
        $conn = $this->connection;
        $table = $this->getTableName('catalog_category_product');
        $sql = $conn->select()
            ->where('category_id = ?', $categoryId)
            ->where('product_id = ?', $productId)
        $row = $conn->fetchRow($sql);
        if (false === $row) {
            $bind = [
                'category_id' => $categoryId,
                'product_id' => $productId
            if (\is_numeric($position)) {
                $bind['position'] = $position;
            $conn->insert($table, $bind);
        } elseif (   \is_numeric($position)
                  && \is_array($row)
                  && $row['position'] != $position
        ) {
                ['position' => $position],
                $conn->quoteInto('entity_id = ?', $row['entity_id'])
        return $this;

It looks like it directly manipulates the db, perhaps this could cause some issues?

The workaround

So far is to clear the cache after the products are discovered to be missing.

  • Hi Chris, you say about this extension, i assume you need it but does this error occur even when the extension is disabled? Can you definitely pinpoint the error down to the extension, or is this just an educated guess? It could help debugging if we could determine for sure the extension was responsible – John Mar 29 '18 at 7:00
  • Hi John, at this point it's just an educated guess, unfortunately it's a rather crucial extension so just disabling it would be difficult, but I am in the middle of figuring out a way. Thanks for your help on this. – Chris Rogers Apr 2 '18 at 22:08
  • Would it be possible to install said extension on a localhost version of Magento, transfer over the files you have currently to that localhost version and determine whether or not it is that extension. If it's crucial, you can still go from there and use it after figuring out what part of it goes wrong and correcting it. Best of luck Chris – John Apr 3 '18 at 7:09
  • 1
    On a fresh Magento install? Nice idea John. Will give it a go and let you know. – Chris Rogers Apr 4 '18 at 7:54
  • Fresh Magento install is always best, that way you can determine it is solely responsible down to that module. We found in Magento 1 our site was clashing with the tens of third party modules we had installed, so since we moved to M2 it's been part of my job to rewrite them so they don't clash, anyway yeah i'm going off topic, best of luck – John Apr 4 '18 at 8:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.