8

I'm working on importing ~40k products into Magento. I need an easy way to remove all products in between tests, but trying to do this from the admin errors out before making it very far.

If I try to truncate the table's directly, I encounter a lot of dependencies that prevent the actions.

Is it possible to erase all products without using the admin?

  • 1
    If it's just for testing, may be the easy way is : backup your database before import and to restore it after. – Arnaud AKA Narno Jul 24 '13 at 21:31
4

If you are importing via Mage_ImportExport there is also an option "DELETE". If you apply this on the same import file, all SKUs contained in this file will be deleted.

In code this is ->setBehavior(Mage_ImportExport_Model_Import::BEHAVIOR_DELETE);

You still can truncate the specific product tables via SQL - but of course you have to resolve all the dependencies and also truncate those tables.

Or: Create a "clean" database dump before importing and apply this dump before each test.

  • Thanks. The catalog is too large to use Mage's default import tools, so we're using a 3rd party module. I think creating a clean DB dump that I can restore from is my best option. – Ryre Jul 24 '13 at 15:33
  • Which module? Maybe that one has also an delete option?Maybe it is still Mage_ImportExport based? – Alex Jul 24 '13 at 15:38
  • It's uRapidflow, and I haven't found a delete option anywhere. – Ryre Jul 24 '13 at 15:47
  • If you have Pro, then check unirgy.com/wiki/urapidflow/fixed_row_format#cpcatalog_product – Petar Dzhambazov Jul 24 '13 at 16:36
7

You can delete all the product using Direct Sql.

Please take backup your database, and run the following sql queries.

Reset all product tables. Beware, below script will delete ALL your product data so do it carefully.

`SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE TABLE `catalog_product_bundle_option`;
TRUNCATE TABLE `catalog_product_bundle_option_value`;
TRUNCATE TABLE `catalog_product_bundle_selection`;
TRUNCATE TABLE `catalog_product_entity_datetime`;
TRUNCATE TABLE `catalog_product_entity_decimal`;
TRUNCATE TABLE `catalog_product_entity_gallery`;
TRUNCATE TABLE `catalog_product_entity_int`;
TRUNCATE TABLE `catalog_product_entity_media_gallery`;
TRUNCATE TABLE `catalog_product_entity_media_gallery_value`;
TRUNCATE TABLE `catalog_product_entity_text`;
TRUNCATE TABLE `catalog_product_entity_tier_price`;
TRUNCATE TABLE `catalog_product_entity_varchar`;
TRUNCATE TABLE `catalog_product_link`;
TRUNCATE TABLE `catalog_product_link_attribute`;
TRUNCATE TABLE `catalog_product_link_attribute_decimal`;
TRUNCATE TABLE `catalog_product_link_attribute_int`;
TRUNCATE TABLE `catalog_product_link_attribute_varchar`;
TRUNCATE TABLE `catalog_product_link_type`;
TRUNCATE TABLE `catalog_product_option`;
TRUNCATE TABLE `catalog_product_option_price`;
TRUNCATE TABLE `catalog_product_option_title`;
TRUNCATE TABLE `catalog_product_option_type_price`;
TRUNCATE TABLE `catalog_product_option_type_title`;
TRUNCATE TABLE `catalog_product_option_type_value`;
TRUNCATE TABLE `catalog_product_super_attribute`;
TRUNCATE TABLE `catalog_product_super_attribute_label`;
TRUNCATE TABLE `catalog_product_super_attribute_pricing`;
TRUNCATE TABLE `catalog_product_super_link`;
TRUNCATE TABLE `catalog_product_enabled_index`;
TRUNCATE TABLE `catalog_product_website`;
TRUNCATE TABLE `catalog_product_entity`;
TRUNCATE TABLE `cataloginventory_stock`;
TRUNCATE TABLE `cataloginventory_stock_item`;
TRUNCATE TABLE `cataloginventory_stock_status`;
INSERT INTO `catalog_product_link_type` VALUES(1, 'relation');
INSERT INTO `catalog_product_link_type` VALUES(3, 'super');
INSERT INTO `catalog_product_link_type` VALUES(4, 'up_sell');
INSERT INTO `catalog_product_link_type` VALUES(5, 'cross_sell');
INSERT INTO `catalog_product_link_attribute` VALUES(1, 1, 'position', 'int');
INSERT INTO `catalog_product_link_attribute` VALUES(2, 3, 'position', 'int');
INSERT INTO `catalog_product_link_attribute` VALUES(3, 3, 'qty', 'decimal');
INSERT INTO `catalog_product_link_attribute` VALUES(4, 4, 'position', 'int');
INSERT INTO `catalog_product_link_attribute` VALUES(5, 5, 'position', 'int');
INSERT INTO `cataloginventory_stock` VALUES(1, 'Default');
SET FOREIGN_KEY_CHECKS = 1;`

Remove the Product counts from category at back-end

`SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE TABLE `catalog_category_product`;
TRUNCATE TABLE `catalog_category_product_index`;
SET FOREIGN_KEY_CHECKS = 1;`

Remove the Bestseller and Most view Products at Dashboards

`SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE TABLE `report_compared_product_index`;
TRUNCATE TABLE `report_viewed_product_aggregated_daily`;
TRUNCATE TABLE `report_viewed_product_aggregated_monthly`;
TRUNCATE TABLE `report_viewed_product_aggregated_yearly`;
TRUNCATE TABLE `report_viewed_product_index`;
TRUNCATE TABLE `sales_bestsellers_aggregated_daily`;
TRUNCATE TABLE `sales_bestsellers_aggregated_monthly`;
TRUNCATE TABLE `sales_bestsellers_aggregated_yearly`;
SET FOREIGN_KEY_CHECKS = 1;`

Flush all the flat table of products

`SET FOREIGN_KEY_CHECKS = 0;
DELETE FROM catalog_product_flat_1;
DELETE FROM catalog_product_flat_2;
DELETE FROM catalog_product_flat_3;
DELETE FROM catalog_product_flat_4;
DELETE FROM catalog_product_flat_5;
SET FOREIGN_KEY_CHECKS = 1;`

Remove the review and rating of products

`SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE table `rating_option_vote`; 
TRUNCATE table `rating_option_vote_aggregated`;
TRUNCATE table `review`; 
TRUNCATE table `review_detail`; 
TRUNCATE table `review_entity_summary`; 
TRUNCATE table `review_store`;
SET FOREIGN_KEY_CHECKS = 1;`

For enterprise flush the following tables

`SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE TABLE `catalog_category_flat_cl`;
TRUNCATE TABLE `catalog_category_product_cat_cl`;
TRUNCATE TABLE `catalog_category_product_index_cl`;
TRUNCATE TABLE `catalog_product_flat_cl`;
TRUNCATE TABLE `catalog_product_index_price_cl`;
TRUNCATE TABLE `cataloginventory_stock_status_cl`;
TRUNCATE TABLE `catalogsearch_fulltext_cl`;
SET FOREIGN_KEY_CHECKS = 1;`
  • It's worth mentioning that if you have truncated your products it wise to run the indexer. Especially if you are using Solr. Also for EE I found the following table could be cleared too: enterprise_catalog_product_rewrite AND for CE and EE: catalogsearch_query catalog_product_entity_url_key – PanPipes Jul 20 '16 at 13:07
  • perfect answer with explanation +1 :) !!! – SagarPPanchal Feb 27 '18 at 14:09
3

Have you looked at Magmi? One of its "advanced" plugins is a Clear Catalog that is super quick and even resets the ID to 1.

Magmi on SourceForge

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.