search

magento / magento2

Prior to making any Submission(s), you must sign an Adobe Contributor License Agreement, available here at: https://opensource.adobe.com/cla.html. All Submissions you make to Adobe Inc. and its affiliates, assigns and subsidiaries (collectively “Adobe”) are subject to the terms of the Adobe Contributor License Agreement.
http://www.magento.com
Open Software License 3.0
11.56k stars 9.32k forks source link

Triggers for scconnector_google_feed_cl are not removed correctly #37472

Closed sippsolutions closed 1 year ago

sippsolutions commented 1 year ago

Preconditions and environment

Steps to reproduce

Try to save new product images

Expected result

Images are saved

Actual result

SQLSTATE[42S02]: Base table or view not found: 1146 Table 'xyz.scconnector_google_feed_cl' doesn't exist, query was: DELETE FROM catalog_product_entity_media_gallery_value WHERE (value_id = 336222 AND row_id = 92521 AND store_id = 0)

Additional information

In \Magento\GoogleShoppingAds\Setup\Recurring the scconnector_google_feed table is removed, but existing triggers are not removed. Seems to be other people are having that issue too: https://magento.stackexchange.com/questions/323050/scconnector-google-feed-cl-error-after-product-save-just-upgraded-to-2-3-5-p2

Issue is about the triggers trg_catalog_product_entity_media_gallery_value_after_insert

BEGIN
SET @entity_id = (SELECT `entity_id` FROM `catalog_product_entity` WHERE `row_id` = NEW.`row_id`);
INSERT IGNORE INTO `scconnector_google_feed_cl` (`entity_id`) values(@entity_id);
END

trg_catalog_product_entity_media_gallery_value_after_update

BEGIN
SET @entity_id = (SELECT `entity_id` FROM `catalog_product_entity` WHERE `row_id` = NEW.`row_id`);
IF (NOT(NEW.`value_id` <=> OLD.`value_id`) OR NOT(NEW.`store_id` <=> OLD.`store_id`) OR NOT(NEW.`label` <=> OLD.`label`) OR NOT(NEW.`position` <=> OLD.`position`) OR NOT(NEW.`disabled` <=> OLD.`disabled`) OR NOT(NEW.`record_id` <=> OLD.`record_id`) OR NOT(NEW.`row_id` <=> OLD.`row_id`)) THEN INSERT IGNORE INTO `scconnector_google_feed_cl` (`entity_id`) values(@entity_id); END IF;
END

trg_catalog_product_entity_media_gallery_value_after_delete

BEGIN
SET @entity_id = (SELECT `entity_id` FROM `catalog_product_entity` WHERE `row_id` = OLD.`row_id`);
INSERT IGNORE INTO `scconnector_google_feed_cl` (`entity_id`) values(@entity_id);
END

Release note

No response

Triage and priority

m2-assistant[bot] commented 1 year ago

Hi @sippsolutions. Thank you for your report. To speed up processing of this issue, make sure that the issue is reproducible on the vanilla Magento instance following Steps to reproduce. To deploy vanilla Magento instance on our environment, Add a comment to the issue:

sippsolutions commented 1 year ago

Patch that should fix the issue:

--- vendor/magento/google-shopping-ads/composer.json
+++ vendor/magento/google-shopping-ads/composer.json
@@ -21,6 +21,7 @@
     "require": {
         "php": "7.0.2||7.0.4||>=7.0.6",
         "magento/framework": ">=101.0.4",
-        "magento/module-eav": ">=101.0.3"
+        "magento/module-eav": ">=101.0.3",
+        "magento/module-catalog": ">=101"
     }
 }
--- vendor/magento/google-shopping-ads/Setup/Recurring.php
+++ vendor/magento/google-shopping-ads/Setup/Recurring.php
@@ -8,6 +8,7 @@

 namespace Magento\GoogleShoppingAds\Setup;

+use Magento\Catalog\Model\ResourceModel\Product\Gallery;
 use Magento\Framework\Mview\View;
 use Magento\Framework\Setup\ModuleContextInterface;
 use Magento\Framework\Setup\ModuleDataSetupInterface;
@@ -62,6 +63,16 @@
             foreach (['scconnector_google_feed_cl', 'scconnector_google_remove_cl'] as $table) {
                 $setup->getConnection()->dropTable($setup->getTable($table));
             }
+
+            foreach (['insert', 'update', 'delete'] as $suffix) {
+                $setup->getConnection()->dropTrigger(
+                    sprintf(
+                        'trg_%s_after_%s',
+                        Gallery::GALLERY_VALUE_TABLE,
+                        $suffix
+                    )
+                );
         }
     }
 }
+}
m2-assistant[bot] commented 1 year ago

Hi @engcom-Dash. 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: :point_down:

engcom-Dash commented 1 year ago

Hi @sippsolutions ,

Thanks for Contribution and collaboration !

Verified the issue in 2.4.6 magento instance and issue is not reproducible.

Kindly refer the below screenshots:

Screenshot 2023-05-11 at 4 59 34 PM Screenshot 2023-05-12 at 10 28 36 AM

Steps to reproduce: 1.Install 2.3.7-p4 Magento instance 2.Upgrade instance to 2.4.6 by following DevDocs 3.Trying add new Product images

We can successfully save the products images without any issue.Kindly provide more information or screenshots if the issue still reproducible.

Thanks!

engcom-Dash commented 1 year ago

Hi @sippsolutions ,

We have noticed that this issue has not been updated for a period of 14 Days.
Hence we assume that this issue is fixed now, so we are closing it. Please raise a fresh ticket or reopen this ticket if you need more assistance on this.

Thanks.

sippsolutions commented 1 year ago

I have now gathered more information.

When updating from 2.3.x there is a scconnector_google_feed entry in the mview_state table. This leads to having the scconnector tables inside the db trigger.

While in 2.4.x these tables are being removed with \Magento\GoogleShoppingAds\Setup\Recurring it is limited to only being executed once (if (version_compare($context->getVersion(), '4.0.1', '<'))) - which contradicts the idea of a recurring script imho.

We were using a third party module that iterates over all indexers and sets them as scheduled.

foreach (array_keys($this->configInterface->getIndexers()) as $indexerId) {
    $indexer = $this->indexerRegistry->get($indexerId);
    $indexer->setScheduled(true);
}

Even if you remove that module, the scconnector indexers are permanently set as scheduled (mode = enabled for scconnector_google_feed in mview_state) because of the 3rd party script that has been executed once and will therefor be included in the triggers that get created/updated within every setup:upgrade - even though the corresponding tables do not even exist anymore.

To fix this the if (version_compare($context->getVersion(), '4.0.1', '<')) should be removed, so the recurring script gets executed every time again - or even better - there should be a one-time patch that removes the entries from the mview_state table, to have a matching data set with clean 2.4.x installs. Alternatively there should be a check if the table exists before adding it to the trigger.

Steps to reproduce:

adam-webee commented 1 year ago

I was facing the same issue, and for me, this was the solution:

delete from mview_state where view_id like '%scconnector%';
sippsolutions commented 1 year ago

@magento @engcom-Dash please re-open the issue

engcom-Dash commented 1 year ago

Hi @sippsolutions ,

We are closing this issue because seems like duplicate of #37801 and we are following with latest issue #37801 .

Thanks.

robjstanley commented 9 months ago

I was facing the same issue, and for me, this was the solution:

delete from mview_state where view_id like '%scconnector%';

Solved the issue for me, thanks.