1

When I try to rollback using a sql dump file created in Magento 2.3.0, I get the following error:

Table 'inventory_stock_1' was not locked with LOCK TABLES

1

I'm answering my own question because I spent all morning on this and thought it might be helpful to someone else.

The issue is that with 2.3.0 inventory_stock_1 is no longer a table, it's now a view. That's all fine except that the code creating the sql backup still wants to treat it like a table. Hopefully this gets fixed in the next version.

My fix was to directly edit the sql dump file using a text editor. The entire block related to this table, starting from this line:

--
-- Table structure for table `inventory_stock_1`
--

DROP TABLE IF EXISTS `inventory_stock_1`;

all the way to this line:

/*!40000 ALTER TABLE `inventory_stock_1` ENABLE KEYS */;
UNLOCK TABLES;

It all needs to be replaced with a create view query instead. Delete all those lines, and in their place include:

CREATE
OR REPLACE
VIEW `inventory_stock_1` AS select
    distinct `legacy_stock_status`.`product_id` AS `product_id`,
    `legacy_stock_status`.`website_id` AS `website_id`,
    `legacy_stock_status`.`stock_id` AS `stock_id`,
    `legacy_stock_status`.`qty` AS `quantity`,
    `legacy_stock_status`.`stock_status` AS `is_salable`,
    `product`.`sku` AS `sku`
from
    ( `cataloginventory_stock_status` `legacy_stock_status`
join `catalog_product_entity` `product` on
        (( `legacy_stock_status`.`product_id` = `product`.`entity_id` )));

Save the dump file and try the rollback again.

I hope this helps!

  • This happened to us after a move. Also remember to do a full product & stock reindex to also remove the last ".. definer/invoker of view lack rights .." error on the inventory_stock_1 view... – Isolde May 11 at 16:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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