Create Store View end with error

[kf_luk]

Hi all,

When I create store view it end with error. The following was found from system.log.

Please let me know if you encounter and have any idea. 

Thanks in advance.

Regards,

kf

[2020-07-09 02:08:26] main.WARNING: Memory size allocated for the temporary table is more than 20% of innodb_buffer_pool_size. Please update innodb_buffer_pool_size or decrease batch size value (which decreases memory usages for the temporary table). Current batch size: 100000; Allocated memory size: 1020000000 bytes; InnoDB buffer pool size: 3221225472 bytes. [] []
[2020-07-09 02:08:26] main.CRITICAL: Exception message: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'returns-12' for key 'URL_REWRITE_REQUEST_PATH_STORE_ID', query was: INSERT  INTO `url_rewrite` (`redirect_type`,`is_autogenerated`,`metadata`,`description`,`store_id`,`entity_type`,`entity_id`,`request_path`,`target_path`) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?, ?, ?)
Trace: <pre>#1 Magento\Framework\DB\Adapter\Pdo\Mysql->query() called at [generated/code/Magento/Framework/DB/Adapter/Pdo/Mysql/Interceptor.php:128]
#2 Magento\Framework\DB\Adapter\Pdo\Mysql\Interceptor->query() called at [vendor/magento/framework/DB/Adapter/Pdo/Mysql.php:2063]
#3 Magento\Framework\DB\Adapter\Pdo\Mysql->insertArray() called at [generated/code/Magento/Framework/DB/Adapter/Pdo/Mysql/Interceptor.php:557]
#4 Magento\Framework\DB\Adapter\Pdo\Mysql\Interceptor->insertArray() called at [vendor/magento/framework/DB/Adapter/Pdo/Mysql.php:2020]
#5 Magento\Framework\DB\Adapter\Pdo\Mysql->insertMultiple() called at [generated/code/Magento/Framework/DB/Adapter/Pdo/Mysql/Interceptor.php:544]
#6 Magento\Framework\DB\Adapter\Pdo\Mysql\Interceptor->insertMultiple() called at [vendor/magento/module-url-rewrite/Model/Storage/DbStorage.php:332]
#7 Magento\UrlRewrite\Model\Storage\DbStorage->insertMultiple() called at [vendor/magento/module-url-rewrite/Model/Storage/DbStorage.php:282]
#8 Magento\UrlRewrite\Model\Storage\DbStorage->doReplace() called at [vendor/magento/module-url-rewrite/Model/Storage/AbstractStorage.php:87]
#9 Magento\UrlRewrite\Model\Storage\AbstractStorage->replace() called at [vendor/magento/framework/Interception/Interceptor.php:58]
#10 Magento\UrlRewrite\Model\Storage\DbStorage\Interceptor->___callParent() called at [vendor/magento/framework/Interception/Interceptor.php:138]
#11 Magento\UrlRewrite\Model\Storage\DbStorage\Interceptor->Magento\Framework\Interception\{closure}() called at [vendor/magento/framework/Interception/Interceptor.php:153]
#12 Magento\UrlRewrite\Model\Storage\DbStorage\Interceptor->___callPlugins() called at [generated/code/Magento/UrlRewrite/Model/Storage/DbStorage/Interceptor.php:39]
#13 Magento\UrlRewrite\Model\Storage\DbStorage\Interceptor->replace() called at [vendor/magento/module-cms-url-rewrite/Plugin/Cms/Model/Store/View.php:77]
#14 Magento\CmsUrlRewrite\Plugin\Cms\Model\Store\View->afterSave() called at [vendor/magento/framework/Interception/Interceptor.php:146]
#15 Magento\Store\Model\ResourceModel\Store\Interceptor->Magento\Framework\Interception\{closure}() called at [vendor/magento/framework/Interception/Interceptor.php:153]
#16 Magento\Store\Model\ResourceModel\Store\Interceptor->___callPlugins() called at [generated/code/Magento/Store/Model/ResourceModel/Store/Interceptor.php:26]
#17 Magento\Store\Model\ResourceModel\Store\Interceptor->save() called at [vendor/magento/framework/Model/AbstractModel.php:655]
#18 Magento\Framework\Model\AbstractModel->save() called at [vendor/magento/framework/Interception/Interceptor.php:58]
#19 Magento\Store\Model\Store\Interceptor->___callParent() called at [vendor/magento/framework/Interception/Interceptor.php:138]
#20 Magento\Store\Model\Store\Interceptor->Magento\Framework\Interception\{closure}() called at [vendor/magento/module-theme/Model/Indexer/Design/Config/Plugin/Store.php:39]
#21 Magento\Theme\Model\Indexer\Design\Config\Plugin\Store->aroundSave() called at [vendor/magento/framework/Interception/Interceptor.php:135]
#22 Magento\Store\Model\Store\Interceptor->Magento\Framework\Interception\{closure}() called at [vendor/magento/framework/Interception/Interceptor.php:153]
#23 Magento\Store\Model\Store\Interceptor->___callPlugins() called at [generated/code/Magento/Store/Model/Store/Interceptor.php:52]
#24 Magento\Store\Model\Store\Interceptor->save() called at [vendor/magento/module-backend/Controller/Adminhtml/System/Store/Save.php:72]
#25 Magento\Backend\Controller\Adminhtml\System\Store\Save->processStoreSave() called at [vendor/magento/module-backend/Controller/Adminhtml/System/Store/Save.php:130]
#26 Magento\Backend\Controller\Adminhtml\System\Store\Save->execute() called at [vendor/magento/framework/App/Action/Action.php:108]
#27 Magento\Framework\App\Action\Action->dispatch() called at [vendor/magento/module-backend/App/AbstractAction.php:231]
#28 Magento\Backend\App\AbstractAction->dispatch() called at [vendor/magento/framework/Interception/Interceptor.php:58]
#29 Magento\Backend\Controller\Adminhtml\System\Store\Save\Interceptor->___callParent() called at [vendor/magento/framework/Interception/Interceptor.php:138]
#30 Magento\Backend\Controller\Adminhtml\System\Store\Save\Interceptor->Magento\Framework\Interception\{closure}() called at [app/code/WeltPixel/Backend/Plugin/Utility.php:76]
#31 WeltPixel\Backend\Plugin\Utility->aroundDispatch() called at [vendor/magento/framework/Interception/Interceptor.php:135]
#32 Magento\Backend\Controller\Adminhtml\System\Store\Save\Interceptor->Magento\Framework\Interception\{closure}() called at [vendor/magento/module-backend/App/Action/Plugin/Authentication.php:143]
#33 Magento\Backend\App\Action\Plugin\Authentication->aroundDispatch() called at [vendor/magento/framework/Interception/Interceptor.php:135]
#34 Magento\Backend\Controller\Adminhtml\System\Store\Save\Interceptor->Magento\Framework\Interception\{closure}() called at [vendor/magento/framework/Interception/Interceptor.php:153]
#35 Magento\Backend\Controller\Adminhtml\System\Store\Save\Interceptor->___callPlugins() called at [generated/code/Magento/Backend/Controller/Adminhtml/System/Store/Save/Interceptor.php:26]
#36 Magento\Backend\Controller\Adminhtml\System\Store\Save\Interceptor->dispatch() called at [vendor/magento/framework/App/FrontController.php:162]
#37 Magento\Framework\App\FrontController->processRequest() called at [vendor/magento/framework/App/FrontController.php:98]
#38 Magento\Framework\App\FrontController->dispatch() called at [vendor/magento/framework/Interception/Interceptor.php:58]
#39 Magento\Framework\App\FrontController\Interceptor->___callParent() called at [vendor/magento/framework/Interception/Interceptor.php:138]
#40 Magento\Framework\App\FrontController\Interceptor->Magento\Framework\Interception\{closure}() called at [vendor/magento/framework/Interception/Interceptor.php:153]
#41 Magento\Framework\App\FrontController\Interceptor->___callPlugins() called at [generated/code/Magento/Framework/App/FrontController/Interceptor.php:26]
#42 Magento\Framework\App\FrontController\Interceptor->dispatch() called at [vendor/magento/framework/App/Http.php:116]
#43 Magento\Framework\App\Http->launch() called at [vendor/magento/framework/App/Bootstrap.php:261]
#44 Magento\Framework\App\Bootstrap->run() called at [pub/index.php:40]
</pre> [] []
[2020-07-09 02:08:31] main.WARNING: Memory size allocated for the temporary table is more than 20% of innodb_buffer_pool_size. Please update innodb_buffer_pool_size or decrease batch size value (which decreases memory usages for the temporary table). Current batch size: 100000; Allocated memory size: 1020000000 bytes; InnoDB buffer pool size: 3221225472 bytes. [] []

[Bhanu Periwal · Accepted solution]

Hi @kf_luk 

Magento can increase the memory for processing a large amount of data by using memory engines instead of InnoDB. The algorithm increases the memory value for the max_heap_table_size and tmp_table_size MySQL parameters.

When the allocated memory size for a temporary table will be greater than 20% of innodb_buffer_pool_size, the error message is written to the Magento log.

To prevent this error message, you need to update default Batching configuration of catalog_category_product (Category Products) indexer because "Current batch size: 100000".

For more details, take a look .
https://devdocs.magento.com/guides/v2.2/extension-dev-guide/indexer-batch.html

It may help you.

Problem Solved? Click on 'Kudos' & Accept as Solution!

[kf_luk]

Hi @Bhanu Periwal,

Thanks for advise, I tried to find a way to decrease batch size but failed before. It mainly mention about module development. Will try to read it again.

Thanks.

Regards,

kf

[kf_luk]

Hi @Bhanu Periwal,

I read again the document you've sent and found the parameter to adjust. Thanks.

Will check again if the store view can create.

Thank you.

Regards,

kf

[kf_luk]

Hi @Bhanu Periwal,

It was fixed after change batch size and make all SEO url unique.

Thank you.

Regards,

kf