How to Delete Products Data in Magento 1.9 Using MySQL Queries (Legacy Guide)

In short: On a Magento 1.9 database, deleting the rows of catalog_product_entity removes all products, because cascading foreign keys clear the linked tables; check that first, back up, then reindex. The older TRUNCATE list leaves rows behind, and Adobe’s last Magento 1 security patches shipped in June 2020.

In this guide you will delete only the products data from a Magento 1.9 store, directly in the MySQL database and without the admin panel. Orders and customers stay. This is useful when you clone a store for testing, or reset a catalogue before a re-import, and the admin delete is too slow or times out on a large catalogue.

Legacy software: Adobe ended support for Magento 1 on 30 June 2020, and the security updates released that month were the last for Magento Open Source 1 and Magento Commerce 1.14^[1]. This guide is for a copy of a Magento 1.9 database that you already run. A live store on Magento 1 needs a plan to leave it (see the last section).

Before you run anything: these statements delete data. With MySQL’s default autocommit setting each successful statement is committed at once, so it cannot be undone^[2], and TRUNCATE can never be rolled back^[3]. Take a backup, run the steps on a restored copy first, and apply them at your own risk. iLogr cannot see your database and accepts no responsibility for data lost.

Tested on: OpenMage LTS 20.18.0 (the community-maintained continuation of the Magento 1.9 line^[4]) with PHP 8.4 and MySQL 8.4, on a scratch database with 202 simple and virtual products, one order and one wishlist item. A second scratch database with a configurable, a bundle and a downloadable product was used to check the other product types. It was not tested on Magento 1.9.2.3 itself, on Magento Commerce 1.14, or on MariaDB. Compare the table names on your own copy before you rely on any list.

What should you do before you delete any products?

Make two things: a backup you can restore, and a restored copy to rehearse on.

1. Dump the database. --single-transaction takes a consistent snapshot of InnoDB tables without blocking the store^[5]. The guide to backing up MySQL and MariaDB with mysqldump covers the other options and the restore check. Replace USER and DBNAME:

mysqldump --single-transaction --routines --triggers -u USER -p DBNAME > before-delete.sql

2. Restore the dump into a new, empty database and run the rest of this guide there first. Check that the dump is complete by comparing the product count in the copy with the original:

SELECT COUNT(*) FROM catalog_product_entity;

3. Block web access and stop cron, imports and any other background writers before you delete. Keep them stopped through the reindex and the checks, then restore normal access.

How do you delete all products with one SQL statement?

Delete from the parent table and let the foreign keys do the work. Magento 1 defines ON DELETE CASCADE foreign keys from its product tables to catalog_product_entity. MySQL's manual describes the behaviour: a cascading foreign key will "automatically delete or update the matching rows in the child table"^[6]. In the test, deleting the product rows cascaded to the attribute values, prices, stock, category links, URL rewrites, wishlist items and quote items, and in the second database also to the configurable, bundle and downloadable option, selection and link rows. Tables that have no cascading relationship need to be checked separately; the reindex step covers the search and index tables.

First check how the foreign keys on your copy behave. Magento 1 supports a table prefix, so confirm the real table names first and adapt every statement below. This query lists the foreign keys that point at the product table and do not cascade:

SELECT table_name, delete_rule
FROM information_schema.referential_constraints
WHERE referenced_table_name = 'catalog_product_entity'
  AND constraint_schema = DATABASE()
  AND delete_rule <> 'CASCADE';

RESTRICT and NO ACTION can block the delete when matching child rows exist, and SET NULL keeps the child rows^[6]. An empty result does not prove that every related table is covered: extension tables and tables without foreign keys need their own look. Cascading only happens while foreign key checks are on, so make sure this returns 1 in the session you will delete from:

SELECT @@SESSION.foreign_key_checks;

In the test, all 54 foreign keys that reference the table cascaded, and the setting was 1.

Then delete. For a small catalogue one statement is enough:

DELETE FROM catalog_product_entity;

For a large catalogue, delete in batches so that no single statement runs for very long. MySQL's manual suggests a limit and a repeat: run the statement again until the number of affected rows is below the limit^[7].

DELETE FROM catalog_product_entity LIMIT 5000;
SELECT ROW_COUNT();

Repeat both lines until ROW_COUNT() shows less than 5000. In the test, five batches of 50 left exactly the same result as a single delete.

What happens to orders, customers and categories?

They stay. Only product data goes. In the test:

• The order and its item row were still there. The item row stays with its own columns, including the SKU. Its product_id now points at a product that no longer exists.
• Customers and categories were untouched. Categories are empty because their product links were removed.
• The next new product receives a new ID, because DELETE does not reset the auto-increment counter. That matters, as the next section shows. This was tested on MySQL 8.4, where the counter survives a server restart^[8]. On an older server, check the next ID on your copy before you rely on it: SHOW TABLE STATUS LIKE 'catalog_product_entity';, column Auto_increment.

Why is the old TRUNCATE list not a good idea?

An earlier version of this guide listed 82 TRUNCATE TABLE statements and told you to switch foreign key checks off first. All 82 tables exist in the tested schema, and the list runs without errors. It still has three problems.

• It leaves rows behind. After the list, the test database still held 201 rows in catalog_product_entity_group_price, 404 product URL rewrites in core_url_rewrite, wishlist and quote items for products that no longer existed, and the search index catalogsearch_fulltext.
• The price reindex then fails. With the orphaned group price rows in place, php shell/indexer.php reindexall stopped on Product Prices with a foreign key error from catalog_product_index_group_price. This affects stores that use group prices.
• IDs are reused. TRUNCATE resets the auto-increment counter^[3]. In the test, the order's item pointed at product ID 1. After the truncate, a new product was created, received ID 1, and the old order item now resolved to that unrelated product.

Without SET FOREIGN_KEY_CHECKS = 0; the first TRUNCATE does not run at all, because MySQL refuses to truncate a table that other tables reference^[3]. That setting can be changed for a single session, and rows added or left while it is off are not checked when it is switched back on^[6]. That is how the orphaned rows survive. The cascade delete does not need it.

How do you delete products with Magento's own code instead?

If you prefer to let Magento remove each product, which fires its normal delete events, run a short script from the command line. It does not have a web time limit. Save it as delete-products.php in the Magento root and run php delete-products.php:

<?php
require 'app/Mage.php';
Mage::app('admin');
Mage::register('isSecureArea', true);

$ids = Mage::getResourceModel('catalog/product_collection')->getAllIds();
foreach ($ids as $id) {
    Mage::getModel('catalog/product')->load($id)->delete();
}
echo count($ids) . " products deleted" . PHP_EOL;

In the test it removed 202 products in 6.5 seconds, about 30 milliseconds each, and also cleared the search index that the SQL delete leaves behind. A catalogue of 50,000 products will take correspondingly longer, so run it in a terminal that survives a dropped connection. Delete the script when you are done.

What should you do after the delete?

1. Rebuild the indexes. From the Magento root run php shell/indexer.php reindexall. In the test it rebuilt all nine indexes without errors and emptied the search index that the SQL delete leaves behind. The temporary index tables also keep a few rows until the next reindex.
2. Clear the cache in System, Cache Management, or empty var/cache.
3. Remove the image files if you want the space back. Magento's product delete code does not remove the picture files, so they stay in media/catalog/product. Copy that folder somewhere first if you may need it, then empty it.
4. Check the result:

SELECT COUNT(*) FROM catalog_product_entity;
SELECT COUNT(*) FROM cataloginventory_stock_item;
SELECT COUNT(*) FROM catalog_product_entity_group_price;
SELECT COUNT(*) FROM core_url_rewrite WHERE product_id IS NOT NULL;

All four counts should be 0. Then open the admin catalogue and the storefront to confirm that they load.

Is it safe to keep running Magento 1?

Adobe's June 2020 security updates were the last for Magento Open Source 1, and Adobe warned merchants that they would carry increased responsibility for the site's security and PCI DSS compliance from then on^[1]. Two routes lead off an old store:

• Stay on the same code base with a maintained fork. OpenMage LTS continues the Magento 1.9 line, and describes itself as an unofficial community project and "a dependable alternative" to the official releases^[4].
• Move to another platform. Adobe's Data Migration Tool moves products, customers and orders from Magento 1 to Magento 2 from the command line^[9]. For a move to Shopify, see the Magento to Shopify migration checklist.

The SQL in this guide targets Magento 1 tables. Do not run it against a Magento 2 database: its schema is different.

References

Numbers in square brackets in the text link to these sources.

1. Help Net Security, "Magento 1 reaches EOL: Merchants urged to upgrade or risk breaches, falling out of PCI DSS compliance" (opens in a new tab), 29 June 2020.Used for: the end-of-support date, last patches and merchant responsibility.
2. MySQL 8.4 Reference Manual, "InnoDB Autocommit, Commit, and Rollback" (opens in a new tab).Used for: autocommit committing each successful statement by default.
3. MySQL 8.4 Reference Manual, "TRUNCATE TABLE Statement" (opens in a new tab).Used for: no rollback, counter reset and the foreign key restriction.
4. OpenMage, "Documentation" (opens in a new tab).Used for: what OpenMage LTS is and its compatibility with Magento 1.9.4.x.
5. MySQL 8.4 Reference Manual, "mysqldump" (opens in a new tab).Used for: the single-transaction backup option and its InnoDB limit.
6. MySQL 8.4 Reference Manual, "FOREIGN KEY Constraints" (opens in a new tab).Used for: ON DELETE CASCADE, the foreign_key_checks setting and unchecked rows.
7. MySQL 8.4 Reference Manual, "DELETE Statement" (opens in a new tab).Used for: the LIMIT clause, repeating until fewer rows are affected and ROW_COUNT().
8. MySQL 8.4 Reference Manual, "InnoDB AUTO_INCREMENT Handling" (opens in a new tab).Used for: the counter surviving a restart on MySQL 8.4.
9. Adobe Experience League, "How migration works" (Data Migration Tool) (opens in a new tab).Used for: the Magento 1 to Magento 2 migration tool and the data it moves.