1

I have a project with 10+ websites, and when my product import runs I need to assign the correct website association. The problem is that something which appears to be a deadlock occurs when magento tries to save the association records to the catalog_product_website table. I created a simple command to test this behavior.

Important things to note:

  • If I don't assign website ids everything works fine.
  • When doing this manually from the adminhtml area the same queries are run with the same data and there are no errors (errors only occur when doing this from CLI)
  • I have been able to reproduce this behavior on two separate environments with identical results
  • On both environments I have tested this with Magento 2.1.7 and with Magento 2.1.8

My code:

namespace Vendor\Module\Console\Command;

use Magento\Catalog\Api\ProductRepositoryInterface;
use Magento\Catalog\Model\Product;
use Magento\Catalog\Model\Product\Type;
use Magento\Catalog\Model\ProductFactory;
use Magento\Framework\App\Area;
use Magento\Framework\App\State;
use Symfony\Component\Console\Command\Command;
use Symfony\Component\Console\Input\InputInterface;
use Symfony\Component\Console\Output\OutputInterface;

class TestProductCommand extends Command
{

    /**
     * @var State
     */
    protected $appState;

    /**
     * @var ProductRepositoryInterface
     */
    protected $productRepository;

    /**
     * @var ProductFactory
     */
    protected $productFactory;

    /**
     * @inheritDoc
     */
    public function __construct(
        State $state,
        ProductRepositoryInterface $productRepository,
        ProductFactory $productFactory,
        $name = null
    ) {
        parent::__construct($name);

        $this->appState = $state;
        $this->productRepository = $productRepository;
        $this->productFactory = $productFactory;
    }

    /**
     * @inheritDoc
     */
    protected function configure()
    {
        $this
            ->setName('test:product')
            ->setDescription('Test product creation');
    }

    /**
     * @inheritDoc
     */
    protected function execute(InputInterface $input, OutputInterface $output)
    {
        $this->appState->emulateAreaCode(Area::AREA_ADMINHTML, [$this, 'createProduct'], [$input, $output]);
    }

    public function createProduct(InputInterface $input, OutputInterface $output)
    {
        try {
            $code = $this->appState->getAreaCode();
            echo "Area code: " . $code . "\n";
            //$output->writeln("<info>Area code:</info> " . $code);
        } catch (\Exception $e) {
            $output->writeln("No area code");
            return;
        }

        /** @var Product $product */
        $product = $this->productFactory->create();
        $sku = 'testsku';
        $productId = $product->getResource()->getIdBySku($sku);

        if ($productId) {
            $product->load($productId);
        }

        $product
            ->setSku($sku)
            ->setName('Test product')
            ->setAttributeSetId($product->getDefaultAttributeSetId())
            ->setTypeId(Type::TYPE_SIMPLE)
            ->setUrlKey('test-product-testsku')
            ->setPrice(110.00)
            ->setWebsiteIds([4]);


        $this->productRepository->save($product);

        $output->writeln("Product id: " . $product->getId());
    }
}

It doesn't matter if I save through the product repository or directly using $product->save();, the result is the same: I get a lock wait timeout exception. The query which causes the timeout is:

INSERT INTO `catalog_product_website` (`product_id`,`website_id`) VALUES  ('1','4')

InnoDb lock information shows that at this point there are two connections: a 'sleeping' connection which is holding the lock on 'catalog_product_entity', and an active connection which is trying to run the aforementioned query but which is waiting for the lock to release (which will never happen before the transaction completes).

Now i know that the usual response is to use show processlist; to find and kill the locking query. This doesn't work because both queries are part of the same transaction so killing the locking query also kills the overall product save transaction.

I have also run SHOW ENGINE INNODB STATUS\G during the lock wait to try to find more information, but there is no mention of deadlocks or foreign key errors in the output.

Google has not been a huge help so far (I learned a whole bunch about MySQL in the last few days but it hasn't solved my problem yet).

So my question is: has anyone had any similar experiences and/or does anyone know how I can solve this issue?

  • Did you find the solution for this? I am facing the same issue. – Sarvagya Jun 4 '18 at 12:13
  • 1
    Hi @Sarvagya, I found out that a different module mucked up the objectmanager in the constructor of one of their CLI commands. The quickest way to find out if this is also the case in your project is to disable all your 3rd party modules and re-enable them one by one. check your cli command after each enabled module and you should find out which module breaks your project fairly quickly. – Vincent Hornikx Jun 6 '18 at 19:32

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.