Magento2: Product EAV index throws error with GTID consistency

[scrivvles]

Attempting to reindex the Product EAV when binary logging or replication is enabled on MySQL 5.7throws a SQL error. This same type of error is thrown when attempting to create a new category.

Preconditions

1. Environment is NGINX 1.10.3 on Ubuntu 16.04.3 LTS running PHP7.0-fpm on a Google Cloud instance.
2. MySQL version is 5.7 running on Google Cloud Platform SQL

Steps to reproduce

1. Make sure that your Google SQL db has binary logging enabled AND/OR you have replication enabled.
2. go to your magento installation and run php bin/magento indexer:reindex

Expected result

All indexes run correctly and display that they have rebuilt successfully

Actual result

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 IF NOT EXISTS catalog_product_index_eav_temp LIKE catalog_product_index_eav_tmp

This error indicates that there is a transaction that violates GTID consistency somewhere in the Product EAV indexing SQL. One suggestion that was made to avoid this problem would be a CREATE VIEW instead of CREATE TEMPORARY TABLE in the indexer module.

[gilles6]

The problem was fixed for me after installing this module:
https://github.com/bangerkuwranger/Magento-2-GTID-Safe-URL-Rewrite-Tables

[scrivvles]

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

[magento-engcom-team]

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

[gilles6]

Great news!

[avra911]

@gvigner for us didnt worked in all projects, it depends if root category is assigned to a store or not, which makes create sub-category failing under some circumstances

[stormbyte]

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

[loganstellway]

Issue 15209 reports the same MySQL error while creating a category with MySQL binary logging enabled.

[pyyick]

Any update for this?

[gwharton]

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.

[volanar]

Magento 2.3.0 don't run on a MySQL installation with binary logging enabled.

[robolmos]

Is there any update?

At the very least the docs should be updated to indicate this issue and a workaround if one is available, such as ignoring temporary tables.

[dharake]

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.

[dferdoille]

Hello,

Any update about this ? Are you working on it and do you have an idea when it will be fixed ?

• Current version : Magento 3.0 - I can't create products (it was ok with Magento 2.2.2 and Bangerkuwranger_GtidSafeUrlRewriteFallback module)

• I can't disable binary logging on my MySQL instance.

• I tried to reconfigure table strategy (#11055) but it didn't work...

• I'm currently trying to disable the use of temporary tables patching Magento lib (maybe not a good idea but I need a workaround to move forward...)

Thanks

[alexgoodey]

@dferdoille you are welcome to try my solution at https://github.com/alexgoodey/magento_gtid_fix

No guarantees.

[bradjones1]

I believe this can be marked as a duplicate of #15209, as that is scheduled for remediation.

[danemacmillan]

@dharake Are you still hosting 2.3 on GCP's cloud SQL service?

Disabling binary logging does not disable the GTID settings. Magento2 still throws exceptions in various places, like updating stock, reorganizing categories, indexing.

[dharake]

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

[danemacmillan]

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.

[danemacmillan]

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

[dharake]

Ah great, glad you figured it out.

[dferdoille]

Thanks @alexgoodey.
I managed to run magento 2.2.7 on a mysql server of a private cloud thanks to your extension.
I also have to disable re-indexing on save mode to switch it to schedule reindexation (especially to avoid updating stock error)

[bradjones1]

It's worth noting the above work-around for 5.7 isn't applicable if you are needing to run in high-availability mode. That said, Google Cloud have announced that later this month, they are moving from MySQL replication to regional persistent disks for the HA backing infrastructure, so it's possible that opens up some additional possibilities here.

[diegosucaria]

@bradjones1 where did you read that? I missed it.

[bradjones1]

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.

[rakeshdonga]

Hello
any updates of this issue i have same issue facing in magento 2.3.1
also i can install below extension but not working still
https://github.com/bangerkuwranger/Magento-2-GTID-Safe-URL-Rewrite-Tables
Thanks!!

[bradjones1]

As an update to https://github.com/magento/magento2/issues/12124#issuecomment-530159163, Google postponed the automatic upgrade of second-gen instances to disk-based replication, but the following was sent Dec. 11 to second-gen instance owners:

We are writing to remind you of an upcoming revision to the automatic upgrade schedule for Regional Persistent Disks. Starting April 13, 2020, we will upgrade your Second Generation MySQL instances’ high availability configuration to Google's Regional Disks. Changes to your instances will be made during your scheduled maintenance window.

So, there's a firm date at least for a change. There is an option to upgrade manually before then, though HA config owners lose the "free" year of a read replica.

There's also the additional scheduled EOL of first-generation instances on March 25.

The combined impact to Magento 2 site owners on Google Cloud appears thus:

1. You will end up on a second-gen instance any way you slice it (if you stay with their Cloud SQL product, of course.)
2. Binary logging for replication appears to be the proximate culprit with second-gen compatibility. Per comments above, disabling binary logging is one path to using second-gen, but it also had until now precluded your use of an HA config.
3. With the move to disk-based HA, it appears binary logging is now only a requirement for read replicas, since they would be truly "replicated." In other words, until/unless Magento 2 gets patched for compatibility with binary logging/GTID transactions, you can run HA but not use a read replica. (The failover instance cannot be read from, per the docs.)

[laurensknoll]

I investigated the EAV indexer code to figure out what's going wrong. Found out that this commit configured the indexer with a TemporaryTableStrategy. This strategy tells the indexer to use a TEMP table instead of SQL tables catalog_product_index_eav_idx or catalog_product_index_eav_tmp.

Because this temp table is causing issues. I disabled the TempTableStrategy to default to the regular TableStrategy for the EAV indexer in file: vendor/magento/module-catalog/etc/di.xml

    <type name="Magento\Catalog\Model\ResourceModel\Product\Indexer\Eav\Source">
        <arguments>
            <!-- <argument name="tableStrategy" xsi:type="object">Magento\Catalog\Model\ResourceModel\Product\Indexer\TemporaryTableStrategy</argument> -->
            <argument name="connectionName" xsi:type="string">indexer</argument>
        </arguments>
    </type>

When using the regular TableStrategy, which is using the catalog_product_index_eav_tmp-table, the indexer ran against my bin log-enabled Cloud SQL MySQL without a GTID issue.

magento@magento-admin:/app$ bin/magento indexer:reindex catalog_product_attribute
Product EAV index has been rebuilt successfully in 00:00:00

However I don't like to alter the di.xml-files, I just want to run Magento! Therefore I looked at the commit history again. The commit message reveals it was meant to fix an issue with Price reindexation. This indexer is not using transactions to sync TEMP table data to the replicaTable.

    private function reindexBatch(PriceInterface $priceIndexer, Select $batch): void
    {
        $entityIds = $this->getEntityIdsFromBatch($batch);

        if (!empty($entityIds)) {
            // Temporary table will created if not exists
            $idxTableName = $this->_defaultIndexerResource->getIdxTable();
            $this->_emptyTable($idxTableName);

            if ($priceIndexer->getIsComposite()) {
                $this->_copyRelationIndexData($entityIds);
            }

            // Reindex entities by id
            $priceIndexer->reindexEntity($entityIds);

            // Sync data from temp table to index table
            $this->_insertFromTable($idxTableName, $this->getReplicaTable());

            // Drop temporary index table
            $this->_defaultIndexerResource->getConnection()->dropTable($idxTableName);
        }
    }

..

    protected function _insertFromTable($sourceTable, $destTable, $where = null)
    {
        $sourceColumns = array_keys($this->getConnection()->describeTable($sourceTable));
        $targetColumns = array_keys($this->getConnection()->describeTable($destTable));
        $select = $this->getConnection()->select()->from($sourceTable, $sourceColumns);
        if ($where) {
            $select->where($where);
        }
        $query = $this->getConnection()->insertFromSelect(
            $select,
            $destTable,
            $targetColumns,
            \Magento\Framework\DB\Adapter\AdapterInterface::INSERT_ON_DUPLICATE
        );
        $this->getConnection()->query($query);
    }

Source:

• reindexBatch
• insertFromTable

I'm no Magento developer, but it seems valid to _not_ use a transaction in this context. Simply because it's just staging data from a clean temp table to a result set.

Anyhow, I first validated this idea by removing the transactions from the Eav syncData-method.

    /**
     * @inheritdoc
     */
    protected function syncData($indexer, $destinationTable, $ids = null): void
    {
        $connection = $indexer->getConnection();
        $sourceTable = $indexer->getIdxTable(); // Here the temp table is created, and in context of a transaction, also our issue..
        $sourceColumns = array_keys($connection->describeTable($sourceTable));
        $targetColumns = array_keys($connection->describeTable($destinationTable));
        $select = $connection->select()->from($sourceTable, $sourceColumns);
        $query = $connection->insertFromSelect(
            $select,
            $destinationTable,
            $targetColumns,
            AdapterInterface::INSERT_ON_DUPLICATE
        );
        $connection->query($query);

        // $connection = $indexer->getConnection();
        // $connection->beginTransaction();
        // try {
        //     $sourceTable = $indexer->getIdxTable();
        //     $sourceColumns = array_keys($connection->describeTable($sourceTable));
        //     $targetColumns = array_keys($connection->describeTable($destinationTable));
        //     $select = $connection->select()->from($sourceTable, $sourceColumns);
        //     $query = $connection->insertFromSelect(
        //         $select,
        //         $destinationTable,
        //         $targetColumns,
        //         AdapterInterface::INSERT_ON_DUPLICATE
        //     );
        //     $connection->query($query);
        //     $connection->commit();
        // } catch (\Exception $e) {
        //     $connection->rollBack();
        //     throw $e;
        // }
    }

This change resolves the GTID issue in my installation. Logically this only fixes the Product EAV GTID issue. So I'm not sure if this makes Magento GTID consistent, but for our installation it would be an improvement.

Like to hear from a Magento developer on how to get this change tested/patched/merged etc.

[Cna59]

I just asked Google why cloud SQL does not support CREATE TEMPORARY TABLE statement.

The Google support create a public issue and ask me to "leaving a comment explaining how this affects your use case, as this will increase its visibility to the Product Team."

If you are interested too, maybe you could add a comment

https://issuetracker.google.com/issues/160213115