Orders grid doesn't load after upgrading 20.1.9 => 20.5.5

[sshymko]

Preconditions:

• Magento CE 2.1.4
• ShipperHQ module v20.1.9
• Existing orders placed on said environment

Steps to reproduce:

1. Upgrade the ShipperHQ modules:

composer update shipperhq/module-shipper --with-dependencies --no-dev

2. Re-deploy the application (upgrade database, compile DI, deploy static, flush caches)
3. Reindex everything
4. Log in to the admin panel
5. Go to Sales > Orders

Actual result:

• Orders grid keeps loading; spinner never finishes
• The following error is seen in the error reports:

Item (Magento\Framework\View\Element\UiComponent\DataProvider\Document) with the same ID "16768" already exists.

• Table shipperhq_order_detail_grid contains more than one record for the same order ID:

mysql> SELECT * FROM shipperhq_order_detail_grid WHERE order_id = 16768;
+-------+----------+---------------+---------------+---------------+-----------+-----------------+-------------------+------------------+-------------------+-----------------+-----------------+---------------+
| id    | order_id | carrier_group | dispatch_date | delivery_date | time_slot | pickup_location | delivery_comments | destination_type | liftgate_required | notify_required | inside_delivery | address_valid |
+-------+----------+---------------+---------------+---------------+-----------+-----------------+-------------------+------------------+-------------------+-----------------+-----------------+---------------+
| 16501 |    16768 | NULL          | NULL          | NULL          | NULL      | NULL            | NULL              | NULL             | NULL              | NULL            | NULL            | NULL          |
| 16502 |    16768 | NULL          | NULL          | NULL          | NULL      | NULL            | NULL              | NULL             | NULL              | NULL            | NULL            | NULL          |
+-------+----------+---------------+---------------+---------------+-----------+-----------------+-------------------+------------------+-------------------+-----------------+-----------------+---------------+
2 rows in set (0.00 sec)

• Orders grid's SELECT query returns duplicate records because of JOIN'ing table shipperhq_order_detail_grid by non-unique order_id column
• Column shipperhq_order_detail_grid.order_id is not a unique key

[wsajosh]

Thanks for raising this issue. We're looking into it and will update here.

[wsajosh]

We've just released an extension update to resolve this by removing the duplicates. Earlier releases resolved the issue with allowing duplicates in the first place. Please update to the latest version and run the upgrade, that will resolve this issue.

[sshymko]

Thanks @wsajosh !
Would be helpful to see the linked commits to explore the fix.
GitHub supports special commit message syntax for that:
https://help.github.com/articles/closing-issues-via-commit-messages/

[sshymko]

@wsajosh
The fix 73004a9 does delete duplicate rows.
However, what would prevent new duplicate rows from being added later on?
Column shipperhq_order_detail_grid.order_id still doesn't have a unique key, does it?

Please consider reopening the ticket.