SQLSTATE[HY000]: General error: 1787 with Magento 2.2.1 when adding subcategory.

[gilles6]

Preconditions

1. Magento CE 2.2.1
2. MySQL 5.6 (Aliyun RDS)
3. PHP Version 7.1.11-1+ubuntu16.04.1+deb.sury.org+1

Steps to reproduce

1. In the backend, go to Catalog > Categories, add a subcategory.

Expected result

1. It should create a subcategory under the chosen root category.

Actual result

1. I get the following message in the backend:

Something went wrong while saving the category.

And in the log, I get this message:

[2017-11-13 04:34:03] main.CRITICAL: 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 tmp_select_fc67d54614b698ebb1a8d4d6da400330 (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('13'))) {"exception":"[object] (Zend_Db_Statement_Exception(code: 0): 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 tmp_select_fc67d54614b698ebb1a8d4d6da400330 (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('13'))) at /var/www/html/myproject/vendor/magento/zendframework1/library/Zend/Db/Statement/Pdo.php:235, PDOException(code: HY000): 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. at /var/www/html/myproject/vendor/magento/zendframework1/library/Zend/Db/Statement/Pdo.php:228)"} []

When I create a new root category, adding a subcategory works fine, but after a while or for some reason, I finally get this error message. I had the same error in my previous installation of Magento (2.1.19), I thought it would be fixed in 2.2.1, but it still remains.

My database server is on Aliyun RDS, I asked them if I could modify GTID settings but they say it can't be changed.

[gilles6]

Problem fixed by this module:
https://github.com/bangerkuwranger/Magento-2-GTID-Safe-URL-Rewrite-Tables

[magento-engcom-team]

@Gvigner, 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.
We tested the issue on 2.3.0-dev, 2.1.10, 2.2.1

[gilles6]

Actually, it only partly fixed my issue. I can now create subcategories, but I can't move them. And when I run bin/magento indexer:reindex, I see this error message:

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

I guess you can reproduce these problems by enable GTID in MySQL.

[stormbyte]

Same problem as #11055

[HamiltonWang]

#11055 is closed but the problem is still there. Workaround doesn’t work. A plain installation will or produce this problem, it normally happened when some other modules are installed by purchasing modules from third party.