Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Something went wrong while saving the category in Magento 2.1.9 #11055

Closed
pareshpa opened this issue Sep 26, 2017 · 37 comments
Closed

Something went wrong while saving the category in Magento 2.1.9 #11055

pareshpa opened this issue Sep 26, 2017 · 37 comments
Labels
Issue: Clear Description Gate 2 Passed. Manual verification of the issue description passed Issue: Format is valid Gate 1 Passed. Automatic verification of issue format passed

Comments

@pareshpa
Copy link

pareshpa commented Sep 26, 2017

Preconditions

  1. After upgrading the version Magento Version from 2.1.7 to 2.1.9

Steps to reproduce

  1. Create a new category and save

Expected result

  1. New Category Page should save without any error

Actual result

  1. I have upgraded my Magento 2.1.7 to Magento 2.1.9, while saving category it's throwing an error as "Something went wrong". report.CRITICAL: PDOException: SQLSTATE[HY000]: General error: 1787 Statement violates GTID consistency: CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE can only be executed outside transactional context. These statements are also not allowed in a function or trigger because functions and triggers are also considered to be multi-statement transactions.

Exception Log:
Next Zend_Db_Statement_Exception: SQLSTATE[HY000]: General error: 1787 Statement violates GTID consistency: CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE can only be executed outside transactional context. These statements are also not allowed in a function or trigger because functions and triggers are also considered to be multi-statement transactions., query was: CREATE TEMPORARY TABLE tmp_select_679617e6f7a49c490f1a60d1a78f7175 (PRIMARY KEY(url_rewrite_id),INDEX HASHKEY_ENTITY_STORE USING HASH(hash_key),INDEX ENTITY_STORE USING HASH(entity_id,store_id)) ENGINE=INNODB IGNORE (SELECT e.*, CONCAT(e.store_id,'_', e.entity_id) AS hash_key FROM url_rewrite AS e WHERE (entity_type = 'category') AND (entity_id IN('134'))) in /var/www/html/training_magento/public/vendor/magento/zendframework1/library/Zend/Db/Statement/Pdo.php:235
Stack trace:
#0 /var/www/html/training_magento/public/vendor/magento/framework/DB/Statement/Pdo/Mysql.php(95): Zend_Db_Statement_Pdo->_execute(Array)
#1 /var/www/html/training_magento/public/vendor/magento/zendframework1/library/Zend/Db/Statement.php(303): Magento\Framework\DB\Statement\Pdo\Mysql->_execute(Array)
#2 /var/www/html/training_magento/public/vendor/magento/zendframework1/library/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute(Array)
#3 /var/www/html/training_magento/public/vendor/magento/zendframework1/library/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('CREATE TEMPORAR...', Array)
#4 /var/www/html/training_magento/public/vendor/magento/framework/DB/Adapter/Pdo/Mysql.php(465): Zend_Db_Adapter_Pdo_Abstract->query('CREATE TEMPORAR...', Array)
#5 /var/www/html/training_magento/public/vendor/magento/framework/DB/Adapter/Pdo/Mysql.php(520): Magento\Framework\DB\Adapter\Pdo\Mysql->_query('CREATE TEMPORAR...', Array)
#6 /var/www/html/training_magento/public/vendor/magento/framework/DB/TemporaryTableService.php(145): Magento\Framework\DB\Adapter\Pdo\Mysql->query('CREATE TEMPORAR...', Array)
#7 /var/www/html/training_magento/public/vendor/magento/module-catalog-url-rewrite/Model/Map/DataCategoryUrlRewriteDatabaseMap.php(116): Magento\Framework\DB\TemporaryTableService->createFromSelect(Object(Magento\Framework\DB\Select), Object(Magento\Framework\DB\Adapter\Pdo\Mysql), Array)
#8 /var/www/html/training_magento/public/vendor/magento/module-catalog-url-rewrite/Model/Map/DataCategoryUrlRewriteDatabaseMap.php(76): Magento\CatalogUrlRewrite\Model\Map\DataCategoryUrlRewriteDatabaseMap->generateData('134')
#9 /var/www/html/training_magento/public/vendor/magento/module-catalog-url-rewrite/Model/Map/DataCategoryUrlRewriteDatabaseMap.php(147): Magento\CatalogUrlRewrite\Model\Map\DataCategoryUrlRewriteDatabaseMap->generateTableAdapter('134')
#10 /var/www/html/training_magento/public/vendor/magento/module-catalog-url-rewrite/Model/Map/UrlRewriteFinder.php(96): Magento\CatalogUrlRewrite\Model\Map\DataCategoryUrlRewriteDatabaseMap->getData('134', '3_134')
#11 /var/www/html/training_magento/public/vendor/magento/module-catalog-url-rewrite/Model/Category/CurrentUrlRewritesRegenerator.php(93): Magento\CatalogUrlRewrite\Model\Map\UrlRewriteFinder->findAllByData('134', '3', 'category', '134')
#12 /var/www/html/training_magento/public/vendor/magento/module-catalog-url-rewrite/Model/CategoryUrlRewriteGenerator.php(190): Magento\CatalogUrlRewrite\Model\Category\CurrentUrlRewritesRegenerator->generate('3', Object(Magento\Catalog\Model\Category\Interceptor), '134')
#13 /var/www/html/training_magento/public/vendor/magento/module-catalog-url-rewrite/Model/CategoryUrlRewriteGenerator.php(120): Magento\CatalogUrlRewrite\Model\CategoryUrlRewriteGenerator->generateForSpecificStoreView('3', Object(Magento\Catalog\Model\Category\Interceptor), '134')
#14 /var/www/html/training_magento/public/vendor/magento/module-catalog-url-rewrite/Model/CategoryUrlRewriteGenerator.php(94): Magento\CatalogUrlRewrite\Model\CategoryUrlRewriteGenerator->generateForGlobalScope(Object(Magento\Catalog\Model\Category\Interceptor), false, '134')
#15 /var/www/html/training_magento/public/vendor/magento/module-catalog-url-rewrite/Observer/CategoryProcessUrlRewriteSavingObserver.php(96): Magento\CatalogUrlRewrite\Model\CategoryUrlRewriteGenerator->generate(Object(Magento\Catalog\Model\Category\Interceptor))
#16 /var/www/html/training_magento/public/vendor/magento/framework/Event/Invoker/InvokerDefault.php(73): Magento\CatalogUrlRewrite\Observer\CategoryProcessUrlRewriteSavingObserver->execute(Object(Magento\Framework\Event\Observer))
#17 /var/www/html/training_magento/public/vendor/magento/framework/Event/Invoker/InvokerDefault.php(61): Magento\Framework\Event\Invoker\InvokerDefault->_callObserverMethod(Object(Magento\CatalogUrlRewrite\Observer\CategoryProcessUrlRewriteSavingObserver), Object(Magento\Framework\Event\Observer))
#18 /var/www/html/training_magento/public/vendor/magento/framework/Event/Manager.php(66): Magento\Framework\Event\Invoker\InvokerDefault->dispatch(Array, Object(Magento\Framework\Event\Observer))
#19 /var/www/html/training_magento/public/var/generation/Magento/Framework/Event/Manager/Proxy.php(95): Magento\Framework\Event\Manager->dispatch('catalog_categor...', Array)
#20 /var/www/html/training_magento/public/vendor/magento/framework/Model/AbstractModel.php(802): Magento\Framework\Event\Manager\Proxy->dispatch('catalog_categor...', Array)
#21 /var/www/html/training_magento/public/vendor/magento/module-catalog/Model/Category.php(1080): Magento\Framework\Model\AbstractModel->afterSave()
#22 /var/www/html/training_magento/public/vendor/magento/framework/EntityManager/Observer/AfterEntitySave.php(34): Magento\Catalog\Model\Category->afterSave()
#23 /var/www/html/training_magento/public/vendor/magento/framework/Event/Invoker/InvokerDefault.php(73): Magento\Framework\EntityManager\Observer\AfterEntitySave->execute(Object(Magento\Framework\Event\Observer))
#24 /var/www/html/training_magento/public/vendor/magento/framework/Event/Invoker/InvokerDefault.php(61): Magento\Framework\Event\Invoker\InvokerDefault->_callObserverMethod(Object(Magento\Framework\EntityManager\Observer\AfterEntitySave), Object(Magento\Framework\Event\Observer))
#25 /var/www/html/training_magento/public/vendor/magento/framework/Event/Manager.php(66): Magento\Framework\Event\Invoker\InvokerDefault->dispatch(Array, Object(Magento\Framework\Event\Observer))
#26 /var/www/html/training_magento/public/var/generation/Magento/Framework/Event/Manager/Proxy.php(95): Magento\Framework\Event\Manager->dispatch('magento_catalog...', Array)
#27 /var/www/html/training_magento/public/vendor/magento/framework/EntityManager/EventManager.php(51): Magento\Framework\Event\Manager\Proxy->dispatch('magento_catalog...', Array)
#28 /var/www/html/training_magento/public/vendor/magento/framework/EntityManager/Operation/Create.php(108): Magento\Framework\EntityManager\EventManager->dispatchEntityEvent('Magento\Catalog...', 'save_after', Array)
#29 /var/www/html/training_magento/public/vendor/magento/framework/EntityManager/EntityManager.php(87): Magento\Framework\EntityManager\Operation\Create->execute(Object(Magento\Catalog\Model\Category\Interceptor), Array)
#30 /var/www/html/training_magento/public/vendor/magento/module-catalog/Model/ResourceModel/Category.php(1041): Magento\Framework\EntityManager\EntityManager->save(Object(Magento\Catalog\Model\Category\Interceptor))
#31 /var/www/html/training_magento/public/vendor/magento/framework/Interception/Interceptor.php(74): Magento\Catalog\Model\ResourceModel\Category->save(Object(Magento\Catalog\Model\Category\Interceptor))
#32 /var/www/html/training_magento/public/vendor/magento/framework/Interception/Chain/Chain.php(70): Magento\Catalog\Model\ResourceModel\Category\Interceptor->___callParent('save', Array)
#33 /var/www/html/training_magento/public/vendor/magento/framework/Interception/Chain/Chain.php(63): Magento\Framework\Interception\Chain\Chain->invokeNext('Magento\Catalog...', 'save', Object(Magento\Catalog\Model\ResourceModel\Category\Interceptor), Array, 'catalogsearchFu...')
#34 /var/www/html/training_magento/public/vendor/magento/module-catalog-search/Model/Indexer/Fulltext/Plugin/Category.php(43): Magento\Framework\Interception\Chain\Chain->Magento\Framework\Interception\Chain{closure}(Object(Magento\Catalog\Model\Category\Interceptor))
#35 /var/www/html/training_magento/public/vendor/magento/module-catalog-search/Model/Indexer/Fulltext/Plugin/Category.php(29): Magento\CatalogSearch\Model\Indexer\Fulltext\Plugin\Category->addCommitCallback(Object(Magento\Catalog\Model\ResourceModel\Category\Interceptor), Object(Closure), Object(Magento\Catalog\Model\Category\Interceptor))
#36 /var/www/html/training_magento/public/vendor/magento/framework/Interception/Chain/Chain.php(67): Magento\CatalogSearch\Model\Indexer\Fulltext\Plugin\Category->aroundSave(Object(Magento\Catalog\Model\ResourceModel\Category\Interceptor), Object(Closure), Object(Magento\Catalog\Model\Category\Interceptor))
#37 /var/www/html/training_magento/public/vendor/magento/framework/Interception/Interceptor.php(138): Magento\Framework\Interception\Chain\Chain->invokeNext('Magento\Catalog...', 'save', Object(Magento\Catalog\Model\ResourceModel\Category\Interceptor), Array, 'clean_cache')
#38 /var/www/html/training_magento/public/vendor/magento/framework/App/Cache/FlushCacheByTags.php(71): Magento\Catalog\Model\ResourceModel\Category\Interceptor->Magento\Framework\Interception{closure}(Object(Magento\Catalog\Model\Category\Interceptor))
#39 /var/www/html/training_magento/public/vendor/magento/framework/Interception/Interceptor.php(142): Magento\Framework\App\Cache\FlushCacheByTags->aroundSave(Object(Magento\Catalog\Model\ResourceModel\Category\Interceptor), Object(Closure), Object(Magento\Catalog\Model\Category\Interceptor))
#40 /var/www/html/training_magento/public/var/generation/Magento/Catalog/Model/ResourceModel/Category/Interceptor.php(52): Magento\Catalog\Model\ResourceModel\Category\Interceptor->___callPlugins('save', Array, Array)
#41 /var/www/html/training_magento/public/vendor/magento/framework/Model/AbstractModel.php(631): Magento\Catalog\Model\ResourceModel\Category\Interceptor->save(Object(Magento\Catalog\Model\Category\Interceptor))
#42 /var/www/html/training_magento/public/vendor/magento/framework/Interception/Interceptor.php(146): Magento\Framework\Model\AbstractModel->save()
#43 /var/www/html/training_magento/public/var/generation/Magento/Catalog/Model/Category/Interceptor.php(26): Magento\Catalog\Model\Category\Interceptor->___callPlugins('save', Array, Array)
#44 /var/www/html/training_magento/public/vendor/magento/module-catalog/Controller/Adminhtml/Category/Save.php(212): Magento\Catalog\Model\Category\Interceptor->save()
#45 /var/www/html/training_magento/public/vendor/magento/framework/App/Action/Action.php(102): Magento\Catalog\Controller\Adminhtml\Category\Save->execute()
#46 /var/www/html/training_magento/public/vendor/magento/module-backend/App/AbstractAction.php(226): Magento\Framework\App\Action\Action->dispatch(Object(Magento\Framework\App\Request\Http))
#47 /var/www/html/training_magento/public/vendor/magento/framework/Interception/Interceptor.php(74): Magento\Backend\App\AbstractAction->dispatch(Object(Magento\Framework\App\Request\Http))
#48 /var/www/html/training_magento/public/vendor/magento/framework/Interception/Chain/Chain.php(70): Magento\Catalog\Controller\Adminhtml\Category\Save\Interceptor->___callParent('dispatch', Array)
#49 /var/www/html/training_magento/public/vendor/magento/framework/Interception/Chain/Chain.php(63): Magento\Framework\Interception\Chain\Chain->invokeNext('Magento\Catalog...', 'dispatch', Object(Magento\Catalog\Controller\Adminhtml\Category\Save\Interceptor), Array, 'adminAuthentica...')
#50 /var/www/html/training_magento/public/vendor/magento/module-backend/App/Action/Plugin/Authentication.php(143): Magento\Framework\Interception\Chain\Chain->Magento\Framework\Interception\Chain{closure}(Object(Magento\Framework\App\Request\Http))
#51 /var/www/html/training_magento/public/vendor/magento/framework/Interception/Chain/Chain.php(67): Magento\Backend\App\Action\Plugin\Authentication->aroundDispatch(Object(Magento\Catalog\Controller\Adminhtml\Category\Save\Interceptor), Object(Closure), Object(Magento\Framework\App\Request\Http))
#52 /var/www/html/training_magento/public/vendor/magento/framework/Interception/Interceptor.php(138): Magento\Framework\Interception\Chain\Chain->invokeNext('Magento\Catalog...', 'dispatch', Object(Magento\Catalog\Controller\Adminhtml\Category\Save\Interceptor), Array, 'adminMassaction...')
#53 /var/www/html/training_magento/public/vendor/magento/module-backend/App/Action/Plugin/MassactionKey.php(33): Magento\Catalog\Controller\Adminhtml\Category\Save\Interceptor->Magento\Framework\Interception{closure}(Object(Magento\Framework\App\Request\Http))
#54 /var/www/html/training_magento/public/vendor/magento/framework/Interception/Interceptor.php(142): Magento\Backend\App\Action\Plugin\MassactionKey->aroundDispatch(Object(Magento\Catalog\Controller\Adminhtml\Category\Save\Interceptor), Object(Closure), Object(Magento\Framework\App\Request\Http))
#55 /var/www/html/training_magento/public/var/generation/Magento/Catalog/Controller/Adminhtml/Category/Save/Interceptor.php(26): Magento\Catalog\Controller\Adminhtml\Category\Save\Interceptor->___callPlugins('dispatch', Array, Array)
#56 /var/www/html/training_magento/public/vendor/magento/framework/App/FrontController.php(55): Magento\Catalog\Controller\Adminhtml\Category\Save\Interceptor->dispatch(Object(Magento\Framework\App\Request\Http))
#57 /var/www/html/training_magento/public/vendor/magento/framework/Interception/Interceptor.php(74): Magento\Framework\App\FrontController->dispatch(Object(Magento\Framework\App\Request\Http))
#58 /var/www/html/training_magento/public/vendor/magento/framework/Interception/Chain/Chain.php(70): Magento\Framework\App\FrontController\Interceptor->___callParent('dispatch', Array)
#59 /var/www/html/training_magento/public/vendor/magento/framework/Interception/Interceptor.php(138): Magento\Framework\Interception\Chain\Chain->invokeNext('Magento\Framewo...', 'dispatch', Object(Magento\Framework\App\FrontController\Interceptor), Array, 'install')
#60 /var/www/html/training_magento/public/vendor/magento/framework/Module/Plugin/DbStatusValidator.php(69): Magento\Framework\App\FrontController\Interceptor->Magento\Framework\Interception{closure}(Object(Magento\Framework\App\Request\Http))
#61 /var/www/html/training_magento/public/vendor/magento/framework/Interception/Interceptor.php(142): Magento\Framework\Module\Plugin\DbStatusValidator->aroundDispatch(Object(Magento\Framework\App\FrontController\Interceptor), Object(Closure), Object(Magento\Framework\App\Request\Http))
#62 /var/www/html/training_magento/public/var/generation/Magento/Framework/App/FrontController/Interceptor.php(26): Magento\Framework\App\FrontController\Interceptor->___callPlugins('dispatch', Array, Array)
#63 /var/www/html/training_magento/public/vendor/magento/framework/App/Http.php(135): Magento\Framework\App\FrontController\Interceptor->dispatch(Object(Magento\Framework\App\Request\Http))
#64 /var/www/html/training_magento/public/vendor/magento/framework/App/Bootstrap.php(258): Magento\Framework\App\Http->launch()
#65 /var/www/html/training_magento/public/pub/index.php(37): Magento\Framework\App\Bootstrap->run(Object(Magento\Framework\App\Http))
#66 {main} [] []

@magento-engcom-team magento-engcom-team added the Issue: Format is valid Gate 1 Passed. Automatic verification of issue format passed label Sep 26, 2017
@klarre1
Copy link

klarre1 commented Sep 26, 2017

We have the same problems, it seems like it's due to GTID-based replication in MySQL.
We use some Google MySQL 2nd Gen instances that has GTID-based replication as default and not as an option, we have had this issue both on 2.1.8 and 2.1.9

MySQL cloud providers seems to use GTID enabled because it's best to protect against data loss during replica creation and failover, and it also makes replication more robust.

Creating categories seems to work on MySQL with GTID disabled at least when it's on localhost. It also works with google SQL instance when using MySQL First Generation.

@ajaitiwariitc
Copy link

Hello, We are facing the exact issue for adding subcategories after upgrading from Magento EE2.1.6 to 2.1.9, we are able to add new root categories and sub categories inside but not to the old ones which we created in 2.1.6..

We are using Google Cloud MySql Second generation too, where we have verified that the GTID is enabled by default, and cannot be turned off.

What is the solution for this issue, as we are unable to Add sub categories to our old root categories. Please resolve this issue or send us a workaround on how to update them.

Please note that we are also using Magento EE2.1.9 with Split Database.

Thank you.

@okorshenko okorshenko added the Issue: Clear Description Gate 2 Passed. Manual verification of the issue description passed label Sep 26, 2017
@scrivvles
Copy link

scrivvles commented Sep 29, 2017

This is also an issue for me. Using MySQL 5.7 on Google Cloud MySql Second Generation. I can't reindex Product EAV as it is giving the General error:

1787 Statement violates GTID consistency: CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE can only be executed outside transactional context. These statements are also not allowed in a function or trigger because functions and triggers are also considered to be multi-statement transactions.,

Is there a workaround for this? I would really like to use replication with my Google Cloud SQL.

After testing, it appears that this same error occurs even if you do not have replication enabled, but are using binary logs. In other words, any time that you are using GTID's it appears to cause a problem.

@pepijnblom
Copy link

This has been a problem since magento 1. We were using Rackspace HA database setup and it broke the checkout. This was 3 years ago already and now I read it still exists in the latest versions. So basically magento does not support proper HA on the biggest clouds, because they ALL use GTID and it can't be turned off.

@TarasSavchuk
Copy link

I have same issue. Magento 2.1.8.

@stormbyte
Copy link

We are using the Google Cloud SQL server where we encountered the same problem.
Using the MySQL 2e gen. 5.7 server gave us the error:

SQLSTATE[HY000]: General error: 1787 Statement violates GTID consistency: CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE can only be executed outside transactional context. 

There for we downgraded to MySQL 1e gen. 5.6 in order to evade the issue.
Can this be solved or will magento2 not be compatible with a High end solution?

@avra911
Copy link

avra911 commented Oct 23, 2017

Maybe it can just change the CREATE TEMPORARY TABLE ... SELECT into CREATE VIEW instead?

This should theoretically solve the GTID consistency problem.

PS: @pareshpa you can try to format your output, maybe using Syntax highlighting: https://guides.github.com/features/mastering-markdown.

@gilles6
Copy link

gilles6 commented Oct 26, 2017

Same problem here after upgrading from Magento 2.1.9 to 2.2.0.
My server in on Ubuntu 16.04 with Mysql 5.7.19 and php 7.0.24.
How should we fix it ?

@avra911
Copy link

avra911 commented Oct 26, 2017 via email

@TarasSavchuk
Copy link

GTID replication is useful. So disabling GTID isn't cool.

@gilles6
Copy link

gilles6 commented Oct 27, 2017

How to disable it ?

I've tried this :
https://dev.mysql.com/doc/refman/5.7/en/replication-mode-change-online-disable-gtids.html

But got an error at the first command :
mysql> STOP SLAVE [FOR CHANNEL 'channel'];
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[FOR CHANNEL 'channel']' at line 1

Also, from this command, I can't see anything showing that I'm using GTID :
mysql> show processlist \G
*************************** 1. row ***************************
Id: 67548028
User: my_server
Host: xxx.xxx.xxx.xxx:40552
db: mysql
Command: Query
Time: 0
State: init
Info: show processlist
1 row in set (0.00 sec)
(cf. https://avdeo.com/2015/02/03/disabling-gtid-in-mysql-replication/)

@magento-engcom-team magento-engcom-team added Issue: Cannot Reproduce Cannot reproduce the issue on the latest `2.4-develop` branch Progress: needs update labels Nov 2, 2017
@magento-engcom-team
Copy link
Contributor

@pareshpa, thank you for your report.
We were not able to reproduce this issue by following the steps you provided. Please provide more detailed steps to reproduce or try to reproduce this issue on a clean installation or latest release.

@warngard
Copy link

warngard commented Nov 2, 2017

@magento-engcom-team I think you need to reproduce it by using a clean installation + Google MySQL 2nd Gen instance (or other sql with same issue).

@magento-engcom-team
Copy link
Contributor

@pareshpa, thank you for your report.
We were not able to reproduce this issue by following the steps you provided. If you'd like to update it, please reopen the issue.

@rehoehle
Copy link

rehoehle commented Nov 6, 2017

@magento-engcom-team we have the same problem with the same version. But we get that only on a second store-view. The default store view is working.

@opobla
Copy link

opobla commented Nov 9, 2017

@magento-engcom-team same problem here with GTID and MySQL 2nd Gen instance

@maxmilton
Copy link

This is still an issue and unforchunately broke a lot of my client installs which are all running on Google Compute Engine.

@magento-engcom-team please don't use canned responces about non-reproducable bugs without actually trying to reproduce. This issue exists in all GCE Cloud SQL 2nd gen instances.

It's simple to reproduce outside of GCE by using the following flag in your MySQL config:

enforce_gtid_consistency = 1

@pareshpa
Copy link
Author

As per my knowledge, GCE does not allow to disable the GTID. If you have any idea regarding how to disable it then please let me know.

@klarre1
Copy link

klarre1 commented Nov 10, 2017

It's due to GTID-based replication in MySQL. So to replicate you need to set up an MySQL server with GTID-based replication.
Ie. use Google MySQL 2nd Gen instances that has GTID-based replication as default and not as an option,.

Install an new Magento 2.1.8 or later and try to add a subcategory under a store view root category and the error appears. I have had it on several installations and the only solution was to downgrade or move MySQL to a none GTID-based replication.

@magento-engcom-team How to re open this issue?

@warngard
Copy link

@magento-engcom-team This should be opened again.

We have someone trying to solve this here
https://github.com/bangerkuwranger/Magento-2-GTID-Safe-URL-Rewrite-Tables

@opobla
Copy link

opobla commented Nov 10, 2017 via email

@gilles6
Copy link

gilles6 commented Nov 13, 2017

@NordicWebTeam Great ! It fixed the problem for me.

@bangerkuwranger
Copy link

@NordicWebTeam the module you've linked to DOES work, but it's currently not transaction safe...

The short version of the caveat is that you are likely to get exceptions and/or data issues if more than one admin user is saving a category at a time... so I DON'T currently recommend this for use in production.

The longer version:
This issue appears to have arisen when the Magento team opted to try to accelerate the admin category save by optimizing the performance of the observer that is triggered when the category or product is saved... this observer attempts to update any url rewrites that the product or category change may affect (and it runs whenever you save, whether or not the url is affected). The original routine used a LOT of joins, which was not super efficient use of the SQL engine and could cause overall application performance degradation. The goal was noble, to be sure... but the implementation (as sometimes happens with these things) created an array (!) of temporary tables (?!?) which, like many other SQL constructs in the application, are not binary safe. Was it faster? Sure... but if you use GTID/binary safe replication for increased uptime, failover support, etc... you're screwed. We could have reverted to using JOINs, and that would have worked... or we could have risked crashing the application by pulling the data into php memory space (yuk), but we opted to create a single permanent table that stores the url rewrite info during this process. It's as efficient, if not moreso, than the current temporary table array method... but at this point, there is the possibility that two admin sessions can access the table, one removing the table's contents before the other can access its url rewrite info, and this is NOT GOOD. So, yes, the module fixes one problem while causing another. It's why we haven't marked it for release just yet. We are working on encapsulating all of these table's processes in zend db transactions, locking the table during the transaction, and streamlining the three or four methods called in the process to do so. This should resolve the possible instability and allow for efficient, binary safe transactions, with the risk being reduced to a 'table locked' exception if simultaneous category saves occur.

Alternatively, as mentioned, any cloud sql instances, if they don't require binary safe replication, can have binary logging disabled (make sure to remove any failover/read clones in gcloud, if that's your system), and set the trust_binary_log_creator flag on your instance. Your uptime may suffer... but you can use the admin category save during the process.

I don't know if the Magento team is planning on resolving this issue in any future releases, but given the way it is structuring tables (I'm glaring warily at the preponderance of sql triggers...), binary safe operation, and DB scalability/uptime in general, is less of a priority than application performance in overall design... so I wouldn't hold my breath. Not necessarily what we'd all like, but that's their choice to make.

I'll drop another note in this thread once the module appears stable enough for production use, for anyone who is facing the same issue.

@bangerkuwranger
Copy link

@magento-engcom-team We've reproduced the issue with a clean installation of v2.1.9 using a Google Cloud SQL 2nd gen instance as well... the important step is to create a failover (turn on high-availability), which will guarantee that the temporary table creation in the category url rewrite module will trigger an exception because it violates GTID consistency. This appears to be true for any high-availability SQL-compatible cloud service, but the specific concerns for GCloud are detailed here: https://cloud.google.com/sql/docs/mysql/features#differences
It would be helpful to, at the very least, note any of these incompatibilities in the requirements documentation for Magento 2 so folks aren't caught unawares when deploying the platform in cloud environments.

@gilles6
Copy link

gilles6 commented Nov 15, 2017

The module by @bangerkuwranger actually worked partly for me. I was able to create new subcategories (in the backend: Catalog > Categories), but I was still unable to change the position of subcategories. When running bin/magento indexer:reindex, I still had an error message:

$ bin/magento indexer:reindex
Design Config Grid index has been rebuilt successfully in 00:00:00
Customer Grid index is locked by another reindex process. Skipping.
Category Products index has been rebuilt successfully in 00:00:00
Product Categories index has been rebuilt successfully in 00:00:00
Product Price index has been rebuilt successfully in 00:00:00
SQLSTATE[HY000]: General error: 1787 When @@GLOBAL.ENFORCE_GTID_CONSISTENCY = 1, the statements CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE can be executed in a non-transactional context only, and require that AUTOCOMMIT = 1., query was: CREATE TEMPORARY TABLE IF NOT EXISTS `catalog_product_index_eav_temp` LIKE `catalog_product_index_eav_tmp`
Stock index has been rebuilt successfully in 00:00:00
Catalog Rule Product index has been rebuilt successfully in 00:00:00
Catalog Product Rule index has been rebuilt successfully in 00:00:00
Catalog Search index has been rebuilt successfully in 00:00:00

To fix this issue temporarily, I finally moved my database from Aliyun RDS cloud to my own MySQL server. My Aliyun RDS server is running MySQL 5.6 with enforce_gtid_consistency=ON but I can upgrade to MySQL 5.7, although it's also set to enforce_gtid_consistency=ON, I'm still wondering: could this upgrade fix the issue ?

mysql> show global variables like '%enforce_gtid_consistency%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| enforce_gtid_consistency | ON   |
+--------------------------+-------+
1 row in set (0.01 sec)  

@scrivvles
Copy link

@Gvigner I don't think that will fix the issue, because certain SQL statements will not work (CREATE TEMPORARY TABLE for example) inside of a transaction. As I understand it, your only option is use SQL statements that don't implicitly commit - see here https://dev.mysql.com/doc/refman/5.7/en/implicit-commit.html

That is part of the reason why this feels so important to have in the base Magento instead of as a plugin. GTID consistency is crucial for high-availability web stores. If you are in the midst of a Black Friday or Cyber Monday sales push, losing even a minute of data can be catastrophic. Replication and binary logging are very important in reducing this risk.

@stormbyte
Copy link

@magento-engcom-team Please reopen the issue and
test the setup with the enforce_gtid_consistency.

@klarre1
Copy link

klarre1 commented Nov 16, 2017

I got this answer from Magento Support regarding same issue on the EE version (New installation of Magento EE 2.2).

"After researching this closer I found that we do not support GTID and have seen a couple of problems including this one. It looks like my colleague opened a feature enhancement to get this looked at at some point. I recommend you refer to the release notes when we release new versions to see if we added anything to accommodate this behavior."

So it looks like @magento-engcom-team should be aware of the problem somehow.

@gilles6
Copy link

gilles6 commented Nov 16, 2017

@scrivvles Thanks for the reply but I have no idea how to implement "SQL statements that don't implicitly commit", the only option I see for me with Aliyun RDS for the moment is to downgrade to MySQL 5.5.

@klarre1
Copy link

klarre1 commented Nov 16, 2017

@pareshpa According to @magento-engcom-team you could reopen this issue.
You could refer to all mentioned above I guess.

@kn-wield
Copy link

We are facing this same issue running Magento 2.1.10 running on a Google Cloud MySQL 2nd Gen 5.7 instance.

Turning off binary logging does work for us but is hardly a viable long term solution.

@gabidavila
Copy link

@benmarks This is the issue I was talking about, disabling GTID is hardly the right solution as GTID helps keeping track of replication.

The reason this happens is certain Magento operations triggers up the reindex of the store where you use the DDL CREATE TEMPORARY TABLE, MySQL doesn't have transactional DDL and the changes with this DDL happens inside a transaction.

When enforce-gtid-consistency flag is enabled you can not execute DDL within transactions, i.e.:

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> create temporary table tmp_configurations like core_config_data;
ERROR 1787 (HY000): Statement violates GTID consistency: CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE can only be executed outside transactional context.  These statements are also not allowed in a function or trigger because functions and triggers are also considered to be multi-statement transactions.
mysql> SELECT @@ENFORCE_GTID_CONSISTENCY;
+----------------------------+
| @@ENFORCE_GTID_CONSISTENCY |
+----------------------------+
| ON                         |
+----------------------------+
1 row in set (0.00 sec)

mysql>

@maghamed
Copy link
Contributor

Hey there, in Magento we usually use TEMPORARY TABLE for intermediate calculations.
We do that during the Indexation (storing data of index being built) and for CatalogSearch (where we store product ids retrieved from the search query, and use this temporary table to speed up Layered Navigation building if we use MySQL adapter).

But we don't recommend to replicate the process of calculation to Slaves, what we recommend to do is just replicate given results.
For example, if we will have replication for Create Temporary Table operation - we will always make replication at the time when user launches the Quick Search (like we will proceed search operation on both Master and all Slaves). But sending this data to Slave is useless.
That's why we recommend not to replicate temporary tables at all.
That's achievable using option_mysqld_replicate-wild-ignore-table MySQL coniguration option.

--replicate-wild-ignore-table=magento.tmp_% -does not replicate updates that use a table where the database name is magento and the table name starts with tmp_ prefix.

@maghamed
Copy link
Contributor

Btw we have this recommendation documented in the Magento Dev Docs since 2.0 version of Magento http://devdocs.magento.com/guides/v2.2/config-guide/multi-master/multi-master_slavedb.html

Performance improvement
To improve the performance of master-slave replication, you can filter some tables on slave instances. We recommend filtering all temporary tables with name pattern search_tmp_% that are used for catalog search.

To do this, add the following line to your my.cnf file on your slave instances:

replicate-wild-ignore-table=%.search\_tmp\_%

@gabidavila
Copy link

gabidavila commented Jan 31, 2018

How is the recommended policy for organizations on cloud providers then? Since if you are using a managed service in the cloud and not a VM for your database, you can't edit replicate-wild-ignore-table dynamically.

The MySQL doc suggests a way to alter it through CHANGE REPLICATION FILTER.

Which also doesn't work with two cloud providers I tried using root:

CHANGE REPLICATION FILTER
    REPLICATE_WILD_IGNORE_TABLE = ('%.tmp_%');

Response:

ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER privilege(s) for this operation

Bottom of line, the way the Magento core is dealing right now with reindex does not match the best practices.

I am more than happy to help you all to find a more definitive and satisfying solution for everyone. The way this is right now it may be marginalizing some organizations.

Looking forward to working with you!

@magento-engcom-team
Copy link
Contributor

Hi @maghamed thank you for your researcher.
@pareshpa please consider the solution described by @maghamed

@magento magento locked and limited conversation to collaborators Mar 5, 2018
@maghamed
Copy link
Contributor

maghamed commented Mar 6, 2018

@gabidavila MySQL Replication with GTIDs does not support usage temporary tables inside transactions

Temporary tables. CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE statements are not supported inside transactions when using GTIDs (that is, when the server was started with the --enforce-gtid-consistency option). It is possible to use these statements with GTIDs enabled, but only outside of any transaction, and only with autocommit=1.

https://dev.mysql.com/doc/refman/5.6/en/replication-gtids-restrictions.html

Currently, Magento Indexation mechanism and its base ResourceModel looks like
https://github.com/magento/magento2/blob/2.2-develop/app/code/Magento/Indexer/Model/ResourceModel/AbstractResource.php#L82-L96

   public function syncData()
    {
        $this->beginTransaction();
        try {
            /**
             * Can't use truncate because of transaction
             */
            $this->getConnection()->delete($this->getMainTable());
            $this->insertFromTable($this->getIdxTable(), $this->getMainTable(), false);
            $this->commit();
        } catch (\Exception $e) {
            $this->rollBack();
            throw $e;
        }
        return $this;
    }

Index table resolved by dedicated StrategyInterface

    /**
     * Get index table name with additional suffix
     *
     * @param string $table
     * @return string
     */
    public function getIdxTable($table = null)
    {
        if ($table) {
            return $this->tableStrategy->prepareTableName($table);
        }
        return $this->tableStrategy->prepareTableName($this->getMainTable());
    }

You can find Strategy Interface here - \Magento\Framework\Indexer\Table\StrategyInterface

There are two implementations of this interface:

  1. \Magento\Framework\Indexer\Table\Strategy
  2. \Magento\Catalog\Model\ResourceModel\Product\Indexer\TemporaryTableStrategy

Currently, TemporaryTableStrategy used for
Price Indexer for different product types.

    <type name="Magento\ConfigurableProduct\Model\ResourceModel\Product\Indexer\Price\Configurable">
        <arguments>
            <argument name="tableStrategy" xsi:type="object">Magento\Catalog\Model\ResourceModel\Product\Indexer\TemporaryTableStrategy</argument>
            <argument name="connectionName" xsi:type="string">indexer</argument>
        </arguments>
    </type>
    <type name="Magento\Downloadable\Model\ResourceModel\Indexer\Price">
        <arguments>
            <argument name="tableStrategy" xsi:type="object">Magento\Catalog\Model\ResourceModel\Product\Indexer\TemporaryTableStrategy</argument>
            <argument name="connectionName" xsi:type="string">indexer</argument>
        </arguments>
    </type>
    <type name="Magento\Bundle\Model\ResourceModel\Indexer\Price">
        <arguments>
            <argument name="tableStrategy" xsi:type="object">Magento\Catalog\Model\ResourceModel\Product\Indexer\TemporaryTableStrategy</argument>
            <argument name="connectionName" xsi:type="string">indexer</argument>
        </arguments>
    </type>
    <type name="Magento\Catalog\Model\ResourceModel\Product\Indexer\Price\DefaultPrice">
        <arguments>
            <argument name="tableStrategy" xsi:type="object">Magento\Catalog\Model\ResourceModel\Product\Indexer\TemporaryTableStrategy</argument>
            <argument name="connectionName" xsi:type="string">indexer</argument>
        </arguments>
    </type>
    <type name="Magento\Catalog\Model\ResourceModel\Product\Indexer\Eav\Source">
        <arguments>
            <argument name="tableStrategy" xsi:type="object">Magento\Catalog\Model\ResourceModel\Product\Indexer\TemporaryTableStrategy</argument>
            <argument name="connectionName" xsi:type="string">indexer</argument>
        </arguments>
    </type>

It's possible to re-configure with DI.xml and provide default strategy \Magento\Framework\Indexer\Table\Strategy instead of TemporaryTable one.

But not sure that it's the only place in Magento where we have Temporary Table Usage inside Transactions

Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
Labels
Issue: Clear Description Gate 2 Passed. Manual verification of the issue description passed Issue: Format is valid Gate 1 Passed. Automatic verification of issue format passed
Projects
None yet
Development

No branches or pull requests