Deleting records from ac_Products
Posted: Mon Sep 13, 2010 10:34 am
Just an FYI of my experience this morning....gotta love Mondays.
I had a bunch of products in ac_Products that were marked as private - all discontinued items automatically marked as my distributor feeds update the catalog.
No biggie I thought, I'll just do a DELETE FROM AC_Products WHERE blah blah blah and nuke them all in one fell swoop.
Well it failed the first time, complaining about a foreign key constraint on ac_ProductReviews. Apparently in my install, there is no Cascade-Delete configured in my ac_ProductReviews for the foreign key back into ac_Products. No biggie, I edited it and set it to cascade.
Now the delete command goes through without error and 1,006 products were deleted. Sweet! So much for manually clearing those puppies out.....
About 30 minutes later I noticed a TON of errors in the Error Log. CategoryGridPage4.aspx is throwing exceptions left and right. Sure enough, I check a few categories and most every category is blowing up back to the home page. Ruh Roh I said.
After a quick debugging, I figure out that there is an ac_CatalogNodes record for all those ProductIds I just nuked from ac_Products. But there is no cascade-delete into ac_CatalogNodes from ac_Products, so the category grid pages are trying to render product records that no longer exist. Ruh Roh x2
I figured out a quick SQL query command that cleans up ac_CatalogNodes of any orphaned productId references.
What I don't get is that I USED TO be able to do this. I don't remember the last time I nuked products directly out of ac_Products, but I sure don't remember having to clean up CatalogNodes as well. Never.
So now that I sit here in AC 7.0.5, I'm left with being unable to manually delete product records directly from the table without blowing up the store. How did this get so complicated
I had a bunch of products in ac_Products that were marked as private - all discontinued items automatically marked as my distributor feeds update the catalog.
No biggie I thought, I'll just do a DELETE FROM AC_Products WHERE blah blah blah and nuke them all in one fell swoop.
Well it failed the first time, complaining about a foreign key constraint on ac_ProductReviews. Apparently in my install, there is no Cascade-Delete configured in my ac_ProductReviews for the foreign key back into ac_Products. No biggie, I edited it and set it to cascade.
Now the delete command goes through without error and 1,006 products were deleted. Sweet! So much for manually clearing those puppies out.....
About 30 minutes later I noticed a TON of errors in the Error Log. CategoryGridPage4.aspx is throwing exceptions left and right. Sure enough, I check a few categories and most every category is blowing up back to the home page. Ruh Roh I said.
After a quick debugging, I figure out that there is an ac_CatalogNodes record for all those ProductIds I just nuked from ac_Products. But there is no cascade-delete into ac_CatalogNodes from ac_Products, so the category grid pages are trying to render product records that no longer exist. Ruh Roh x2
I figured out a quick SQL query command that cleans up ac_CatalogNodes of any orphaned productId references.
Code: Select all
delete from ac_catalognodes
where catalognodeid not in (select productid from ac_products)
and catalognodetypeid = 1
So now that I sit here in AC 7.0.5, I'm left with being unable to manually delete product records directly from the table without blowing up the store. How did this get so complicated