Something went wrong while saving the category in Magento 2.1.9

[pareshpa]

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

[klarre1]

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]

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.

[scrivvles]

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]

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]

I have same issue. Magento 2.1.8.

[stormbyte]

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]

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]

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]

did you disabled GTID?

…

---- On Thu, 26 Oct 2017 19:03:58 +0300 gvi <notifications@github.com> wrote ---- Same problem here when 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 I fix it ? — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub, or mute the thread.

[TarasSavchuk]

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

[gilles6]

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/)