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 #11055

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

[lstellway]

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.

[loid345]

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 (Something went wrong while saving the category in Magento 2.1.9 #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.

[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

[jorgb90]

We need this solved..

[ihor-sviziev]

Hi @scrivvles,
From the documentation https://cloud.google.com/sql/docs/features#unsupported-statements, it seems like

The following statement is unsupported in MySQL for MySQL for Cloud SQL 5.6 and 5.7, but is supported in MySQL for Cloud SQL 8.0.

CREATE TEMPORARY TABLE statements inside transactions

Currently, Magento 2.4 supports MySQL 8 https://devdocs.magento.com/guides/v2.4/install-gde/system-requirements.html.

Could you confirm that this issue is fixed now?

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

[ihor-sviziev]

Note I created PR that adds note to dev docs. magento/devdocs#9111