ImportExport: Indexers should be locked when importing

[piotrekkaminski]

From @paales on August 5, 2016 9:20

Preconditions

1. Magento 2.1

Steps to reproduce

1. Run an import
2. Run an import again while the cron indexers have just triggered.

Expected result

1. Indexers should be halted / killed and the importer should run
2. After the importer has ran the importer should resume.

Actual result

1. Deadlocks occur because the indexer is trying to do stuff (with insert from select, which might a bad idea in itself)

SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction, query was: DELETE FROM `catalog_category_product` WHERE (product_id IN ('3855', '3856', '3857', '3858', '3859', '3860', '3861', '3862', '3863', '3864', '3865', '3866', '3867', '3868', '3869', '3870', '3871', '3872', '3873', '3874', '3875', '3876', '3877', '3878', '3879', '3880', '3881', '3882', '3883', '3884', '3885', '3886', '3887', '3888', '3889', '3890', '3891', '3892', '3893', '3894', '3895', '3896', '3897', '3898', '3899', '3900', '3901', '3902', '3903', '3904', '3905', '3906', '3909', '3910', '3911', '3912', '3913', '3914', '3915', '3916', '3917', '3918', '3919', '3920', '3921', '3922', '3923', '3924', '3925', '3927', '3928', '3929', '3930', '12908', '3931', '3932', '3933', '3934', '3935', '3936', '3937', '3938', '3939', '3940', '3941', '3942', '3943', '3944', '3945', '3946', '3947', '3948', '3949', '3950', '3951', '3952', '3953', '3954', '3955', '3956'))

Copied from original issue: magento/magento2#6004

[piotrekkaminski]

From @southerncomputer on August 5, 2016 21:16

Here here - when importing say 100k - to 1 MIL products- cron reindexing can cause database failure of import due to DEADLOCKS - which seem to not bother the indexer. It would be nice to be able to disable indexers via CLI or during product import routines!

Now my both my importer and indexer runs via cron - so we need a lockout routine to detect if another separate cron process happens to be indexing to wait!

[piotrekkaminski]

From @slopukhov on August 22, 2016 15:16

Hi @paales

Thank you for reporting the issue. It is reported as MAGETWO-57344 internally.

[piotrekkaminski]

From @hostep on January 31, 2017 9:51

Any chance this is already in the pipeline of being fixed in the near future? We also run into this.

Thanks!

[piotrekkaminski]

From @kandy on February 1, 2017 16:0

@hostep, yes, we plan improvements in indexers, but looks like it will be available only in next major release

[piotrekkaminski]

From @koenner01 on February 2, 2017 10:36

Currently we are bypassing this problem by using a lock file during our import and using a plugin on the tryLockJob function on the Cron\Schedule

public function __construct(
    \Magento\Cron\Model\ConfigInterface $cronConfig,
    ...
){
    $this->cronConfig = $cronConfig;
    ...
}

public function aroundTryLockJob(
    $subject,
    $proceed
){
    $jobConfig = $this->getJobConfig($subject->getJobCode());
    if (isset($jobConfig['group']) AND $jobConfig['group'] === 'index') {
        try {
            // Check if the import lock is currently locked (to see if the import is running)
            ...
        }
        catch (\Exception $e) {
            throw new \Exception("Cronjob {$job['name']} should not run while import is running");
        }
    }

    $result = $proceed();
    return $result;
}

private function getJobConfig($jobCode)
{
    foreach ($this->cronConfig->getJobs() as $jobGroupCode => $jobGroup) {
        foreach ($jobGroup as $job) {
            if ($job['name'] == $jobCode) {
                $job['group'] = $jobGroupCode;
                return $job;
            }
        }
    }

    return [];
}

[dmanners]

I would like to check about this one as this may be already covered with the indexer improvements.

[TomashKhamlai]

@dmanners, I cannot export 120 000 products.
Error - 500

Apache:

develop.conf:80 127.0.0.1 - - [27/Dec/2017:10:39:04 +0200] "GET /admin/admin/export/ HTTP/1.1" 200 10181 "http://develop.conf/admin/admin/cache/" "Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/63.0.3239.108 Safari/537.36"
develop.conf:80 127.0.0.1 - - [27/Dec/2017:10:39:12 +0200] "GET /pub/static/version1514362743/adminhtml/Magento/backend/en_US/requirejs-config.js HTTP/1.1" 200 2336 "http://develop.conf/admin/admin/export/" "Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/63.0.3239.108 Safari/537.36"
develop.conf:80 127.0.0.1 - - [27/Dec/2017:10:39:13 +0200] "GET /pub/static/version1514362743/adminhtml/Magento/backend/en_US/css/styles-old.css HTTP/1.1" 200 24468 "http://develop.conf/admin/admin/export/" "Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/63.0.3239.108 Safari/537.36"
develop.conf:80 127.0.0.1 - - [27/Dec/2017:10:39:13 +0200] "GET /pub/static/version1514362743/adminhtml/Magento/backend/en_US/css/styles.css HTTP/1.1" 200 56690 "http://develop.conf/admin/admin/export/" "Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/63.0.3239.108 Safari/537.36"
develop.conf:80 127.0.0.1 - - [27/Dec/2017:10:39:13 +0200] "GET /admin/mui/index/render/?namespace=notification_area&sorting%5Bfield%5D=created_at&sorting%5Bdirection%5D=asc&isAjax=true HTTP/1.1" 200 728 "http://develop.conf/admin/admin/export/" "Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/63.0.3239.108 Safari/537.36"
develop.conf:80 127.0.0.1 - - [27/Dec/2017:10:39:17 +0200] "POST /admin/admin/export/getFilter/entity/catalog_product?isAjax=true HTTP/1.1" 200 10515 "http://develop.conf/admin/admin/export/" "Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/63.0.3239.108 Safari/537.36"
develop.conf:80 127.0.0.1 - - [27/Dec/2017:10:39:24 +0200] "POST /admin/admin/export/export/entity/catalog_product/file_format/csv HTTP/1.1" 500 583 "http://develop.conf/admin/admin/export/" "Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/63.0.3239.108 Safari/537.36"

PHP:

[27-Dec-2017 08:47:32 UTC] PHP Fatal error:  Allowed memory size of 792723456 bytes exhausted (tried to allocate 772621696 bytes) in /var/www/html/develop/vendor/magento/zendframework1/library/Zend/Db/Statement/Pdo.php on line 228
[27-Dec-2017 08:47:32 UTC] PHP Stack trace:
[27-Dec-2017 08:47:32 UTC] PHP   1. {main}() /var/www/html/develop/index.php:0
[27-Dec-2017 08:47:32 UTC] PHP   2. Magento\Framework\App\Bootstrap->run() /var/www/html/develop/index.php:39
[27-Dec-2017 08:47:32 UTC] PHP   3. Magento\Framework\App\Http->launch() /var/www/html/develop/lib/internal/Magento/Framework/App/Bootstrap.php:256
[27-Dec-2017 08:47:32 UTC] PHP   4. Magento\Framework\App\FrontController\Interceptor->dispatch() /var/www/html/develop/lib/internal/Magento/Framework/App/Http.php:135
[27-Dec-2017 08:47:32 UTC] PHP   5. Magento\Framework\App\FrontController\Interceptor->___callPlugins() /var/www/html/develop/generated/code/Magento/Framework/App/FrontController/Interceptor.php:26
[27-Dec-2017 08:47:32 UTC] PHP   6. Magento\Framework\App\FrontController\Interceptor->Magento\Framework\Interception\{closure}() /var/www/html/develop/lib/internal/Magento/Framework/Interception/Interceptor.php:153
[27-Dec-2017 08:47:32 UTC] PHP   7. Magento\Framework\App\FrontController\Interceptor->___callParent() /var/www/html/develop/lib/internal/Magento/Framework/Interception/Interceptor.php:138
[27-Dec-2017 08:47:32 UTC] PHP   8. Magento\Framework\App\FrontController\Interceptor->dispatch() /var/www/html/develop/lib/internal/Magento/Framework/Interception/Interceptor.php:58
[27-Dec-2017 08:47:32 UTC] PHP   9. Magento\ImportExport\Controller\Adminhtml\Export\Export\Interceptor->dispatch() /var/www/html/develop/lib/internal/Magento/Framework/App/FrontController.php:55
[27-Dec-2017 08:47:32 UTC] PHP  10. Magento\ImportExport\Controller\Adminhtml\Export\Export\Interceptor->___callPlugins() /var/www/html/develop/generated/code/Magento/ImportExport/Controller/Adminhtml/Export/Export/Interceptor.php:39
[27-Dec-2017 08:47:32 UTC] PHP  11. Magento\ImportExport\Controller\Adminhtml\Export\Export\Interceptor->Magento\Framework\Interception\{closure}() /var/www/html/develop/lib/internal/Magento/Framework/Interception/Interceptor.php:153
[27-Dec-2017 08:47:32 UTC] PHP  12. Magento\Backend\App\Action\Plugin\Authentication->aroundDispatch() /var/www/html/develop/lib/internal/Magento/Framework/Interception/Interceptor.php:135
[27-Dec-2017 08:47:32 UTC] PHP  13. Magento\ImportExport\Controller\Adminhtml\Export\Export\Interceptor->Magento\Framework\Interception\{closure}() /var/www/html/develop/app/code/Magento/Backend/App/Action/Plugin/Authentication.php:143
[27-Dec-2017 08:47:32 UTC] PHP  14. Magento\ImportExport\Controller\Adminhtml\Export\Export\Interceptor->___callParent() /var/www/html/develop/lib/internal/Magento/Framework/Interception/Interceptor.php:138
[27-Dec-2017 08:47:32 UTC] PHP  15. Magento\ImportExport\Controller\Adminhtml\Export\Export\Interceptor->dispatch() /var/www/html/develop/lib/internal/Magento/Framework/Interception/Interceptor.php:58
[27-Dec-2017 08:47:32 UTC] PHP  16. Magento\ImportExport\Controller\Adminhtml\Export\Export\Interceptor->dispatch() /var/www/html/develop/app/code/Magento/Backend/App/AbstractAction.php:229
[27-Dec-2017 08:47:32 UTC] PHP  17. Magento\ImportExport\Controller\Adminhtml\Export\Export\Interceptor->execute() /var/www/html/develop/lib/internal/Magento/Framework/App/Action/Action.php:107
[27-Dec-2017 08:47:32 UTC] PHP  18. Magento\ImportExport\Controller\Adminhtml\Export\Export\Interceptor->execute() /var/www/html/develop/generated/code/Magento/ImportExport/Controller/Adminhtml/Export/Export/Interceptor.php:24
[27-Dec-2017 08:47:32 UTC] PHP  19. Magento\ImportExport\Model\Export->export() /var/www/html/develop/app/code/Magento/ImportExport/Controller/Adminhtml/Export/Export.php:60
[27-Dec-2017 08:47:32 UTC] PHP  20. Magento\CatalogImportExport\Model\Export\Product->export() /var/www/html/develop/app/code/Magento/ImportExport/Model/Export.php:182
[27-Dec-2017 08:47:32 UTC] PHP  21. Magento\CatalogImportExport\Model\Export\Product->getExportData() /var/www/html/develop/app/code/Magento/CatalogImportExport/Model/Export/Product.php:842
[27-Dec-2017 08:47:32 UTC] PHP  22. Magento\CatalogImportExport\Model\Export\Product->collectMultirawData() /var/www/html/develop/app/code/Magento/CatalogImportExport/Model/Export/Product.php:889
[27-Dec-2017 08:47:32 UTC] PHP  23. Magento\CatalogImportExport\Model\Export\Product->getMediaGallery() /var/www/html/develop/app/code/Magento/CatalogImportExport/Model/Export/Product.php:1073
[27-Dec-2017 08:47:32 UTC] PHP  24. Magento\Framework\DB\Adapter\Pdo\Mysql->query() /var/www/html/develop/app/code/Magento/CatalogImportExport/Model/Export/Product.php:548
[27-Dec-2017 08:47:32 UTC] PHP  25. Magento\Framework\DB\Adapter\Pdo\Mysql->_query() /var/www/html/develop/lib/internal/Magento/Framework/DB/Adapter/Pdo/Mysql.php:580
[27-Dec-2017 08:47:32 UTC] PHP  26. Magento\Framework\DB\Adapter\Pdo\Mysql->query() /var/www/html/develop/lib/internal/Magento/Framework/DB/Adapter/Pdo/Mysql.php:517
[27-Dec-2017 08:47:32 UTC] PHP  27. Magento\Framework\DB\Adapter\Pdo\Mysql->query() /var/www/html/develop/vendor/magento/zendframework1/library/Zend/Db/Adapter/Pdo/Abstract.php:238
[27-Dec-2017 08:47:32 UTC] PHP  28. Magento\Framework\DB\Statement\Pdo\Mysql->execute() /var/www/html/develop/vendor/magento/zendframework1/library/Zend/Db/Adapter/Abstract.php:480
[27-Dec-2017 08:47:32 UTC] PHP  29. Magento\Framework\DB\Statement\Pdo\Mysql->_execute() /var/www/html/develop/vendor/magento/zendframework1/library/Zend/Db/Statement.php:303
[27-Dec-2017 08:47:32 UTC] PHP  30. Magento\Framework\DB\Statement\Pdo\Mysql->_execute() /var/www/html/develop/lib/internal/Magento/Framework/DB/Statement/Pdo/Mysql.php:93
[27-Dec-2017 08:47:32 UTC] PHP  31. PDOStatement->execute() /var/www/html/develop/vendor/magento/zendframework1/library/Zend/Db/Statement/Pdo.php:228

After changing the memory_limit value to -1 the error remains the same.
Ubuntu Desktop, 8Gb RAM, 2 Cores, multithreading(4 threads), 3.3GHz, Apache 2.4, PHP v7.0.26-2+ubuntu16.04.1+deb.sury.org+2