Unable to upgrade from <1.1.22 to 1.1.26 due to SQL error

[captain-melanie]

Hi, we have setup_version v1.1.21 installed and would like to upgrade to setup_version v1.1.26, but a sql error in the upgrade script broke the whole process. You could reproduce the error by upgrading v1.1.21 to v1.1.26.

$ php bin/magento setup:upgrade

...
Module 'ShipperHQ_Common':
Module 'ShipperHQ_Logger':
Module 'ShipperHQ_Shipper':
Upgrading schema.. SQLSTATE[HY000]: General error: 1553 Cannot drop index 'SHIPPERHQ_ORDER_DETAIL_GRID_ORDER_ID': needed in a foreign key constraint

In log:

SQLSTATE[HY000]: General error: 1553 Cannot drop index 'SHIPPERHQ_ORDER_DETAIL_GRID_ORDER_ID': needed in a foreign key constraint, query was: ALTER TABLE `shipperhq_order_detail_grid` DROP KEY `SHIPPERHQ_ORDER_DETAIL_GRID_ORDER_ID`

We found that the error comes from this line https://github.com/shipperhq/module-shipper/blob/main/src/Setup/UpgradeSchema.php#L990 where the script tries to drop an index but fails due to a foreign key constraint in MySQL https://dev.mysql.com/doc/refman/8.0/en/create-table-foreign-keys.html. MySQL requires at least 1 index on foreign key. This constraint is consistent across MySQL versions.

MySQL requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. Such an index is created on the referencing table automatically if it does not exist. This index might be silently dropped later if you create another index that can be used to enforce the foreign key constraint. index_name, if given, is used as described previously.

To fix this issue, we could add a temporary index to satisfy the constraint then drop it once the proper index has been added to the table.

pr: https://github.com/shipperhq/module-shipper/pull/100

[wsadasmit]

Hi @captain-melanie , I've installed module-shipper version 20.25.0 (the version with setup_version v1.1.21) to a fresh instance of Magento 2.4.3, and then upgraded via the process outlined in our install docs, and did not encounter this issue.

If you'd like to troubleshoot this further, can you please send details of your environment including Magento version and PHP version to support@shipperhq.com? Thanks,

[brunogemelli]

Same issue for me. I think it is related to the MySQL version.

@wsadasmit can you test it using MySQL 5.7 or higher?

[wsajosh]

Issue has been replicated. Thanks for the PR @captain-melanie

[wsajosh]

Thanks for raising this issue. We'll be creating a new release shortly which addresses it. Thanks for the fix @captain-melanie !