17 April 2015 von teiling88
Snippets

Shopware Foreign Keys reparieren funktioniert nicht

Shopware bietet ein SQL-Script an mit denen man die Foreign Keys in der Shopware 4.X Datenbank wiederherstellen/reparieren kann. Dies funktioniert leider nicht immer. Es können, wenn die Datenbank nicht mehr Konsistent ist, folgende Fehlermeldungen auftreten:

ERROR 1452 (23000) at line 204: Cannot add or update a child row: a foreign key constraint fails (`usr_XXXXXXXX`.`#sql-e49_10dbaa34`, CONSTRAINT `#sql-e49_10dbaa34_ibfk_1` FOREIGN KEY (`blog_id`) REFERENCES `s_blog` (`id`) ON DELETE CASCADE
ON UPDATE NO ACTION)

Abhilfe schafft folgendes SQL Statement welches alle Fehlerhaften Einträge inkl. Dateileichen entfernt (unbedingt vorher eine DB-Sicherung erstellen, Verwendung auf eigener Gefahr):

DELETE saa.* FROM s_articles_attributes saa
LEFT JOIN s_articles sa ON saa.articleID = sa.id
WHERE sa.id IS NULL;
 
DELETE saa.* FROM s_articles_attributes saa
LEFT JOIN s_articles_details sad ON saa.articledetailsID = sad.id
WHERE sad.id IS NULL;

DELETE sada.* FROM s_articles_downloads_attributes sada 
LEFT JOIN s_articles_downloads sad ON sada.downloadID = sad.id
WHERE sad.id IS NULL;

DELETE saea.* FROM s_articles_esd_attributes saea
LEFT JOIN s_articles_esd sae ON saea.esdID = sae.id
WHERE sae.id IS NULL;

DELETE saia.* FROM s_articles_img_attributes saia 
LEFT JOIN s_articles_img sai ON sai.id = saia.imageID
WHERE sai.id IS NULL;

DELETE saia.* FROM s_articles_information_attributes saia
LEFT JOIN s_articles_information sai ON sai.id = saia.informationID
WHERE sai.id IS NULL;

DELETE sapa.* FROM s_articles_prices_attributes sapa
LEFT JOIN s_articles_prices sap ON sap.id = sapa.priceID
WHERE sap.id IS NULL;

DELETE sasa.* FROM s_articles_supplier_attributes sasa
LEFT JOIN s_articles_supplier sas ON sas.id = sasa.supplierID
WHERE sas.id IS NULL;

DELETE sacta.* FROM s_article_configurator_templates_attributes sacta 
LEFT JOIN s_article_configurator_templates sact ON sact.id = sacta.template_id
WHERE sact.id IS NULL;

DELETE sactpa.* FROM s_article_configurator_template_prices_attributes sactpa
LEFT JOIN s_article_configurator_template_prices sactp ON sactp.id = sactpa.template_price_id
WHERE sactp.id IS NULL;

DELETE sba.* FROM s_blog_attributes sba
LEFT JOIN s_blog sb ON sba.blog_id = sb.id
WHERE sb.id IS NULL;

DELETE sca.* FROM s_categories_attributes sca
LEFT JOIN s_categories sc ON sc.id = sca.categoryID
WHERE sc.id IS NULL;

DELETE scsa.* FROM s_cms_static_attributes scsa
LEFT JOIN s_cms_static scs ON scs.id = scsa.cmsStaticID
WHERE scs.id IS NULL;

DELETE scsa.* FROM s_cms_support_attributes scsa
LEFT JOIN s_cms_support scs ON scs.id = scsa.cmsSupportID
WHERE scs.id IS NULL;

DELETE scaa.* FROM s_core_auth_attributes scaa
LEFT JOIN s_core_auth sca ON sca.id = scaa.authID
WHERE sca.id IS NULL;

DELETE sccma.* FROM s_core_config_mails_attributes sccma 
LEFT JOIN s_core_config_mails sccm ON sccm.id = sccma.mailID
WHERE sccm.id IS NULL;

DELETE scca.* FROM s_core_countries_attributes scca
LEFT JOIN s_core_countries scc ON scc.id = scca.countryID
WHERE scc.id IS NULL;

DELETE sccsa.* FROM s_core_countries_states_attributes sccsa
LEFT JOIN s_core_countries_states sccs ON sccs.id = sccsa.stateID
WHERE sccs.id IS NULL;

DELETE scca.* FROM s_core_customergroups_attributes scca
LEFT JOIN s_core_customergroups scc ON scc.id = scca.customerGroupID
WHERE scc.id IS NULL;

DELETE scpa.* FROM s_core_paymentmeans_attributes scpa
LEFT JOIN s_core_paymentmeans scp ON scp.id = scpa.paymentmeanID
WHERE scp.id IS NULL;

DELETE seba.* FROM s_emarketing_banners_attributes seba
LEFT JOIN s_emarketing_banners seb ON seb.id = seba.bannerID
WHERE seb.id IS NULL;

DELETE seva.* FROM s_emarketing_vouchers_attributes seva
LEFT JOIN s_emarketing_vouchers sev ON sev.id = seva.voucherID
WHERE sev.id IS NULL;

DELETE sea.* FROM s_emotion_attributes sea
LEFT JOIN s_emotion se ON se.id = sea.emotionID
WHERE se.id IS NULL;

DELETE sea.* FROM s_export_attributes sea
LEFT JOIN s_export se ON se.id = sea.exportID
WHERE se.id IS NULL;

DELETE sfa.* FROM s_filter_attributes sfa
LEFT JOIN s_filter sf ON sf.id = sfa.filterID
WHERE sf.id IS NULL;

DELETE sma.* FROM s_media_attributes sma
LEFT JOIN s_media sm ON sm.id = sma.mediaID
WHERE sm.id IS NULL;

DELETE soa.* FROM s_order_attributes soa
LEFT JOIN s_order so ON so.id = soa.orderID
WHERE so.id IS NULL;

DELETE soba.* FROM s_order_basket_attributes soba
LEFT JOIN s_order_basket sob ON sob.id = soba.basketID
WHERE sob.id IS NULL;

DELETE soba.* FROM s_order_billingaddress_attributes soba
LEFT JOIN s_order_billingaddress sob ON sob.id = soba.billingID
WHERE sob.id IS NULL;

DELETE soda.* FROM s_order_details_attributes soda
LEFT JOIN s_order_details sod ON sod.id = soda.detailID
WHERE sod.id IS NULL;

DELETE soda.* FROM s_order_documents_attributes soda
LEFT JOIN s_order_documents sod ON sod.id = soda.documentID
WHERE sod.id IS NULL;

DELETE sosa.* FROM s_order_shippingaddress_attributes sosa
LEFT JOIN s_order_shippingaddress sos ON sos.id = sosa.shippingID
WHERE sos.id IS NULL;

DELETE spda.* FROM s_premium_dispatch_attributes spda
LEFT JOIN s_premium_dispatch spd ON spd.id = spda.dispatchID
WHERE spd.id IS NULL;

DELETE sua.* FROM s_user_attributes sua
LEFT JOIN s_user su ON su.id = sua.userID
WHERE su.id IS NULL;

DELETE suba.* FROM s_user_billingaddress_attributes suba
LEFT JOIN s_user_billingaddress sub ON sub.id = suba.billingID
WHERE sub.id IS NULL;

DELETE susa.* FROM s_user_shippingaddress_attributes susa
LEFT JOIN s_user_shippingaddress sus ON sus.id = susa.shippingID
WHERE sus.id IS NULL;

  Wenn ich eine Datenbanktabelle übersehen haben sollte gebt mir bitte Bescheid :-)

Loading…