Magento2: Database backup missing table information "inventory_stock_1"

[in-session]

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

[magento-engcom-team]

Hi @in-session. Thank you for your report.
To help us process this issue please make sure that you provided the following information:

• [ ] Summary of the issue
• [ ] Information on your environment
• [ ] Steps to reproduce
• [ ] Expected and actual results

Please make sure that the issue is reproducible on the vanilla Magento instance following Steps to reproduce. To deploy vanilla Magento instance on our environment, please, add a comment to the issue:

@magento-engcom-team give me $VERSION instance

where $VERSION is version tags (starting from 2.2.0+) or develop branches (for example: 2.3-develop).
For more details, please, review the Magento Contributor Assistant documentation.

@in-session do you confirm that you was able to reproduce the issue on vanilla Magento instance following steps to reproduce?

• [ ] yes
• [ ] no

[magento-engcom-team]

Hi @engcom-backlog-nazar. Thank you for working on this issue.
In order to make sure that issue has enough information and ready for development, please read and check the following instruction: :point_down:

• [x] 1. Verify that issue has all the required information. (Preconditions, Steps to reproduce, Expected result, Actual result).
  Details

  If the issue has a valid description, the label Issue: Format is valid will be added to the issue automatically. Please, edit issue description if needed, until label Issue: Format is valid appears.

• [ ] 2. Verify that issue has a meaningful description and provides enough information to reproduce the issue. If the report is valid, add Issue: Clear Description label to the issue by yourself.

• [ ] 3. Add Component: XXXXX label(s) to the ticket, indicating the components it may be related to.

• [ ] 4. Verify that the issue is reproducible on 2.3-develop branch

  Details

  - Add the comment @magento-engcom-team give me 2.3-develop instance to deploy test instance on Magento infrastructure.
  - If the issue is reproducible on 2.3-develop branch, please, add the label Reproduced on 2.3.x.
  - If the issue is not reproducible, add your comment that issue is not reproducible and close the issue and _stop verification process here_!
  

• [ ] 5. Verify that the issue is reproducible on 2.2-develop branch.

  Details

  - Add the comment @magento-engcom-team give me 2.2-develop instance to deploy test instance on Magento infrastructure.
  - If the issue is reproducible on 2.2-develop branch, please add the label Reproduced on 2.2.x

• [ ] 6. Add label Issue: Confirmed once verification is complete.

• [ ] 7. Make sure that automatic system confirms that report has been added to the backlog.

[ghost]

Hi @in-session. Thank you for your report.
To help us process this issue please make sure that you provided the following information:

• [ ] Summary of the issue
• [ ] Information on your environment
• [ ] Steps to reproduce
• [ ] Expected and actual results

and then reopen the issue.

[Drakas]

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

[lpetri-orion]

Same error::

Static analysis:

1 errors were found during analysis.

Unrecognized statement type. (near "LOCK" at position 53)
SQL query:

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

MySQL said: Documentation

1146 - Table 'magento.inventory_stock_1' doesn't exist

[jghaanstra]

Just a heads up. Using mysqldump to create the backup and restoring this backup will not give you this error. It has to do something with Magento's own database backup functionality.

[EDIT]
Just noticed this method drops the inventory_stock_1 altogether and causes exceptions on the target site.

[pratikkamani]

It looks like an issue with database backup.
Create View on the database.
In my case. I have used this query at PHPMyAdmin
CREATE ALGORITHM=UNDEFINED DEFINER=mustperf@localhostSQL SECURITY INVOKER VIEWinventory_stock_1AS SELECT DISTINCTlegacy_stock_status.product_idASproduct_id,legacy_stock_status.website_idASwebsite_id,legacy_stock_status.stock_idASstock_id,legacy_stock_status.qtyASquantity,legacy_stock_status.stock_statusASis_salable,product.skuASskuFROM (cataloginventory_stock_statuslegacy_stock_statusJOINcatalog_product_entityproductON((legacy_stock_status.product_id=product.entity_id)))

[alkrenawizid]

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

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

[Dan547]

Has anyone got a solution for this?

I am trying to migrate an existing Magento 2.3 website to a new server, I ran a full database exporting using Magento's internal backup system - bin/magento magento setup:backup --db

When I try and import the .sql it generated, I keep getting the following error - Table 'inventory_stock_1' was not locked with LOCK TABLES

I have tried the above suggestion -
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 )));

but this has not worked for me.

[sbodak]

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

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;

[bhavin786]

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

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;

That works for me! Great Thanks!

[mourjohn]

bypassed that problem with ssh
cat sql_to_migrate.sql | grep -v 'old_db_user_name' > new_sql_to_import.sql

mysql adds this line in sql export
/*!50013 DEFINER=old_db_user_name@%SQL SECURITY INVOKER */
new mysql does not have access to that db user removing this line creates the view or any views without any problems

so cat the file | remove that line with export > export to new sql
importing the new sql inventory_stock_1 or any other view will be created normally.

[marco7319]

Hello
I have the same problem when I import my magento 2.3.2 database into a new database at a new host.
Can you explain to me the steps please in my old database and my new database ?
delete the view = delete the table?

what does it mean " Remove your current inventory_stock_1" ? delete the table in the old database ?
Thank you

[sbodak]

Hello
I have the same problem when I import my magento 2.3.2 database into a new database at a new host.
Can you explain to me the steps please in my old database and my new database ?
delete the view = delete the table?

what does it mean " Remove your current inventory_stock_1" ? delete the table in the old database ?
Thank you

No, delete the view != delete the table. The view is not persistent and all data are populated dynamically.