Magento 2 script wait due to CREATE TEMPORARY TABLE

Question

I'm running a custom products import script ( importing configurable, simple products with extra attributes, categories photos etc ).

I am facing a strange "wait" at script execution when assigning categories to a newly created product using ( $categories contains an array of category ids )

$categoryLinkRepository->assignProductToCategories($product->getSku(), $categories);

After investigation , i've found that the delay starts when the script executes the 2nd for loop in the following function

 public function assignProductToCategories($productSku, array $categoryIds)
{
    $product = $this->getProductRepository()->get($productSku);
    $assignedCategories = $this->getProductResource()->getCategoryIds($product);
    foreach (array_diff($assignedCategories, $categoryIds) as $categoryId) {
        $this->getCategoryLinkRepository()->deleteByIds($categoryId, $productSku);
    }

    foreach (array_diff($categoryIds, $assignedCategories) as $categoryId) {
        /** @var \Magento\Catalog\Api\Data\CategoryProductLinkInterface $categoryProductLink */
        $categoryProductLink = $this->productLinkFactory->create();
        $categoryProductLink->setSku($productSku);
        $categoryProductLink->setCategoryId($categoryId);
        $categoryProductLink->setPosition(0);
        $this->getCategoryLinkRepository()->save($categoryProductLink);
    }
    $productCategoryIndexer = $this->getIndexerRegistry()->get(Indexer\Product\Category::INDEXER_ID);
    if (!$productCategoryIndexer->isScheduled()) {
        $productCategoryIndexer->reindexRow($product->getId());
    }
    return true;
}

The strange thing is that the same function faces a delay only for newly created products, when i run it for an existing products its runs fine.

Mysql Processes at the server shows a repeating query waiting for 66secs

Sending data    CREATE TEMPORARY TABLE `tmp_select_tUFtPSHTf3LaHnv19OnapfygPIfXxdCU` (PRIMARY KEY(`url_rewrite_id`),

I am only facing this issue at the production server ( Running MariaDB 10.3.16 ) and not on my local development VM ( Mysql 5.7 )

I think the issue is related with mysql configuration at the server. Any ideas are more than welcome

I also have problems with MariaDB 10.3.18 on prod with indexing, while 10.1.x worked fine locally: https://magento.stackexchange.com/questions/292867/which-server-settings-can-cause-magento-reindex-to-run-really-slow — Alex, Oct 18 '19 at 17:52
https://devdocs.magento.com/guides/v2.3/install-gde/system-requirements-tech.html "Magento is also compatible with MySQL NDB Cluster 7.4.*, MariaDB 10.0, 10.1, 10.2, Percona 5.7, and other binary-compatible MySQL technologies." — Alex, Oct 18 '19 at 17:55

score 1 · Answer 1 · answered Jul 09 '19 at 05:32

1

I think new product saves makes you wait because it is making an entry in url_rewrite for that product as per category you have assigned to that product

answered Jul 09 '19 at 05:32

nikunj

121
2

Ok that is true, but how to avoid creating temp tables? – sarakinos Jul 09 '19 at 05:47
creating a tmp table is necessary to make an entry in URL rewrite – nikunj Jul 09 '19 at 06:01
So you think this delay is normal? – sarakinos Jul 09 '19 at 20:58
yes its depends on how many categories you have assigned to a product – nikunj Jul 10 '19 at 08:17
Tried to run the project in a small "blank vm" with only PHP, Mysql 5.6 and there are not any temp tables in mysql log. The delay fell from 70sec to 14sec , so there has to be something with the database ( production runs in cpanel ) – sarakinos Jul 11 '19 at 21:41

Magento 2 script wait due to CREATE TEMPORARY TABLE

1 Answers1