Magento Daily Maintenance Script

Clearing All Product Data In Magento

Today we just want to share a little script we use on a regular basis that we thought may come in handy for a few of you out there. This simply clears all product data from your Magento installation – very handy if you import your data on a regular basis and it needs refreshing or if you need to delete old demo data.

First connect to MySQL using the following in SSH:

mysql -u YOUR-USERNAME -p

Next you’ll be prompted for your password, just type it in and hit enter.

Now, connect to the Magento database:

use YOUR-MAGENTO-DATABASE-NAME;

Lastly, run the commands below to clear all product data.

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_label`;
TRUNCATE TABLE `catalog_product_super_attribute_pricing`;
TRUNCATE TABLE `catalog_product_super_attribute`;
TRUNCATE TABLE `catalog_product_super_link`;
TRUNCATE TABLE `catalog_product_enabled_index`;
TRUNCATE TABLE `catalog_product_website`;
TRUNCATE TABLE `catalog_category_product_index`;
TRUNCATE TABLE `catalog_category_product`;
TRUNCATE TABLE `cataloginventory_stock_item`;
TRUNCATE TABLE `cataloginventory_stock_status`;
TRUNCATE TABLE `cataloginventory_stock`;
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`;
INSERT  INTO `catalog_product_link_type`(`link_type_id`,`code`) VALUES (1,'relation'),(2,'bundle'),(3,'super'),(4,'up_sell'),(5,'cross_sell');
INSERT  INTO `catalog_product_link_attribute`(`product_link_attribute_id`,`link_type_id`,`product_link_attribute_code`,`data_type`) VALUES (1,2,'qty','decimal'),(2,1,'position','int'),(3,4,'position','int'),(4,5,'position','int'),(6,1,'qty','decimal'),(7,3,'position','int'),(8,3,'qty','decimal');
INSERT  INTO `cataloginventory_stock`(`stock_id`,`stock_name`) VALUES (1,'Default');
TRUNCATE TABLE `catalog_product_entity`;
SET FOREIGN_KEY_CHECKS = 1;

You’re all set! Navigate back to your Magento and give it a quick double check, you should find that all products have now gone.

If you need to do this on a regular basis then a cron job may be more fitting, just bung the above code in a .sql file and use a cron job similar to the below:

00 22 * * * PATH/TO/MYSQL MAGENTO-DATABASE-NAME < clear-products.sql

Simply swap in your MySQL path and Magento database name, upload the .sql file you created earlier to your root directory and you are good to go. This cron runs daily at 10PM so change that according to your own needs. For example mine would be the below which runs at 10PM every Friday:

00 22 * * 5 /usr/bin/mysql magento < clear-products.sql

Let us know below if you need anymore help and thanks for reading!

0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *