{"action":"created","comment":{"url":"https://api.github.com/repos/iFixit/itbrokeand.ifixit.com/pulls/comments/36438919","id":36438919,"diff_hunk":"@@ -0,0 +1,80 @@\n+---\n+layout: with-comments\n+title: \"Renaming MySQL Tables With Zero Downtime\"\n+author: Marc Zych\n+author_url: https://github.com/marczych\n+summary: We found a way to rename MySQL tables with zero downtime by using\n+         updatable views and atomic rename table commands.\n+---\n+\n+\n+Sometimes you end up with tables in your database schema that just aren't named what you want anymore.\n+At first glance, renaming them without any downtime isn't feasible because updating the code that uses the table at the same instant that the table is renamed isn't easily accomplished.\n+\n+We recently came across a few database tables in our schema that were begging to be renamed.\n+Bringing our site down for several minutes to run the migrations and deploy the updated code was unacceptable so I started investigating to come up with a solution.\n+\n+In this example, we will be renaming `people` to `users`.\n+\n+# Using a View\n+\n+The first thing I came up with was using [MySQL Views] to alias the table so it would be accessible by both names (`people` and `users`).\n+I came to find out that [many MySQL Views are updatable and insertable][updatable views]:\n+\n+> Some views are updatable and references to them can be used to specify tables to be updated in data change statements. That is, you can use them in statements such as UPDATE, DELETE, or INSERT to update the contents of the underlying table.\n+\n+This is super cool because it means that _all_ of the queries, reads and writes, can be done against the new name.\n+Here we create a view with the new name that we want to end up with:\n+\n+{% highlight mysql %}\n+CREATE VIEW `users` AS SELECT * FROM `people`;\n+{% endhighlight %}\n+\n+# Updating Queries in Code\n+\n+Now that we have a view that acts just like a proper table, we can deploy the code that updates all queries to reference `users` instead of `people`.","path":"_posts/2015-08-06-renaming-tables-with-zero-downtime.md","position":35,"original_position":35,"commit_id":"2081ec94c8e8af547a9fb95cb6758089ce036ba3","original_commit_id":"2081ec94c8e8af547a9fb95cb6758089ce036ba3","user":{"login":"andyg0808","id":1283490,"avatar_url":"https://avatars.githubusercontent.com/u/1283490?v=3","gravatar_id":"","url":"https://api.github.com/users/andyg0808","html_url":"https://github.com/andyg0808","followers_url":"https://api.github.com/users/andyg0808/followers","following_url":"https://api.github.com/users/andyg0808/following{/other_user}","gists_url":"https://api.github.com/users/andyg0808/gists{/gist_id}","starred_url":"https://api.github.com/users/andyg0808/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/andyg0808/subscriptions","organizations_url":"https://api.github.com/users/andyg0808/orgs","repos_url":"https://api.github.com/users/andyg0808/repos","events_url":"https://api.github.com/users/andyg0808/events{/privacy}","received_events_url":"https://api.github.com/users/andyg0808/received_events","type":"User","site_admin":false},"body":"Maybe \"Since the view acts just like a proper table, we can now deploy the code...\"? Avoids making it sound like it's something special about our view that makes it act like a table.","created_at":"2015-08-06T17:03:59Z","updated_at":"2015-08-06T17:03:59Z","html_url":"https://github.com/iFixit/itbrokeand.ifixit.com/pull/30#discussion_r36438919","pull_request_url":"https://api.github.com/repos/iFixit/itbrokeand.ifixit.com/pulls/30","_links":{"self":{"href":"https://api.github.com/repos/iFixit/itbrokeand.ifixit.com/pulls/comments/36438919"},"html":{"href":"https://github.com/iFixit/itbrokeand.ifixit.com/pull/30#discussion_r36438919"},"pull_request":{"href":"https://api.github.com/repos/iFixit/itbrokeand.ifixit.com/pulls/30"}}},"pull_request":{"url":"https://api.github.com/repos/iFixit/itbrokeand.ifixit.com/pulls/30","id":41646538,"html_url":"https://github.com/iFixit/itbrokeand.ifixit.com/pull/30","diff_url":"https://github.com/iFixit/itbrokeand.ifixit.com/pull/30.diff","patch_url":"https://github.com/iFixit/itbrokeand.ifixit.com/pull/30.patch","issue_url":"https://api.github.com/repos/iFixit/itbrokeand.ifixit.com/issues/30","number":30,"state":"open","locked":false,"title":"Post: Renaming MySQL Tables With Zero Downtime","user":{"login":"marczych","id":1094445,"avatar_url":"https://avatars.githubusercontent.com/u/1094445?v=3","gravatar_id":"","url":"https://api.github.com/users/marczych","html_url":"https://github.com/marczych","followers_url":"https://api.github.com/users/marczych/followers","following_url":"https://api.github.com/users/marczych/following{/other_user}","gists_url":"https://api.github.com/users/marczych/gists{/gist_id}","starred_url":"https://api.github.com/users/marczych/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/marczych/subscriptions","organizations_url":"https://api.github.com/users/marczych/orgs","repos_url":"https://api.github.com/users/marczych/repos","events_url":"https://api.github.com/users/marczych/events{/privacy}","received_events_url":"https://api.github.com/users/marczych/received_events","type":"User","site_admin":false},"body":"I've been meaning to write a post about how we renamed a table without\r\nany downtime for a month or two now and finally got around to it\r\ntonight. It's still a little rough so I'd appreciate any and all\r\nfeedback!","created_at":"2015-08-05T06:18:44Z","updated_at":"2015-08-06T17:03:59Z","closed_at":null,"merged_at":null,"merge_commit_sha":"e359bbfadfe67cbe1b47225f2a7ee857e714c5fb","assignee":null,"milestone":null,"commits_url":"https://api.github.com/repos/iFixit/itbrokeand.ifixit.com/pulls/30/commits","review_comments_url":"https://api.github.com/repos/iFixit/itbrokeand.ifixit.com/pulls/30/comments","review_comment_url":"https://api.github.com/repos/iFixit/itbrokeand.ifixit.com/pulls/comments{/number}","comments_url":"https://api.github.com/repos/iFixit/itbrokeand.ifixit.com/issues/30/comments","statuses_url":"https://api.github.com/repos/iFixit/itbrokeand.ifixit.com/statuses/2081ec94c8e8af547a9fb95cb6758089ce036ba3","head":{"label":"iFixit:post--renaming-tables","ref":"post--renaming-tables","sha":"2081ec94c8e8af547a9fb95cb6758089ce036ba3","user":{"login":"iFixit","id":318148,"avatar_url":"https://avatars.githubusercontent.com/u/318148?v=3","gravatar_id":"","url":"https://api.github.com/users/iFixit","html_url":"https://github.com/iFixit","followers_url":"https://api.github.com/users/iFixit/followers","following_url":"https://api.github.com/users/iFixit/following{/other_user}","gists_url":"https://api.github.com/users/iFixit/gists{/gist_id}","starred_url":"https://api.github.com/users/iFixit/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/iFixit/subscriptions","organizations_url":"https://api.github.com/users/iFixit/orgs","repos_url":"https://api.github.com/users/iFixit/repos","events_url":"https://api.github.com/users/iFixit/events{/privacy}","received_events_url":"https://api.github.com/users/iFixit/received_events","type":"Organization","site_admin":false},"repo":{"id":5125959,"name":"itbrokeand.ifixit.com","full_name":"iFixit/itbrokeand.ifixit.com","owner":{"login":"iFixit","id":318148,"avatar_url":"https://avatars.githubusercontent.com/u/318148?v=3","gravatar_id":"","url":"https://api.github.com/users/iFixit","html_url":"https://github.com/iFixit","followers_url":"https://api.github.com/users/iFixit/followers","following_url":"https://api.github.com/users/iFixit/following{/other_user}","gists_url":"https://api.github.com/users/iFixit/gists{/gist_id}","starred_url":"https://api.github.com/users/iFixit/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/iFixit/subscriptions","organizations_url":"https://api.github.com/users/iFixit/orgs","repos_url":"https://api.github.com/users/iFixit/repos","events_url":"https://api.github.com/users/iFixit/events{/privacy}","received_events_url":"https://api.github.com/users/iFixit/received_events","type":"Organization","site_admin":false},"private":false,"html_url":"https://github.com/iFixit/itbrokeand.ifixit.com","description":"iFixit Developer Blog: Sometimes things break, and then we've got to fix them.","fork":false,"url":"https://api.github.com/repos/iFixit/itbrokeand.ifixit.com","forks_url":"https://api.github.com/repos/iFixit/itbrokeand.ifixit.com/forks","keys_url":"https://api.github.com/repos/iFixit/itbrokeand.ifixit.com/keys{/key_id}","collaborators_url":"https://api.github.com/repos/iFixit/itbrokeand.ifixit.com/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/iFixit/itbrokeand.ifixit.com/teams","hooks_url":"https://api.github.com/repos/iFixit/itbrokeand.ifixit.com/hooks","issue_events_url":"https://api.github.com/repos/iFixit/itbrokeand.ifixit.com/issues/events{/number}","events_url":"https://api.github.com/repos/iFixit/itbrokeand.ifixit.com/events","assignees_url":"https://api.github.com/repos/iFixit/itbrokeand.ifixit.com/assignees{/user}","branches_url":"https://api.github.com/repos/iFixit/itbrokeand.ifixit.com/branches{/branch}","tags_url":"https://api.github.com/repos/iFixit/itbrokeand.ifixit.com/tags","blobs_url":"https://api.github.com/repos/iFixit/itbrokeand.ifixit.com/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/iFixit/itbrokeand.ifixit.com/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/iFixit/itbrokeand.ifixit.com/git/refs{/sha}","trees_url":"https://api.github.com/repos/iFixit/itbrokeand.ifixit.com/git/trees{/sha}","statuses_url":"https://api.github.com/repos/iFixit/itbrokeand.ifixit.com/statuses/{sha}","languages_url":"https://api.github.com/repos/iFixit/itbrokeand.ifixit.com/languages","stargazers_url":"https://api.github.com/repos/iFixit/itbrokeand.ifixit.com/stargazers","contributors_url":"https://api.github.com/repos/iFixit/itbrokeand.ifixit.com/contributors","subscribers_url":"https://api.github.com/repos/iFixit/itbrokeand.ifixit.com/subscribers","subscription_url":"https://api.github.com/repos/iFixit/itbrokeand.ifixit.com/subscription","commits_url":"https://api.github.com/repos/iFixit/itbrokeand.ifixit.com/commits{/sha}","git_commits_url":"https://api.github.com/repos/iFixit/itbrokeand.ifixit.com/git/commits{/sha}","comments_url":"https://api.github.com/repos/iFixit/itbrokeand.ifixit.com/comments{/number}","issue_comment_url":"https://api.github.com/repos/iFixit/itbrokeand.ifixit.com/issues/comments{/number}","contents_url":"https://api.github.com/repos/iFixit/itbrokeand.ifixit.com/contents/{+path}","compare_url":"https://api.github.com/repos/iFixit/itbrokeand.ifixit.com/compare/{base}...{head}","merges_url":"https://api.github.com/repos/iFixit/itbrokeand.ifixit.com/merges","archive_url":"https://api.github.com/repos/iFixit/itbrokeand.ifixit.com/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/iFixit/itbrokeand.ifixit.com/downloads","issues_url":"https://api.github.com/repos/iFixit/itbrokeand.ifixit.com/issues{/number}","pulls_url":"https://api.github.com/repos/iFixit/itbrokeand.ifixit.com/pulls{/number}","milestones_url":"https://api.github.com/repos/iFixit/itbrokeand.ifixit.com/milestones{/number}","notifications_url":"https://api.github.com/repos/iFixit/itbrokeand.ifixit.com/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/iFixit/itbrokeand.ifixit.com/labels{/name}","releases_url":"https://api.github.com/repos/iFixit/itbrokeand.ifixit.com/releases{/id}","created_at":"2012-07-20T17:09:34Z","updated_at":"2015-01-20T22:01:27Z","pushed_at":"2015-08-06T16:43:48Z","git_url":"git://github.com/iFixit/itbrokeand.ifixit.com.git","ssh_url":"git@github.com:iFixit/itbrokeand.ifixit.com.git","clone_url":"https://github.com/iFixit/itbrokeand.ifixit.com.git","svn_url":"https://github.com/iFixit/itbrokeand.ifixit.com","homepage":"http://itbrokeand.ifixit.com/","size":887,"stargazers_count":3,"watchers_count":3,"language":"CSS","has_issues":true,"has_downloads":true,"has_wiki":true,"has_pages":true,"forks_count":3,"mirror_url":null,"open_issues_count":2,"forks":3,"open_issues":2,"watchers":3,"default_branch":"gh-pages"}},"base":{"label":"iFixit:gh-pages","ref":"gh-pages","sha":"6865725d1c63f05572c604a2680da33073007ddf","user":{"login":"iFixit","id":318148,"avatar_url":"https://avatars.githubusercontent.com/u/318148?v=3","gravatar_id":"","url":"https://api.github.com/users/iFixit","html_url":"https://github.com/iFixit","followers_url":"https://api.github.com/users/iFixit/followers","following_url":"https://api.github.com/users/iFixit/following{/other_user}","gists_url":"https://api.github.com/users/iFixit/gists{/gist_id}","starred_url":"https://api.github.com/users/iFixit/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/iFixit/subscriptions","organizations_url":"https://api.github.com/users/iFixit/orgs","repos_url":"https://api.github.com/users/iFixit/repos","events_url":"https://api.github.com/users/iFixit/events{/privacy}","received_events_url":"https://api.github.com/users/iFixit/received_events","type":"Organization","site_admin":false},"repo":{"id":5125959,"name":"itbrokeand.ifixit.com","full_name":"iFixit/itbrokeand.ifixit.com","owner":{"login":"iFixit","id":318148,"avatar_url":"https://avatars.githubusercontent.com/u/318148?v=3","gravatar_id":"","url":"https://api.github.com/users/iFixit","html_url":"https://github.com/iFixit","followers_url":"https://api.github.com/users/iFixit/followers","following_url":"https://api.github.com/users/iFixit/following{/other_user}","gists_url":"https://api.github.com/users/iFixit/gists{/gist_id}","starred_url":"https://api.github.com/users/iFixit/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/iFixit/subscriptions","organizations_url":"https://api.github.com/users/iFixit/orgs","repos_url":"https://api.github.com/users/iFixit/repos","events_url":"https://api.github.com/users/iFixit/events{/privacy}","received_events_url":"https://api.github.com/users/iFixit/received_events","type":"Organization","site_admin":false},"private":false,"html_url":"https://github.com/iFixit/itbrokeand.ifixit.com","description":"iFixit Developer Blog: Sometimes things break, and then we've got to fix them.","fork":false,"url":"https://api.github.com/repos/iFixit/itbrokeand.ifixit.com","forks_url":"https://api.github.com/repos/iFixit/itbrokeand.ifixit.com/forks","keys_url":"https://api.github.com/repos/iFixit/itbrokeand.ifixit.com/keys{/key_id}","collaborators_url":"https://api.github.com/repos/iFixit/itbrokeand.ifixit.com/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/iFixit/itbrokeand.ifixit.com/teams","hooks_url":"https://api.github.com/repos/iFixit/itbrokeand.ifixit.com/hooks","issue_events_url":"https://api.github.com/repos/iFixit/itbrokeand.ifixit.com/issues/events{/number}","events_url":"https://api.github.com/repos/iFixit/itbrokeand.ifixit.com/events","assignees_url":"https://api.github.com/repos/iFixit/itbrokeand.ifixit.com/assignees{/user}","branches_url":"https://api.github.com/repos/iFixit/itbrokeand.ifixit.com/branches{/branch}","tags_url":"https://api.github.com/repos/iFixit/itbrokeand.ifixit.com/tags","blobs_url":"https://api.github.com/repos/iFixit/itbrokeand.ifixit.com/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/iFixit/itbrokeand.ifixit.com/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/iFixit/itbrokeand.ifixit.com/git/refs{/sha}","trees_url":"https://api.github.com/repos/iFixit/itbrokeand.ifixit.com/git/trees{/sha}","statuses_url":"https://api.github.com/repos/iFixit/itbrokeand.ifixit.com/statuses/{sha}","languages_url":"https://api.github.com/repos/iFixit/itbrokeand.ifixit.com/languages","stargazers_url":"https://api.github.com/repos/iFixit/itbrokeand.ifixit.com/stargazers","contributors_url":"https://api.github.com/repos/iFixit/itbrokeand.ifixit.com/contributors","subscribers_url":"https://api.github.com/repos/iFixit/itbrokeand.ifixit.com/subscribers","subscription_url":"https://api.github.com/repos/iFixit/itbrokeand.ifixit.com/subscription","commits_url":"https://api.github.com/repos/iFixit/itbrokeand.ifixit.com/commits{/sha}","git_commits_url":"https://api.github.com/repos/iFixit/itbrokeand.ifixit.com/git/commits{/sha}","comments_url":"https://api.github.com/repos/iFixit/itbrokeand.ifixit.com/comments{/number}","issue_comment_url":"https://api.github.com/repos/iFixit/itbrokeand.ifixit.com/issues/comments{/number}","contents_url":"https://api.github.com/repos/iFixit/itbrokeand.ifixit.com/contents/{+path}","compare_url":"https://api.github.com/repos/iFixit/itbrokeand.ifixit.com/compare/{base}...{head}","merges_url":"https://api.github.com/repos/iFixit/itbrokeand.ifixit.com/merges","archive_url":"https://api.github.com/repos/iFixit/itbrokeand.ifixit.com/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/iFixit/itbrokeand.ifixit.com/downloads","issues_url":"https://api.github.com/repos/iFixit/itbrokeand.ifixit.com/issues{/number}","pulls_url":"https://api.github.com/repos/iFixit/itbrokeand.ifixit.com/pulls{/number}","milestones_url":"https://api.github.com/repos/iFixit/itbrokeand.ifixit.com/milestones{/number}","notifications_url":"https://api.github.com/repos/iFixit/itbrokeand.ifixit.com/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/iFixit/itbrokeand.ifixit.com/labels{/name}","releases_url":"https://api.github.com/repos/iFixit/itbrokeand.ifixit.com/releases{/id}","created_at":"2012-07-20T17:09:34Z","updated_at":"2015-01-20T22:01:27Z","pushed_at":"2015-08-06T16:43:48Z","git_url":"git://github.com/iFixit/itbrokeand.ifixit.com.git","ssh_url":"git@github.com:iFixit/itbrokeand.ifixit.com.git","clone_url":"https://github.com/iFixit/itbrokeand.ifixit.com.git","svn_url":"https://github.com/iFixit/itbrokeand.ifixit.com","homepage":"http://itbrokeand.ifixit.com/","size":887,"stargazers_count":3,"watchers_count":3,"language":"CSS","has_issues":true,"has_downloads":true,"has_wiki":true,"has_pages":true,"forks_count":3,"mirror_url":null,"open_issues_count":2,"forks":3,"open_issues":2,"watchers":3,"default_branch":"gh-pages"}},"_links":{"self":{"href":"https://api.github.com/repos/iFixit/itbrokeand.ifixit.com/pulls/30"},"html":{"href":"https://github.com/iFixit/itbrokeand.ifixit.com/pull/30"},"issue":{"href":"https://api.github.com/repos/iFixit/itbrokeand.ifixit.com/issues/30"},"comments":{"href":"https://api.github.com/repos/iFixit/itbrokeand.ifixit.com/issues/30/comments"},"review_comments":{"href":"https://api.github.com/repos/iFixit/itbrokeand.ifixit.com/pulls/30/comments"},"review_comment":{"href":"https://api.github.com/repos/iFixit/itbrokeand.ifixit.com/pulls/comments{/number}"},"commits":{"href":"https://api.github.com/repos/iFixit/itbrokeand.ifixit.com/pulls/30/commits"},"statuses":{"href":"https://api.github.com/repos/iFixit/itbrokeand.ifixit.com/statuses/2081ec94c8e8af547a9fb95cb6758089ce036ba3"}}}}