5

We are in the process of migrating to Magento 2.3, but need to remove all test data from the database and reset all increment ID's back to 1. Having some troubles removing all order information:

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

ALTER TABLE `sales_order` AUTO_INCREMENT=1;

SET FOREIGN_KEY_CHECKS = 1;

Produces the following SQL Error:

TRUNCATE TABLE quote

Cannot truncate a table referenced in a foreign key constraint (m2.quote_address, CONSTRAINT QUOTE_ADDRESS_QUOTE_ID_QUOTE_ENTITY_ID FOREIGN KEY (quote_id) REFERENCES m2.quote (entity_id))

  • where do you run your SQL query? on a cli or PHPMyAdmin? – fmsthird Mar 3 at 19:28
  • Running from PHPMyAdmin – AJ47 Mar 3 at 22:03
1
+50

I was checking your SQL query and there seems to be no error or mistake on it, it should work on a MySQL CLI but having said that, the only thing I can assume is:

you run your SQL query on a phpMyadmin SQL interface with the Enable foreign key checks checked. Usually, it is found at the lower right of the SQL query interface.
Try to uncheck it and your query should work.

Note: In order to reset the Order number, you have to include this in your query, TRUNCATE TABLE sequence_order_1 — this table is responsible for generating the increment_id or the order number on the frontend.

Hope that helps!

  • 1
    This fixed it! I wasn't unchecking Foregn Key Checks, also thanks for the tip on the sequence_order_1, I missed that as well. Cheers! – AJ47 Mar 4 at 14:28

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.