Database backup missing table information "inventory_stock_1" #19542

in-session · 2018-12-04T12:46:38Z

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

The text was updated successfully, but these errors were encountered:

magento-engcom-team · 2018-12-04T12:46:40Z

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 · 2018-12-04T12:47:52Z

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: 👇

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 · 2018-12-04T13:25:12Z

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 · 2018-12-07T20:40:49Z

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 · 2018-12-18T17:43:04Z

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 · 2018-12-30T23:42:20Z

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 · 2019-02-21T07:04:54Z

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.skuASsku FROM (cataloginventory_stock_status legacy_stock_statusJOINcatalog_product_entity product ON((legacy_stock_status.product_id=product.entity_id)))

alkrenawizid · 2019-02-25T21:51:11Z

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

Dan547 · 2019-03-13T11:56:45Z

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 · 2019-03-19T10:37:27Z

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

bhavin786 · 2019-04-14T22:55:44Z

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

That works for me! Great Thanks!

mourjohn · 2019-07-11T13:50:33Z

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 · 2019-08-14T16:02:49Z

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 · 2019-08-15T09:28:53Z

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.

NoorSabah18 · 2022-12-20T12:37:40Z

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;

I Have been having the same issue, i have tried the above query by deleting inventory_stock_1. then i get the following error #1932 - Table 'magento2.cataloginventory_stock_status' doesn't exist in engine

pratikkamani · 2022-12-20T12:59:45Z

Have you recently enabled MSI? or what version of Magento are you using? more information needed

…

On Tue, Dec 20, 2022 at 6:07 PM NoorSabah ***@***.***> wrote: 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; I Have been having the same issue, i have tried the above query by deleting inventory_stock_1. then i get the following error #1932 <#1932> - Table 'magento2.cataloginventory_stock_status' doesn't exist in engine — Reply to this email directly, view it on GitHub <#19542 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ACG4T6SPOOFFUJBS35RSY63WOGSB7ANCNFSM4GICKMJQ> . You are receiving this because you commented.Message ID: ***@***.***>

NoorSabah18 · 2022-12-20T13:17:15Z

i am using Magento 2.4.5 and Xampp and originally i am getting the following error

_In Mysql.php line 109:

SQLSTATE[42S02]: Base table or view not found: 1932 Table 'magento2.cataloginventory_stock_status' doesn't exist
in engine, query was: SHOW INDEXES FROM cataloginventory_stock_status WHERE Non_unique = 1

In Mysql.php line 90:

SQLSTATE[42S02]: Base table or view not found: 1932 Table 'magento2.cataloginventory_stock_status' doesn't exist
in engine_

Have you recently enabled MSI? or what version of Magento are you using? more information needed
…
On Tue, Dec 20, 2022 at 6:07 PM NoorSabah @.> wrote: 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; I Have been having the same issue, i have tried the above query by deleting inventory_stock_1. then i get the following error #1932 <#1932> - Table 'magento2.cataloginventory_stock_status' doesn't exist in engine — Reply to this email directly, view it on GitHub <#19542 (comment)>, or unsubscribe https://github.com/notifications/unsubscribe-auth/ACG4T6SPOOFFUJBS35RSY63WOGSB7ANCNFSM4GICKMJQ . You are receiving this because you commented.Message ID: @.>

magento-engcom-team added the Issue: Format is not valid Gate 1 Failed. Automatic verification of issue format is failed label Dec 4, 2018

in-session changed the title ~~Database backup missing data table information inventory_stock_1~~ Database backup missing data of table information inventory_stock_1 Dec 4, 2018

in-session changed the title ~~Database backup missing data of table information inventory_stock_1~~ Database backup missing data of table "inventory_stock_1" Dec 4, 2018

ghost self-assigned this Dec 4, 2018

in-session changed the title ~~Database backup missing data of table "inventory_stock_1"~~ Database backup missing table information "inventory_stock_1" Dec 4, 2018

ghost closed this as completed Dec 4, 2018

tuyennn mentioned this issue Jun 4, 2024

does not work on magento v 2.4.7 tuyennn/magento2-outofstockatlast#55

Closed

tuyennn mentioned this issue Jul 3, 2024

Not working with big collections. tuyennn/magento2-outofstockatlast#56

Closed

This issue was closed.

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Database backup missing table information "inventory_stock_1" #19542

Database backup missing table information "inventory_stock_1" #19542

in-session commented Dec 4, 2018 •

edited

Loading

magento-engcom-team commented Dec 4, 2018

magento-engcom-team commented Dec 4, 2018 •

edited by ghost

Loading

ghost commented Dec 4, 2018

Drakas commented Dec 7, 2018

lpetri-orion commented Dec 18, 2018

jghaanstra commented Dec 30, 2018 •

edited

Loading

pratikkamani commented Feb 21, 2019

alkrenawizid commented Feb 25, 2019

Dan547 commented Mar 13, 2019

sbodak commented Mar 19, 2019

bhavin786 commented Apr 14, 2019

mourjohn commented Jul 11, 2019 •

edited

Loading

marco7319 commented Aug 14, 2019 •

edited

Loading

sbodak commented Aug 15, 2019

NoorSabah18 commented Dec 20, 2022

pratikkamani commented Dec 20, 2022 via email

NoorSabah18 commented Dec 20, 2022

Database backup missing table information "inventory_stock_1" #19542

Database backup missing table information "inventory_stock_1" #19542

Comments

in-session commented Dec 4, 2018 • edited Loading

magento-engcom-team commented Dec 4, 2018

magento-engcom-team commented Dec 4, 2018 • edited by ghost Loading

ghost commented Dec 4, 2018

Drakas commented Dec 7, 2018

lpetri-orion commented Dec 18, 2018

jghaanstra commented Dec 30, 2018 • edited Loading

pratikkamani commented Feb 21, 2019

alkrenawizid commented Feb 25, 2019

Dan547 commented Mar 13, 2019

sbodak commented Mar 19, 2019

bhavin786 commented Apr 14, 2019

mourjohn commented Jul 11, 2019 • edited Loading

marco7319 commented Aug 14, 2019 • edited Loading

sbodak commented Aug 15, 2019

NoorSabah18 commented Dec 20, 2022

pratikkamani commented Dec 20, 2022 via email

NoorSabah18 commented Dec 20, 2022

in-session commented Dec 4, 2018 •

edited

Loading

magento-engcom-team commented Dec 4, 2018 •

edited by ghost

Loading

jghaanstra commented Dec 30, 2018 •

edited

Loading

mourjohn commented Jul 11, 2019 •

edited

Loading

marco7319 commented Aug 14, 2019 •

edited

Loading