magento2: Something went wrong while saving the category in Magento 2.1.9

Preconditions

After upgrading the version Magento Version from 2.1.7 to 2.1.9

Steps to reproduce

Create a new category and save

Expected result

New Category Page should save without any error

Actual result

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} [] []

About this issue

Original URL
State: closed
Created 7 years ago
Reactions: 1
Comments: 37 (6 by maintainers)

Most upvoted comments

@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.

magento-engcom-team on Nov 2, 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.

klarre1 on Nov 16, 2017

@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.

bangerkuwranger on Nov 13, 2017

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

stormbyte on Nov 16, 2017

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

maxmilton on Nov 9, 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.

avra911 on Oct 23, 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.

scrivvles on Oct 3, 2017

@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>

gabidavila on Jan 22, 2018

@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.

scrivvles on Nov 15, 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?

klarre1 on Nov 10, 2017

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.

pareshpa on Nov 10, 2017

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

opobla on Nov 9, 2017

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?

stormbyte on Oct 23, 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.

klarre1 on Sep 26, 2017