Delete a website from Magento, unable to index Groups Catalog after

[bdgeorge]

I have a Magento instance with 2 websites and around 5000 skus, I change the second website to be the default site and then delete the first (this is all on a staging server of course),

After doing that I'm unable to index GroupsCatalog Products without getting out of memory errors.

I then flush out all orders and customers from the history, so the only thing in the database is the 5000 skus - of course this makes no difference as it's not hitting that part of the DB.

I've tried merging in the indexing patch from 017d395 with no improvement I'm afraid.

[Vinai]

Thanks for your notice. Which version of Magento and the extension are you using?

[bdgeorge]

Magento 1.6.2 with 0.2.7

[bdgeorge]

For my next test I've removed the deleted store entries from core_store and core_store_group - deleting a store from the UI doesn't remove these and other indexing issues arise with these still in place, no change I'm afraid!

[Vinai]

Somethings wrong with your DB I'm afraid, because the core_store table has a on delete cascade constraint to the the core_website table

So what should happen is:

1. you delete a website via the backend UI
2. the record for that website is removed from the core_website table
3. that cascades to core_store (and core_store_group, too, by the way)
4. that in turn also cascade deletes all affected records from the groupscatalog tables (and many other core tables, too)

Please check the tables in question use the InnoDB engine and the foreign keys are set up correctly.

[bdgeorge]

Interesting, I'll double check - the number of people with similar errors (separate to GroupsCatalog) and the message from the delete action made me think that it didn't delete this stuff: Deleting a %1$s will not delete the information associated with the %1$s (e.g. categories, products, etc.), but the %1$s will not be able to be restored. It is suggested that you create a database backup before deleting the %1$s. (Mage_Adminhtml_System_StoreController)

Also if I create a new website and then delete it straight away without adding anything into it remains in the core_store* tables.

Let me try on a completely fresh instance!

[Vinai]

When I do a SHOW CREATE TABLE core_store I get the following table description:

CREATE TABLE `core_store` (
  `store_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Store Id',
  `code` varchar(32) DEFAULT NULL COMMENT 'Code',
  `website_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Website Id',
  `group_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Group Id',
  `name` varchar(255) NOT NULL COMMENT 'Store Name',
  `sort_order` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Store Sort Order',
  `is_active` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Store Activity',
  PRIMARY KEY (`store_id`),
  UNIQUE KEY `UNQ_CORE_STORE_CODE` (`code`),
  KEY `IDX_CORE_STORE_WEBSITE_ID` (`website_id`),
  KEY `IDX_CORE_STORE_IS_ACTIVE_SORT_ORDER` (`is_active`,`sort_order`),
  KEY `IDX_CORE_STORE_GROUP_ID` (`group_id`),
  CONSTRAINT `FK_CORE_STORE_GROUP_ID_CORE_STORE_GROUP_GROUP_ID` FOREIGN KEY (`group_id`) REFERENCES `core_store_group` (`group_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_CORE_STORE_WEBSITE_ID_CORE_WEBSITE_WEBSITE_ID` FOREIGN KEY (`website_id`) REFERENCES `core_website` (`website_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT='Stores'

Also I just tested it, when I delete a record from core_website, the corresponding core_store entry is removed, as well as any records referencing it from for example the catalog_product_entity_int table (which also uses a on delete cascade foreign key on core_store.store_id

To be honest, I don't know what that warning message refers to - it might be obsolete.

[bdgeorge]

Hmm, I'm missing those constraints, this store started life as 1.3.2.4 with MyISAM tables (Innodb not supported at that time on that hosting company).

Since then I've switched to InnoDB using the Magento DB repair tool and then upgraded it from 1.3 -> 1.6.

Looking through the mysql-upgrades I think the first place those constraints are rebuilt is only at the mysql4-upgrade-1.5.9.9-1.6.0.0.php script, but for some reason they weren't.

So somehow I got unlucky and missed those constraints being added either by the repair tool or by the store upgrade (!) and that must be a big part of the problem, if it missed them what else did it miss....

I'll try the DB repair tool again and come back to this ticket once I've retested - were you able to reindex correctly after a store deletion?

[Vinai]

No problem reindexing after deleting a website or a store view.

[bdgeorge]

I'm going to put this down to a large and slightly corrupted database then, thanks heaps for looking into it and being so quick to respond, much appreciated!