magento2: Product EAV index throws error with GTID consistency

Still broken on 2.2 and 2.3 over a year after being reported 😦 Magento should be able to run on a MySQL installation with binary logging enabled.

FOR GOOGLE CLOUD SQL USERS

I’m trying to migrate a 2.3 CE database to Google Cloud SQL on a 2nd gen server. Upon reindexing I get the “violates GTID consistency” error for the Product EAV index.

There’s little info on the forums so far, so thought I’d contribute my findings.

1. Forget about the module (https://github.com/bangerkuwranger/Magento-2-GTID-Safe-URL-Rewrite-Tables), the guy made it for a project on 2.1 and has no plans to maintain it. Won’t install on M2.3

2. Downgrading to a legacy SQL server on Google presented other issues with VM connectivity (ipv4 only, so latency issues / also being discontinued in around a years time)

SOLUTION:

I’m on M2.3 CE and not using db replication, so I just tried to disable “Binary Logging” on the SQL Server. Go to instance > click Edit > go to Enable auto-backups > Uncheck “Enable binary logging (required for replication and earlier position point-in-time recovery)”.

Test bin/magento indexer:reindex My reindexation of Magento completed without the previous error “violates GTID consistency”.

I understand the limitations to this approach, but until Magento gets their act together (don’t hold your breath, issue ongoing since 2017), this is the only way I can find to run M2.3 on Google SQL 2nd Gen Servers.

Any update for this?

@scrivvles, thank you for your report. We’ve created internal ticket(s) MAGETWO-82828 to track progress on the issue.

For anyone coming by or reading from various GitHub issues that all that needs to be done to host Magento 2 on Google Cloud SQL is to simply disable binary logging and having no luck, here is the bit of information that is required to get it working:

You need to be using MySQL version 5.7. You cannot host on Google Cloud’s SQL service using its 5.6 version.

https://dev.mysql.com/doc/refman/5.7/en/replication-options-gtids.html#option_mysqld_enforce-gtid-consistency

Here is the important paragraph that is not mentioned in the 5.6 documentation:

--enforce-gtid-consistency only takes effect if binary logging takes place for a statement. If binary logging is disabled on the server, or if statements are not written to the binary log because they are removed by a filter, GTID consistency is not checked or enforced for the statements that are not logged.

In other words, in 5.6, even with gtid_mode=off, and binary logging off, the enforce-gtid-consistency option will still throw an error. In 5.7 it will not be “checked or enforced” under these same conditions.

@gvigner Thanks for the tip! It seems like this is one of those things that belongs in core.

Received this June 26:

Dear Cloud SQL for MySQL user,

We are writing to let you know that on September 25, 2019, we’re releasing functionality to reduce the replication lag time for high availability on Second Generation MySQL instances. Changes to your instances will be made during your scheduled maintenance window, with a downtime of less than two minutes for most instances.

What do I need to know?

Starting September 25, 2019, we will automatically upgrade your Second Generation MySQL instances’ high availability configuration to Google’s Regional Disks, which synchronously replicate data at the block-level between two zones in a region.

The upgrade to Regional Disks will eliminate replication lag and, combined with Cloud SQL’s automatic failover, provide isolation from many types of infrastructure, hardware and software failures. Regional Disks will also ensure that all previously committed database transactions are persisted and available after failover right up to the time of the failure.

Your instance’s failover replica will be retained as a read replica.

There is no price change for instances configured for high availability. Failover replicas will automatically be retained as read replicas and you won’t be billed for the read replica for 12 months. After September 2020, you will be billed for the read replica at normal rates (you will receive an additional reminder before you are billed).

It’s worth noting there’s no documentation on this at the moment on the Cloud SQL second gen docs, however this change does more or less mean the HA mechanism for MySQL instances will match the current model for PostgreSQL.

@dharake Yeah I figured out how to get it working in GCP. It was a very subtle difference in the documentation that clued me into the possibility that 5.7 would behave differently.

@danemacmillan I am indeed. I’ve hosted 2.3.0-2.3.2 without issues.

Kindly find my settings in the attached screenshots, I hope that can help you.

Linking to https://github.com/magento/magento2/issues/11055

The Module https://github.com/bangerkuwranger/Magento-2-GTID-Safe-URL-Rewrite-Tables only solves part of the problem with the queries