Elias Interactive

We Grow Beautiful Websites

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

· · 101 Comments

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). On the other hand, techniques such as card counting had been famous in almost every business transactions.

Discover Holidays API, this API was created by HolidayData, the office Holidays API end point is located at holidaydata.io, you can find the HolidayData portal i this link.

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

…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!

Reader Interactions

Comments

  2. Marty says

    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.

    Reply

  4. Marty says

    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.

    Reply

  5. timothyleetaylor says

    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

    Reply

  6. timothyleetaylor says

    @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

    Reply

  7. Jesse says

    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.

    Reply

  8. timothyleetaylor says

    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

    Reply

  10. Yaani says

    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!

    Reply

  12. Yaani says

    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

    Reply

  13. timothyleetaylor says

    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

    Reply

  14. Yaani says

    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

    Reply

  15. yaani says

    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!

    Reply

  18. David says

    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?

    Reply

  20. David says

    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

    Reply

  21. David says

    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

    Reply

  22. timothyleetaylor says

    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

    Reply

  23. Boaz says

    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

    Reply

  24. timothyleetaylor says

    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

    Reply

  25. Boaz says

    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

    Reply

  26. timothyleetaylor says

    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

    Reply

  27. Boaz says

    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

    Reply

  28. timothyleetaylor says

    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

    Reply

  30. timothyleetaylor says

    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

    Reply

  31. benz says

    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

    Reply

  32. timothyleetaylor says

    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

    Reply

  34. david says

    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.

    Reply

  35. Boaz says

    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

    Reply

  36. Don says

    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!!!!

    Reply

  37. timothyleetaylor says

    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

    Reply

  38. timothyleetaylor says

    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

    Reply

  39. Boaz says

    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

    Reply

  42. iTeam - Magento design says

    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?

    Reply

  44. timothyleetaylor says

    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

    Reply

  46. Stewart says

    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!

    Reply

  47. timothyleetaylor says

    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

    Reply

  48. Stewart says

    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]?

    Reply

  50. Natalie says

    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.

    Reply

  51. Rune says

    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 🙂

    Reply

  52. Rune says

    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 🙂

    Reply

  53. timothyleetaylor says

    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

    Reply

  54. timothyleetaylor says

    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!

    Reply

  55. natalie says

    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

    Reply

  59. david says

    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??

    Reply

  61. Shinobi says

    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?

    Reply

  62. Shinobi says

    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!

    Reply

  63. Mosses Akizian says

    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.

    Reply

  64. Gabriel Zamprogna says

    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.

    Reply

  65. Brady says

    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

    Reply

  66. Ratna says

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

    Reply

  67. evuska says

    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!

    Reply

  68. evuska says

    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

    Reply

  71. ch3ckmat3 says

    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

    Reply

  72. Rich Y says

    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

    Reply

  73. Lee Taylor says

    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

    Reply

  74. bill S says

    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.

    Reply

  76. Lee Taylor says

    @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

    Reply

  78. Rob says

    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?

    Reply

  80. Ramon de la Fuente says

    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!

    Reply

  86. Dhiren says

    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

    Reply

  87. Dhiren says

    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

    Reply

  88. Dhiren says

    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

    Reply

  89. Inventory software says

    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.

    Reply

  91. Benstrowger says

    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

    Reply

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.