Magento 2: How to truncate customers, products, reviews and orders table

NOTE :: Please take database backup prior to executing these queries.

SET FOREIGN_KEY_CHECKS=0;

Truncate order tables

# Clean order history

TRUNCATE TABLE `sales_bestsellers_aggregated_daily`;

TRUNCATE TABLE `sales_bestsellers_aggregated_monthly`;

TRUNCATE TABLE `sales_bestsellers_aggregated_yearly`;

# Clean order infos

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`;

# Clean cart infos

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`;

# Reset indexes (if you want your orders number start back to 1

TRUNCATE TABLE sequence_invoice_1;

TRUNCATE TABLE sequence_order_1;

TRUNCATE TABLE sequence_shipment_1;

TRUNCATE TABLE sequence_creditmemo_1;

DO NOT TRUNCATE / EMPTY THE FOLLOWING :

sales_order_status

sales_sequence_meta

sales_sequence_profile

sales_order_status_label

sales_order_status_state

Truncate Customer tables

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 `wishlist`;

TRUNCATE TABLE `wishlist_item`;

TRUNCATE TABLE `wishlist_item_option`;

Truncate Review tables

TRUNCATE TABLE `review`;

TRUNCATE TABLE `review_detail`;

TRUNCATE TABLE `review_entity_summary`;

TRUNCATE TABLE `review_store`;

Truncate Product tables

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`;

SET FOREIGN_KEY_CHECKS=1;

What startSetup() and endSetup() methods actually does?

The startSetup() and endSetup() methods are used in setup scripts. They are often at the beginning and the end of an upgrade/install method, like in “upgrade()” method of Magento/Catalog/Setup/UpgradeData.php The question is “do you really need them?” Or does it just “look necessary”? This blog post explores what these functions do and then explains when you do and do not need to use these methods. Let’s see what these methods are doing. First, startSetup(): public function startSetup() { $this->rawQuery("SET SQL_MODE=''"); $this->rawQuery("SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0"); $this->rawQuery("SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO'"); return $this; } 1. Disable foreign keys check. It may be necessary in some rare cases (for example, in a case of cyclic references between tables), but it’s not needed in common situations. It even may lead to hiding real problems...

Magento Blog | For Simple Technical Solutions

Search This Blog

Magento 2: How to truncate customers, products, reviews and orders table

Comments

Post a Comment

Popular posts from this blog

Magento 2 product collection Filtering multi-select attribute values

magento 2 best seller product display based on current category and subcategories

What startSetup() and endSetup() methods actually does?