Posting in the Magento forums has been disabled pending the implementation of a new and improved forum solution which should better serve the community.

For new questions please post at magento.stackexchange.com, the community-run support site for the Magento community. We will be providing updates on the new forum solution soon. For questions or concerns please email community@magento.com.

Magento Forum

Page 1 of 5
Possible to delete all products and reset product id’s? 
 
Shpigford
Sr. Member
 
Avatar
Total Posts:  171
Joined:  2007-09-03
Denver, CO
 

When I did a big product import, some items ended up with the same product id within Magento.

Is it possible for me to just delete all products and reset the product id count?

I would just re-install the whole database but we’ve already input hundreds of categories.

 
Magento Community Magento Community
Magento Community
Magento Community
 
Shpigford
Sr. Member
 
Avatar
Total Posts:  171
Joined:  2007-09-03
Denver, CO
 

Anybody care to offer some insight now? Magento devs?

 
Magento Community Magento Community
Magento Community
Magento Community
 
no.mongo
Jr. Member
 
Avatar
Total Posts:  2
Joined:  2008-12-18
 

Nice one hmpierson!

Run the SQL query
TRUNCATE TABLE `catalog_product_entity`

Happy days

 
Magento Community Magento Community
Magento Community
Magento Community
 
Mike Smullin
Jr. Member
 
Avatar
Total Posts:  23
Joined:  2008-10-25
Eagle Mountain, Utah
 

er, not quite.

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

insert  into `catalog_product_link_type`(`link_type_id`,`code`) values (1,'relation'),(2,'bundle'),(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,2,'qty','decimal'),(2,1,'position','int'),(3,4,'position','int'),(4,5,'position','int'),(6,1,'qty','decimal'),(7,3,'position','int'),(8,3,'qty','decimal');
insert  into `cataloginventory_stock`(`stock_id`,`stock_name`) values (1,'Default');

Probably should also clear/refresh/disable cache from System > Cache Management. I also like to delete everything under my ./var directory as well just to be doubly sure its all clear.

If you forget to truncate the other tables, or if you forget to restore the default values afterward, you’ll have foreign key issues later.

see also: Beautiful up-to-date PDF Database Diagram
http://www.magentocommerce.com/wiki/development/magento_database_diagram

 
Magento Community Magento Community
Magento Community
Magento Community
 
Mike Smullin
Jr. Member
 
Avatar
Total Posts:  23
Joined:  2008-10-25
Eagle Mountain, Utah
 

and here’s how to truncate all magento categories:

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,'0000-00-00 00:00:00','2009-02-20 00:25:34','1',1,0,1),(2,3,3,0,'2009-02-20 00:25:34','2009-02-20 00:25:34','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,32,0,2,1),(2,3,32,1,2,1);
insert  into `catalog_category_entity_varchar`(`value_id`,`entity_type_id`,`attribute_id`,`store_id`,`entity_id`,`value`) values (1,3,31,0,1,'Root Catalog'),(2,3,33,0,1,'root-catalog'),(3,3,31,0,2,'Default Category'),(4,3,39,0,2,'PRODUCTS'),(5,3,33,0,2,'default-category');
 
Magento Community Magento Community
Magento Community
Magento Community
 
B00MER
Sr. Member
 
Avatar
Total Posts:  130
Joined:  2007-12-27
DFW, TX
 

Awesome find Mike Smullin!  I was able to clear out my entire import that got fouled up, Since I’m on a development server right now Magento’s admin would simply bomb out on multiple delete’s of products.  Now I can get back to Re-Importing.  And your query didn’t even clear out my product attributes.  Is it Okie if I repost these queries on my personal blog (with credit of course!) ?

Thanks again, this saved me a big headache!

 
Magento Community Magento Community
Magento Community
Magento Community
 
jmlee
Member
 
Total Posts:  53
Joined:  2008-06-23
 

Great find, just want i was looking for after i have screwed up a test database with the Import tool.

This is off-topic, but just curious has anybody had problem with the Import utility working on the local development system, but having it not work in a hosted production server environment?

Again, thanks Mike for the wonderful tip!

 
Magento Community Magento Community
Magento Community
Magento Community
 
B00MER
Sr. Member
 
Avatar
Total Posts:  130
Joined:  2007-12-27
DFW, TX
 
jmlee - 11 March 2009 02:09 PM

Great find, just want i was looking for after i have screwed up a test database with the Import tool.

This is off-topic, but just curious has anybody had problem with the Import utility working on the local development system, but having it not work in a hosted production server environment?

Again, thanks Mike for the wonderful tip!

Compare your local dev environment to production particularly php.ini and my.cnf same hosting environments? or one unix and other win32?

 
Magento Community Magento Community
Magento Community
Magento Community
 
jesse_dev
Jr. Member
 
Total Posts:  27
Joined:  2009-03-03
 

here’s a script i wrote to help track down all the tables affected when you delete a product.
I started with a script that spits out the count of every table, then I deleted a product, then I ran the script again to get the new counts of all the tables. I took the 2 outputs and did a diff and tracked everything down. I’m pretty sure I covered everything.
basically there are 2 main foreign keys: entity_id and product_id.. there’s the one table with rule_product_id too

this should be helpful. you can easily replace SELECT * with DELETE
all the tables affected should be between the 3 arrays

just pass in the product_id into the url:
http://server/script.php?product_id=X

Magento ver. 1.2.1.1

<?php

$mysql_conn
=mysql_connect('localhost''root''***') or die("mysql error: couldn't connect");
mysql_select_db("magento") or die("mysql error: couldn't select db");

if (isset(
$_GET['product_id'])) {
    $product_id
=(int) $_GET['product_id'];
else echo "<br>no product id? nothing to do."}

$total_rows
=0;

$tables=array();
$tables[]="catalog_product_bundle_option";
$tables[]="catalog_product_bundle_option_value";
$tables[]="catalog_product_bundle_selection";
$tables[]="catalog_product_entity_datetime";
$tables[]="catalog_product_entity_decimal";
$tables[]="catalog_product_entity_gallery";
$tables[]="catalog_product_entity_int";
$tables[]="catalog_product_entity_media_gallery";
$tables[]="catalog_product_entity_media_gallery_value";
$tables[]="catalog_product_entity_text";
$tables[]="catalog_product_entity_tier_price";
$tables[]="catalog_product_entity_varchar";
$tables[]="catalog_product_entity";
$tables[]="catalog_product_link";
$tables[]="catalog_product_link_attribute";
$tables[]="catalog_product_link_attribute_decimal";
$tables[]="catalog_product_link_attribute_int";
$tables[]="catalog_product_link_attribute_varchar";
$tables[]="catalog_product_link_type";
$tables[]="catalog_product_option";
$tables[]="catalog_product_option_price";
$tables[]="catalog_product_option_title";
$tables[]="catalog_product_option_type_price";
$tables[]="catalog_product_option_type_title";
$tables[]="catalog_product_option_type_value";
$tables[]="catalog_product_super_attribute";
$tables[]="catalog_product_super_attribute_label";
$tables[]="catalog_product_super_attribute_pricing";
$tables[]="catalog_product_super_link";
$tables[]="catalogindex_eav";
$tables[]="catalogindex_price";
$tables[]="eav_entity_datetime";
$tables[]="eav_entity_decimal";
$tables[]="eav_entity_int";
$tables[]="eav_entity_text";
$tables[]="eav_entity_varchar";

foreach(
$tables as $table{
$table_count
=0;
$query="select * from `$table` where `entity_id`=$product_id";
$rs=mysql_query($query$mysql_conn);
$num_rows= @ mysql_num_rows($rs);
if (
$num_rows 0{
    
echo "<br><br>**data in $table";
    while (
$data=mysql_fetch_assoc($rs)) {
        $total_rows
++;
        
$table_count++;
        echo 
"<br>";
        foreach(
$data as $k=>$v
            
$$k=$v;
            echo 
"<br>$k=$v";
        
}
    }
    
echo "<br>rows: $table_count";
}
}

$tables2
=array();
$tables2[]="catalog_category_product";
$tables2[]="catalog_category_product_index";
$tables2[]="catalogsearch_result";
$tables2[]="catalogsearch_fulltext";
$tables2[]="catalogrule_affected_product";
$tables2[]="catalogrule_product_price";
$tables2[]="product_alert_price";
$tables2[]="product_alert_stock";
$tables2[]="catalog_product_website";
$tables2[]="catalog_product_enabled_index";
$tables2[]="core_url_rewrite";
$tables2[]="cataloginventory_stock_item";
$tables2[]="cataloginventory_stock_status";

foreach(
$tables2 as $table{
$table_count
=0;
$query="select * from `$table` where `product_id`=$product_id";
$rs=mysql_query($query$mysql_conn);
$num_rows= @ mysql_num_rows($rs);
if (
$num_rows 0{
    
echo "<br><br>**data in $table";
    while (
$data=mysql_fetch_assoc($rs)) {
        $total_rows
++;
        
$table_count++;
        echo 
"<br>";
        foreach(
$data as $k=>$v
            
$$k=$v;
            echo 
"<br>$k=$v";
        
}
    }
    
echo "<br>rows: $table_count";
}
}

$tables3[]
=array();
$tables3['catalogrule_product']="rule_product_id";

foreach(
$tables3 as $table=>$field{
$table_count
=0;
$query="select * from `$table` where `$field`=$product_id";
$rs=mysql_query($query$mysql_conn);
$num_rows= @ mysql_num_rows($rs);
if (
$num_rows 0{
    
echo "<br><br>**data in $table";
    while (
$data=mysql_fetch_assoc($rs)) {
        $total_rows
++;
        
$table_count++;
        echo 
"<br>";
        foreach(
$data as $k=>$v
            
$$k=$v;
            echo 
"<br>$k=$v";
        
}
    }
    
echo "<br>rows: $table_count";
}
}

mysql_close
($mysql_conn);
echo 
"<br><br>TOTAL ROWS: $total_rows";

?>
 
Magento Community Magento Community
Magento Community
Magento Community
 
Nemphys
Member
 
Total Posts:  54
Joined:  2009-01-28
 

In version 1.2.1.2 the following two lines are also needed:

TRUNCATE TABLE `catalog_category_product`;
TRUNCATE TABLE `catalog_category_product_index`;

 
Magento Community Magento Community
Magento Community
Magento Community
 
Pixxa
Sr. Member
 
Total Posts:  275
Joined:  2008-10-23
 

Ive run

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,'0000-00-00 00:00:00','2009-02-20 00:25:34','1',1,0,1),(2,3,3,0,'2009-02-20 00:25:34','2009-02-20 00:25:34','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,32,0,2,1),(2,3,32,1,2,1);
insert  into `catalog_category_entity_varchar`(`value_id`,`entity_type_id`,`attribute_id`,`store_id`,`entity_id`,`value`) values (1,3,31,0,1,'Root Catalog'),(2,3,33,0,1,'root-catalog'),(3,3,31,0,2,'Default Category'),(4,3,39,0,2,'PRODUCTS'),(5,3,33,0,2,'default-category');

My categories are gone indeed but now I have a category ‘Default Category’ which wont turn op one the front end and subcategories neither.

Im on Magento 1.3.2 with Flat Catagog / Products enabled. Run every cache refresh and deleted all the files in /var/chache and /var/session - None helped

What to do?

FORGET ABOVE:

Forget to set my Root Category in my Store view.

in the admin panel goto
System->Manage Store
click “Main Website Store” and check that “Root Category” is set to your default category.

 
Magento Community Magento Community
Magento Community
Magento Community
 
bradartigue
Jr. Member
 
Total Posts:  3
Joined:  2009-06-08
 

I have an issue with the inserts I’m trying to sort through…

The 2nd category insert statement fails with:

Cannot add or update a child row: a foreign key constraint fails (`magento`.`catalog_category_entity_int`, CONSTRAINT `FK_CATALOG_CATEGORY_EMTITY_INT_ATTRIBUTE` FOREIGN KEY (`attribute_id`) REFERENCES `eav_attribute` (`attribute_id`) ON DELETE CASCADE ON UPD)

Thanks!

 
Magento Community Magento Community
Magento Community
Magento Community
 
perandre
Jr. Member
 
Total Posts:  30
Joined:  2009-02-06
 

Thanks for the “delete all products"-trick; worked fine! i kept getting execution time errors when deleting, so this really helped.

 
Magento Community Magento Community
Magento Community
Magento Community
 
loeffel
Guru
 
Avatar
Total Posts:  427
Joined:  2009-02-03
 

This is likely to be a stupid question, but if I delete all products and reimport my database backup, will the products id still be resetted? My problem is that my product ids start at 17 and I would like them to start at 1 again but I dont want to have to manually insert those products. Thanks!

 
Magento Community Magento Community
Magento Community
Magento Community
 
Sam Figueroa
Jr. Member
 
Avatar
Total Posts:  21
Joined:  2009-02-08
 

I think while this will be a whole lot slower it is more future-version-proof-ish since it works against the API.
Works for me.

function deleteAllProducts()
  
{
    $products 
Mage::getResourceModel('catalog/product_collection')->setStoreId(STORE_ID)->getAllIds();
    if(
is_array($products))
    
{
      
foreach ($products as $key => $productId)
      
{
        
try {
          $product 
Mage::getSingleton('catalog/product')->load($productId);
          
Mage::dispatchEvent('catalog_controller_product_delete', array('product' => $product));
          
$product->delete();          
        
catch (Exception $e{
          
echo "<br/>Can't delete product w/ id: $productId";
        
}
      } 
    }
  }
 
Magento Community Magento Community
Magento Community
Magento Community
 
Sam Figueroa
Jr. Member
 
Avatar
Total Posts:  21
Joined:  2009-02-08
 

double post OP plz delete this.

 
Magento Community Magento Community
Magento Community
Magento Community
Magento Community
Magento Community
Back to top
Page 1 of 5