We've got a live site that we're currently having a issue with. It's got about 10k products (500 configurable products with about 10k total simple products). It has about 80 sites, where outside of the main website most only carry a few products with some minor variations.
If I create a new website + store + store view, and would like to add an existing product and its configurations to that store, I'm selecting the main configurable product and each of its configurations in the product grid and selecting update attributes from mass actions. Then I'm selecting websites, and adding it to my new store (I've done nothing else with the store, just created it).
After about ~1 minute this results in an error message. Checking the exceptions log shows a rather massive query with a 1205 lock wait timeout exceeded error. I'm adding that here as a snippet just for reference (at least the first part of it, otherwise it's too long to post here), but I'm trying to figure out what's going on. Can anyone tell me what it's trying to do here that has caused this? It looks like it's doing something with all products, but I'm not sure what.
One other piece of information, If I add products to other existing sites on my server the same way, they work without issue.
INSERT INTO `catalog_category_product_index_store1_tmp` (`category_id`, `product_id`, `position`, `is_parent`, `store_id`, `visibility`) SELECT 2 AS `category_id`, `cp`.`entity_id` AS `product_id`, IF(ccp.product_id IS NOT NULL, ccp.position, 0) AS `position`, IF(ccp.product_id IS NOT NULL, 1, 0) AS `is_parent`, 1 AS `store_id`, IFNULL(cpvs.value, cpvd.value) AS `visibility` FROM `catalog_product_entity` AS `cp`
INNER JOIN `catalog_product_website` AS `cpw` ON cpw.product_id = cp.entity_id
INNER JOIN `catalog_product_entity_int` AS `cpsd` ON cpsd.row_id = cp.row_id AND cpsd.store_id = 0 AND cpsd.attribute_id = 97
LEFT JOIN `catalog_product_entity_int` AS `cpss` ON cpss.row_id = cp.row_id AND cpss.attribute_id = cpsd.attribute_id AND cpss.store_id = 1
INNER JOIN `catalog_product_entity_int` AS `cpvd` ON cpvd.row_id = cp.row_id AND cpvd.store_id = 0 AND cpvd.attribute_id = 99
LEFT JOIN `catalog_product_entity_int` AS `cpvs` ON cpvs.row_id = cp.row_id AND cpvs.attribute_id = cpvd.attribute_id AND cpvs.store_id = 1
LEFT JOIN `catalog_category_product` AS `ccp` ON ccp.product_id = cp.entity_id WHERE ((cpw.website_id = '1') AND (IFNULL(cpss.value, cpsd.value) = 1) AND (IFNULL(cpvs.value, cpvd.value) IN (2, 3, 4)) AND (cp.entity_id IN (163, 164, 165, 166, 167, 168, 169, 170, 171, 172, 173, 174, 175, 176, 177, 178, 179, 180, 181, 182, 183, 184, 185, 186, 187, 188, 189, 190, 191, 192, 193, 194, 195, 196, 197, 198, 199, 200, 201, 202, 203, 204, 205, 206, 207, 208, 209, 210, 211, 212, 213, 214, 215, 216, 217, 218, 219, 220, 221, 222, 223, 224, 225, 226, 227, 228, 229, 230, 231, 232, 233, 234, 235, 236, 237, 238, 239, 240, 241, 242, 243, 244, 245, 246, 247, 248, 249, 250, 251, 252, 253, 254, 255, 256, 257
