Delete test orders in Magento

7 September 2009 No Comment

You got a Magento project to develop, you created a Magento theme, you placed initial products and categories and you also placed some test orders to see if Shipping and Payment methods work as expected. Everything seems to be cool and the client wishes to launch the site. You launch it. When you enter the administration for the first time after the launch, you will see all your test orders there. You know those should be deleted. But how?

If you try to delete orders in the backend, you will find out that you can only set the status to “cancelled” and the order is still there. Unfortunately, Magento doesn’t enable us to delete those via administration, so you will not see any “Delete order” button. This can be quite frustrating both to developers and the merchants. People coming from an SAP world find the inability to delete to have some merit but there should be a status that removes the sales count from the reports i.e. sales, inventory, etc.

So, what to do?

You need to go to MySQL admin client and run this query:

view source print ?

01.SET FOREIGN_KEY_CHECKS=0;

02.

03.TRUNCATE `sales_order`;

04.TRUNCATE `sales_order_datetime`;

05.TRUNCATE `sales_order_decimal`;

06.TRUNCATE `sales_order_entity`;

07.TRUNCATE `sales_order_entity_datetime`;

08.TRUNCATE `sales_order_entity_decimal`;

09.TRUNCATE `sales_order_entity_int`;

10.TRUNCATE `sales_order_entity_text`;

11.TRUNCATE `sales_order_entity_varchar`;

12.TRUNCATE `sales_order_int`;

13.TRUNCATE `sales_order_text`;

14.TRUNCATE `sales_order_varchar`;

15.TRUNCATE `sales_flat_quote`;

16.TRUNCATE `sales_flat_quote_address`;

17.TRUNCATE `sales_flat_quote_address_item`;

18.TRUNCATE `sales_flat_quote_item`;

19.TRUNCATE `sales_flat_quote_item_option`;

20.TRUNCATE `sales_flat_order_item`;

21.TRUNCATE `sendfriend_log`;

22.TRUNCATE `tag`;

23.TRUNCATE `tag_relation`;

24.TRUNCATE `tag_summary`;

25.TRUNCATE `wishlist`;

26.TRUNCATE `log_quote`;

27.TRUNCATE `report_event`;

28.

29.ALTER TABLE `sales_order` AUTO_INCREMENT=1;

30.ALTER TABLE `sales_order_datetime` AUTO_INCREMENT=1;

31.ALTER TABLE `sales_order_decimal` AUTO_INCREMENT=1;

32.ALTER TABLE `sales_order_entity` AUTO_INCREMENT=1;

33.ALTER TABLE `sales_order_entity_datetime` AUTO_INCREMENT=1;

34.ALTER TABLE `sales_order_entity_decimal` AUTO_INCREMENT=1;

35.ALTER TABLE `sales_order_entity_int` AUTO_INCREMENT=1;

36.ALTER TABLE `sales_order_entity_text` AUTO_INCREMENT=1;

37.ALTER TABLE `sales_order_entity_varchar` AUTO_INCREMENT=1;

38.ALTER TABLE `sales_order_int` AUTO_INCREMENT=1;

39.ALTER TABLE `sales_order_text` AUTO_INCREMENT=1;

40.ALTER TABLE `sales_order_varchar` AUTO_INCREMENT=1;

41.ALTER TABLE `sales_flat_quote` AUTO_INCREMENT=1;

42.ALTER TABLE `sales_flat_quote_address` AUTO_INCREMENT=1;

43.ALTER TABLE `sales_flat_quote_address_item` AUTO_INCREMENT=1;

44.ALTER TABLE `sales_flat_quote_item` AUTO_INCREMENT=1;

45.ALTER TABLE `sales_flat_quote_item_option` AUTO_INCREMENT=1;

46.ALTER TABLE `sales_flat_order_item` AUTO_INCREMENT=1;

47.ALTER TABLE `sendfriend_log` AUTO_INCREMENT=1;

48.ALTER TABLE `tag` AUTO_INCREMENT=1;

49.ALTER TABLE `tag_relation` AUTO_INCREMENT=1;

50.ALTER TABLE `tag_summary` AUTO_INCREMENT=1;

51.ALTER TABLE `wishlist` AUTO_INCREMENT=1;

52.ALTER TABLE `log_quote` AUTO_INCREMENT=1;

53.ALTER TABLE `report_event` AUTO_INCREMENT=1;

54.

55.-- reset customers

56.TRUNCATE `customer_address_entity`;

57.TRUNCATE `customer_address_entity_datetime`;

58.TRUNCATE `customer_address_entity_decimal`;

59.TRUNCATE `customer_address_entity_int`;

60.TRUNCATE `customer_address_entity_text`;

61.TRUNCATE `customer_address_entity_varchar`;

62.TRUNCATE `customer_entity`;

63.TRUNCATE `customer_entity_datetime`;

64.TRUNCATE `customer_entity_decimal`;

65.TRUNCATE `customer_entity_int`;

66.TRUNCATE `customer_entity_text`;

67.TRUNCATE `customer_entity_varchar`;

68.TRUNCATE `log_customer`;

69.TRUNCATE `log_visitor`;

70.TRUNCATE `log_visitor_info`;

71.

72.ALTER TABLE `customer_address_entity` AUTO_INCREMENT=1;

73.ALTER TABLE `customer_address_entity_datetime` AUTO_INCREMENT=1;

74.ALTER TABLE `customer_address_entity_decimal` AUTO_INCREMENT=1;

75.ALTER TABLE `customer_address_entity_int` AUTO_INCREMENT=1;

76.ALTER TABLE `customer_address_entity_text` AUTO_INCREMENT=1;

77.ALTER TABLE `customer_address_entity_varchar` AUTO_INCREMENT=1;

78.ALTER TABLE `customer_entity` AUTO_INCREMENT=1;

79.ALTER TABLE `customer_entity_datetime` AUTO_INCREMENT=1;

80.ALTER TABLE `customer_entity_decimal` AUTO_INCREMENT=1;

81.ALTER TABLE `customer_entity_int` AUTO_INCREMENT=1;

82.ALTER TABLE `customer_entity_text` AUTO_INCREMENT=1;

83.ALTER TABLE `customer_entity_varchar` AUTO_INCREMENT=1;

84.ALTER TABLE `log_customer` AUTO_INCREMENT=1;

85.ALTER TABLE `log_visitor` AUTO_INCREMENT=1;

86.ALTER TABLE `log_visitor_info` AUTO_INCREMENT=1;

87.

88.-- Reset all ID counters

89.TRUNCATE `eav_entity_store`;

90.ALTER TABLE `eav_entity_store` AUTO_INCREMENT=1;

91.

92.SET FOREIGN_KEY_CHECKS=1;

After you have it executed, the test orders will not be in the database any more. Keep in mind that this will delete ALL orders, in the database. So, you should execute this queries immediately after launch.

By Tomislav Bilic from Inchoo.net