Statement violates GTID consistency

[raybog]

We are using the Google Cloud SQL (MySQL 2e gen. 5.7) server where we encountered the following problem, when trying to add subcategory.

This can be "solved" by setting Binary logging to disable at GCP but it is not a good practice.

Preconditions

Clean installation of Magento 2.2.3 connected to Google Cloud SQL (MySQL 2e gen. 5.7) with Binary logging enabled.

Steps to reproduce

Add subcategory via Admin Panel

Expected result

Category should be added

Actual result

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.

[lstellway]

I am using the same setup and experience this issue during a reindex:

php bin/magento indexer:reindex

The reindex issue is addressed in 12124. The source of the issue is probably the same.

[ghost]

Hi @raybog , thank you for your report. Please follow these guidelines for proper tracking of your issue. You can report Commerce-related issues in one of two ways:
You can use the Support portal associated with your account
or
If you are a Partner reporting on behalf of a merchant, use the Partner portal.

GitHub is intended for Magento Open Source users to report on issues related to Open Source only. There are no account management services associated with GitHub.

[gwharton]

Why was this closed!!. This is still broken in 2.2 and 2.3. Magento should be able to run on a MySQL installation with binary logging enabled.

[ghost]

@gwharton Because this is Commerce-related issues

[gwharton]

What makes you think this is commerce related. The OP does not mention it and i get the bug in open source.

[magento-engcom-team]

Hi @engcom-backlog-nazar. Thank you for working on this issue.
In order to make sure that issue has enough information and ready for development, please read and check the following instruction: 👇

• 1. Verify that issue has all the required information. (Preconditions, Steps to reproduce, Expected result, Actual result).

  Details

  If the issue has a valid description, the label Issue: Format is valid will be added to the issue automatically. Please, edit issue description if needed, until label Issue: Format is valid appears.

• 2. Verify that issue has a meaningful description and provides enough information to reproduce the issue. If the report is valid, add Issue: Clear Description label to the issue by yourself.

• 3. Add Component: XXXXX label(s) to the ticket, indicating the components it may be related to.

• 4. Verify that the issue is reproducible on 2.3-develop branch

  Details

  - Add the comment @magento-engcom-team give me 2.3-develop instance to deploy test instance on Magento infrastructure.
  - If the issue is reproducible on 2.3-develop branch, please, add the label Reproduced on 2.3.x.
  - If the issue is not reproducible, add your comment that issue is not reproducible and close the issue and stop verification process here!

• 5. Verify that the issue is reproducible on 2.2-develop branch.

  Details

  - Add the comment @magento-engcom-team give me 2.2-develop instance to deploy test instance on Magento infrastructure.
  - If the issue is reproducible on 2.2-develop branch, please add the label Reproduced on 2.2.x

• 6. Add label Issue: Confirmed once verification is complete.

• 7. Make sure that automatic system confirms that report has been added to the backlog.

[ghost]

@gwharton but if we accept this we will have 2 duplicate issues ?

[gwharton]

True, but this issue is failing adding a category and the other is failed reindexing. We are assuming the root cause is the same for both (admitedly seems like it is but we dont know that for sure yet do we?).

[magento-engcom-team]

@engcom-backlog-nazar Thank you for verifying the issue. Based on the provided information internal tickets MAGETWO-97511, MAGETWO-97512 were created

[rakeshdonga]

hello @engcom-backlog-nazar
i have same issue facing any update?
Thanks!!!

[ebanolopes]

Hello! Is this something that Magento team is working on for next versions or it is on backlog at this moment? Thanks!

[bradjones1]

This probably most seriously impacts Google Cloud SQL users; there is a notable event with the EOL of first-generation instances which did not utilize binary logging for HA replication on March 5. See #12124 (comment) for a more complete write-up.

@magento-engcom-team, per #15209 (comment) above, is binary logging/GTID transaction compatibility on the roadmap or is this firmly backlogged/needing community work?

[ebanolopes]

@bradjones1 not really only for Google Cloud, all managed databases are following this. Azure will also block non GTIDs instances on a close future. We're using Google Cloud, moved to Azure to avoid this and now we're working to try to fix this, as we don't want to have a self managed database inside our business.

[bradjones1]

@ebanolopes Indeed this is not just a Google Cloud issue, however it is pertinent in so far as they are sunsetting related functionality as per above.

I do not have any insight into Magento development priorities; to be honest, I am not a real fan of the product and we are moving away from M2. Sorry I don't have any information for you there. I suspect this issue will either only move forward if enterprise customers ask for it, or there is a community contribution.

[jorgb90]

How can this still be a problem in 2.4?!

[github-jira-sync-bot]

❌ Cannot export the issue. This GitHub issue is already linked to Jira issue(s): https://jira.corp.magento.com/browse/AC-804

[ihor-sviziev]

According to https://cloud.google.com/sql/docs/features#differences, MySQL for Cloud SQL 8.0 does support CREATE TEMPORARY TABLE statement, and Magento 2.4 does support MySQL 8.

So, this is not an issue for Magento 2.4 installations, that does support MySQL 8.

I created PR that adds this info to the docs: magento/devdocs#9111

[m2-assistant]

Hi @ihor-sviziev. Thank you for working on this issue.
Looks like this issue is already verified and confirmed. But if you want to validate it one more time, please, go though the following instruction:

• 1. Add/Edit Component: XXXXX label(s) to the ticket, indicating the components it may be related to.
• 2. Verify that the issue is reproducible on 2.4-develop branch
     Details

     - Add the comment @magento give me 2.4-develop instance to deploy test instance on Magento infrastructure.
     - If the issue is reproducible on 2.4-develop branch, please, add the label Reproduced on 2.4.x.
     - If the issue is not reproducible, add your comment that issue is not reproducible and close the issue and stop verification process here!
• 3. If the issue is not relevant or is not reproducible any more, feel free to close it.

---

• Join Magento Community Engineering Slack and ask your questions in #github channel.