2
Answers
Accidental product removal through database?
I accidentally removed a product in the commerce_product table.
How can I go about finding the remaining links and references to this product in order to remove it completely from the databse?
Apparently I'm getting a error message when re-indexing my products and I'm guessing this has something to do with the accidentally removed products.
Which tables should I check for refferences to the missing product and how would I go about doing this?
Comments
When I log all queries I see the following queries were run when I delete a product through the UI:
DELETE FROM node_comment_statistics WHERE (nid = ''395''),DELETE FROM url_alias WHERE (source = ''node/395''),
DELETE FROM taxonomy_index WHERE (nid = ''395''),
DELETE FROM commerce_product WHERE (product_id IN (''377'')),
DELETE FROM commerce_product_revision WHERE (product_id IN (''377'')),
DELETE FROM field_data_commerce_price WHERE (entity_type = ''commerce_product'') AND (entity_id = ''377''),
DELETE FROM field_revision_commerce_price WHERE (entity_type = ''commerce_product'') AND (entity_id = ''377''),
DELETE FROM field_data_field_images WHERE (entity_type = ''commerce_product'') AND (entity_id = ''377''),
DELETE FROM field_revision_field_images WHERE (entity_type = ''commerce_product'') AND (entity_id = ''377''),
DELETE FROM field_data_title_field WHERE (entity_type = ''commerce_product'') AND (entity_id = ''377''),
DELETE FROM field_revision_title_field WHERE (entity_type = ''commerce_product'') AND (entity_id = ''377''),
DELETE FROM field_data_field_price_table WHERE (entity_type = ''commerce_product'') AND (entity_id = ''377''),
DELETE FROM field_revision_field_price_table WHERE (entity_type = ''commerce_product'') AND (entity_id = ''377''),
DELETE FROM field_data_field_from_price WHERE (entity_type = ''commerce_product'') AND (entity_id = ''377''),
DELETE FROM field_revision_field_from_price WHERE (entity_type = ''commerce_product'') AND (entity_id = ''377''),
DELETE FROM field_data_field_kleur_product WHERE (entity_type = ''commerce_product'') AND (entity_id = ''377''),
DELETE FROM field_revision_field_kleur_product WHERE (entity_type = ''commerce_product'') AND (entity_id = ''377''),
DELETE FROM field_data_field_formaat_product WHERE (entity_type = ''commerce_product'') AND (entity_id = ''377''),
DELETE FROM field_revision_field_formaat_product WHERE (entity_type = ''commerce_product'') AND (entity_id = ''377''),
DELETE FROM field_data_field_body WHERE (entity_type = ''commerce_product'') AND (entity_id = ''377''),
DELETE FROM field_revision_field_body WHERE (entity_type = ''commerce_product'') AND (entity_id = ''377''),
DELETE FROM cache_field WHERE (cid = ''field:commerce_product:377''),
DELETE FROM field_data_body WHERE (entity_type = ''node'') AND (entity_id = ''394'') AND (language IN (''und'')),
DELETE FROM field_revision_body WHERE (entity_type = ''node'') AND (entity_id = ''394'') AND (revision_id = ''394'') AND (language IN (''und'')),
DELETE FROM field_data_field_product WHERE (entity_type = ''node'') AND (entity_id = ''394'') AND (language IN (''und'')),
DELETE FROM field_revision_field_product WHERE (entity_type = ''node'') AND (entity_id = ''394'') AND (revision_id = ''394'') AND (language IN (''und'')),
DELETE FROM field_data_field_product_category WHERE (entity_type = ''node'') AND (entity_id = ''394'') AND (language IN (''und'')),
DELETE FROM field_revision_field_product_category WHERE (entity_type = ''node'') AND (entity_id = ''394'') AND (revision_id = ''394'') AND (language IN (''und'')),
DELETE FROM field_data_title_field WHERE (entity_type = ''node'') AND (entity_id = ''394'') AND (language IN (''und'')),
DELETE FROM field_revision_title_field WHERE (entity_type = ''node'') AND (entity_id = ''394'') AND (revision_id = ''394'') AND (language IN (''und'')),
DELETE FROM field_data_field_waardering WHERE (entity_type = ''node'') AND (entity_id = ''394'') AND (language IN (''und'')),
DELETE FROM field_revision_field_waardering WHERE (entity_type = ''node'') AND (entity_id = ''394'') AND (revision_id = ''394'') AND (language IN (''und'')),
DELETE FROM field_data_field_leverancier WHERE (entity_type = ''node'') AND (entity_id = ''394'') AND (language IN (''und'')),
DELETE FROM field_revision_field_leverancier WHERE (entity_type = ''node'') AND (entity_id = ''394'') AND (revision_id = ''394'') AND (language IN (''und'')),
DELETE FROM field_data_field_artikelnummer_leverancier WHERE (entity_type = ''node'') AND (entity_id = ''394'') AND (language IN (''und'')),
DELETE FROM field_revision_field_artikelnummer_leverancier WHERE (entity_type = ''node'') AND (entity_id = ''394'') AND (revision_id = ''394'') AND (language IN (''und'')),
DELETE FROM field_data_field_modelnummer_leverancier WHERE (entity_type = ''node'') AND (entity_id = ''394'') AND (language IN (''und'')),
DELETE FROM field_revision_field_modelnummer_leverancier WHERE (entity_type = ''node'') AND (entity_id = ''394'') AND (revision_id = ''394'') AND (language IN (''und'')),
DELETE FROM field_data_field_ean_nummer WHERE (entity_type = ''node'') AND (entity_id = ''394'') AND (language IN (''und'')),
DELETE FROM field_revision_field_ean_nummer WHERE (entity_type = ''node'') AND (entity_id = ''394'') AND (revision_id = ''394'') AND (language IN (''und'')),
DELETE FROM field_data_field_pagina_in_catalogus_levera WHERE (entity_type = ''node'') AND (entity_id = ''394'') AND (language IN (''und'')),
DELETE FROM field_revision_field_pagina_in_catalogus_levera WHERE (entity_type = ''node'') AND (entity_id = ''394'') AND (revision_id = ''394'') AND (language IN (''und'')),
DELETE FROM field_data_field_land_van_oorsprong WHERE (entity_type = ''node'') AND (entity_id = ''394'') AND (language IN (''und'')),
DELETE FROM field_revision_field_land_van_oorsprong WHERE (entity_type = ''node'') AND (entity_id = ''394'') AND (revision_id = ''394'') AND (language IN (''und'')),
DELETE FROM field_data_field_cbs_goederencode WHERE (entity_type = ''node'') AND (entity_id = ''394'') AND (language IN (''und'')),
DELETE FROM field_revision_field_cbs_goederencode WHERE (entity_type = ''node'') AND (entity_id = ''394'') AND (revision_id = ''394'') AND (language IN (''und'')),
DELETE FROM field_data_field_volume_m3_ WHERE (entity_type = ''node'') AND (entity_id = ''394'') AND (language IN (''und'')),
DELETE FROM field_revision_field_volume_m3_ WHERE (entity_type = ''node'') AND (entity_id = ''394'') AND (revision_id = ''394'') AND (language IN (''und'')),
DELETE FROM field_data_field_gerelateerd_product WHERE (entity_type = ''node'') AND (entity_id = ''394'') AND (language IN (''und'')),
DELETE FROM field_revision_field_gerelateerd_product WHERE (entity_type = ''node'') AND (entity_id = ''394'') AND (revision_id = ''394'') AND (language IN (''und'')),
DELETE FROM cache_field WHERE (cid = ''field:node:394''),
DELETE FROM taxonomy_index WHERE (nid = ''394''),
DELETE FROM cache WHERE (cid LIKE ''node\\\\_types:%'' ESCAPE ''\\\\''),
DELETE FROM semaphore WHERE (name = ''menu_rebuild'') AND (value = ''102649713553621a22bcbe00.05893366''),
DELETE FROM node_access WHERE (nid = ''394''),
DELETE FROM field_data_body WHERE (entity_type = ''node'') AND (entity_id = ''395'') AND (language IN (''und'')),
DELETE FROM field_revision_body WHERE (entity_type = ''node'') AND (entity_id = ''395'') AND (revision_id = ''395'') AND (language IN (''und'')),
DELETE FROM field_data_field_product WHERE (entity_type = ''node'') AND (entity_id = ''395'') AND (language IN (''und'')),
DELETE FROM field_revision_field_product WHERE (entity_type = ''node'') AND (entity_id = ''395'') AND (revision_id = ''395'') AND (language IN (''und'')),
DELETE FROM field_data_field_product_category WHERE (entity_type = ''node'') AND (entity_id = ''395'') AND (language IN (''und'')),
DELETE FROM field_revision_field_product_category WHERE (entity_type = ''node'') AND (entity_id = ''395'') AND (revision_id = ''395'') AND (language IN (''und'')),
DELETE FROM field_data_title_field WHERE (entity_type = ''node'') AND (entity_id = ''395'') AND (language IN (''und'')),
DELETE FROM field_revision_title_field WHERE (entity_type = ''node'') AND (entity_id = ''395'') AND (revision_id = ''395'') AND (language IN (''und'')),
DELETE FROM field_data_field_waardering WHERE (entity_type = ''node'') AND (entity_id = ''395'') AND (language IN (''und'')),
DELETE FROM field_revision_field_waardering WHERE (entity_type = ''node'') AND (entity_id = ''395'') AND (revision_id = ''395'') AND (language IN (''und'')),
DELETE FROM field_data_field_leverancier WHERE (entity_type = ''node'') AND (entity_id = ''395'') AND (language IN (''und'')),
DELETE FROM field_revision_field_leverancier WHERE (entity_type = ''node'') AND (entity_id = ''395'') AND (revision_id = ''395'') AND (language IN (''und'')),
DELETE FROM field_data_field_artikelnummer_leverancier WHERE (entity_type = ''node'') AND (entity_id = ''395'') AND (language IN (''und'')),
DELETE FROM field_revision_field_artikelnummer_leverancier WHERE (entity_type = ''node'') AND (entity_id = ''395'') AND (revision_id = ''395'') AND (language IN (''und'')),
DELETE FROM field_data_field_modelnummer_leverancier WHERE (entity_type = ''node'') AND (entity_id = ''395'') AND (language IN (''und'')),
DELETE FROM field_revision_field_modelnummer_leverancier WHERE (entity_type = ''node'') AND (entity_id = ''395'') AND (revision_id = ''395'') AND (language IN (''und'')),
DELETE FROM field_data_field_ean_nummer WHERE (entity_type = ''node'') AND (entity_id = ''395'') AND (language IN (''und'')),
DELETE FROM field_revision_field_ean_nummer WHERE (entity_type = ''node'') AND (entity_id = ''395'') AND (revision_id = ''395'') AND (language IN (''und'')),
DELETE FROM field_data_field_pagina_in_catalogus_levera WHERE (entity_type = ''node'') AND (entity_id = ''395'') AND (language IN (''und'')),
DELETE FROM field_revision_field_pagina_in_catalogus_levera WHERE (entity_type = ''node'') AND (entity_id = ''395'') AND (revision_id = ''395'') AND (language IN (''und'')),
DELETE FROM field_data_field_land_van_oorsprong WHERE (entity_type = ''node'') AND (entity_id = ''395'') AND (language IN (''und'')),
DELETE FROM field_revision_field_land_van_oorsprong WHERE (entity_type = ''node'') AND (entity_id = ''395'') AND (revision_id = ''395'') AND (language IN (''und'')),
DELETE FROM field_data_field_cbs_goederencode WHERE (entity_type = ''node'') AND (entity_id = ''395'') AND (language IN (''und'')),
DELETE FROM field_revision_field_cbs_goederencode WHERE (entity_type = ''node'') AND (entity_id = ''395'') AND (revision_id = ''395'') AND (language IN (''und'')),
DELETE FROM field_data_field_volume_m3_ WHERE (entity_type = ''node'') AND (entity_id = ''395'') AND (language IN (''und'')),
DELETE FROM field_revision_field_volume_m3_ WHERE (entity_type = ''node'') AND (entity_id = ''395'') AND (revision_id = ''395'') AND (language IN (''und'')),
DELETE FROM field_data_field_gerelateerd_product WHERE (entity_type = ''node'') AND (entity_id = ''395'') AND (language IN (''und'')),
DELETE FROM field_revision_field_gerelateerd_product WHERE (entity_type = ''node'') AND (entity_id = ''395'') AND (revision_id = ''395'') AND (language IN (''und'')),
DELETE FROM cache_field WHERE (cid = ''field:node:395''),
DELETE FROM taxonomy_index WHERE (nid = ''395''),
DELETE FROM cache WHERE (cid LIKE ''node\\\\_types:%'' ESCAPE ''\\\\''),
DELETE FROM semaphore WHERE (name = ''menu_rebuild'') AND (value = ''102649713553621a22bcbe00.05893366''),
DELETE FROM node_access WHERE (nid = ''395''),
DELETE FROM search_api_item WHERE (item_id IN (''395'')) AND (index_id IN (''1'')),
DELETE FROM search_api_db_product_display WHERE (item_id IN (''395'')),
DELETE FROM search_api_db_product_display_field_product WHERE (item_id IN (''395'')),
DELETE FROM search_api_db_product_display WHERE (item_id IN (''395'')),
DELETE FROM search_api_db_product_display_field_product_category WHERE (item_id IN (''395'')),
DELETE FROM search_api_db_product_display WHERE (item_id IN (''395'')),
DELETE FROM search_api_db_product_display_search_api_aggregation_1 WHERE (item_id IN (''395'')),
DELETE FROM search_api_db_product_display_field_product_category_name WHERE (item_id IN (''395'')),
DELETE FROM search_api_db_product_display_field_product_commerce_price_amo WHERE (item_id IN (''395'')),
DELETE FROM search_api_db_product_display WHERE (item_id IN (''395'')),
DELETE FROM search_api_db_product_display WHERE (item_id IN (''395'')),
DELETE FROM search_api_db_product_display_search_api_aggregation_2 WHERE (item_id IN (''395'')),
DELETE FROM search_api_db_product_display_field_product_sku WHERE (item_id IN (''395'')),
DELETE FROM field_data_body WHERE (entity_type = ''node'') AND (entity_id = ''395''),
DELETE FROM field_revision_body WHERE (entity_type = ''node'') AND (entity_id = ''395''),
DELETE FROM field_data_field_product WHERE (entity_type = ''node'') AND (entity_id = ''395''),
DELETE FROM field_revision_field_product WHERE (entity_type = ''node'') AND (entity_id = ''395''),
DELETE FROM field_data_field_product_category WHERE (entity_type = ''node'') AND (entity_id = ''395''),
DELETE FROM field_revision_field_product_category WHERE (entity_type = ''node'') AND (entity_id = ''395''),
DELETE FROM field_data_title_field WHERE (entity_type = ''node'') AND (entity_id = ''395''),
DELETE FROM field_revision_title_field WHERE (entity_type = ''node'') AND (entity_id = ''395''),
DELETE FROM field_data_field_waardering WHERE (entity_type = ''node'') AND (entity_id = ''395''),
DELETE FROM field_revision_field_waardering WHERE (entity_type = ''node'') AND (entity_id = ''395''),
DELETE FROM field_data_field_leverancier WHERE (entity_type = ''node'') AND (entity_id = ''395''),
DELETE FROM field_revision_field_leverancier WHERE (entity_type = ''node'') AND (entity_id = ''395''),
DELETE FROM field_data_field_artikelnummer_leverancier WHERE (entity_type = ''node'') AND (entity_id = ''395''),
DELETE FROM field_revision_field_artikelnummer_leverancier WHERE (entity_type = ''node'') AND (entity_id = ''395''),
DELETE FROM field_data_field_modelnummer_leverancier WHERE (entity_type = ''node'') AND (entity_id = ''395''),
DELETE FROM field_revision_field_modelnummer_leverancier WHERE (entity_type = ''node'') AND (entity_id = ''395''),
DELETE FROM field_data_field_ean_nummer WHERE (entity_type = ''node'') AND (entity_id = ''395''),
DELETE FROM field_revision_field_ean_nummer WHERE (entity_type = ''node'') AND (entity_id = ''395''),
DELETE FROM field_data_field_pagina_in_catalogus_levera WHERE (entity_type = ''node'') AND (entity_id = ''395''),
DELETE FROM field_revision_field_pagina_in_catalogus_levera WHERE (entity_type = ''node'') AND (entity_id = ''395''),
DELETE FROM field_data_field_land_van_oorsprong WHERE (entity_type = ''node'') AND (entity_id = ''395''),
DELETE FROM field_revision_field_land_van_oorsprong WHERE (entity_type = ''node'') AND (entity_id = ''395''),
DELETE FROM field_data_field_cbs_goederencode WHERE (entity_type = ''node'') AND (entity_id = ''395''),
DELETE FROM field_revision_field_cbs_goederencode WHERE (entity_type = ''node'') AND (entity_id = ''395''),
DELETE FROM field_data_field_volume_m3_ WHERE (entity_type = ''node'') AND (entity_id = ''395''),
DELETE FROM field_revision_field_volume_m3_ WHERE (entity_type = ''node'') AND (entity_id = ''395''),
DELETE FROM field_data_field_gerelateerd_product WHERE (entity_type = ''node'') AND (entity_id = ''395''),
DELETE FROM field_revision_field_gerelateerd_product WHERE (entity_type = ''node'') AND (entity_id = ''395''),
DELETE FROM cache_field WHERE (cid = ''field:node:395''),
DELETE FROM node WHERE (nid IN (''395'')),
DELETE FROM node_revision WHERE (nid IN (''395'')),
DELETE FROM history WHERE (nid IN (''395'')),
DELETE FROM node_access WHERE (nid IN (''395'')),
How can I find the right nid's of node entity's and product entities linking to a commerce_product entity so I can trace back the problem from my commerce_product id to the other entities that are attached to them?
EDIT: also, you guys might want to add: overflow-x: scroll on your div.codeblock.
Currently I'm trying to use a refference table to see which products were removed by hand in the commerce_product table.
I know the following id's are missing (gaps) from my commerce_product table:
203, 204, 206, 215, 218, 221, 223, 224, 225, 226, 227, 230, 232, 233, 234, 235, 254, 255, 256, 276, 277, 280, 288, 290, 291, 293. 294, 295, 298, 302, 303, 304, 305, 306, 307, 308, 312, 313, 314, 318, 319, 330, 331, 332, 333, 335, 336, 344, 345, 346, 348, 349, 350, 351, 352, 355, 356, 360, 361, 362, 363, 364, 366, 367, 368, 369, 371, 375, 376, 378, 386, 387, 388, 389, 394, 398
Someone gave me a script to insert a product with the same id as one of the above and use commerce_product_delete($product_id) in order to remove the refferences to that product. It's the same function being used by the UI to remove a product.
So far this seems to work. I'm trying to change the script into doing several id's at once. The script seems to fail when a product has already been removed through the UI because the refferences do not exist. It also fails when the same product has already been input.
I'm going to remove all the test products and create a loop to do them all at once.
I'm still trying to figure out when commerce_product_delete causes the page to return an unknown error.
This way by creating a fail-safe I can remove the test product when it fails and go on to the next.
Hopefully after all the product refferences have removed the following error will dissapear when I re-index my products:
EntityMetadataWrapperException: Unable to get the data property status as the parent data structure is not set.
My understanding is that this occurs because there is some messed up link in the database.
SimbaClaws, I'm happy to see you got this resolved. Hopefully you learned a bunch as we worked through it over on #drupal-commerce. Please make sure you take some time to develop a better backup strategy so you can more quickly recover should something like this happen again!
Cheers,
Adam