Magento 2: How to Fix Database Backup Missing Table/View Issue | inventory_stock_1 doest exist

When working with Magento 2, many developers encounter a common issue: database backups missing tables. This usually happens when running Magento’s built‑in backup commands or when exporting the database manually. Missing tables can lead to broken restores, incomplete migrations, or errors during upgrades.

Understanding the Issue

Magento’s database is large and complex, with hundreds of tables and views. One of the most common problems after restoring a backup is the inventory_stock_1 view being missing. This view is part of Magento’s Multi‑Source Inventory (MSI) system and is required for product salability checks.

Step 1: Verify Missing View

Run the following command to check if inventory_stock_1 exists:

mysql -u username -p -e "SHOW FULL TABLES WHERE Table_type = 'VIEW';" magento_db

If inventory_stock_1 is missing, you’ll need to recreate it.

Step 2: Recreate inventory_stock_1 View

Use the following SQL query to recreate the missing 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` AS `legacy_stock_status`
JOIN
    `catalog_product_entity` AS `product`
ON
    `legacy_stock_status`.`product_id` = `product`.`entity_id`;

This query rebuilds the inventory_stock_1 view by joining cataloginventory_stock_status with catalog_product_entity, ensuring Magento can correctly calculate product salability.

Step 3: Flush Cache and Reindex

After recreating the view, run the following commands:

bin/magento cache:flush
bin/magento indexer:reindex

This ensures Magento recognizes the restored view and updates product stock data.

Troubleshooting Tips

• Check var/log/system.log for errors after restoring backups.
• Ensure your MySQL user has privileges to create views (CREATE VIEW).
• If multiple inventory views are missing (inventory_stock_2, etc.), recreate them using similar queries.

Best Practices

• Always use mysqldump with --routines --triggers to include views in backups.
• Test restores regularly to confirm views are included.
• Document custom fixes like this for your team.

Conclusion

The “database backup missing table/view” issue in Magento 2 is common, especially with MSI views like inventory_stock_1. By recreating the view with the correct SQL query and reindexing, you can restore functionality quickly. Following best practices for backups will help prevent this issue in the future.

Happy Coding & Safe Backups!