Löschen von Datenbanktabellen in MySQL5 schlägt fehl Fr, Jun 11. 2010
Beim Löschen von Tabellen kann es folgenden Fehler geben:
#1217 - Cannot delete or update a parent row: a foreign key
constraint fails
Dies kann z.B. beim Löschen aller Magento-Tabellen mit dem MySQL-Hilfstool phpMyadmin vorkommen, wenn alle Tabellen ausgewählt werden und dann das rote Kreuz zum Löschen der Tabellen betätigt wird.
Dies ist der Befehl, den phpmyadmin versucht auszuführen:
DROP TABLE `adminnotification_inbox` ,
`admin_assert` ,
`admin_role` ,
`admin_rule` ,
`admin_user` ,
`api_assert` ,
`api_role` ,
`api_rule` ,
`api_session` ,
`api_user` ,
`catalogindex_aggregation` ,
`catalogindex_aggregation_tag` ,
`catalogindex_aggregation_to_tag` ,
`catalogindex_eav` ,
`catalogindex_minimal_price` ,
`catalogindex_price` ,
`cataloginventory_stock` ,
`cataloginventory_stock_item` ,
`cataloginventory_stock_status` ,
`cataloginventory_stock_status_idx` ,
`cataloginventory_stock_status_tmp` ,
`catalogrule` ,
`catalogrule_affected_product` ,
`catalogrule_group_website` ,
`catalogrule_product` ,
`catalogrule_product_price` ,
`catalogsearch_fulltext` ,
`catalogsearch_query` ,
`catalogsearch_result` ,
`catalog_category_anc_categs_index_idx` ,
`catalog_category_anc_categs_index_tmp` ,
`catalog_category_anc_products_index_idx` ,
`catalog_category_anc_products_index_tmp` ,
`catalog_category_entity` ,
`catalog_category_entity_datetime` ,
`catalog_category_entity_decimal` ,
`catalog_category_entity_int` ,
`catalog_category_entity_text` ,
`catalog_category_entity_varchar` ,
`catalog_category_flat_store_1` ,
`catalog_category_product` ,
`catalog_category_product_index` ,
`catalog_category_product_index_enbl_idx` ,
`catalog_category_product_index_enbl_tmp` ,
`catalog_category_product_index_idx` ,
`catalog_category_product_index_tmp` ,
`catalog_compare_item` ,
`catalog_eav_attribute` ,
`catalog_product_bundle_option` ,
`catalog_product_bundle_option_value` ,
`catalog_product_bundle_price_index` ,
`catalog_product_bundle_selection` ,
`catalog_product_bundle_stock_index` ,
`catalog_product_enabled_index` ,
`catalog_product_entity` ,
`catalog_product_entity_datetime` ,
`catalog_product_entity_decimal` ,
`catalog_product_entity_gallery` ,
`catalog_product_entity_int` ,
`catalog_product_entity_media_gallery` ,
`catalog_product_entity_media_gallery_value` ,
`catalog_product_entity_text` ,
`catalog_product_entity_tier_price` ,
`catalog_product_entity_varchar` ,
`catalog_product_flat_1` ,
`catalog_product_index_eav` ,
`catalog_product_index_eav_decimal` ,
`catalog_product_index_eav_decimal_idx` ,
`catalog_product_index_eav_decimal_tmp` ,
`catalog_product_index_eav_idx` ,
`catalog_product_index_eav_tmp` ,
`catalog_product_index_price` ,
`catalog_product_index_price_bundle_idx` ,
`catalog_product_index_price_bundle_opt_idx` ,
`catalog_product_index_price_bundle_opt_tmp` ,
`catalog_product_index_price_bundle_sel_idx` ,
`catalog_product_index_price_bundle_sel_tmp` ,
`catalog_product_index_price_bundle_tmp` ,
`catalog_product_index_price_cfg_opt_agr_idx` ,
`catalog_product_index_price_cfg_opt_agr_tmp` ,
`catalog_product_index_price_cfg_opt_idx` ,
`catalog_product_index_price_cfg_opt_tmp` ,
`catalog_product_index_price_downlod_idx` ,
`catalog_product_index_price_downlod_tmp` ,
`catalog_product_index_price_final_idx` ,
`catalog_product_index_price_final_tmp` ,
`catalog_product_index_price_idx` ,
`catalog_product_index_price_opt_agr_idx` ,
`catalog_product_index_price_opt_agr_tmp` ,
`catalog_product_index_price_opt_idx` ,
`catalog_product_index_price_opt_tmp` ,
`catalog_product_index_price_tmp` ,
`catalog_product_index_tier_price` ,
`catalog_product_index_website` ,
`catalog_product_link` ,
`catalog_product_link_attribute` ,
`catalog_product_link_attribute_decimal` ,
`catalog_product_link_attribute_int` ,
`catalog_product_link_attribute_varchar` ,
`catalog_product_link_type` ,
`catalog_product_option` ,
`catalog_product_option_price` ,
`catalog_product_option_title` ,
`catalog_product_option_type_price` ,
`catalog_product_option_type_title` ,
`catalog_product_option_type_value` ,
`catalog_product_relation` ,
`catalog_product_super_attribute` ,
`catalog_product_super_attribute_label` ,
`catalog_product_super_attribute_pricing` ,
`catalog_product_super_link` ,
`catalog_product_website` ,
`checkout_agreement` ,
`checkout_agreement_store` ,
`cms_block` ,
`cms_block_store` ,
`cms_page` ,
`cms_page_store` ,
`core_cache` ,
`core_cache_option` ,
`core_cache_tag` ,
`core_config_data` ,
`core_email_template` ,
`core_flag` ,
`core_layout_link` ,
`core_layout_update` ,
`core_resource` ,
`core_session` ,
`core_store` ,
`core_store_group` ,
`core_translate` ,
`core_url_rewrite` ,
`core_variable` ,
`core_variable_value` ,
`core_website` ,
`coupon_aggregated` ,
`coupon_aggregated_order` ,
`cron_schedule` ,
`customer_address_entity` ,
`customer_address_entity_datetime` ,
`customer_address_entity_decimal` ,
`customer_address_entity_int` ,
`customer_address_entity_text` ,
`customer_address_entity_varchar` ,
`customer_eav_attribute` ,
`customer_entity` ,
`customer_entity_datetime` ,
`customer_entity_decimal` ,
`customer_entity_int` ,
`customer_entity_text` ,
`customer_entity_varchar` ,
`customer_group` ,
`dataflow_batch` ,
`dataflow_batch_export` ,
`dataflow_batch_import` ,
`dataflow_import_data` ,
`dataflow_profile` ,
`dataflow_profile_history` ,
`dataflow_session` ,
`design_change` ,
`directory_country` ,
`directory_country_format` ,
`directory_country_region` ,
`directory_country_region_name` ,
`directory_currency_rate` ,
`downloadable_link` ,
`downloadable_link_price` ,
`downloadable_link_purchased` ,
`downloadable_link_purchased_item` ,
`downloadable_link_title` ,
`downloadable_sample` ,
`downloadable_sample_title` ,
`eav_attribute` ,
`eav_attribute_group` ,
`eav_attribute_label` ,
`eav_attribute_option` ,
`eav_attribute_option_value` ,
`eav_attribute_set` ,
`eav_entity` ,
`eav_entity_attribute` ,
`eav_entity_datetime` ,
`eav_entity_decimal` ,
`eav_entity_int` ,
`eav_entity_store` ,
`eav_entity_text` ,
`eav_entity_type` ,
`eav_entity_varchar` ,
`eav_form_element` ,
`eav_form_fieldset` ,
`eav_form_fieldset_label` ,
`eav_form_type` ,
`eav_form_type_entity` ,
`gift_message` ,
`googlebase_attributes` ,
`googlebase_items` ,
`googlebase_types` ,
`googlecheckout_api_debug` ,
`googleoptimizer_code` ,
`index_event` ,
`index_process` ,
`index_process_event` ,
`log_customer` ,
`log_quote` ,
`log_summary` ,
`log_summary_type` ,
`log_url` ,
`log_url_info` ,
`log_visitor` ,
`log_visitor_info` ,
`log_visitor_online` ,
`newsletter_problem` ,
`newsletter_queue` ,
`newsletter_queue_link` ,
`newsletter_queue_store_link` ,
`newsletter_subscriber` ,
`newsletter_template` ,
`paygate_authorizenet_debug` ,
`paypaluk_api_debug` ,
`paypal_api_debug` ,
`paypal_settlement_report` ,
`paypal_settlement_report_row` ,
`poll` ,
`poll_answer` ,
`poll_store` ,
`poll_vote` ,
`product_alert_price` ,
`product_alert_stock` ,
`rating` ,
`rating_entity` ,
`rating_option` ,
`rating_option_vote` ,
`rating_option_vote_aggregated` ,
`rating_store` ,
`rating_title` ,
`report_compared_product_index` ,
`report_event` ,
`report_event_types` ,
`report_viewed_product_index` ,
`review` ,
`review_detail` ,
`review_entity` ,
`review_entity_summary` ,
`review_status` ,
`review_store` ,
`salesrule` ,
`salesrule_coupon` ,
`salesrule_coupon_usage` ,
`salesrule_customer` ,
`salesrule_label` ,
`sales_bestsellers_aggregated_daily` ,
`sales_bestsellers_aggregated_monthly` ,
`sales_bestsellers_aggregated_yearly` ,
`sales_billing_agreement` ,
`sales_billing_agreement_order` ,
`sales_flat_creditmemo` ,
`sales_flat_creditmemo_comment` ,
`sales_flat_creditmemo_grid` ,
`sales_flat_creditmemo_item` ,
`sales_flat_invoice` ,
`sales_flat_invoice_comment` ,
`sales_flat_invoice_grid` ,
`sales_flat_invoice_item` ,
`sales_flat_order` ,
`sales_flat_order_address` ,
`sales_flat_order_grid` ,
`sales_flat_order_item` ,
`sales_flat_order_payment` ,
`sales_flat_order_status_history` ,
`sales_flat_quote` ,
`sales_flat_quote_address` ,
`sales_flat_quote_address_item` ,
`sales_flat_quote_item` ,
`sales_flat_quote_item_option` ,
`sales_flat_quote_payment` ,
`sales_flat_quote_shipping_rate` ,
`sales_flat_shipment` ,
`sales_flat_shipment_comment` ,
`sales_flat_shipment_grid` ,
`sales_flat_shipment_item` ,
`sales_flat_shipment_track` ,
`sales_invoiced_aggregated` ,
`sales_invoiced_aggregated_order` ,
`sales_order_aggregated_created` ,
`sales_order_tax` ,
`sales_payment_transaction` ,
`sales_recurring_profile` ,
`sales_recurring_profile_order` ,
`sales_refunded_aggregated` ,
`sales_refunded_aggregated_order` ,
`sales_shipping_aggregated` ,
`sales_shipping_aggregated_order` ,
`sendfriend_log` ,
`shipping_tablerate` ,
`sitemap` ,
`tag` ,
`tag_properties` ,
`tag_relation` ,
`tag_summary` ,
`tax_calculation` ,
`tax_calculation_rate` ,
`tax_calculation_rate_title` ,
`tax_calculation_rule` ,
`tax_class` ,
`tax_order_aggregated_created` ,
`weee_discount` ,
`weee_tax` ,
`widget` ,
`widget_instance` ,
`widget_instance_page` ,
`widget_instance_page_layout` ,
`wishlist` ,
`wishlist_item` ;
Lösung:
Gehe in das SQL-Abfragefenster und kopiere die SQL-Abfrage dort hinein:
Dort vor die Abfrage SET FOREIGN_KEY_CHECKS=0; und nach der Abfrage SET FOREIGN_KEY_CHECKS=1; angeben, dann sollte es funktionieren:
SET FOREIGN_KEY_CHECKS=0;
DROP TABLE ...
SET FOREIGN_KEY_CHECKS=1;
Danach sollte man mit F5 das Browserfenster neu laden und die Tabellen sollten verschwunden sein.
Das Problem rührt wohl von der InnoDB-Engine her.
#1 - ohcibi schrieb:
15.06.2010 16:34 -
Das ist kein "Problem" sondern ein Constraint (Englisch für Einschränkung), welche es in MyISAM nicht gibt und deswegen dort nie solche Probleme auftreten. In diesem Fall ist es eine normale Fremdschluesseleinschraenkung, die dir einfach verbietet Datensaetze zu loeschen fuer die es noch Fremdschluesselreferenzen in Datensaetzen einer Tabelle gibt. Siehe auch: http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html
#1.1 - Chris Gurk 15.06.2010 16:54 -
Ja, da hast Du vollkommen Recht, es handelt sich um eine Einschränkung. Für den Benutzer ist es aber im ersten Moment ein Problem, denn er kann die Tabellen nicht wie gewohnt mit der Maus Klick-Klack-Klick in die Checkboxen per phpMyAdmin löschen. Per SQL-Abfragefenster und mit der Anweisung "SET FOREIGN_KEY_CHECKS=0;" sollte es dann jedoch gehen (Problem mit der Beschränkung gelöst).
#2 - Kai 05.07.2010 10:50 -
Aber vielleicht lieber "DROP TABLE ..." statt "DROP DATABASE ..."
#2.1 - Chris Gurk 18.07.2010 23:26 -
Ja, vollkommen korrekt. DROP TABLE sollte es besser heißen. Habe es oben im Beitrag geändert.
Bookmarks:
Delicious Facebook Google Yahoo Mr. Wong Linkarena Digg