I’m writing this down to warn you and to burn it into my memory.<\/p>\n
For one of my projects I am using MySQL with InnoDB tables.\u00c2\u00a0 Which are great, by the way.\u00c2\u00a0 So I have this really huge table – about 8 GBytes with few million rows – that I want to empty.\u00c2\u00a0 Without much thinking I start Eventually, in about one hour, I got bored.\u00c2\u00a0 So, I terminate the query.\u00c2\u00a0 That was without much thinking too.\u00c2\u00a0 Can you guess why?<\/p>\n Right.\u00c2\u00a0 MySQL uses transactions for InnoDB tables.\u00c2\u00a0 Interrupted query triggered a That made me angry.\u00c2\u00a0 So, I decided that I should restart MySQL service.\u00c2\u00a0 I thought that killing MySQL process will make it forget about the whole In fact, it made the situation even worth.\u00c2\u00a0 MySQL service refused to start until the Of course, while it was working, I was searching for the solution on the Web, mostly in MySQL manuals and forums.\u00c2\u00a0 I came across a couple of other requests that were exactly like mine, but there were no answers.\u00c2\u00a0 I then went to #mysql channel on IRC, where the guys told me that they feel sorry for me (how kind), but there was no solution, except to wait.\u00c2\u00a0 Dang!<\/p>\n After the service came up, I simply removed the table ( I’m writing this down to warn you and to burn it into my memory. For one of my projects I am using MySQL with InnoDB tables.\u00c2\u00a0 Which are great, by the way.\u00c2\u00a0 So I have this really huge table – about 8 GBytes with few million rows – that I want to empty.\u00c2\u00a0 Without much … Continue reading MySQL misunderstanding<\/span><\/a><\/p>\n\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_feature_clip_id":0,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2},"jetpack_post_was_ever_published":false,"_links_to":"","_links_to_target":""},"categories":[1],"tags":[],"keyring_services":[],"class_list":["post-10028","post","type-post","status-publish","format-standard","hentry","category-general"],"aioseo_notices":[],"aioseo_head":"\n\t\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\tDELETE FROM mytable<\/code>.\u00c2\u00a0 And I wait.\u00c2\u00a0 And wait.\u00c2\u00a0 And wait.\u00c2\u00a0 Then I go for a coffee.\u00c2\u00a0 I come back and wait.\u00c2\u00a0 Then I wait for some more.<\/p>\nrollback<\/code> operation.\u00c2\u00a0 So, not only I just spent an hour waiting for the stuff to get deleted, but it won’t be deleted afterall.<\/p>\nrollback<\/code> thing.\u00c2\u00a0 It didn’t work.<\/p>\nrollback<\/code> would finish.\u00c2\u00a0 Or maybe it was repairing table, I am not sure.\u00c2\u00a0 But it seemed more like a rollback<\/code>.\u00c2\u00a0 So for the next thirty minutes, it was fixing stuff, while none of other projects could connect to the database.\u00c2\u00a0 Other projects were mostly websites that I host on my home server.\u00c2\u00a0 Very nice.<\/p>\nDROP TABLE mytable<\/code>) and re-created it empty (CREATE TABLE mytable (...)<\/code>).\u00c2\u00a0\u00c2\u00a0 I also found another way of emptying the table, which is TRUNCATE TABLE mytable<\/code>, although it would behave exactly the same as DELETE FROM TABLE mytable<\/code> on my version of MySQL (4.something).<\/p>\n\n","protected":false},"excerpt":{"rendered":"\n