search

postnl / postnl-magento2

This is the official Magento 2 extension for the logistics company PostNL. Add shipping options and parcelshops to your checkout. Create labels with track and trace functionality from the backend.
59 stars 60 forks source link

[BUG] You cannot define a correlation name 'magento_sales_order_grid_archive' more than once #322

Closed orkuncv closed 1 year ago

orkuncv commented 2 years ago

To Reproduce Steps to reproduce the behavior:

  1. Make order on Commerce 2.4.4
  2. Go to admin
  3. Go to sales order grid in admin panel
  4. See if last order is in sales order grid

Expected result Sales order grid going to be updated after new order is created

Actual result Sales order grid won't be updated on admin panel because of missing crons. Grid Async is enabled.
After disabling tig postnl module sales order grid works fine.

Errors report.ERROR: Cron Job sales_grid_order_async_insert has an error: You cannot define a correlation name magento_sales_order_grid_archive' more than once. report.ERROR: Cron Job sales_grid_order_creditmemo_async_insert has an error: You cannot define a correlation name 'magento_sales_order_grid_archive' more than once.

Stacktrace `[2022-07-11T07:44:14.814179+00:00] report.CRITICAL: Zend_Db_Select_Exception: You cannot define a correlation name 'magento_sales_order_grid_archive' more than once in vendor/magento/zendframework1/library/Zend/Db/Select.php:833 Stack trace:

0 /vendor/magento/framework/DB/Select.php(305): Zend_Db_Select->_join('left join', Array, 'sales_order.ent...', Array, NULL)

8 vendor/magento/module-sales/Model/ResourceModel/Provider/NotSyncedDataProvider.php(42): Magento\Sales\Model\ResourceModel\Provider\UpdatedIdListProvider->getIds('sales_order', 'sales_order_gri...')

9 /vendor/tig/postnl-magento2/Plugin/Order/AsyncPlugin.php(104): Magento\Sales\Model\ResourceModel\Provider\NotSyncedDataProvider->getIds('sales_order', 'sales_order_gri...')

10 /generated/code/TIG/PostNL/Plugin/Order/AsyncPlugin/Interceptor.php(23): TIG\PostNL\Plugin\Order\AsyncPlugin->beforeRefreshBySchedule()

11 /vendor/magento/framework/Interception/Interceptor.php(121): TIG\PostNL\Plugin\Order\AsyncPlugin\Interceptor->beforeRefreshBySchedule(Object(Magento\Sales\Model\ResourceModel\Grid\Interceptor))

tig-jeffreybranderhorst commented 2 years ago

Hi @orkuncv ,

Thank you for submitting this issue, we will test this on a default Magento environment. I will be back with the results.

Have a great day, Jeffrey

tig-jeffreybranderhorst commented 2 years ago

Hi @orkuncv ,

We have tested this on a Clean Magento install only with PostNL and the crons run successfully with Async on, we are not able to reproduce this.
I suspect there is a conflict with another extension. I recommend to contact PostNL about this for further support, you can contact them via digitaleklantsupport@postnl.nl . They can help you investigate this issue further!

Have a great day, Jeffrey

tig-jeffreybranderhorst commented 2 years ago

Hi @orkuncv ,

Because we could not reproduce it, I will close this issue on github. If this issue needs to be investigated further on your environment, we suggest you contact PostNL via digitaleklantsupport@postnl.nl

Have a great day, Jeffrey

leonhelmus commented 2 years ago

@tig-jeffreybranderhorst we have the same issue did you try it on magento commerce or magento open source?

Magento open source does not have archive option while commerce does. It also first needs to be enabled otherwise you will not get this error.

tig-jeffreybranderhorst commented 2 years ago

Hi @leonhelmus ,

I tested once more on 2.4.5 and seems like we can reproduce it now, I just placed it on the backlog.

Have a great day, Jeffrey

leonhelmus commented 2 years ago

@tig-jeffreybranderhorst can it be open again than :)

mihaimatei commented 1 year ago

Having the same issue on Enterprise 2.4.5-p1, with latest v1.12.7 and is throwing error on these cron jobs:

All above are showing the same error: report.CRITICAL: Zend_Db_Select_Exception: You cannot define a correlation name 'magento_sales_order_grid_archive' more than once and same stack trace:

#0 /app/vendor/magento/framework/DB/Select.php(306): Zend_Db_Select->_join('left join', Array, 'sales_order.ent...', Array, NULL)
#1 /app/vendor/magento/zendframework1/library/Zend/Db/Select.php(378): Magento\Framework\DB\Select->_join('left join', Array, 'sales_order.ent...', Array, NULL)
#2 /app/vendor/magento/module-sales/Model/ResourceModel/Provider/Query/IdListBuilder.php(111): Zend_Db_Select->joinLeft(Array, 'sales_order.ent...', Array)
#3 /app/vendor/magento/framework/Interception/Interceptor.php(58): Magento\Sales\Model\ResourceModel\Provider\Query\IdListBuilder->build('sales_order', 'sales_order_gri...')
#4 /app/vendor/magento/framework/Interception/Interceptor.php(138): Magento\Sales\Model\ResourceModel\Provider\Query\IdListBuilder\Interceptor->___callParent('build', Array)
#5 /app/vendor/magento/framework/Interception/Interceptor.php(153): Magento\Sales\Model\ResourceModel\Provider\Query\IdListBuilder\Interceptor->Magento\Framework\Interception\{closure}('sales_order', 'sales_order_gri...')
#6 /app/generated/code/Magento/Sales/Model/ResourceModel/Provider/Query/IdListBuilder/Interceptor.php(23): Magento\Sales\Model\ResourceModel\Provider\Query\IdListBuilder\Interceptor->___callPlugins('build', Array, Array)
#7 /app/vendor/magento/module-sales/Model/ResourceModel/Provider/UpdatedIdListProvider.php(53): Magento\Sales\Model\ResourceModel\Provider\Query\IdListBuilder\Interceptor->build('sales_order', 'sales_order_gri...')
#8 /app/vendor/magento/module-sales/Model/ResourceModel/Provider/NotSyncedDataProvider.php(42): Magento\Sales\Model\ResourceModel\Provider\UpdatedIdListProvider->getIds('sales_order', 'sales_order_gri...')
#9 /app/vendor/tig/postnl-magento2/Plugin/Order/AsyncPlugin.php(104): Magento\Sales\Model\ResourceModel\Provider\NotSyncedDataProvider->getIds('sales_order', 'sales_order_gri...')
#10 /app/vendor/magento/framework/Interception/Interceptor.php(121): TIG\PostNL\Plugin\Order\AsyncPlugin->beforeRefreshBySchedule(Object(Magento\Sales\Model\ResourceModel\Grid\Interceptor))
#11 /app/vendor/magento/framework/Interception/Interceptor.php(153): Magento\Sales\Model\ResourceModel\Grid\Interceptor->Magento\Framework\Interception\{closure}()
#12 /app/generated/code/Magento/Sales/Model/ResourceModel/Grid/Interceptor.php(23): Magento\Sales\Model\ResourceModel\Grid\Interceptor->___callPlugins('refreshBySchedu...', Array, Array)
#13 /app/vendor/magento/module-sales/Model/GridAsyncInsert.php(58): Magento\Sales\Model\ResourceModel\Grid\Interceptor->refreshBySchedule()
#14 /app/vendor/magento/module-sales/Cron/GridAsyncInsert.php(45): Magento\Sales\Model\GridAsyncInsert->asyncInsert()
#15 [internal function]: Magento\Sales\Cron\GridAsyncInsert->execute(Object(Magento\Cron\Model\Schedule))
tig-vincentthart commented 1 year ago

Hello @mihaimatei , @leonhelmus & @orkuncv

We are looking into a solution for this to handle this from within the PostNL plugin, without having to overwrite to much Magento 2 code. Until then we have created a patch for Magento for the following file: vendor/magento/module-sales/Model/ResourceModel/Provider/Query/IdListBuilder.php, which should resolve the issue you encounter:

Index: Model/ResourceModel/Provider/Query/IdListBuilder.php
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>UTF-8
===================================================================
diff --git a/Model/ResourceModel/Provider/Query/IdListBuilder.php b/Model/ResourceModel/Provider/Query/IdListBuilder.php
--- a/Model/ResourceModel/Provider/Query/IdListBuilder.php  
+++ b/Model/ResourceModel/Provider/Query/IdListBuilder.php  (date 1675156338140)
@@ -49,6 +49,9 @@
      */
     public function addAdditionalGridTable(string $table): IdListBuilder
     {
+        if (in_array($table, $this->additionalGridTables)) {
+            return $this;
+        }
         $this->additionalGridTables[] = $table;
         return $this;
     }

Can you let me know if this works for you?

kaplansin commented 1 year ago

Hi @tig-vincentthart This patch will bypass the error "You cannot define a correlation name 'magento_sales_order_grid_archive' more than once"

but it will not fix the issue in here, https://github.com/tig-nl/postnl-magento2/issues/344

tig-vincentthart commented 1 year ago

@kaplansin

thanks for the fast response, we will look further into #344

kaplansin commented 1 year ago

Here our temporary solution @tig-vincentthart 


# TIG_Postnl has following plugins which adds additional tables in query builder while updating grid tables.
# <type name="Magento\Sales\Model\ResourceModel\Grid">
#         <plugin name="TIG_PostNL_OrderPlace" type="TIG\PostNL\Plugin\Order\AsyncPlugin" sortOrder="1" />
#         <plugin name="TIG_PostNL_ShipmentPlace" type="TIG\PostNL\Plugin\Shipment\AsyncPlugin" sortOrder="1" />
#     </type>
# Magento uses same query builder in same transaction and final generated query contains
# previous additional tables.
#
# Expected Query:
# SELECT `sales_creditmemo`.`entity_id` FROM `sales_creditmemo`
#  LEFT JOIN `magento_sales_creditmemo_grid_archive`
#    ON sales_creditmemo.entity_id = magento_sales_creditmemo_grid_archive.entity_id
#       WHERE (sales_creditmemo.updated_at >= '022-10-31 15:48:10') AND
#       (magento_sales_creditmemo_grid_archive.entity_id IS NULL)
# Query with additional Tables after TIG_PostNL plugins
# SELECT `sales_creditmemo`.`entity_id` FROM `sales_creditmemo`
#  LEFT JOIN `magento_sales_order_grid_archive` ON sales_creditmemo.entity_id = magento_sales_order_grid_archive.entity_id
#  LEFT JOIN `magento_sales_shipment_grid_archive` ON sales_creditmemo.entity_id = magento_sales_shipment_grid_archive.entity_id
#  LEFT JOIN `magento_sales_creditmemo_grid_archive` ON sales_creditmemo.entity_id = magento_sales_creditmemo_grid_archive.entity_id
#   WHERE (sales_creditmemo.updated_at >= '2022-11-16 07:47:31') AND
#    (magento_sales_order_grid_archive.entity_id IS NULL) AND
#    (magento_sales_shipment_grid_archive.entity_id IS NULL) AND
#    (magento_sales_creditmemo_grid_archive.entity_id IS NULL)
#
#
# Created by Youwe.
#
Index: Model/ResourceModel/Provider/Query/IdListBuilder.php
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>UTF-8
===================================================================
diff --git a/Model/ResourceModel/Provider/Query/IdListBuilder.php b/Model/ResourceModel/Provider/Query/IdListBuilder.php
--- a/Model/ResourceModel/Provider/Query/IdListBuilder.php
+++ b/Model/ResourceModel/Provider/Query/IdListBuilder.php  (date 16686026656
@@ -116,4 +116,13 @@
         }
         return $select;
     }
+
+    /**
+     * Reset additional grid tables
+     *
+     * @return void
+     */
+    public function resetAdditionalGridTables(): void {
+        $this->additionalGridTables = [];
+    }
 }
Index: Model/ResourceModel/Provider/NotSyncedDataProvider.php
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>UTF-8
===================================================================
diff --git a/Model/ResourceModel/Provider/NotSyncedDataProvider.php b/Model/ResourceModel/Provider/NotSyncedDataProvider.php
--- a/Model/ResourceModel/Provider/NotSyncedDataProvider.php
+++ b/Model/ResourceModel/Provider/NotSyncedDataProvider.php    (date 166860239829)
@@ -44,4 +44,14 @@
         return array_unique(array_merge([], ...$result));
     }
+
+    /**
+     * Reset additional grid tables for all providers
+     * @return void
+     */
+    public function resetAdditionalGridTables(): void {
+        foreach ($this->providers as $provider) {
+            $provider->resetAdditionalGridTables();
+        }
+    }
 }
Index: Model/ResourceModel/Provider/UpdatedAtListProvider.php
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>UTF-8
===================================================================
diff --git a/Model/ResourceModel/Provider/UpdatedAtListProvider.php b/Model/ResourceModel/Provider/UpdatedAtListProvider.php
--- a/Model/ResourceModel/Provider/UpdatedAtListProvider.php
+++ b/Model/ResourceModel/Provider/UpdatedAtListProvider.php    (date 1668602636061)
@@ -64,4 +64,13 @@
         return $this->connection->fetchAll($select, [], \Zend_Db::FETCH_COLUMN);
     }
+
+    /**
+     * This provider does not have additional grid tables
+     * But uses the same interface NotSyncedDataProviderInterface
+     * We will call this function foreach provider.
+     *
+     * @return void
+     */
+    public function resetAdditionalGridTables(){ }
 }
Index: Model/ResourceModel/Provider/UpdatedIdListProvider.php
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>UTF-8
===================================================================
diff --git a/Model/ResourceModel/Provider/UpdatedIdListProvider.php b/Model/ResourceModel/Provider/UpdatedIdListProvider.php
--- a/Model/ResourceModel/Provider/UpdatedIdListProvider.php
+++ b/Model/ResourceModel/Provider/UpdatedIdListProvider.php    (date 1668602644737)
@@ -67,4 +67,1 @@
         return $this->connection;
     }
+
+    /**
+     * Reset additional grid tables for idListQueryBuilder
+     *
+     * @return void
+     */
+    public function resetAdditionalGridTables(): void {
+        if ($this->idListQueryBuilder) {
+            $this->idListQueryBuilder->resetAdditionalGridTables();
+        }
+    }
 }
kaplansin commented 1 year ago

Then you can call resetAdditionalGridTables in this plugins


# Github Issue:  https://github.com/tig-nl/postnl-magento/issues/344
# TIG_Postnl has following plugins which adds additional tables in query builder while updating grid tables.
# Those plugins are adding additionaltables into query builder. We should reset them after getIds call
# <type name="Magento\Sales\Model\ResourceModel\Grid">
#         <plugin name="TIG_PostNL_OrderPlace" type="TIG\PostNL\Plugin\Order\AsyncPlugin" sortOrder="1" />
#         <plugin name="TIG_PostNL_ShipmentPlace" type="TIG\PostNL\Plugin\Shipment\AsyncPlugin" sortOrder="1" />
#     </type>
# This patch should be applied first
# magento-2/patch/magento/module-sales/magento-reset-additional-grid-tables-103.0.4-p1.patch
Index: Plugin/Order/AsyncPlugin.php
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>UTF-8
===================================================================
diff --git a/Plugin/Order/AsyncPlugin.php b/Plugin/Order/AsyncPlugin.php
--- a/Plugin/Order/AsyncPlugin.php
+++ b/Plugin/Order/AsyncPlugin.php 776808)
@@ -102,6 +102,7 @@
     public function beforeRefreshBySchedule()
     {
         $this->notSyncedIds = $this->notSyncedDataProvider->getIds($this->mainTableName, $this->gridTableName);
+        $this->notSyncedDataProvider->resetAdditionalGridTables();
     }
     public function afterRefreshBySchedule()
Index: Plugin/Shipment/AsyncPlugin.php
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>UTF-8
===================================================================
diff --git a/Plugin/Shipment/AsyncPlugin.php b/Plugin/Shipment/AsyncPlugin.php
--- a/Plugin/Shipment/AsyncPlugin.php
+++ b/Plugin/Shipment/AsyncPlugin.php 68604782276)
@@ -102,6 +102 @@
     public function beforeRefreshBySchedule()
     {
         $this->notSyncedIds = $this->notSyncedDataProvider->getIds($this->mainTableName, $this->gridTableName);
+        $this->notSyncedDataProvider->resetAdditionalGridTables();
     }
     public function afterRefreshBySchedule()
tig-vincentthart commented 1 year ago

Hello @kaplansin , @leonhelmus , @orkuncv ,

It looks like Magento has resolved this within the 2.4.6 release. Therefore we close this issue.

Thanks again for sharing your input.

Greetings,

Vincent