Delete test orders in Magento

Dec 13, 2011 Dejan Radic Magento, Orders 194 Comments

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?
This is a new version of the same article.

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 create php script in Magento root folder, copy this code into script and run script.

/**
 * @author Dejan Radic <dejan.radic@inchoo.net>
 */
 
if (version_compare(phpversion(), '5.2.0', '<')===true) {
    echo  '<div style="font:12px/1.35em arial, helvetica, sans-serif;"><div style="margin:0 0 25px 0; border-bottom:1px solid #ccc;"><h3 style="margin:0; font-size:1.7em; font-weight:normal; text-transform:none; text-align:left; color:#2f2f2f;">Whoops, it looks like you have an invalid PHP version.</h3></div><p>Magento supports PHP 5.2.0 or newer. <a href="http://www.magentocommerce.com/install" target="">Find out</a> how to install</a> Magento using PHP-CGI as a work-around.</p></div>';
    exit;
}
 
error_reporting(E_ALL | E_STRICT);
ini_set('display_errors', 1);
 
$mageFilename = 'app/Mage.php';
 
if (!file_exists($mageFilename)) {
    echo $mageFilename." was not found";
    exit;
}
 
require_once $mageFilename;
 
Mage::app();
 
$executionPath = null;
 
/*
 * determine Magento Edition
 */
if (file_exists('LICENSE_EE.txt')) {
    $edition = 'EE';
}elseif (file_exists('LICENSE_PRO.html')) {
    $edition = 'PE';
} else {
    $edition = 'CE';    
}
 
if(($edition=='EE' && version_compare(Mage::getVersion(), '1.11.0.0.', '<')===true)
        || ($edition=='PE' && version_compare(Mage::getVersion(), '1.11.0.0.', '<')===true)
        || ($edition=='CE' && version_compare(Mage::getVersion(), '1.6.0.0.', '<')===true)
  ){
   $executionPath = 'old'; 
} else {
   $executionPath = 'new';  
}
 
$xpathEntity = 'global/models/sales_entity/entities//table';
 
if ($executionPath == 'old') {
    $xpathResource = 'global/models/sales_mysql4/entities//table';
} else {
    $xpathResource = 'global/models/sales_resource/entities//table';
}
 
$salesEntitiesConf = array_merge(
    Mage::getSingleton('core/config')->init()->getXpath($xpathEntity), 
    Mage::getSingleton('core/config')->init()->getXpath($xpathResource)
);
 
$resource = Mage::getSingleton('core/resource');
$connection = $resource->getConnection('core_write');
 
 
/*
 * If you want delete System/Order Statuses (Status and State) you
 * should comments below lines (46-51)
 */
$skipTables = array (
        $resource->getTableName('sales_order_status'),
        $resource->getTableName('sales_order_status_state'),
        $resource->getTableName('sales_order_status_label')
    );
$salesEntitiesConf = array_diff($salesEntitiesConf, $skipTables);
 
 
/*
 
Multiple RDBMS Support in Magento CE 1.6+ / EE 1.11+
    http://www.magentocommerce.com/images/uploads/RDBMS_Guide2.pdf
 
2.2. Adapters:
 
... The new Varien_DB_Adapter_Interface was added to sign a contract that all 
developed adapters must execute in order to get Magento working on an actual 
database. The interface describes the list of methods and constants that can be used by resource models...
 
Used below in the loop:
 
 * If $executionPath == 'old'
    * Varien_Db_Adapter_Pdo_Mysql::showTableStatus()
    * Varien_Db_Adapter_Pdo_Mysql::truncate()  
 * Else
    * Varien_Db_Adapter_Interface::isTableExists()
    * Varien_Db_Adapter_Interface::truncateTable()
 
*/
 
while ($table = current($salesEntitiesConf) ){
    $table = $resource->getTableName($table);
 
    if ($executionPath == 'old') {
        $isTableExists = $connection->showTableStatus($table);
    } else {
        $isTableExists = $connection->isTableExists($table);
    }
    if ($isTableExists) {
        try {
            if ($executionPath == 'old') {
                $connection->truncate($table);
            } else {
                $connection->truncateTable($table);
            }
 
            printf('Successfully truncated the <i style="color:green;">%s</i> table.<br />', $table);
        } catch(Exception $e) {
            printf('Error <i style="color:red;">%s</i> occurred truncating the <i style="color:red;">%s</i> table.<br />', $e->getMessage(), $table);
        }
    }
 
    next($salesEntitiesConf);
}
 
exit('All done...');

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.

This script is tested on following Magento versions: 1.6.1.0 CE, 1.6.0.0 CE, 1.5.1.0. CE, 1.11.0.2 EE, 1.10.0.2 EE, 1.9.0.0 EE, 1.11.0.0 PE, 1.10.0.2 PE and 1.9.0.0 PE.
You can download script here.

194 comments

  2. Awesome Post !
    Used with Magento 1.9. Simple installation and effective solution to delete orders. Great tool!

    Reply

  3. I added also 

    $connection->query("ALTER TABLE {$table} AUTO_INCREMENT=1");

    after truncate, just for sake of completeness.

    Reply

    1. To avoid ‘1701 Cannot truncate a table referenced in a foreign key constraint’ issue in above script, you can add (to disable foreign check)

      $connection->query("SET FOREIGN_KEY_CHECKS=0");

      after connection object is created. And add below at end.(to enable the foreign check)

      $connection->query("SET FOREIGN_KEY_CHECKS=1");

  7. All ok to remove all orders. I’ve maked a test with new order and invoiced it’s all right.

    Magento Version 1.9.0.1 😉

    Thanks.

    Reply

  10. Sales->order data will delete but On Dashboard, Lifetime Sales and Average Order values are available

    Why?

    Reply

    1. Try Below script.. its working for me..

      ===========

      SET FOREIGN_KEY_CHECKS=0;
      TRUNCATE catalogsearch_query;
      ALTER TABLE catalogsearch_query AUTO_INCREMENT=1;

      TRUNCATE sales_flat_order;
      TRUNCATE sales_flat_order_address;
      TRUNCATE sales_flat_order_grid;
      TRUNCATE sales_flat_order_item;
      TRUNCATE sales_flat_order_payment;
      TRUNCATE sales_flat_order_status_history;

      TRUNCATE sales_flat_quote;
      TRUNCATE sales_flat_quote_address;
      TRUNCATE sales_flat_quote_address_item;
      TRUNCATE sales_flat_quote_item;
      TRUNCATE sales_flat_quote_item_option;
      TRUNCATE sales_flat_order_item;
      TRUNCATE sendfriend_log;
      TRUNCATE tag;
      TRUNCATE tag_relation;
      TRUNCATE tag_summary;
      TRUNCATE wishlist;
      TRUNCATE log_quote;
      TRUNCATE report_event;
      TRUNCATE tag_relation;
      TRUNCATE tag_summary;
      TRUNCATE wishlist;
      TRUNCATE log_quote;
      TRUNCATE report_event;

      ALTER TABLE sales_flat_order AUTO_INCREMENT=1;
      ALTER TABLE sales_flat_order_address AUTO_INCREMENT=1;
      ALTER TABLE sales_flat_order_grid AUTO_INCREMENT=1;
      ALTER TABLE sales_flat_order_item AUTO_INCREMENT=1;
      ALTER TABLE sales_flat_order_payment AUTO_INCREMENT=1;
      ALTER TABLE sales_flat_order_status_history AUTO_INCREMENT=1;

      ALTER TABLE sales_flat_quote AUTO_INCREMENT=1;
      ALTER TABLE sales_flat_quote_address AUTO_INCREMENT=1;
      ALTER TABLE sales_flat_quote_address_item AUTO_INCREMENT=1;
      ALTER TABLE sales_flat_quote_item AUTO_INCREMENT=1;
      ALTER TABLE sales_flat_quote_item_option AUTO_INCREMENT=1;
      ALTER TABLE sales_flat_order_item AUTO_INCREMENT=1;
      ALTER TABLE sendfriend_log AUTO_INCREMENT=1;
      ALTER TABLE tag AUTO_INCREMENT=1;
      ALTER TABLE tag_relation AUTO_INCREMENT=1;
      ALTER TABLE tag_summary AUTO_INCREMENT=1;
      ALTER TABLE wishlist AUTO_INCREMENT=1;
      ALTER TABLE log_quote AUTO_INCREMENT=1;
      ALTER TABLE report_event AUTO_INCREMENT=1;

      TRUNCATE eav_entity_store;
      ALTER TABLE eav_entity_store AUTO_INCREMENT=1;

      INSERT INTO ‘YOUR-DATABASE-NAME’.eav_entity_store’ (‘entity_store_id’ ,’entity_type_id’ ,’store_id’ ,’increment_prefix’ ,’increment_last_id’) VALUES(\’1\’, \’11\’, \’1\’, \’1\’, \’000000000\’);
      update eav_entity_store’ set ‘increment_prefix’= 1 where ‘entity_type_id’=\’5\’ and ‘store_id’=\’1\;
      update eav_entity_store’ set ‘increment_last_id’= \’000000000\’ where ‘entity_type_id’=\’4\’ and ‘store_id’=\’1\;

      INSERT INTO ‘YOUR-DATABASE-NAME’.eav_entity_store’ (‘entity_store_id’ ,’entity_type_id’ ,’store_id’ ,’increment_prefix’ ,’increment_last_id’) VALUES(\’2\’, \’16\’, \’1\’, \’2\’, \’000000000\’);
      update eav_entity_store’ set ‘increment_prefix’= 2 where ‘entity_type_id’=\’6\’ and ‘store_id’=\’1\;
      update eav_entity_store’ set ‘increment_last_id’= \’000000000\’ where ‘entity_type_id’=\’18\’ and ‘store_id’=\’1\;

      INSERT INTO ‘YOUR-DATABASE-NAME’.eav_entity_store’ (‘entity_store_id’ ,’entity_type_id’ ,’store_id’ ,’increment_prefix’ ,’increment_last_id’) VALUES(\’3\’, \’19\’, \’1\’, \’3\’, \’000000000\’);
      update eav_entity_store’ set ‘increment_prefix’= 3 where ‘entity_type_id’=\’8\’ and ‘store_id’=\’1\;
      update eav_entity_store’ set ‘increment_last_id’= \’000000000\’ where ‘entity_type_id’=\’24\’ and ‘store_id’=\’1\;

      INSERT INTO ‘YOUR-DATABASE-NAME’.eav_entity_store’ (‘entity_store_id’ ,’entity_type_id’ ,’store_id’ ,’increment_prefix’ ,’increment_last_id’) VALUES(\’4\’, \’23\’, \’1\’, \’4\’, \’000000000\’);
      update eav_entity_store’ set ‘increment_prefix’= 4 where ‘entity_type_id’=\’7\’ and ‘store_id’=\’1\;
      update eav_entity_store’ set ‘increment_last_id’= \’000000000\’ where ‘entity_type_id’=\’28\’ and ‘store_id’=\’1\;

      SET FOREIGN_KEY_CHECKS=1;

      =============

  15. Seems to cause issues with CE 1.8.1.0. Invoices are no longer created correctly following running this code.

    Reply

  19. Perfect solution! Simple and fast! Downloaded from link at the end of the post and Worked like a charm with no editings. Just uploaded (magento root folder) and executed it.

    Thank you for sharing this.

    Reply

  23. Hi again, Okay i just took the plunge and run the script and adding the reset order numbers and invoice numbers mentioned by Darko Goles.

    But this still did not work, running magento version 1.7.0.2.. any help would be appreciated.
    thanks

    Reply

  24. Hi, i’ve used your script to delete my test orders, but have slight problem, i have a live order and when i try and process the invoice for this i get the error,

    Unable to save the invoice.

    I looked at my orders and i just have the 1 order, but looking in the database, under eav_entity_store
    I have some of my test order and the new order.. Looks like i had to reset the order numbers and the invoice numbers.. can anyone help me as how i can do this without loosing my 1 order details..

    thanks.

    Reply

  25. Work for me, Darko Goles. I meet the foreign key problem all of time. You help me save much time since I don’t have to delete many tables in many times. Thanks

    Reply

  26. For anybody that have FOREIGN KEY check errors, just add this before truncating tables:

    $q1 = "SET foreign_key_checks = 0";
$connection->query($q1);

    And then on the end of the file we could add:

    $q1 = "SET foreign_key_checks = 1";
$connection->query($q1);

    And sometimes is neccesary to reset order numbers, invoice numbers etc. after deleting this, so before end of the file we can add:

    $eavEntityStore = $resource->getTableName('eav/entity_store');
$connection->truncateTable($eavEntityStore);
    Reply

  29. Hello,

    I use magento 1.7.0.2 and I’ve got error messages like this:
    “Error SQLSTATE[42000]: Syntax error or access violation: 1701 Cannot truncate a table referenced in a foreign key constraint (`database`.`sales_flat_quote_address`, CONSTRAINT `FK_ZA_SALES_FLAT_QUOTE_ADDR_QUOTE_ID_ZA_SALES_FLAT_QUOTE_ENTT_ID` FOREIGN KEY (`quote_id`) REFERENCES `database`.`sales_flat_quote` (`entity_) occurred truncating the sales_flat_quote table.”

    To solve it we need to execute “SET FOREIGN_KEY_CHECKS = 0;” before and “SET FOREIGN_KEY_CHECKS = 1;” after the truncation commands.

    For this reason I’ve modified this script at lines 97 and 124 and added follow lines before and after “while” :
    $connection->query(“SET FOREIGN_KEY_CHECKS = 0”);
    while ($table = current($salesEntitiesConf) ){
    ….
    }
    $connection->query(“SET FOREIGN_KEY_CHECKS = 1”);

    Regards
    Zaour

    Reply

  31. Awesome script!
    Just had a chance to use it and worked like a charm.

    If in case the script is executed accidently in the live environment it will delete live orders, so it’s good to put the following line of code at the last of the script:

    @unlink(__FILE__);

    which will delete the script file once it is run.

    Cheers!

    Reply

  32. Hey Dejan! I appreciate your kindness to help people getting rid of the terrible nightmare (yes nightmare) of having to erase test orders in Magento. You surely are a great programmer since you produced that great script! But I have to say I’m too lazy, you know, I allways look for an easier way for getting things done. So I didn’t use your script but I user this extension: Seamless Delete Order. Thank you anyway for the good will!

    Reply

  33. Hi,

    Script works successfully. But it truncates status related also. So it will create problems in order status. @Dejan Radic, pls change the script. If any one face like this problem, you can reset the following tables from any other magento installation. 1. sales_order_status 2.sales_order_status_state 3.sales_order_status_label. thanks guys 🙂

    Reply

  36. Today, I tried to decompile and then compile again but it make my site down without any error show. After I delete cache folder and then delete this code, it work well again. Any idea why?

    Reply

  39. Hm i have some errors when I receive new sales.
    After using the script it always troubles with Integrity constraint violations.
    Any experience with this?

    Reply

    1. You should not kill the Foreign key constraints, they are there to preserve integrity. The better idea is to run the queries in test mode and reorganize all the queries to delete the children first.

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <blockquote cite=""> <code> <del datetime=""> <em> <s> <strike> <strong>. You may use following syntax for source code: <pre><code>$current = "Inchoo";</code></pre>.