Magento 2: Clean All the Test Data After Development

It is very important to remove all the test data before moving the website from the development environment to production. Because this junk data will affect website performance, sales reports, stock inventory, and etc.

This post will help you to clean the test data from the following section in Magento 2 database.

  • Clean all the products
  • Clean all the categories
  • Clean all the customers
  • Clean all the wishlists
  • Clean all the reviews
  • Clean all the search results
  • Clean all the orders and related data (invoice, shipment & creditmemo)
  • Clean all the salesrules
  • Clean all the reports
  • Reset all increment Ids

Log in to your PHPMyAdmin using root access and run the following SQL queries to clear those test data.

Note: Please take a backup before performing any database operations.

Clean all the products:

To clean all the product related test data (i.e, products, inventory, downloadable links, URL rewrite), run the below SQL queries,

SET FOREIGN_KEY_CHECKS=0;

TRUNCATE TABLE `cataloginventory_stock_item`;
TRUNCATE TABLE `cataloginventory_stock_status`;
TRUNCATE TABLE `cataloginventory_stock_status_idx`;
TRUNCATE TABLE `cataloginventory_stock_status_tmp`;
TRUNCATE TABLE `catalog_category_product`;
TRUNCATE TABLE `catalog_category_product_index`;
TRUNCATE TABLE `catalog_category_product_index_tmp`;
TRUNCATE TABLE `catalog_compare_item`;
TRUNCATE TABLE `catalog_product_bundle_option`;
TRUNCATE TABLE `catalog_product_bundle_option_value`;
TRUNCATE TABLE `catalog_product_bundle_price_index`;
TRUNCATE TABLE `catalog_product_bundle_selection`;
TRUNCATE TABLE `catalog_product_bundle_selection_price`;
TRUNCATE TABLE `catalog_product_bundle_stock_index`;
TRUNCATE TABLE `catalog_product_entity`;
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_media_gallery_value_to_entity`;
TRUNCATE TABLE `catalog_product_entity_media_gallery_value_video`;
TRUNCATE TABLE `catalog_product_entity_text`;
TRUNCATE TABLE `catalog_product_entity_tier_price`;
TRUNCATE TABLE `catalog_product_entity_varchar`;
TRUNCATE TABLE `catalog_product_index_eav`;
TRUNCATE TABLE `catalog_product_index_eav_decimal`;
TRUNCATE TABLE `catalog_product_index_eav_decimal_idx`;
TRUNCATE TABLE `catalog_product_index_eav_decimal_tmp`;
TRUNCATE TABLE `catalog_product_index_eav_idx`;
TRUNCATE TABLE `catalog_product_index_eav_tmp`;
TRUNCATE TABLE `catalog_product_index_price`;
TRUNCATE TABLE `catalog_product_index_price_bundle_idx`;
TRUNCATE TABLE `catalog_product_index_price_bundle_opt_idx`;
TRUNCATE TABLE `catalog_product_index_price_bundle_opt_tmp`;
TRUNCATE TABLE `catalog_product_index_price_bundle_sel_idx`;
TRUNCATE TABLE `catalog_product_index_price_bundle_sel_tmp`;
TRUNCATE TABLE `catalog_product_index_price_bundle_tmp`;
TRUNCATE TABLE `catalog_product_index_price_cfg_opt_agr_idx`;
TRUNCATE TABLE `catalog_product_index_price_cfg_opt_agr_tmp`;
TRUNCATE TABLE `catalog_product_index_price_cfg_opt_idx`;
TRUNCATE TABLE `catalog_product_index_price_cfg_opt_tmp`;
TRUNCATE TABLE `catalog_product_index_price_downlod_idx`;
TRUNCATE TABLE `catalog_product_index_price_downlod_tmp`;
TRUNCATE TABLE `catalog_product_index_price_final_idx`;
TRUNCATE TABLE `catalog_product_index_price_final_tmp`;
TRUNCATE TABLE `catalog_product_index_price_idx`;
TRUNCATE TABLE `catalog_product_index_price_opt_agr_idx`;
TRUNCATE TABLE `catalog_product_index_price_opt_agr_tmp`;
TRUNCATE TABLE `catalog_product_index_price_opt_idx`;
TRUNCATE TABLE `catalog_product_index_price_opt_tmp`;
TRUNCATE TABLE `catalog_product_index_price_tmp`;
TRUNCATE TABLE `catalog_product_index_tier_price`;
TRUNCATE TABLE `catalog_product_index_website`;
TRUNCATE TABLE `catalog_product_link`;
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_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_relation`;
TRUNCATE TABLE `catalog_product_super_attribute`;
TRUNCATE TABLE `catalog_product_super_attribute_label`;
TRUNCATE TABLE `catalog_product_super_link`;
TRUNCATE TABLE `catalog_product_website`;
TRUNCATE TABLE `catalog_url_rewrite_product_category`;
TRUNCATE TABLE `downloadable_link`;
TRUNCATE TABLE `downloadable_link_price`;
TRUNCATE TABLE `downloadable_link_purchased`;
TRUNCATE TABLE `downloadable_link_purchased_item`;
TRUNCATE TABLE `downloadable_link_title`;
TRUNCATE TABLE `downloadable_sample`;
TRUNCATE TABLE `downloadable_sample_title`;
TRUNCATE TABLE `product_alert_price`;
TRUNCATE TABLE `product_alert_stock`;
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`;
ALTER TABLE `cataloginventory_stock_item` AUTO_INCREMENT=1;
ALTER TABLE `cataloginventory_stock_status` AUTO_INCREMENT=1;
ALTER TABLE `cataloginventory_stock_status_idx` AUTO_INCREMENT=1;
ALTER TABLE `cataloginventory_stock_status_tmp` AUTO_INCREMENT=1;
ALTER TABLE `catalog_category_product` AUTO_INCREMENT=1;
ALTER TABLE `catalog_category_product_index` AUTO_INCREMENT=1;
ALTER TABLE `catalog_category_product_index_tmp` AUTO_INCREMENT=1;
ALTER TABLE `catalog_compare_item` AUTO_INCREMENT=1;
ALTER TABLE `catalog_product_bundle_option` AUTO_INCREMENT=1;
ALTER TABLE `catalog_product_bundle_option_value` AUTO_INCREMENT=1;
ALTER TABLE `catalog_product_bundle_price_index` AUTO_INCREMENT=1;
ALTER TABLE `catalog_product_bundle_selection` AUTO_INCREMENT=1;
ALTER TABLE `catalog_product_bundle_selection_price` AUTO_INCREMENT=1;
ALTER TABLE `catalog_product_bundle_stock_index` AUTO_INCREMENT=1;
ALTER TABLE `catalog_product_entity` AUTO_INCREMENT=1;
ALTER TABLE `catalog_product_entity_datetime` AUTO_INCREMENT=1;
ALTER TABLE `catalog_product_entity_decimal` AUTO_INCREMENT=1;
ALTER TABLE `catalog_product_entity_gallery` AUTO_INCREMENT=1;
ALTER TABLE `catalog_product_entity_int` AUTO_INCREMENT=1;
ALTER TABLE `catalog_product_entity_media_gallery` AUTO_INCREMENT=1;
ALTER TABLE `catalog_product_entity_media_gallery_value` AUTO_INCREMENT=1;
ALTER TABLE `catalog_product_entity_media_gallery_value_to_entity` AUTO_INCREMENT=1;
ALTER TABLE `catalog_product_entity_media_gallery_value_video` AUTO_INCREMENT=1;
ALTER TABLE `catalog_product_entity_text` AUTO_INCREMENT=1;
ALTER TABLE `catalog_product_entity_tier_price` AUTO_INCREMENT=1;
ALTER TABLE `catalog_product_entity_varchar` AUTO_INCREMENT=1;
ALTER TABLE `catalog_product_index_eav` AUTO_INCREMENT=1;
ALTER TABLE `catalog_product_index_eav_decimal` AUTO_INCREMENT=1;
ALTER TABLE `catalog_product_index_eav_decimal_idx` AUTO_INCREMENT=1;
ALTER TABLE `catalog_product_index_eav_decimal_tmp` AUTO_INCREMENT=1;
ALTER TABLE `catalog_product_index_eav_idx` AUTO_INCREMENT=1;
ALTER TABLE `catalog_product_index_eav_tmp` AUTO_INCREMENT=1;
ALTER TABLE `catalog_product_index_price` AUTO_INCREMENT=1;
ALTER TABLE `catalog_product_index_price_bundle_idx` AUTO_INCREMENT=1;
ALTER TABLE `catalog_product_index_price_bundle_opt_idx` AUTO_INCREMENT=1;
ALTER TABLE `catalog_product_index_price_bundle_opt_tmp` AUTO_INCREMENT=1;
ALTER TABLE `catalog_product_index_price_bundle_sel_idx` AUTO_INCREMENT=1;
ALTER TABLE `catalog_product_index_price_bundle_sel_tmp` AUTO_INCREMENT=1;
ALTER TABLE `catalog_product_index_price_bundle_tmp` AUTO_INCREMENT=1;
ALTER TABLE `catalog_product_index_price_cfg_opt_agr_idx` AUTO_INCREMENT=1;
ALTER TABLE `catalog_product_index_price_cfg_opt_agr_tmp` AUTO_INCREMENT=1;
ALTER TABLE `catalog_product_index_price_cfg_opt_idx` AUTO_INCREMENT=1;
ALTER TABLE `catalog_product_index_price_cfg_opt_tmp` AUTO_INCREMENT=1;
ALTER TABLE `catalog_product_index_price_downlod_idx` AUTO_INCREMENT=1;
ALTER TABLE `catalog_product_index_price_downlod_tmp` AUTO_INCREMENT=1;
ALTER TABLE `catalog_product_index_price_final_idx` AUTO_INCREMENT=1;
ALTER TABLE `catalog_product_index_price_final_tmp` AUTO_INCREMENT=1;
ALTER TABLE `catalog_product_index_price_idx` AUTO_INCREMENT=1;
ALTER TABLE `catalog_product_index_price_opt_agr_idx` AUTO_INCREMENT=1;
ALTER TABLE `catalog_product_index_price_opt_agr_tmp` AUTO_INCREMENT=1;
ALTER TABLE `catalog_product_index_price_opt_idx` AUTO_INCREMENT=1;
ALTER TABLE `catalog_product_index_price_opt_tmp` AUTO_INCREMENT=1;
ALTER TABLE `catalog_product_index_price_tmp` AUTO_INCREMENT=1;
ALTER TABLE `catalog_product_index_tier_price` AUTO_INCREMENT=1;
ALTER TABLE `catalog_product_index_website` AUTO_INCREMENT=1;
ALTER TABLE `catalog_product_link` AUTO_INCREMENT=1;
ALTER TABLE `catalog_product_link_attribute_decimal` AUTO_INCREMENT=1;
ALTER TABLE `catalog_product_link_attribute_int` AUTO_INCREMENT=1;
ALTER TABLE `catalog_product_link_attribute_varchar` AUTO_INCREMENT=1;
ALTER TABLE `catalog_product_option` AUTO_INCREMENT=1;
ALTER TABLE `catalog_product_option_price` AUTO_INCREMENT=1;
ALTER TABLE `catalog_product_option_title` AUTO_INCREMENT=1;
ALTER TABLE `catalog_product_option_type_price` AUTO_INCREMENT=1;
ALTER TABLE `catalog_product_option_type_title` AUTO_INCREMENT=1;
ALTER TABLE `catalog_product_option_type_value` AUTO_INCREMENT=1;
ALTER TABLE `catalog_product_relation` AUTO_INCREMENT=1;
ALTER TABLE `catalog_product_super_attribute` AUTO_INCREMENT=1;
ALTER TABLE `catalog_product_super_attribute_label` AUTO_INCREMENT=1;
ALTER TABLE `catalog_product_super_link` AUTO_INCREMENT=1;
ALTER TABLE `catalog_product_website` AUTO_INCREMENT=1;
ALTER TABLE `catalog_url_rewrite_product_category` AUTO_INCREMENT=1;
ALTER TABLE `downloadable_link` AUTO_INCREMENT=1;
ALTER TABLE `downloadable_link_price` AUTO_INCREMENT=1;
ALTER TABLE `downloadable_link_purchased` AUTO_INCREMENT=1;
ALTER TABLE `downloadable_link_purchased_item` AUTO_INCREMENT=1;
ALTER TABLE `downloadable_link_title` AUTO_INCREMENT=1;
ALTER TABLE `downloadable_sample` AUTO_INCREMENT=1;
ALTER TABLE `downloadable_sample_title` AUTO_INCREMENT=1;
ALTER TABLE `product_alert_price` AUTO_INCREMENT=1;
ALTER TABLE `product_alert_stock` AUTO_INCREMENT=1;
ALTER TABLE `report_compared_product_index` AUTO_INCREMENT=1;
ALTER TABLE `report_viewed_product_aggregated_daily` AUTO_INCREMENT=1;
ALTER TABLE `report_viewed_product_aggregated_monthly` AUTO_INCREMENT=1;
ALTER TABLE `report_viewed_product_aggregated_yearly` AUTO_INCREMENT=1;
ALTER TABLE `report_viewed_product_index` AUTO_INCREMENT=1;

SET FOREIGN_KEY_CHECKS=1;

Clean all the categories:

To clean all the test categories, run the below SQL queries,

SET FOREIGN_KEY_CHECKS = 0;
 
TRUNCATE TABLE `catalog_category_entity`;
TRUNCATE TABLE `catalog_category_entity_datetime`;
TRUNCATE TABLE `catalog_category_entity_decimal`;
TRUNCATE TABLE `catalog_category_entity_int`;
TRUNCATE TABLE `catalog_category_entity_text`;
TRUNCATE TABLE `catalog_category_entity_varchar`;
TRUNCATE TABLE `catalog_category_product`;
TRUNCATE TABLE `catalog_category_product_index`;
TRUNCATE TABLE `catalog_category_product_index_tmp`;
INSERT INTO `catalog_category_entity` (`entity_id`, `attribute_set_id`, `parent_id`, `created_at`, `updated_at`, `path`, `position`, `level`, `children_count`) VALUES (1, 3, 0, '2018-03-29 00:00:59', '2018-03-29 00:00:59', '1', 0, 0, 1);
 
SET FOREIGN_KEY_CHECKS = 1;

Clean all the customers:

To clean all the customer related test data ( i.e, customer, billing address, shipping address ), run the below SQL queries,

SET FOREIGN_KEY_CHECKS=0;

TRUNCATE TABLE `customer_address_entity`;
TRUNCATE TABLE `customer_address_entity_datetime`;
TRUNCATE TABLE `customer_address_entity_decimal`;
TRUNCATE TABLE `customer_address_entity_int`;
TRUNCATE TABLE `customer_address_entity_text`;
TRUNCATE TABLE `customer_address_entity_varchar`;
TRUNCATE TABLE `customer_entity`;
TRUNCATE TABLE `customer_entity_datetime`;
TRUNCATE TABLE `customer_entity_decimal`;
TRUNCATE TABLE `customer_entity_int`;
TRUNCATE TABLE `customer_entity_text`;
TRUNCATE TABLE `customer_entity_varchar`;
TRUNCATE TABLE `customer_grid_flat`;
TRUNCATE TABLE `customer_log`;
TRUNCATE TABLE `customer_visitor`;
TRUNCATE TABLE `persistent_session`;
TRUNCATE TABLE `newsletter_subscriber`;
TRUNCATE TABLE `product_alert_price`;
TRUNCATE TABLE `product_alert_stock`;
TRUNCATE TABLE `vault_payment_token`;
TRUNCATE TABLE `vault_payment_token_order_payment_link`;
ALTER TABLE `customer_address_entity` AUTO_INCREMENT=1;
ALTER TABLE `customer_address_entity_datetime` AUTO_INCREMENT=1;
ALTER TABLE `customer_address_entity_decimal` AUTO_INCREMENT=1;
ALTER TABLE `customer_address_entity_int` AUTO_INCREMENT=1;
ALTER TABLE `customer_address_entity_text` AUTO_INCREMENT=1;
ALTER TABLE `customer_address_entity_varchar` AUTO_INCREMENT=1;
ALTER TABLE `customer_entity` AUTO_INCREMENT=1;
ALTER TABLE `customer_entity_datetime` AUTO_INCREMENT=1;
ALTER TABLE `customer_entity_decimal` AUTO_INCREMENT=1;
ALTER TABLE `customer_entity_int` AUTO_INCREMENT=1;
ALTER TABLE `customer_entity_text` AUTO_INCREMENT=1;
ALTER TABLE `customer_entity_varchar` AUTO_INCREMENT=1;
ALTER TABLE `customer_grid_flat` AUTO_INCREMENT=1;
ALTER TABLE `customer_log` AUTO_INCREMENT=1;
ALTER TABLE `customer_visitor` AUTO_INCREMENT=1;
ALTER TABLE `persistent_session` AUTO_INCREMENT=1;
ALTER TABLE `newsletter_subscriber` AUTO_INCREMENT=1;
ALTER TABLE `product_alert_price` AUTO_INCREMENT=1;
ALTER TABLE `product_alert_stock` AUTO_INCREMENT=1;
ALTER TABLE `vault_payment_token` AUTO_INCREMENT=1;
ALTER TABLE `vault_payment_token_order_payment_link` AUTO_INCREMENT=1;

SET FOREIGN_KEY_CHECKS=1;

Clean all the wishlists:

To clean all the test wishlist, run the below SQL queries,

SET FOREIGN_KEY_CHECKS = 0;

TRUNCATE TABLE `wishlist`;
TRUNCATE TABLE `wishlist_item`;
TRUNCATE TABLE `wishlist_item_option`;
ALTER TABLE `wishlist` AUTO_INCREMENT=1;
ALTER TABLE `wishlist_item` AUTO_INCREMENT=1;
ALTER TABLE `wishlist_item_option` AUTO_INCREMENT=1;

SET FOREIGN_KEY_CHECKS = 1;

Clean all the reviews:

To clean all the test reviews, run the below SQL queries,

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`;
ALTER TABLE `review` AUTO_INCREMENT=1;
ALTER TABLE `review_detail` AUTO_INCREMENT=1;
ALTER TABLE `review_entity_summary` AUTO_INCREMENT=1;
ALTER TABLE `review_store` AUTO_INCREMENT=1;
ALTER TABLE `rating_option_vote` AUTO_INCREMENT=1;
ALTER TABLE `rating_option_vote_aggregated` AUTO_INCREMENT=1;
 
SET FOREIGN_KEY_CHECKS = 1;

Clean all the search results:

To clean all the test search results, run the below SQL queries,

SET FOREIGN_KEY_CHECKS = 0;
 
TRUNCATE TABLE `catalogsearch_fulltext_scope1`;
TRUNCATE TABLE `search_query`;
TRUNCATE TABLE `search_synonyms`;
 
SET FOREIGN_KEY_CHECKS = 1;

Clean all the orders:

To clean all the order related test data (i.e, quote, orders, invoice, shipment & creditmemo), run the below SQL queries,

SET FOREIGN_KEY_CHECKS=0;

TRUNCATE TABLE `gift_message`;
TRUNCATE TABLE `quote`;
TRUNCATE TABLE `quote_address`;
TRUNCATE TABLE `quote_address_item`;
TRUNCATE TABLE `quote_id_mask`;
TRUNCATE TABLE `quote_item`;
TRUNCATE TABLE `quote_item_option`;
TRUNCATE TABLE `quote_payment`;
TRUNCATE TABLE `quote_shipping_rate`;
TRUNCATE TABLE `reporting_orders`;
TRUNCATE TABLE `sales_bestsellers_aggregated_daily`;
TRUNCATE TABLE `sales_bestsellers_aggregated_monthly`;
TRUNCATE TABLE `sales_bestsellers_aggregated_yearly`;
TRUNCATE TABLE `sales_creditmemo`;
TRUNCATE TABLE `sales_creditmemo_comment`;
TRUNCATE TABLE `sales_creditmemo_grid`;
TRUNCATE TABLE `sales_creditmemo_item`;
TRUNCATE TABLE `sales_invoice`;
TRUNCATE TABLE `sales_invoiced_aggregated`;
TRUNCATE TABLE `sales_invoiced_aggregated_order`;
TRUNCATE TABLE `sales_invoice_comment`;
TRUNCATE TABLE `sales_invoice_grid`;
TRUNCATE TABLE `sales_invoice_item`;
TRUNCATE TABLE `sales_order`;
TRUNCATE TABLE `sales_order_address`;
TRUNCATE TABLE `sales_order_aggregated_created`;
TRUNCATE TABLE `sales_order_aggregated_updated`;
TRUNCATE TABLE `sales_order_grid`;
TRUNCATE TABLE `sales_order_item`;
TRUNCATE TABLE `sales_order_payment`;
TRUNCATE TABLE `sales_order_status_history`;
TRUNCATE TABLE `sales_order_tax`;
TRUNCATE TABLE `sales_order_tax_item`;
TRUNCATE TABLE `sales_payment_transaction`;
TRUNCATE TABLE `sales_refunded_aggregated`;
TRUNCATE TABLE `sales_refunded_aggregated_order`;
TRUNCATE TABLE `sales_shipment`;
TRUNCATE TABLE `sales_shipment_comment`;
TRUNCATE TABLE `sales_shipment_grid`;
TRUNCATE TABLE `sales_shipment_item`;
TRUNCATE TABLE `sales_shipment_track`;
TRUNCATE TABLE `sales_shipping_aggregated`;
TRUNCATE TABLE `sales_shipping_aggregated_order`;
TRUNCATE TABLE `tax_order_aggregated_created`;
TRUNCATE TABLE `tax_order_aggregated_updated`;
TRUNCATE TABLE `paypal_payment_transaction`;
TRUNCATE TABLE `paypal_settlement_report`;
TRUNCATE TABLE `paypal_settlement_report_row`;
ALTER TABLE `gift_message` AUTO_INCREMENT=1;
ALTER TABLE `quote` AUTO_INCREMENT=1;
ALTER TABLE `quote_address` AUTO_INCREMENT=1;
ALTER TABLE `quote_address_item` AUTO_INCREMENT=1;
ALTER TABLE `quote_id_mask` AUTO_INCREMENT=1;
ALTER TABLE `quote_item` AUTO_INCREMENT=1;
ALTER TABLE `quote_item_option` AUTO_INCREMENT=1;
ALTER TABLE `quote_payment` AUTO_INCREMENT=1;
ALTER TABLE `quote_shipping_rate` AUTO_INCREMENT=1;
ALTER TABLE `reporting_orders` AUTO_INCREMENT=1;
ALTER TABLE `sales_bestsellers_aggregated_daily` AUTO_INCREMENT=1;
ALTER TABLE `sales_bestsellers_aggregated_monthly` AUTO_INCREMENT=1;
ALTER TABLE `sales_bestsellers_aggregated_yearly` AUTO_INCREMENT=1;
ALTER TABLE `sales_creditmemo` AUTO_INCREMENT=1;
ALTER TABLE `sales_creditmemo_comment` AUTO_INCREMENT=1;
ALTER TABLE `sales_creditmemo_grid` AUTO_INCREMENT=1;
ALTER TABLE `sales_creditmemo_item` AUTO_INCREMENT=1;
ALTER TABLE `sales_invoice` AUTO_INCREMENT=1;
ALTER TABLE `sales_invoiced_aggregated` AUTO_INCREMENT=1;
ALTER TABLE `sales_invoiced_aggregated_order` AUTO_INCREMENT=1;
ALTER TABLE `sales_invoice_comment` AUTO_INCREMENT=1;
ALTER TABLE `sales_invoice_grid` AUTO_INCREMENT=1;
ALTER TABLE `sales_invoice_item` AUTO_INCREMENT=1;
ALTER TABLE `sales_order` AUTO_INCREMENT=1;
ALTER TABLE `sales_order_address` AUTO_INCREMENT=1;
ALTER TABLE `sales_order_aggregated_created` AUTO_INCREMENT=1;
ALTER TABLE `sales_order_aggregated_updated` AUTO_INCREMENT=1;
ALTER TABLE `sales_order_grid` AUTO_INCREMENT=1;
ALTER TABLE `sales_order_item` AUTO_INCREMENT=1;
ALTER TABLE `sales_order_payment` AUTO_INCREMENT=1;
ALTER TABLE `sales_order_status_history` AUTO_INCREMENT=1;
ALTER TABLE `sales_order_tax` AUTO_INCREMENT=1;
ALTER TABLE `sales_order_tax_item` AUTO_INCREMENT=1;
ALTER TABLE `sales_payment_transaction` AUTO_INCREMENT=1;
ALTER TABLE `sales_refunded_aggregated` AUTO_INCREMENT=1;
ALTER TABLE `sales_refunded_aggregated_order` AUTO_INCREMENT=1;
ALTER TABLE `sales_shipment` AUTO_INCREMENT=1;
ALTER TABLE `sales_shipment_comment` AUTO_INCREMENT=1;
ALTER TABLE `sales_shipment_grid` AUTO_INCREMENT=1;
ALTER TABLE `sales_shipment_item` AUTO_INCREMENT=1;
ALTER TABLE `sales_shipment_track` AUTO_INCREMENT=1;
ALTER TABLE `sales_shipping_aggregated` AUTO_INCREMENT=1;
ALTER TABLE `sales_shipping_aggregated_order` AUTO_INCREMENT=1;
ALTER TABLE `tax_order_aggregated_created` AUTO_INCREMENT=1;
ALTER TABLE `tax_order_aggregated_updated` AUTO_INCREMENT=1;
ALTER TABLE `paypal_payment_transaction` AUTO_INCREMENT=1;
ALTER TABLE `paypal_settlement_report` AUTO_INCREMENT=1;
ALTER TABLE `paypal_settlement_report_row` AUTO_INCREMENT=1;

SET FOREIGN_KEY_CHECKS=1;

Clean all the salesrules:

To clean all the salesrule related data, run the below SQL queries,

SET FOREIGN_KEY_CHECKS=0;

TRUNCATE TABLE `salesrule`;
TRUNCATE TABLE `salesrule_coupon`;
TRUNCATE TABLE `salesrule_coupon_aggregated`;
TRUNCATE TABLE `salesrule_coupon_aggregated_order`;
TRUNCATE TABLE `salesrule_coupon_aggregated_updated`;
TRUNCATE TABLE `salesrule_coupon_usage`;
TRUNCATE TABLE `salesrule_customer`;
TRUNCATE TABLE `salesrule_customer_group`;
TRUNCATE TABLE `salesrule_label`;
TRUNCATE TABLE `salesrule_product_attribute`;
TRUNCATE TABLE `salesrule_website`;

SET FOREIGN_KEY_CHECKS=1;

Clean all the reports:

To clean all the test reports, run the below SQL queries,

SET FOREIGN_KEY_CHECKS=0;

TRUNCATE TABLE `report_event`;
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`;
ALTER TABLE `report_event` AUTO_INCREMENT=1;
ALTER TABLE `report_compared_product_index` AUTO_INCREMENT=1;
ALTER TABLE `report_viewed_product_aggregated_daily` AUTO_INCREMENT=1;
ALTER TABLE `report_viewed_product_aggregated_monthly` AUTO_INCREMENT=1;
ALTER TABLE `report_viewed_product_aggregated_yearly` AUTO_INCREMENT=1;
ALTER TABLE `report_viewed_product_index` AUTO_INCREMENT=1;

SET FOREIGN_KEY_CHECKS=1;

Reset all increment Ids:

SET FOREIGN_KEY_CHECKS = 0;
 
TRUNCATE `eav_entity_store`;
ALTER TABLE `eav_entity_store` AUTO_INCREMENT=1;
 
SET FOREIGN_KEY_CHECKS = 1;

Hope this helps.

Leave a Reply

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