Think

Read the inside scoop about working on the web and running a tech company.



100
Comments

Magento eCommerce: How To Reset All Test Order Information and Set Unique Prefix For Orders, Invoices, Shipments, and Credit Memos


About: How To Reset Test Orders, Dashboard Information, and Set Unique Starting Values for Orders, Invoices, Shipments, and Credit Memos

Who’s Interested: Informative to the technical gurus
What: Plug ‘n Play Database Script that runs queries to customize after store is installed and product(s) have been created
Magento Version Relevence: 1.1.6
Magento Store Setup: Single-Store Setup

A client of ours went in to test products, orders, invoices, etc. to get the feel for the overall “purchase order to shipment” process from start to finish. After creating many test users, orders, etc. – they now desire to have everything reset back to “0″ (zero).

Not only that, but they also want to separate the prefixes for the following items just for ease of dissimilarity when using the Magento ADMIN tools:

  • Orders (set prefix to begin with “1″)
  • Invoices (set prefix to begin with “2″)
  • Shipments (set prefix to begin with “3″)
  • Credit Memo’s (set prefix to begin with “4″)

Thus, we have the following script (and a special thanks to Tomislav at Inchoo for getting us started!)

Instructions:

  1. Find “YOUR_DB_NAME” down below and change to match your database name (though leave the ticks “)
  2. Copy and paste code into SQL manager (PhpMyAdmin or similar) and then run the query
  3. Feel free to Create test order, invoice, shipment, and credit memo’s via the ADMIN (easier than going through order process on frontend)
  4. Once you like the changes you see, go back and run the same SQL query
  5. …the following will now be in effect:

    • All data pertaining to orders and search terms (Dashboard) are reset to 0 (zero)
    • Order number prefix with 0, Invoice number prefix with 2, Shipment number prefix with 3, Credit Memo number prefix with 4

Please feel free to copy and paste into your PhpMyAdmin

-- Reset Magento TEST Data

SET FOREIGN_KEY_CHECKS=0;

-- reset dashboard search queries
TRUNCATE `catalogsearch_query`;
ALTER TABLE `catalogsearch_query` AUTO_INCREMENT=1;

-- reset sales order info
TRUNCATE `sales_order`;
TRUNCATE `sales_order_datetime`;
TRUNCATE `sales_order_decimal`;
TRUNCATE `sales_order_entity`;
TRUNCATE `sales_order_entity_datetime`;
TRUNCATE `sales_order_entity_decimal`;
TRUNCATE `sales_order_entity_int`;
TRUNCATE `sales_order_entity_text`;
TRUNCATE `sales_order_entity_varchar`;
TRUNCATE `sales_order_int`;
TRUNCATE `sales_order_text`;
TRUNCATE `sales_order_varchar`;
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`;

ALTER TABLE `sales_order` AUTO_INCREMENT=1;
ALTER TABLE `sales_order_datetime` AUTO_INCREMENT=1;
ALTER TABLE `sales_order_decimal` AUTO_INCREMENT=1;
ALTER TABLE `sales_order_entity` AUTO_INCREMENT=1;
ALTER TABLE `sales_order_entity_datetime` AUTO_INCREMENT=1;
ALTER TABLE `sales_order_entity_decimal` AUTO_INCREMENT=1;
ALTER TABLE `sales_order_entity_int` AUTO_INCREMENT=1;
ALTER TABLE `sales_order_entity_text` AUTO_INCREMENT=1;
ALTER TABLE `sales_order_entity_varchar` AUTO_INCREMENT=1;
ALTER TABLE `sales_order_int` AUTO_INCREMENT=1;
ALTER TABLE `sales_order_text` AUTO_INCREMENT=1;
ALTER TABLE `sales_order_varchar` 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;

-- Reset all ID counters
TRUNCATE `eav_entity_store`;
ALTER TABLE  `eav_entity_store` AUTO_INCREMENT=1;

SET FOREIGN_KEY_CHECKS=1;

-- set appropriate prefixes for orders, invoices, shipments, credit memos
INSERT INTO  `YOUR_DB_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`='4' 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_DB_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`='18' 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_DB_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`='24' 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_DB_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`='28' and `store_id`='1';
update `eav_entity_store` set `increment_last_id`= '000000000' where `entity_type_id`='28' and `store_id`='1';

For SQL Script download: Magento Database Modifications

Let us know how you like it!

Thanks,
Lee Taylor







  • http://eliasinteractive.com Eric Clark

    Great post, Lee. I’m sure many will find this useful in the Magento community!

  • http://url Marty

    Thanks for the script Lee. I found a typo in your script.

    After you “TRUNCATE `eav_entity_store`; ” you are doing an update on that table based on an ‘entity_type_id’ that does not exist. The last eight update statements do nothing.

    Also, it should be noted that this script assumes a single store setup. If you have multiple stores you would need to add additional insert statements with proper store_id.

    Thanks again for the script.

  • http://tools.zinnia.org Joy

    Why not use letters instead of numbers for the various prefixes? The table is set up to be a varchar already.

  • http://url Marty

    One more thing. I’ve been messing around in that part of the system today and I think you’ll definitely want to remove the “TRUNCATE `eav_entity_store`;” statement. The reason is; that table also holds counters for other parts of the system, such as customer number sequence. If you truncate it you may end up breaking other parts of Magento. Probably better to do updates only on that table.

  • timothyleetaylor

    Hi @Marty,

    In reference to your first post: you are correct in seeing that this is assumed for a single-store Magento setup. We could go in and reconfigure some of the values to be modified for multiple stores.

    Also, with the TRUNCATE command, we don’t actually delete the table, but all the information in it. We then, in the last 8 commands (check lines 79, 83, 87, 91) create values inside that table to be modified. The first statement clears the table, and the second set of statements inserts the structure to be modified.

    As for using the TRUNCATE on that table, you may be correct in that it may be more safe to simply update on that table. What errors have you encountered by removing the customer number sequence?

    Thanks for your thoughts!
    Lee

  • timothyleetaylor

    @Joy,

    The reason we used numbers was simply by client’s request. You can go ahead and insert letters as the datatype is varchar-appropriate.

    Thanks for mentioning the possibility!
    - Lee

  • http://url Jesse

    Thanks for the script Lee. In regard to the posts concerned with Truncating the table, have you experienced any problems yourself with live sites after having used this script? We plan on launching a site tomorrow and have yet to give your script a go. Would you still advise using it as is? Just hoping we won’t run into any problems along the way..

    Many thanks.

  • timothyleetaylor

    Hi Jesse,

    This script should work correctly for you, as we’ve tested it on a couple sites thus far, though recently. Do you have anyone familiar with SQL code? If so, let them walk you through it and make you feel more comfortable with it.

    As always when modifying database data, I would be sure to create a backup via Magento tools in the ADMIN or via your SQL interface such as PhpMyAdmin.

    In regards to the concerns posted, this only involves using this script after the site has been acquired various customer accounts, thus possibly effecting the numbering sequence.

    So if you do have customer accounts you’d like to keep, it may be wise to wait for an updated script that I’ll try and get onto as time allows. Otherwise, please feel free to contact us with any needs you may have regarding web development, design, or marketing – and we’d be glad to assist you!

    Thanks,
    Lee

  • http://url Jesse

    Thanks for the quick reply. I don’t need to keep any accounts so I’ll give it a shot tomorrow.

  • http://www.modsnroses.com Yaani

    I tried this & it seemed to delete all the test orders but I also got this error

    SQL query:

    INSERT INTO `mystoredatabase`.`eav_entity_store` (
    `entity_store_id` ,
    `entity_type_id` ,
    `store_id` ,
    `increment_prefix` ,
    `increment_last_id`
    )
    VALUES (
    ’3′, ’19′, ’1′, ’3′, ’000000000′
    );

    MySQL said: Documentation
    #1452 – Cannot add or update a child row: a foreign key constraint fails (`modsnroses/eav_entity_store`, CONSTRAINT `FK_eav_entity_store_entity_type` FOREIGN KEY (`entity_type_id`) REFERENCES `eav_entity_type` (`entity_type_id`) ON DELETE CASCADE ON UPDATE CASCADE)

    Can you tell me if there is anything else I need to do in regard to this error?

    Thanks!

  • http://www.modsnroses.com Yaani

    I think I found the answer to my error problem above here
    http://www.magentocommerce.com/wiki/restoring_a_backup_of_a_magento_database

    Thanks!

  • http://www.modsnroses.com Yaani

    No that didn’t fix the following error

    SQL query:

    INSERT INTO `modsnroses`.`eav_entity_store` (
    `entity_store_id` ,
    `entity_type_id` ,
    `store_id` ,
    `increment_prefix` ,
    `increment_last_id`
    )
    VALUES (
    ’3′, ’19′, ’1′, ’3′, ’000000000′
    );

    MySQL said: Documentation
    #1452 – Cannot add or update a child row: a foreign key constraint fails (`modsnroses/eav_entity_store`, CONSTRAINT `FK_eav_entity_store_entity_type` FOREIGN KEY (`entity_type_id`) REFERENCES `eav_entity_type` (`entity_type_id`) ON DELETE CASCADE ON UPDATE CASCADE)

    Should I just try to create those last two table entries?

    It seems the first two tables are created
    1 11 1 1 000000000
    2 16 1 2 000000000

  • timothyleetaylor

    Hi @Yaani,

    Thanks for your interest on this SQL script. Can you verify that you’re using Magento 1.1.6? The db structure is necessary for this script to be applicable. If you’re using a lower version, feel free to go into `eav_entity_type` and see what values are for orders, invoices, shipments, and credit memo’s. They may have a different value for `entity_type_id`.

    I’m thinking an indifference there may be what’s causing that kind of error.

    Thanks!
    Lee

  • http://www.modsnroses.com Yaani

    It’s Magento 1.1.6

    I went into the database & looked at the eav_intity_type table & it has two entries that contain the following data

    1 11 1 1 000000000
    2 16 1 2 000000000

  • http://www.modsnroses.com yaani

    I was able to get rid of the mysql error by moving the line of code “SET FOREIGN_KEY_CHECKS=1;” to the end of the sql query & everything worked great! Thanks!

  • http://www.hearsaynashville.com Kev

    Thanks! Works great for 1.1.6! I’m about to upgrade though, so I need to know if anyone has tried this with 1.1.7?

  • http://url Matt Albiniak

    Great work, Lee. Thanks for sharing this with the community.

  • http://url David

    I’m completely new to databases I open phpmyadmin and its like looking at the cockpit of a 747, but clearing out my test orders is something I really want to do. When I select my database in phpMyadmin and then select query paste the code with my database name in the SQL query on database XXXXX: it brings up the message: “You have to choose at least one column to display”. So, I’m lost, what am I missing?

  • http://eliasinteractive.com Eric Clark

    Hi David, would you mind posting the exact error message from phpmyadmin? Also make sure you find/replace all instances of YOUR_DB_NAME in that query.

  • http://url David

    Yes, I have replaced all instances of “YOUR_DB_NAME” and the error message is:
    “You have to choose at least one column to display”

    I know this is operator error. Here is a little more data, The screen I’m looking at is the Query tab of my database. There are 3 areas to work in. There is a box up top with the pulldowns field, and sort. A checkbox for show. A set of fields titled Criteria:. More checkboxes for ins:,Del:, And:, and Or: etc. The second box is “Use Tables”, I assume that I select all of them. The third box is “SQL query on database XXXXX: this is where I post the code.

    Thanks for the help

  • http://url David

    A little more progress, I was selecting the wrong tab. It should have been the SQL tab. But its not over yet. now the error is:

    #1064 – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘magento’ at line 1

  • timothyleetaylor

    Hi @David,

    I would recommend you start by Googling “How to run SQL query in PHPMyAdmin” or something similar. Then come back once you get the basics down.

    This is the best initial step for both of us before trying to troubleshoot with you I believe.

    Cheers!
    Lee

  • http://url Boaz

    Hi Lee,

    The script is running (after removing the SET FOREIGN_KEY_CHECKS=1) and orders are cleared but order and invoices number don’t change: they all start with 4 (the first is #400000001).

    Any ideas?

    Boaz

  • timothyleetaylor

    Hi @Boaz,

    No problem, I’d be glad to help.

    If you’d like, try to go through and run the last several lines starting with the “INSERT” statements. They are designed to create (if there aren’t any remaining values) and modify to increment the prefixes for orders, invoice, shipments, and credit memos.

    To check manually, you can go into your db and access the `eav_entity_store` table to view. Please make sure there are incrementing values for`entity_store_id` and make sure the `entity_type_id` values are correct for your version of Magento. Lastly, make sure the prefixes are correctly stored in the `increment_prefix` column.

    Though this screenshot resembles version 1.1.6, please feel free to view this screenshot as an example.

    Let us know if you have any more problems!

    Thanks, and all the best,
    Lee

  • http://url Boaz

    Lee,

    Thanks for your quick replay.

    After posting my question I want and check the eav_entity_store table. I found the newly inserted records there and also another record with store_id=4 and increment_last_id = 400000001.
    I’m not sure why my store_id was 4, maybe it’s because I played around with creating and deleting stores, but anyway I tried deleting this record and changing the store_id to 4 in the script and rerun it, but it fails too. Magento just recreated an additional record with entity_type=4, store_id=4 and increment_last_id = 400000001.
    At this point I edited that record in the DB, change the increment_last_id to 000000001 and the increment_prefix to ” “. With this change I managed to get the order id starting with 0. Magento also created a record for the invoices with entity_type = 18 which I also changed manually to control the invoices numeration.

    So… I managed to manually make it work but I don’t really understand how the script was suppose to make it work. Just hope that my changes are not causing any internal Magento problems (so far it seems OK).

    Boaz

  • timothyleetaylor

    Hi Boaz,

    It’s important to note here that the script is meant only for a single-store setup (which you can verify via the values being “1″ in the `store_id` column). Thus, the script will only insert values for your store associated with the `store_id` value of “1″.

    If you need help reading the SQL code, try this as a helpful resource: http://www.w3schools.com/sql/default.asp

    The script works correctly with what you performed manually, but for a single-store setup. My apologies if I hadn’t made that clear enough for you in this post. I’ll review and add notification for that up above.

    Thanks,
    Lee

  • http://url Boaz

    Lee,

    Thanks. I realized that I need to match the store_id the the id that I have (it’s a single store setup but I had several stores there before as a test which I deleted), but for some reason it didn’t work as expected even after changing the store_id in the script.
    SQL syntax is very clear. I don’t fully understand the logic: why do you create entity_types with new entity_ids (11,16,19, and 23) and then try to update record with entity_ids that doesn’t exists (deleted by the previous TRUNCATE `eav_entity_store` statement)? I’m sure that there is some logic here, I just don’t know what it is.

    In any case, this script was a great help. I would never dare to touch those tables without seeing how it’s done in this example.

    Thanks again,
    Boaz

  • timothyleetaylor

    Hi Boaz,

    So glad you found this as beneficial towards your Magento efforts. We’ll continue sharing with the community findings we see as relevant as we progress.

    As for this specific script, we truncate to reset, prior to re-creating the separate entries for the necessary db values that in the `eav_entity_store` records. The prefixes are associated to a specific values that Magento uses set for `entity_type_id`. Each `entity_type_id` value relates to orders, invoices, shipments, and credit memo’s respectively.

    Thanks again for the dialogue and feedback!
    Lee

  • http://url Shreehari

    Hi,
    Is is possible to refresh (reset) a part of the database.

  • timothyleetaylor

    Hi @Shreehari,

    I’m not sure if I know what you mean exactly. Feel free to email us via the contact form below if you’re needing some consulting. Otherwise, try to be more specific with what you intend to happen, and we’ll do our best to help.

    Thanks!
    Lee

  • http://url benz

    Hi all,
    apologies if i have skimmed and missed some info -
    What I am wondering is if this will clear ALL of the orders in my Admin?

    I have several test orders (cancelled), and now several real orders – I would like to remove the tests whilst keeping any new orders that have come in..

    Will this script only remove cancelled orders?
    thanks in advance, and great job!
    Ben

  • timothyleetaylor

    Hi @benz,

    Glad to hear from you! No worries with asking the question. This script actually removes all orders in the ADMIN. It’s definitely possible to create a script that removes test orders – though we’d probably want to work with you on that specifically together. Many variations in store setup/management would probably create the need for a specific-script relating to your store setup, rather than an easy “plug ‘n play” script like the one above.

    Should you desire this, please feel free to contact us via the contact form below and we’d be glad to assist you towards a solution. It shouldn’t take too much work to get something like this together for you.

    All the best, and feel free to let us know how you proceed!

    Thanks,
    Lee

  • http://www.kehot.com.ar david

    Beautiful! Absolut beauty!

    WORKS FOR 1.1.8

    Thanks for shearing!
    D

  • http://www.kehot.com.ar david

    Oh.. and… it cleared all the undesired data from all my stores and websites.

    My configuration:

    Website1 | store | storeview (ES)
    Website1 | store | storeview (EN)

    Website2 | store | storeview (ES)
    Website2 | store | storeview (EN)

    I just didn’t understand how to change the prefix from numbers to characters… I would like to do that.

  • http://www.emercado.co.cr Boaz

    Hi Lee,
    I wonder if you have any idea how to solve the following:
    My store has 2 store views: English and Spanish. Magento seem to use different store_id value for each store view. This means that there is a separate set of sequences used for the order ids and invoice ids of each view.
    I don’t care so much about the order id but the separate invoice ids are a big problem. This is one store with different views and according to law I must have sequential invoice numbers for all transactions.
    I’m looking for a way to impose all the invoices from all the store views to use one sequence.
    Any ideas will be appreciated.

    Boaz

  • http://store.reformu.com Don

    Thank you so much. Worked perfect right off the bat. Reset the data and boom. What I am going to do now is change the bottom part where it resets all the order numbers and stuff to “00000000″ and just put some arbitrary numbers in there. I just don’t like all of those 0′s.

    Thanks again!!!!

  • timothyleetaylor

    Way to go Don!

    We’re glad this was helpful for you. Rock on with Magento, and keep taking advantage of what it provides.

    Thanks!
    Lee

  • timothyleetaylor

    Hey @Boaz, what you mention here will take a bit more customization than what we could discuss via blog comments.

    Should you desire to hire someone to make this functionality present, feel free to contact us via our contact form below.

    All the best with your endeavors!
    Lee

  • http://www.emercado.co.cr Boaz

    Lee,

    Thanks for the offer. I may use it for other things but for this specific problem I found a simple solution that seems to be working perfectly:

    In eav_entity_store added two entries:
    For orders: 
    entity_type: 11
    store_id: 0
    increment_prefix: 0
    increment_last_id: 010000000 (of any other start id that you choose)

    For invoices: 
    entity_type: 16
    store_id: 0
    increment_prefix: 0
    increment_last_id: 000000001 (of any other start id that you choose)

    additionally: changed the values of “increment_per_store” from 1 to 0 for entity_type_code 11 and 16 (order and invoice) in eav_entity_type.

    Credits to Linz (http://www.magentocommerce.com/boards/viewthread/5893/P15/)

    Boaz

  • http://www.infinitytungsten.com Stewart Goodwin

    Does this work for version 1.2.0.2?

  • http://url Charles

    Ditto!

    Does this work for version 1.2.0.2?

  • http://iteam.dk iTeam – Magento design

    Stewart – tried running it on Magento 1.2.0.2 – and while it did remove the pipeline results the orders’ still there:

    I did get this error in phpmyadmin after running the script:

    – reset dashboard search queries
    TRUNCATE `catalogsearch_query` ;

    MySQL returned: Documentation
    #1146 – Table ‘mydatabase_id.catalogsearch_query’ doesn’t exist

    Ideas?

  • http://url Stewart Goodwin

    I have been unsuccesful in getting it to work on 1.2.0.2. We are going live Feb 1. Anybody have any success removing test orders?

  • timothyleetaylor

    Hi Stewart,

    We have not updated this for version 1.2.0.2 yet. I’m interested in what errors you’re getting? Would you mind posting them?

    Also, be sure to check and make sure the following fields contain the proper updated values (values can be seen via phpMyAdmin or similar software):

    `entity_store_id` ,
    `entity_type_id` ,
    `store_id` ,
    `increment_prefix` ,
    `increment_last_id`

    Thanks,
    Lee

  • http://www.madsnygaard.com Mads Nygaard Pedersen

    Thanks Lee – will check the 1.2.0.2 version out – meanwhile the 1.2.0.3 has been released – hopefully not obstacles there ;-)

  • http://url Stewart

    To be honest I am a little nervous running the script knowing it won’t work. The site is set to go live Friday and any setbacks would definitely hurt time wise… Any suggestions for getting rid of the test transactions or do you have any time line for when an updated script will be ready? Thanks!

  • timothyleetaylor

    Hi @Stewart,

    I completely understand your hesitation here. It can get a bit tricky/sensitive when working with the Magento database.

    We are available for hire should you desire to get this job done professionally with the best Magento practices!

    Unfortunately outside of that, we do not yet have a planned date for the new compatibility release. We’ve just been too busy at this point. I apologize for the inconvenience.

    Thanks, and let us know if you need assistance with this,
    Lee

  • http://url Stewart

    well… I figured I would give it a shot before subbing it out. The script works perfectly on 1.2.0.3. Something to note on the errors above. Most likely you got the “does not exist” errors because you have a table prefix (which most hosts use for databases). Once I added the db prefix to all the tables in the script everything worked great.

    One question though… How do I delete customer downloadable link info? For example when I log into my customer account it still shows downloadable product links from old orders [the links don't work obviously]?

  • http://url Stewart

    …found it! Just delete the unneeded entries from downloadable_link_purchased_item and downloadable_link_purchased

    hope this helps someone.

  • http://www.cerocaustralia.com.au/shop Natalie

    Hi

    Thanks Lee for the script

    It works for me in ver 1.2.1

    I added four extra tables to be truncated:
    `sales_flat_quote_payment`;
    `sales_flat_quote_shipping_rate`;
    `sales_order_tax`;
    `wishlist_item`;

    Let me know if you think there’s any reason not to empty these tables.

    Also I manually updated the eav_entity_store table as I my entity_type_id field had different values from the script. I also wanted to keep the customer sequence number.

    I tried using a letter for the increment_prefix, which seemed to work in most places but failed in the list of orders / invoices etc in the admin backend, so I stuck to numeric prefixes.

  • http://None Rune

    For some reason this causes my payment module from Quickpay to crash. When placing my order I get the error msg: “There was an error processing your order. Please contact us or try again later.”

    I’ve enabled Magento logging and in exception.log i get:

    http://pastebin.com/m33e9c1a

    Once I restore the DB from before truncating – everything works fine. But I have to truncate date before going live.
    I really hope to get some help. Thank you :)

  • http://None Rune

    I solved the issue. Since I truncated the Magento order tables the order id was starting from the beginning. When submitting the order to Quickpay they refused because the order id was already in their system – due to my previous testing.

    Hope this helps someone :)

  • timothyleetaylor

    Hey @Rune,

    I apologize for not getting a chance to get back to you before you found the solution. But it looks like you have been able to sort things out yourself! Great work, and thanks for reporting here to let everyone in on the findings.

    Keep us updated with how things are going with you!

    Thanks,
    Lee

  • timothyleetaylor

    Hi @Natalie,

    Thank you as well for your input on the discussion here. I’m curious as to what failed for you, and what errors you were getting when trying to run the script.

    I haven’t verified that the script works with 1.2 yet, but hope to soon.

    And as for those tables you mentioned, I don’t see any issue in truncating them unless you have a need for maintaining that information relating to current customers you have. At first glance, I think you should be fine – though what is your situation regarding maintaining current customer data?

    Thanks!

  • http://www.cerocaustralia.com.au/shop natalie

    Hi @Lee

    No errors when I ran the “truncate” part of the script

    I only added the extra tables to truncate as it seemed logical that they also be emptied out

    I did the eav_entity_store table by hand as I could see my entity_type_id fields were different and it was easier to update manually than edit the script.

    I have no need to maintain current customer data – re orders etc, but wanted to retain the customers already entered. They are the client ones and it’s easier for them to keep the same customers for their testing at the moment

    Thanks again

  • Pingback: Wijzigen ordernummer en factuurnummer - Magento webshop | Nederlands Magento forum voor webwinkels en ontwikkelaars

  • http://www.kehot.com.ar David G.

    You may all be interested in this extension to Delete Orders:
    http://www.magentocommerce.com/extension/873/delete-orders

    Extension KEY: magento-community/BoutikCircus_DeleteOrders-beta

    (it works!)

  • http://www.condomgirl.co.uk Condoms Girl

    Thanks for the post :-) very useful. I’m going to go away and have a look at my magento site, http://www.condomgirl.co.uk, and see if I can generate random invoice numbers rather than consecutive.

  • http://www.madsnygaard.com Mads Nygaard Pedersen

    David G. – thanks for the tip :-)

    /Mads

  • http://www.kehot.com.ar david

    Hello. I’m experiencing serious problems in orders and since I used this script before going live I wonder if it has anything to do with it or if any of you experienced these errors.

    I have PayPal standard and Saved CC payment methods available.
    When I make an order with a credit card and a real address the new order has the following problems:

    - Payment method shows wrongly as a PayPal Standard payment instead of Credit Card purchase.
    - The shipping and billing addresses are correct in the e-mail of a new order notification but they are wrong in the backend (it shows a random address saved somewhere in the DB from when I was testing).
    - No credit card information in backend!! The credit card info is shown in the new order e-mail but not in the backend. It shows as a paypal standard payment and its not.

    I tried disabling paypal and test gave same wrong result.

    - Running Magento 1.2.1.2

    The site is live… Is this something you’ve seen before??

  • Pingback: South Lake Tahoe Web Design Blog - RogueEasyweb Blog » Magento Tips, Tricks and Hacks

  • http://mediavangelist.com/ Jay Benfield

    Just what I was looking for – thanks!

  • http://url Shinobi

    Magento noob here. This is great. Just what I’m looking for. Question though – is it possible with this script to not just prefix orders, invoices, etc. but to start the numbering with something like say 595080398? By changing the ‘last_increment_id’ to whatever number and then it increments by 1 from there? or whatever increment amount I want?

  • http://url Shinobi

    OK, I gave it a shot on a local instance of the store I’m developing. Magento v1.2.1.1 – seems to work fine. Deleted all my test orders and reset everything.

    However, if you notice in the script where it updates Orders, Invoices, etc., the Update statement uses the Entity IDs 11, 16,19, and 23 while the Insert Statements reference the IDs 4, 18, 24 and 28. Was that just a typo or a change from versions or something? I made them match and now my orders have been reset, are prefixed with the numbers and start at the new number I set .

    Thank you very much. My store is almost ready and I needed to clean things up before going live and my client asked to change the starting order number. Perfect!

  • http://www.monocat.com Mosses Akizian

    Tried it in v. 1.3.1 and seems to be working fine for checkout with one store. Was also able to use alphanumeric prefix.

    Tested for multi-store by copying last 4 main commands. Worked fine at backend via phpMyAdmin. Don’t forget to assign 2nd store_id and increment per entity_store_id. Didn’t test for checkout though. Anyone with success, please report.

  • http://gabriel.indexainternet.com.br Gabriel Zamprogna

    I am experiencing a problem here: after I delete the test orders, I can’t create invoice for any new order! The system says “Order not longer exist”.
    It seems like something got wrong in my database, but I can’t find what…

    My version is 1.3.1 and I have only 1 day to complete this specific installation.

  • Pingback: Remove all orders « Phi & Fashion, turned into a Magento blog..?

  • http://bradysewall.com Brady

    Wow – what an excellent article!

    I’m trying to piece together a personal solution. I’m using Magento 1.3.1.1 and need to add a prefix to only Credit Memos.

    In my database, the Credit Memos are entry_type_id = 23. I change the increment_prefix to “R1″.

    Everything seems to go through fine, except when I look in the Admin > Sales > Refunds, the Refund Number simply shows up as 0, even though in Admin > Sales > Orders, then clicking on the Refund tab shows the correct refund number.

    Correct: http://img.skitch.com/20090528-pqei694783ahcqhkywdrcr34n4.jpg

    Incorrect: http://img.skitch.com/20090528-k1fthj41a26ihgswp4sdx8t6rn.jpg

  • http://url Ratna

    Thank you for all the info.

    The script worked splendidly in 1.3.1. You may want to add the following two tables as well -

    TRUNCATE `downloadable_link_purchased`;
    TRUNCATE `downloadable_link_purchased_item`;

  • http://url evuska

    Hi,

    I don’t understand the querys of the eav_entity_store table.

    - first you truncate eav_entity_store, ok!
    - then you insert values in eav_entity_store with the entity_type_ids : 11, 16, 19 y 23. why do you use this entity_type_ids? Why don’t you insert values with entity_type_ids: 4,18,24,28??

    In my database
    entity_type_id|entity_type_code
    11 | quote
    16 | quote_payment
    19 | doesn’t exists
    23 | invoice_comment

    4 | order
    18 | invoice
    24 | shipment
    28 | creditmemo

    - and finally, you try to update record with entity_type_ids = 4,18,24,28 that doesn’t exists in the table, because they were deleted by the previous truncate statement.

    Can you explain it me please!

    thanks!

  • http://url evuska

    Hi again

    I think it’s necessary to add the following tables:
    - product_alert_price
    - product_alert_stock
    - log_customer
    - log_summary
    - log_summary_type
    - log_url
    - log_url_info
    - log_visitor
    - log_visitor_info
    - paypal_api_debug
    - gift_message

    Do you think so?
    Thanks

  • http://www.jira.nl Ray Bogman

    Well check this, a total solution for deleting Magento order “Delete Any Order”

    https://www.yireo.com/software/delete-any-order

  • http://url Bill Bartmann

    Great site…keep up the good work.

  • http://url ch3ckmat3

    hay Taylor,

    thats a great article, but i have a slight different situation. i want to delete all data only related to any given order number, instead of truncating all the relevant tables for all orders.

    for example, I posted an order in my live store and set is as complete for testing purpose. now i cant just truncate any tables. I just want to clear this particular order from my store.
    I’m sure your above query can do the trick, but I don’t know much about the fields and FK relationships in the magento db. can you help?

    TIA

  • Pingback: How to clear dashboard stats in magento, Voodish Articles.

  • Pingback: Clearing Test Data From Magento « The Cutting Edge

  • http://url Rich Y

    Is this script still relevant and complete for 1.3.2.4?

    FYI: The link to download SQL is non-existent, and it’d be nice to do without the numbered lines (can’t easily copy paste code without having those line numbers in there).

    Thanks

  • Lee Taylor

    Hi @Rich Y,

    My apologies on that. Feel free to check the link again now. Also, I cannot confirm that it is working with the latest version of Magento. Please see the responses above to determine which database values need changed/monitored for version upgrades.

    Note:
    `entity_store_id` ,
    `entity_type_id` ,
    `store_id` ,
    `increment_prefix` ,
    `increment_last_id`

    Cheers,
    Lee

  • http://url bill S

    My current install, ver 1.3.2.4:

    – Dumping data for table `eav_entity_store`

    INSERT INTO `eav_entity_store` (`entity_store_id`, `entity_type_id`, `store_id`, `increment_prefix`, `increment_last_id`) VALUES
    (1, 1, 0, ’0′, ’000000025′),
    (2, 11, 1, ’1′, ’100000085′),
    (3, 16, 1, ’1′, ’100000007′),
    (4, 23, 1, ’1′, ’100000002′),
    (5, 19, 1, ’1′, ’100000002′);

    Your script addresses 4 entity_store_ids total, and type 1 according to my eav_entity_type table is “customers”. Is this a major db change?

    Also confused by the update commands referencing 4, 18, 24 and 28. why not matching the 11, 16, 19 and 23? I dont even have an entity type above 25.

  • http://url center
  • Lee Taylor

    @center, that posting is a good resource, though it doesn’t allow you to change the prefix for any orders, invoices, etc. Mainly, it uses the same truncate statements here without the prefix-modifying code.

    Thanks,
    Lee

  • http://www.netsalestrainer.com Ecommerce Training

    Magento can be a real fiddle but we have made lots of video tutorials over at http://www.netsalestrainer.com that everyone can watch for free.

  • http://treespun.com Rob

    I just started working with Magento and have to say I was quite disappointed that there is no way inside the program itself to delete data. Everyone is going to test the order system before you go live, you would think it should be standard. You’re site was the only one I could find with a mention of how to reset data — unfortunately seems your queries are for an older version of Magento — Any plays to make this viable for 1.4.0.1?

  • http://url Rich Y

    @Rob:
    See my post here for 1.4x:
    http://www.magentocommerce.com/boards/viewthread/76136/#t223990

    I THINK the only two lines to add to the clear out SQL is:

    TRUNCATE `sales_payment_transaction`;
    ALTER TABLE `sales_payment_transaction` AUTO_INCREMENT=1;

  • Pingback: Klantnummer en order ID

  • Pingback: Magento Tips: Edit Contact, AOL, email Templates, Free Shipping and more... | Rogue Easyweb

  • http://Newslettersubscribers.. Ramon de la Fuente

    I just ran the script and also added the previously mentioned tables:

    `sales_flat_quote_payment`;
    `sales_flat_quote_shipping_rate`;
    `sales_order_tax`;

    And because in this case we were not preparing an empty site with test orders and customers but rather copying an existing site, I also added the newsletter table. I think it should be included in the script because it references the customer_id.

    `newsletter_subscribers`;

    Other than that – thanks for sharing, this post saved a lot of research time!

  • http://zoosper.com/ Damu

    just what i was looking for. Thanks for the post.

  • http://bit.ly/dz2rg1 Oliver

    Hey Guys,
    i just posted a script which i tested with magento 1.4.1. It is written in german but i guess you know translate.google.com ;)
    Thx for your post it helps a lot!
    http://swebdesign.blogspot.com/2010/07/loschen-von-testdaten-und-setzten-eines.html

    Greets from germany

  • http://url Tanner

    Another useful SQL Tutorial is http://www.1keydata.com/sql/sql.html

  • http://modulesoft.biz Modulesoft

    You can easily Manage your orders and delete it via Magento Order Book Extension;
    search for Magento order book extension in magento website

  • http://greenmorebeer.com sandy
  • Dhiren

    Hello friends, Now it is very easy to Manage your Orders and Delete it through the Orderbook. Here you can delete orders on any status and many more. Just look at it-http://store.modulesoft.in/manage-orders-by-orderbook.html

  • Dhiren

    Hello friends, Now it is very easy to Manage your Orders and Delete it through the Orderbook. Here you can delete orders on any status and many more. Just look at it-http://store.modulesoft.in/manage-orders-by-orderbook.html

  • Dhiren

    Hello friends, Now it is very easy to Manage your Orders and Delete it through the Orderbook. Here you can delete orders on any status and many more. Just look at it-http://store.modulesoft.in/manage-orders-by-orderbook.html

  • http://www.outoftheboxtechnology.com/ Inventory software

    My understanding is that the memos were very detailed, giving
    information on minimum and maximum temperatures allowed, the number of
    “pours” allowed in each waterboarding session, and the number of
    sessions allowed per day.

  • Neelaj

    Do you know how can I change the customer number in magento?

  • Benstrowger

    Hello there

    I am really interested in this feature, has anybody tried this in the latest version of magento 1.6.1.0??

    Many Thanks

  • http://www.magebuzz.com/ Neo

    Hi Lee,
    Thanks for your sharing.
    Your post is quite old, so I made some changes to work with Magento 1.6.2.0.
    I posted my updated code here: http://www.magebuzz.com/blog/2012/04/23/how-to-reset-test-orders-and-dashboard-statistics-magento-1-6-2-0
    Let me know if there is no problem. Also, you can review my code and let me know if there is any problem.

    Thanks,
    Neo.

  • http://magento.ikantam.com/ Alex

    Will it work for 1.7?