Database table triggers creation using mview multiple subscriptions with different entity columns for one table

[aleksejsbaranovs10]

[Linked PR in magento2 project] -> magento/magento2#31192

Issue description

Two indexers with mview configuration, subscribed to the same table but using different "entity_column" values override each other when creating triggers (enabling indexers to work on schedule mode).

For reproduction was used Magento core indexer "Catalog Search" and custom indexer.
Both indexers subscribed to "catalog_product_super_link" table. "Catalog Search" indexer uses "entity_column" value "product_id". Custom indexer uses "entity_column" value "parent_id".

"catalogsearch_fulltext" mview subscription to "catalog_product_super_link" table changes:

<table name="catalog_product_super_link" entity_column="product_id" />

Custom indexer mview subscription to "catalog_product_super_link" table changes:

<table name="catalog_product_super_link" entity_column="parent_id" />

Preconditions (*)

1. Issue is reproducible both on CE & EE versions. "2.4-develop" branch
2. MySQL 8

Steps to reproduce (*)

1. Create test indexer. Example:

<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:Indexer/etc/indexer.xsd">
    <indexer id="test_view_id"
             view_id="test_view_id"
             class="Magento\TestModule\Model\Indexer\TestIndexer"
    >
        <title translate="true">Test indexer title</title>
    </indexer>
</config>

2. Create mview configuration for test indexer with subscription on "catalog_product_super_link" and entity column "parent_id". Example:

<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:noNamespaceSchemaLocation="urn:magento:framework:Mview/etc/mview.xsd">
    <view id="test_view_id"
          class="Magento\TestModule\Model\Indexer\TestIndexer"
          group="indexer"
    >
        <subscriptions>
            <table name="catalog_product_super_link" entity_column="parent_id" />
        </subscriptions>
    </view>
</config>

3. Enable test indexer to work on schedule mode.
4. Enable "Catalog Search" indexer to work on schedule.
5. Validate "catalog_product_super_link" table triggers.

Actual result (*)

1. On reproduction step 3, after enabling the test indexer to work on schedule mode the following trigger is configured for "catalog_product_super_link" table (e.g insert trigger):

BEGIN
INSERT IGNORE INTO `test_view_id_cl` (`entity_id`) VALUES (NEW.`parent_id`);
END

2. On reproduction step 4, after enabling the "catalog search" to work on schedule mode the following trigger is configured for "catalog_product_super_link" table (e.g insert trigger):

BEGIN
INSERT IGNORE INTO `catalogsearch_fulltext_cl` (`entity_id`) VALUES (NEW.`product_id`);
INSERT IGNORE INTO `test_view_id_cl` (`entity_id`) VALUES (NEW.`product_id`);
END

3. Enabling "catalog search" indexer to work on schedule mode rewrote previously created trigger. For "test_view_id_cl" "parent_id" was changed to "product_id".

Expected result (*)

1. On reproduction step 4, enabling the "catalog search" to work on schedule mode should not rewrite previously created trigger and change "entity_column" for that:

BEGIN
INSERT IGNORE INTO `catalogsearch_fulltext_cl` (`entity_id`) VALUES (NEW.`product_id`);
INSERT IGNORE INTO `test_view_id_cl` (`entity_id`) VALUES (NEW.`parent_id`);
END

Additional Information (*)

• CE trigger creation class -> \Magento\Framework\Mview\View\Subscription
• EE trigger creation class -> \Magento\CatalogStaging\Model\Mview\View\Attribute\Subscription

[m2-assistant]

Hi @aleksejsbaranovs10. Thank you for your report.
To help us process this issue please make sure that you provided sufficient information.

Please, add a comment to assign the issue: @magento I am working on this

---

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