magento2: Database backup missing table information "inventory_stock_1"

Database backup cannot be restored within the same version of Magento in a blank database.

Magento 2.3.0 PHP 7.2.10 MySQL 5.7

--
-- Dumping data for table `inventory_stock_1`
--
LOCK TABLES `inventory_stock_1` WRITE;
#1146 - Table '(table).inventory_stock_1' doesn't exist

I think the following part is missing in the sql backup file:

-- 
-- Substitute structure of the view `inventory_stock_1`. 
-- 
CREATE TABLE IF NOT EXISTS `inventory_stock_1` (
`product_id` int(10) unsigned
,`website_id` smallint(5) unsigned
,`stock_id` smallint(5) unsigned
,`quantity` decimal(12,4)
,`is_salable` smallint(5) unsigned
,`sku` varchar(64)
);

About this issue

Original URL
State: closed
Created 6 years ago
Comments: 17 (2 by maintainers)

Most upvoted comments

@Dan547 Remove your current inventory_stock_1 view and run this one:

CREATE
SQL SECURITY INVOKER
VIEW `inventory_stock_1`
  AS
    SELECT
    DISTINCT    
      legacy_stock_status.product_id,
      legacy_stock_status.website_id,
      legacy_stock_status.stock_id,
      legacy_stock_status.qty quantity,
      legacy_stock_status.stock_status is_salable,
      product.sku
    FROM `cataloginventory_stock_status` `legacy_stock_status`
      INNER JOIN `catalog_product_entity` product
        ON legacy_stock_status.product_id = product.entity_id;

+10

sbodak on Mar 19, 2019

The CREATE ALGORITHM statement requires elevated MySQL access. It is likely that the server you are working on does not have this and will halt the import process.

This line needs to come out of the SQL being migrated.

Instead run the following to create the view

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` )));

alkrenawizid on Feb 25, 2019

I can confirm the same problem, error that occurs:

Analisi statica:

Sono stati trovati 1 errori durante l’analisi.

Tipo statement non riconosciuto. (near “LOCK” at position 53) Query SQL:

– – Dumping data for table inventory_stock_1 – LOCK TABLES inventory_stock_1 WRITE

Messaggio di MySQL: Documentazione

Drakas on Dec 7, 2018