1

I see orders that are closed, canceled or completed that were created in the reservation table but never removed and they won't be removed since it's triggered when the order changes the status.

I check the reservation running this command below.

select * from inventory_reservation where sku='822189036724';

Magento 2 MSI inventory reservation compensation fix

I don't have the lasted version of MSI which includes a command to fix it, so how could I fix it in the DB directly?

1 Answer 1

1

Magento 2.3 or newer

This CLI below was implemented to solve this issue.

php -d memory_limit=-1 bin/magento inventory:reservation:list-inconsistencies -r -c | xargs -L1 php -d memory_limit=-1 bin/magento inventory:reservation:create-compensations

Magento 2.2 or older

This query will return the reservation IDs from the reservations from every order that is either closed, complete or canceled, was created more than 2 weeks ago but has its reservation still different than 0 (which affects the salable quantity on the store, once their reservations were not correctly finished).

SELECT to_be_deleted.reservation_id FROM (
    SELECT ir3.*, SUBSTRING_INDEX(SUBSTRING_INDEX(ir3.metadata, '_id":"', -1), '"', 1) AS order_id FROM inventory_reservation ir3
) to_be_deleted WHERE to_be_deleted.order_id IN (
    SELECT entity_id FROM sales_order WHERE entity_id IN (
        SELECT msi_info.order_id FROM (
            SELECT ir1.*, SUBSTRING_INDEX(SUBSTRING_INDEX(ir1.metadata, '_id":"', -1), '"', 1) AS order_id FROM inventory_reservation ir1
        ) msi_info WHERE msi_info.order_id IN (
            SELECT msi_info.order_id FROM (
                SELECT ir2.*, SUBSTRING_INDEX(SUBSTRING_INDEX(ir2.metadata, '_id":"', -1), '"', 1) AS order_id FROM inventory_reservation ir2
            ) msi_info GROUP BY msi_info.order_id HAVING sum(msi_info.quantity)<>0
        )
    ) AND created_at < NOW() - INTERVAL 2 WEEK 
);

You might be able to fix it by running this query.

DELETE FROM inventory_reservation WHERE reservation_id IN (
    SELECT to_be_deleted.reservation_id FROM (
        SELECT ir3.*, SUBSTRING_INDEX(SUBSTRING_INDEX(ir3.metadata, '_id":"', -1), '"', 1) AS order_id FROM inventory_reservation ir3
    ) to_be_deleted WHERE to_be_deleted.order_id IN (
        SELECT entity_id FROM sales_order WHERE entity_id IN (
            SELECT msi_info.order_id FROM (
                SELECT ir1.*, SUBSTRING_INDEX(SUBSTRING_INDEX(ir1.metadata, '_id":"', -1), '"', 1) AS order_id FROM inventory_reservation ir1
            ) msi_info WHERE msi_info.order_id IN (
                SELECT msi_info.order_id FROM (
                    SELECT ir2.*, SUBSTRING_INDEX(SUBSTRING_INDEX(ir2.metadata, '_id":"', -1), '"', 1) AS order_id FROM inventory_reservation ir2
                ) msi_info GROUP BY msi_info.order_id HAVING sum(msi_info.quantity)<>0
            )
        ) AND created_at < NOW() - INTERVAL 1 WEEK AND state IN ('closed', 'complete', 'canceled')
    )
);
2
  • this query dont work properly because it take the last _id:" try to update with object_id:" Jul 29, 2021 at 9:29
  • Hi @nino.aratari, to me it worked, what did you change to make it work for you? Aug 2, 2021 at 20:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.