Reset / Delete magento category / products by SQL for magento version 1.7


I was writing a bulk category/product import script for magento 1.7. For debugging I had to frequently clear all the erroneous data inserted while coding and run my script again and again. Searching for an easy solution I came into this magento wiki that has the queries to delete all the products and categories for Magento 1.5 and 1.6. In 1.7 the attribute ids are not same so I needed an updated one. So here is the queries for 1.7 to reset your product / category tables to the initial position just after fresh magento installation.

Please note the first and last sql commands that are used to ignore the foreign key constraints. Without these 2 lines, you will get error.

UPDATE: As everybody was complaining and at last “camulatz” noted the issue that the ' was being converted by wordpress. I have updated the SQLs and should be working now.

SET FOREIGN_KEY_CHECKS = 0;

TRUNCATE TABLE `catalog_product_bundle_option`;
TRUNCATE TABLE `catalog_product_bundle_option_value`;
TRUNCATE TABLE `catalog_product_bundle_selection`;
TRUNCATE TABLE `catalog_product_entity_datetime`;
TRUNCATE TABLE `catalog_product_entity_decimal`;
TRUNCATE TABLE `catalog_product_entity_gallery`;
TRUNCATE TABLE `catalog_product_entity_int`;
TRUNCATE TABLE `catalog_product_entity_media_gallery`;
TRUNCATE TABLE `catalog_product_entity_media_gallery_value`;
TRUNCATE TABLE `catalog_product_entity_text`;
TRUNCATE TABLE `catalog_product_entity_tier_price`;
TRUNCATE TABLE `catalog_product_entity_varchar`;
TRUNCATE TABLE `catalog_product_link`;
TRUNCATE TABLE `catalog_product_link_attribute`;
TRUNCATE TABLE `catalog_product_link_attribute_decimal`;
TRUNCATE TABLE `catalog_product_link_attribute_int`;
TRUNCATE TABLE `catalog_product_link_attribute_varchar`;
TRUNCATE TABLE `catalog_product_link_type`;
TRUNCATE TABLE `catalog_product_option`;
TRUNCATE TABLE `catalog_product_option_price`;
TRUNCATE TABLE `catalog_product_option_title`;
TRUNCATE TABLE `catalog_product_option_type_price`;
TRUNCATE TABLE `catalog_product_option_type_title`;
TRUNCATE TABLE `catalog_product_option_type_value`;
TRUNCATE TABLE `catalog_product_super_attribute`;
TRUNCATE TABLE `catalog_product_super_attribute_label`;
TRUNCATE TABLE `catalog_product_super_attribute_pricing`;
TRUNCATE TABLE `catalog_product_super_link`;
TRUNCATE TABLE `catalog_product_enabled_index`;
TRUNCATE TABLE `catalog_product_website`;
TRUNCATE TABLE `catalog_product_entity`;

TRUNCATE TABLE `cataloginventory_stock`;
TRUNCATE TABLE `cataloginventory_stock_item`;
TRUNCATE TABLE `cataloginventory_stock_status`;

TRUNCATE TABLE `catalog_category_entity`;
TRUNCATE TABLE `catalog_category_entity_datetime`;
TRUNCATE TABLE `catalog_category_entity_decimal`;
TRUNCATE TABLE `catalog_category_entity_int`;
TRUNCATE TABLE `catalog_category_entity_text`;
TRUNCATE TABLE `catalog_category_entity_varchar`;
TRUNCATE TABLE `catalog_category_product`;
TRUNCATE TABLE `catalog_category_product_index`;

INSERT INTO `catalog_category_entity` (`entity_id`, `entity_type_id`, `attribute_set_id`, `parent_id`, `created_at`, `updated_at`, `path`, `position`, `level`, `children_count`) VALUES (1, 3, 0, 0, '2012-06-17 22:20:47', '2012-06-17 22:20:47', '1', 0, 0, 1), (2, 3, 3, 1, '2012-06-17 22:20:47', '2012-06-17 22:20:47', '1/2', 1, 1, 0);
INSERT INTO `catalog_category_entity_int` (`value_id`, `entity_type_id`, `attribute_id`, `store_id`, `entity_id`, `value`) VALUES (1, 3, 67, 0, 1, 1), (2, 3, 67, 1, 1, 1), (3, 3, 42, 0, 2, 1), (4, 3, 67, 0, 2, 1), (5, 3, 42, 1, 2, 1), (6, 3, 67, 1, 2, 1);
INSERT INTO `catalog_category_entity_varchar` (`value_id`, `entity_type_id`, `attribute_id`, `store_id`, `entity_id`, `value`) VALUES (1, 3, 41, 0, 1, 'Root Catalog'), (2, 3, 41, 1, 1, 'Root Catalog'), (3, 3, 43, 1, 1, 'root-catalog'), (4, 3, 41, 0, 2, 'Default Category'), (5, 3, 41, 1, 2, 'Default Category'), (6, 3, 49, 1, 2, 'PRODUCTS'), (7, 3, 43, 1, 2, 'default-category');

INSERT INTO `catalog_product_link_type` (`link_type_id`, `code`) VALUES (1, 'relation'), (3, 'super'), (4, 'up_sell'), (5, 'cross_sell');
INSERT INTO `catalog_product_link_attribute` (`product_link_attribute_id`, `link_type_id`, `product_link_attribute_code`, `data_type`) VALUES (1, 1, 'position', 'int'), (2, 3, 'position', 'int'), (3, 3, 'qty', 'decimal'), (4, 4, 'position', 'int'), (5, 5, 'position', 'int');
INSERT INTO `cataloginventory_stock` (`stock_id`, `stock_name`) VALUES (1, 'Default');

SET FOREIGN_KEY_CHECKS = 1;

,

  1. #1 by David Radovanovic on July 13, 2012 - 11:28 pm

    Thanks for sharing. However, you may want to fix the makup a bit.

  2. #2 by Kevin on August 7, 2012 - 1:32 am

    Thanks, being a newb though this does nothing to explain to me what I need to do with this information. On top of that I now have questions from what David said but his reply does nothing for anyone except ask more questions. Why reply to a thread where all you do is try and look smart with nothing to back it up is beyond me but worthless non the less!

    Also please use a different Captcha, being color blind I had to ask for help because I do not see anything here. Very user unfriendly!

  3. #3 by Kevin on August 10, 2012 - 6:12 am

    I am so sick and tired of people writing blogs and then not supporting what they write. Of course the reply from David is no better since he has no comment either (typical New Yorker though)…

  4. #4 by karan on September 8, 2012 - 5:53 pm

    idiot first chk the code at your end than post to help other, it has make my db empty. posted fake query ass hole.

  5. #5 by Imran on September 27, 2012 - 5:59 pm

    First of all, sorry for replying late. I am not that much privileged to spend long time on my blog.. anyway
    @David Radovanovic – What did you mean by “Fixing the makeup” – u meant the post layout / style, right?
    @Kevin – If you do not know even how to run mysql queries, please do not dare to work on Magento modification. These are all mysql queries that resets your magento database to its initial position. There is nothing to explain. This post is written for a very specific purpose.
    @Karan – Now listen “Idiot” – this is the post that makes the database empty, and it is written in big words on the title. So next time when you try anyones’ code, put something hard in your “ass hole”.

  6. #6 by camulatz on September 28, 2012 - 2:13 am

    Hi Imran, I made a correction :

    INSERT INTO `catalog_category_entity` (`entity_id`, `entity_type_id`, `attribute_set_id`, `parent_id`, `created_at`, `updated_at`, `path`, `position`, `level`, `children_count`) VALUES (1, 3, 0, 0, ’2012-06-17 22:20:47′, ’2012-06-17 22:20:47′, ’1′, 0, 0, 1), (2, 3, 3, 1, ’2012-06-17 22:20:47′, ’2012-06-17 22:20:47′, ’1/2′, 1, 1, 0);
    INSERT INTO `catalog_category_entity_int` (`value_id`, `entity_type_id`, `attribute_id`, `store_id`, `entity_id`, `value`) VALUES (1, 3, 67, 0, 1, 1), (2, 3, 67, 1, 1, 1), (3, 3, 42, 0, 2, 1), (4, 3, 67, 0, 2, 1), (5, 3, 42, 1, 2, 1), (6, 3, 67, 1, 2, 1);
    INSERT INTO `catalog_category_entity_varchar` (`value_id`, `entity_type_id`, `attribute_id`, `store_id`, `entity_id`, `value`) VALUES (1, 3, 41, 0, 1, ‘Root Catalog’), (2, 3, 41, 1, 1, ‘Root Catalog’), (3, 3, 43, 1, 1, ‘root-catalog’), (4, 3, 41, 0, 2, ‘Default Category’), (5, 3, 41, 1, 2, ‘Default Category’), (6, 3, 49, 1, 2, ‘PRODUCTS’), (7, 3, 43, 1, 2, ‘default-category’);
    INSERT INTO `catalog_product_link_type` (`link_type_id`, `code`) VALUES (1, ‘relation’), (3, ‘super’), (4, ‘up_sell’), (5, ‘cross_sell’);
    INSERT INTO `catalog_product_link_attribute` (`product_link_attribute_id`, `link_type_id`, `product_link_attribute_code`, `data_type`) VALUES (1, 1, ‘position’, ‘int’), (2, 3, ‘position’, ‘int’), (3, 3, ‘qty’, ‘decimal’), (4, 4, ‘position’, ‘int’), (5, 5, ‘position’, ‘int’);
    INSERT INTO `cataloginventory_stock` (`stock_id`, `stock_name`) VALUES (1, ‘Default’);

    Now works for me. Thank you.

    F.

  7. #7 by camulatz on September 28, 2012 - 2:14 am

    Hi Imran, I made a correction :

    INSERT INTO `catalog_category_entity` (`entity_id`, `entity_type_id`, `attribute_set_id`, `parent_id`, `created_at`, `updated_at`, `path`, `position`, `level`, `children_count`) VALUES (1, 3, 0, 0, \’2012-06-17 22:20:47\’, \’2012-06-17 22:20:47\’, \’1\’, 0, 0, 1), (2, 3, 3, 1, \’2012-06-17 22:20:47\’, \’2012-06-17 22:20:47\’, \’1/2\’, 1, 1, 0);
    INSERT INTO `catalog_category_entity_int` (`value_id`, `entity_type_id`, `attribute_id`, `store_id`, `entity_id`, `value`) VALUES (1, 3, 67, 0, 1, 1), (2, 3, 67, 1, 1, 1), (3, 3, 42, 0, 2, 1), (4, 3, 67, 0, 2, 1), (5, 3, 42, 1, 2, 1), (6, 3, 67, 1, 2, 1);
    INSERT INTO `catalog_category_entity_varchar` (`value_id`, `entity_type_id`, `attribute_id`, `store_id`, `entity_id`, `value`) VALUES (1, 3, 41, 0, 1, \’Root Catalog\’), (2, 3, 41, 1, 1, \’Root Catalog\’), (3, 3, 43, 1, 1, \’root-catalog\’), (4, 3, 41, 0, 2, \’Default Category\’), (5, 3, 41, 1, 2, \’Default Category\’), (6, 3, 49, 1, 2, \’PRODUCTS\’), (7, 3, 43, 1, 2, \’default-category\’);
    INSERT INTO `catalog_product_link_type` (`link_type_id`, `code`) VALUES (1, \’relation\’), (3, \’super\’), (4, \’up_sell\’), (5, \’cross_sell\’);
    INSERT INTO `catalog_product_link_attribute` (`product_link_attribute_id`, `link_type_id`, `product_link_attribute_code`, `data_type`) VALUES (1, 1, \’position\’, \’int\’), (2, 3, \’position\’, \’int\’), (3, 3, \’qty\’, \’decimal\’), (4, 4, \’position\’, \’int\’), (5, 5, \’position\’, \’int\’);
    INSERT INTO `cataloginventory_stock` (`stock_id`, `stock_name`) VALUES (1, \’Default\’);

    Now works for me. Thank you.

    F.

  8. #8 by camulatz on September 28, 2012 - 2:20 am

    … without the “\” ( not in the code and inserted automatically by wp )

  9. #9 by camulatz on September 28, 2012 - 2:42 am

    … sorry for double posting

  10. #10 by camulatz on September 28, 2012 - 2:52 am

    … this is my last comment.

    I have understood that wp transform the right code pasted in ( as in my posts and in your original code ) !!

    The ‘ is transformed in ’ and queries fail !!

  11. #11 by Imran on October 1, 2012 - 5:55 pm

    @camulatz – Thanks a lot for pointing out this.. Updated the post

  12. #12 by and on October 19, 2012 - 4:14 pm

    many thanks

  13. #13 by MageMojo Magento Hosting on October 24, 2012 - 12:28 am

    Works great! We were running into issues where we’d truncate then try loading the products would just hang eventually throwing an integrity constraint error. But your code for 1.7 works. Thanks!

  14. #14 by Matthijs on November 9, 2012 - 11:58 pm

    Hi,

    After running the SQL commands, “Product Flat Data” and the “Category Products” need indexing.
    But now when I want to index the site, it says”Cannot initialize the indexer process.”
    Any ideas?

    Cheers,
    Matthijs

  15. #15 by Imran on November 10, 2012 - 12:45 am

    Hi Mathijs,

    When you ran the SQLs did all the sqls executed without any error? What I feel is there was some error in the mysql commands and not all the tables were truncated properly. This error occurs because of a foreign key constraints. What I would suggest you is, try running all the queries and make sure all of the queries are executed without any error.

    If you still find the same issue, I have seen couple of similar posts like the one below:

    http://www.magpleasure.com/blog/magento-cant-initialize-indexer-process.html

  16. #16 by Matthijs on November 29, 2012 - 1:08 am

    Here are the results:

    SET FOREIGN_KEY_CHECKS = 0;
    TRUNCATE TABLE `catalog_product_bundle_option`;
TRUNCATE TABLE `catalog_product_bundle_option_value`;
TRUNCATE TABLE `catalog_product_bundle_selection`;
TRUNCATE TABLE `catalog_product_entity_datetime`;
TRUNCATE TABLE `catalog_product_entity_decimal`;
TRUNCATE TABLE `catalog_product_entity_gallery`;
TRUNCATE TABLE `catalog_product_entity_int`;
TRUNCATE TABLE `catalog_product_entity_media_gallery`;
TRUNCATE TABLE `catalog_product_entity_media_gallery_value`;
TRUNCATE TABLE `catalog_product_entity_text`;
TRUNCATE TABLE `catalog_product_entity_tier_price`;
TRUNCATE TABLE `catalog_product_entity_varchar`;
TRUNCATE TABLE `catalog_product_link`;
TRUNCATE TABLE `catalog_product_link_attribute`;
TRUNCATE TABLE `catalog_product_link_attribute_decimal`;
TRUNCATE TABLE `catalog_product_link_attribute_int`;
TRUNCATE TABLE `catalog_product_link_attribute_varchar`;
TRUNCATE TABLE `catalog_product_link_type`;
TRUNCATE TABLE `catalog_product_option`;
TRUNCATE TABLE `catalog_product_option_price`;
TRUNCATE TABLE `catalog_product_option_title`;
TRUNCATE TABLE `catalog_product_option_type_price`;
TRUNCATE TABLE `catalog_product_option_type_title`;
TRUNCATE TABLE `catalog_product_option_type_value`;
TRUNCATE TABLE `catalog_product_super_attribute`;
TRUNCATE TABLE `catalog_product_super_attribute_label`;
TRUNCATE TABLE `catalog_product_super_attribute_pricing`;
TRUNCATE TABLE `catalog_product_super_link`;
TRUNCATE TABLE `catalog_product_enabled_index`;
TRUNCATE TABLE `catalog_product_website`;
TRUNCATE TABLE `catalog_product_entity`;
    TRUNCATE TABLE `cataloginventory_stock`;
TRUNCATE TABLE `cataloginventory_stock_item`;
TRUNCATE TABLE `cataloginventory_stock_status`;
    TRUNCATE TABLE `catalog_category_entity`;
TRUNCATE TABLE `catalog_category_entity_datetime`;
TRUNCATE TABLE `catalog_category_entity_decimal`;
TRUNCATE TABLE `catalog_category_entity_int`;
TRUNCATE TABLE `catalog_category_entity_text`;
TRUNCATE TABLE `catalog_category_entity_varchar`;
TRUNCATE TABLE `catalog_category_product`;
TRUNCATE TABLE `catalog_category_product_index`;
    INSERT INTO `catalog_category_entity` (`entity_id`, `entity_type_id`, `attribute_set_id`, `parent_id`, `created_at`, `updated_at`, `path`, `position`, `level`, `children_count`) VALUES (1, 3, 0, 0, ’2012-06-17 22:20:47′, ’2012-06-17 22:20:47′, ’1′, 0, 0, 1), (2, 3, 3, 1, ’2012-06-17 22:20:47′, ’2012-06-17 22:20:47′, ’1/2′, 1, 1, 0);
INSERT INTO `catalog_category_entity_int` (`value_id`, `entity_type_id`, `attribute_id`, `store_id`, `entity_id`, `value`) VALUES (1, 3, 67, 0, 1, 1), (2, 3, 67, 1, 1, 1), (3, 3, 42, 0, 2, 1), (4, 3, 67, 0, 2, 1), (5, 3, 42, 1, 2, 1), (6, 3, 67, 1, 2, 1);
INSERT INTO `catalog_category_entity_varchar` (`value_id`, `entity_type_id`, `attribute_id`, `store_id`, `entity_id`, `value`) VALUES (1, 3, 41, 0, 1, ‘Root Catalog’), (2, 3, 41, 1, 1, ‘Root Catalog’), (3, 3, 43, 1, 1, ‘root-catalog’), (4, 3, 41, 0, 2, ‘Default Category’), (5, 3, 41, 1, 2, ‘Default Category’), (6, 3, 49, 1, 2, ‘PRODUCTS’), (7, 3, 43, 1, 2, ‘default-category’);
    INSERT INTO `catalog_product_link_type` (`link_type_id`, `code`) VALUES (1, ‘relation’), (3, ‘super’), (4, ‘up_sell’), (5, ‘cross_sell’);
INSERT INTO `catalog_product_link_attribute` (`product_link_attribute_id`, `link_type_id`, `product_link_attribute_code`, `data_type`) VALUES (1, 1, ‘position’, ‘int’), (2, 3, ‘position’, ‘int’), (3, 3, ‘qty’, ‘decimal’), (4, 4, ‘position’, ‘int’), (5, 5, ‘position’, ‘int’);
INSERT INTO `cataloginventory_stock` (`stock_id`, `stock_name`) VALUES (1, ‘Default’);
    SET FOREIGN_KEY_CHECKS = 1;

    SET FOREIGN_KEY_CHECKS = 0;# MySQL gaf een lege resultatenset terug (0 rijen).
    TRUNCATE TABLE `catalog_product_bundle_option`;# MySQL gaf een lege resultatenset terug (0 rijen).
    TRUNCATE TABLE `catalog_product_bundle_option_value`;# MySQL gaf een lege resultatenset terug (0 rijen).
    TRUNCATE TABLE `catalog_product_bundle_selection`;# MySQL gaf een lege resultatenset terug (0 rijen).
    TRUNCATE TABLE `catalog_product_entity_datetime`;# MySQL gaf een lege resultatenset terug (0 rijen).
    TRUNCATE TABLE `catalog_product_entity_decimal`;# MySQL gaf een lege resultatenset terug (0 rijen).
    TRUNCATE TABLE `catalog_product_entity_gallery`;# MySQL gaf een lege resultatenset terug (0 rijen).
    TRUNCATE TABLE `catalog_product_entity_int`;# MySQL gaf een lege resultatenset terug (0 rijen).
    TRUNCATE TABLE `catalog_product_entity_media_gallery`;# MySQL gaf een lege resultatenset terug (0 rijen).
    TRUNCATE TABLE `catalog_product_entity_media_gallery_value`;# MySQL gaf een lege resultatenset terug (0 rijen).
    TRUNCATE TABLE `catalog_product_entity_text`;# MySQL gaf een lege resultatenset terug (0 rijen).
    TRUNCATE TABLE `catalog_product_entity_tier_price`;# MySQL gaf een lege resultatenset terug (0 rijen).
    TRUNCATE TABLE `catalog_product_entity_varchar`;# MySQL gaf een lege resultatenset terug (0 rijen).
    TRUNCATE TABLE `catalog_product_link`;# MySQL gaf een lege resultatenset terug (0 rijen).
    TRUNCATE TABLE `catalog_product_link_attribute`;# MySQL gaf een lege resultatenset terug (0 rijen).

    TRUNCATE TABLE `catalog_product_link_attribute_decimal`;# MySQL gaf een lege resultatenset terug (0 rijen).
    TRUNCATE TABLE `catalog_product_link_attribute_int`;# MySQL gaf een lege resultatenset terug (0 rijen).
    TRUNCATE TABLE `catalog_product_link_attribute_varchar`;# MySQL gaf een lege resultatenset terug (0 rijen).
    TRUNCATE TABLE `catalog_product_link_type`;# MySQL gaf een lege resultatenset terug (0 rijen).
    TRUNCATE TABLE `catalog_product_option`;# MySQL gaf een lege resultatenset terug (0 rijen).
    TRUNCATE TABLE `catalog_product_option_price`;# MySQL gaf een lege resultatenset terug (0 rijen).
    TRUNCATE TABLE `catalog_product_option_title`;# MySQL gaf een lege resultatenset terug (0 rijen).
    TRUNCATE TABLE `catalog_product_option_type_price`;# MySQL gaf een lege resultatenset terug (0 rijen).
    TRUNCATE TABLE `catalog_product_option_type_title`;# MySQL gaf een lege resultatenset terug (0 rijen).
    TRUNCATE TABLE `catalog_product_option_type_value`;# MySQL gaf een lege resultatenset terug (0 rijen).
    TRUNCATE TABLE `catalog_product_super_attribute`;# MySQL gaf een lege resultatenset terug (0 rijen).
    TRUNCATE TABLE `catalog_product_super_attribute_label`;# MySQL gaf een lege resultatenset terug (0 rijen).
    TRUNCATE TABLE `catalog_product_super_attribute_pricing`;# MySQL gaf een lege resultatenset terug (0 rijen).
    TRUNCATE TABLE `catalog_product_super_link`;# MySQL gaf een lege resultatenset terug (0 rijen).
    TRUNCATE TABLE `catalog_product_enabled_index`;# MySQL gaf een lege resultatenset terug (0 rijen).
    TRUNCATE TABLE `catalog_product_website`;# MySQL gaf een lege resultatenset terug (0 rijen).
    TRUNCATE TABLE `catalog_product_entity`;# MySQL gaf een lege resultatenset terug (0 rijen).
    TRUNCATE TABLE `cataloginventory_stock`;# MySQL gaf een lege resultatenset terug (0 rijen).
    TRUNCATE TABLE `cataloginventory_stock_item`;# MySQL gaf een lege resultatenset terug (0 rijen).
    TRUNCATE TABLE `cataloginventory_stock_status`;# MySQL gaf een lege resultatenset terug (0 rijen).
    TRUNCATE TABLE `catalog_category_entity`;# MySQL gaf een lege resultatenset terug (0 rijen).
    TRUNCATE TABLE `catalog_category_entity_datetime`;# MySQL gaf een lege resultatenset terug (0 rijen).
    TRUNCATE TABLE `catalog_category_entity_decimal`;# MySQL gaf een lege resultatenset terug (0 rijen).
    TRUNCATE TABLE `catalog_category_entity_int`;# MySQL gaf een lege resultatenset terug (0 rijen).
    TRUNCATE TABLE `catalog_category_entity_text`;# MySQL gaf een lege resultatenset terug (0 rijen).
    TRUNCATE TABLE `catalog_category_entity_varchar`;# MySQL gaf een lege resultatenset terug (0 rijen).
    TRUNCATE TABLE `catalog_category_product`;# MySQL gaf een lege resultatenset terug (0 rijen).
    TRUNCATE TABLE `catalog_category_product_index`;# MySQL gaf een lege resultatenset terug (0 rijen).

    INSERT INTO `catalog_category_entity` (`entity_id`, `entity_type_id`, `attribute_set_id`, `parent_id`, `created_at`, `updated_at`, `path`, `position`, `level`, `children_count`) VALUES (1, 3, 0, 0, ’2012-06-17 22:20:47′, ’2012-06-17 22:20:47′, ’1′, 0, 0, 1), (2, 3, 3, 1, ’2012-06-17 22:20:47′, ’2012-06-17 22:20:47′, ’1/2′, 1, 1, 0);# 2 rijen bijgewerkt.

    INSERT INTO `catalog_category_entity_int` (`value_id`, `entity_type_id`, `attribute_id`, `store_id`, `entity_id`, `value`) VALUES (1, 3, 67, 0, 1, 1), (2, 3, 67, 1, 1, 1), (3, 3, 42, 0, 2, 1), (4, 3, 67, 0, 2, 1), (5, 3, 42, 1, 2, 1), (6, 3, 67, 1, 2, 1);# 6 rijen bijgewerkt.

    INSERT INTO `catalog_category_entity_varchar` (`value_id`, `entity_type_id`, `attribute_id`, `store_id`, `entity_id`, `value`) VALUES (1, 3, 41, 0, 1, ‘Root Catalog’), (2, 3, 41, 1, 1, ‘Root Catalog’), (3, 3, 43, 1, 1, ‘root-catalog’), (4, 3, 41, 0, 2, ‘Default Category’), (5, 3, 41, 1, 2, ‘Default Category’), (6, 3, 49, 1, 2, ‘PRODUCTS’), (7, 3, 43, 1, 2, ‘default-category’);# 7 rijen bijgewerkt.

    INSERT INTO `catalog_product_link_type` (`link_type_id`, `code`) VALUES (1, ‘relation’), (3, ‘super’), (4, ‘up_sell’), (5, ‘cross_sell’);# 4 rijen bijgewerkt.

    INSERT INTO `catalog_product_link_attribute` (`product_link_attribute_id`, `link_type_id`, `product_link_attribute_code`, `data_type`) VALUES (1, 1, ‘position’, ‘int’), (2, 3, ‘position’, ‘int’), (3, 3, ‘qty’, ‘decimal’), (4, 4, ‘position’, ‘int’), (5, 5, ‘position’, ‘int’);# 5 rijen bijgewerkt.

    INSERT INTO `cataloginventory_stock` (`stock_id`, `stock_name`) VALUES (1, ‘Default’);# 1 rij bijgewerkt.

    SET FOREIGN_KEY_CHECKS = 1;# MySQL gaf een lege resultatenset terug (0 rijen).

  17. #17 by PureLinen on January 19, 2013 - 10:38 am

    Using Magmi Importer with Magento 1.7.0.2 .. I hit index violations with grouped products after running the script above … needed this table truncated >>> catalog_product_relation

    TRUNCATE TABLE `catalog_product_relation`;

  18. #18 by Sorcy on February 20, 2013 - 5:41 pm

    Hi,

    Just tried this on 1.7.0.2.
    Everything worked fine, but when I tried to rebuil product flat index, I ended with this error :

    SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails

    If you are in this particular case, and have this error too, the solution is to login to your phpmyadmin and then delete every product_flat tables.

    Theses tables are named “catalog_product_flat_X” (where X is a incrementing number)

    Just DROP these catalog_product_flat_X tables and rebuild your index, it should be better now.

    Thank you for the script too btw.

    Sorcy

  19. #19 by Rupesh on April 10, 2013 - 12:19 pm

    @Imran .. Thanks for this post. Really helps on Magento1.7 ..

    @Imran and @Scorcy .. This whole query set has one one or more catalog_product_flat_X table(s) missing. Add these too and it would be perfect.

  20. #20 by Justin on August 6, 2013 - 10:30 am

    Also need to truncate the table catalog_product_entity_group_price
    or you will get reindexing errors with the price indexer.

  21. #21 by Visvanathan on August 21, 2013 - 11:36 am

    @imran.. thanks for this post i used ur queries fine but i had re index problem “Product Flat Data” how to overcome this problem plz help

  22. #22 by Imran on August 21, 2013 - 1:29 pm

    @Visvanathan,

    I think “Rupesh” already mentioned that:

    This whole query set has one one or more catalog_product_flat_X table(s) missing.

    Please run following queries:

    TRUNCATE TABLE catalog_product_flat_X

    X would be 1, 2, 3 etc please check your database to know how many of them there.

    Thanks

  23. #23 by camulatz on November 27, 2013 - 6:29 pm

    Hi,

    Just tried this on 1.8.0.0.

    After, in Magento ->Admin Panel ->System->Index Management cannot reindex:

    /*Table: catalog_category_product_index*/

  24. #24 by camulatz on November 27, 2013 - 6:52 pm

    camulatz :
    Hi,
    Just tried this on 1.8.0.0.
    After, in Magento ->Admin Panel ->System->Index Management cannot reindex:
    /*Table: catalog_category_product_index*/

    Investigating.

  25. #25 by Andrej on December 28, 2013 - 3:06 am

    I have a problem.

    On these command:

    INSERT INTO `catalog_category_entity_varchar` (`value_id`, `entity_type_id`, `attribute_id`, `store_id`, `entity_id`, `value`) VALUES (1, 3, 41, 0, 1, ‘Root Catalog’), (2, 3, 41, 1, 1, ‘Root Catalog’), (3, 3, 43, 1, 1, ‘root-catalog’), (4, 3, 41, 0, 2, ‘Default Category’), (5, 3, 41, 1, 2, ‘Default Category’), (6, 3, 49, 1, 2, ‘PRODUCTS’), (7, 3, 43, 1, 2, ‘default-category’);

    I got these error:

    #1452 – Cannot add or update a child row: a foreign key constraint fails (`adbook_magento`.`catalog_category_entity_varchar`, CONSTRAINT `FK_CAT_CTGR_ENTT_VCHR_ATTR_ID_EAV_ATTR_ATTR_ID` FOREIGN KEY (`attribute_id`) REFERENCES `eav_attribute` (`attribute_id`) ON DELE)

    Any suggestion?

    Thank you!

  26. #26 by Eddy on January 28, 2014 - 12:28 pm

    Many thanks, this is a working script (MAG 1.8.1).
    :-)

  27. #27 by brettser on April 5, 2014 - 3:12 am

    thanks for this, appreciate it!

(will not be published)


one + 7 =