Loren Ipsum: Tales of a New Dev

Large Migrations: Rails, MySQL, and A Big Database

Before starting my first (and current) job as a web developer, I had no experience with older databases. My experience mostly came from generating migrations in Rails for a relatively small database.

I have now gained some solid experience working with a big database, and I can say that they can be quite ornery and problematic.

This blog post centers around a particular issue I was tasked with addressing, namely determining how to do a Rails migration with our biggest table.

What’s a Rails Migration?

Some of you reading may have worked with Rails, which means you’ve probably done this in one fashion or another:

bin/rails generate migration AddSomethingCleverToATable something_clever:string 

And then:

rails db:migrate

If you’re thinking…

Allow me to explain (briefly).

rails generate migration creates an ActiveRecord::Migration that gives directions on how to change a schema in the database, and db:migrate actually carries out that change.

For example, say I have a table full of 90’s shows. This table has many columns, among them is a column for a TV channel. But, if I’m honest with myself, I don’t even know what a TV channel is anymore, so I decide to remove it from the table. I could go about this by creating a simple migration like this:

bin/rails generate migration RemoveChannelFromShows channel:integer

This lovely little command generates a class and a change method describing the removal of the channel column from the table of shows:

class RemoveChannelFromShows < ActiveRecord::Migration
  def change
    remove_column :shows, :channel, :integer
  end
end

Rails allows developers to write all this code in Ruby, but ultimately converts it into raw SQL to make changes to the database. So, by running rails db:migrate the magic of ActiveRecord::Migration actually changes the schema, and the channel column in my shows table will be removed in the database.

Problem: Your table has a bagillion records and you need to change something

Let’s say that my parents let me watch A LOT of TV and my shows table is a really big table, but I still want to remove that channel column. I could use a standard Rails migration, but I discovered that whilst running a migration, MySQL under the hood actually creates a temporary copy of the original table that can be read while the original table is altered. This temporary copy is stored in a temporary file directory, but this causes a problem when that temporary directory doesn’t have enough space for the temporary copy. Simply put, if it doesn’t have the space to fit the copy of the table you want to change, you can’t run the migration.

Solution: Bypass Rails and Use Raw SQL

So in the midst of despair I turned to the only thing I could turn to: DOCUMENTATION!

What the documentation revealed is that MySQL 5.7.11 has the variable innodb_tmpdir that allows you to point temporary files that are created by an ALTER TABLE operation to another directory during a particular MySQL session. It looks a bit like this:

ALTER TABLE shows SET SESSION innodb_tmpdir=/the/path/to/another/dir
DROP COLUMN channel

Why is this a good solution?

This option is great because it’s not a permanent change, like setting the tmpdir global variable, because let’s face it, you may not have that option. It also allows your application to continue to read from the temporary table while the original table is altered, and ensures that your table won’t lock.

Thanks for reading, folks!

Want to know more? I got you covered:

Rails migrations

Session vs Global variables in MySQL

Being an Apprentice: what's the secret ingredient?

As some of you reading may know, before becoming a full time web developer (which happened quite recently I may add) I was an apprentice for Manhiem’s Technology Apprenticeship Pilot Program in Atlanta. This meant that for three whole months I was paid to learn, practice my coding skills, and pick the brains of some very talented programmers. I sincerely believe that starting my career first as an apprentice, and not a junior dev, has served me and will continue to serve me well into the future.

A Brief Intro to Eagerloading in Rails

ActiveRecord is the Rails component that takes care of all of your database needs. It’s pretty spiffy, but a little complex. I’m still uncovering what ActiveRecord can do, as well as how generally awesome it is. One of the cool things I discovered this week is the different types of eager loading.

Ruby’s Eigenclass

You may have heard of Ruby’s Eigenclass before, I know I had seen it in others’ code far sooner than knowing what it was actually called, and you may have, too.

Mob Programming

This past Thursday I had the opportunity to learn about mob programming. Mob programming is the concept of having an entire group or team (four or more) of developers writing code together with only one computer, one keyboard, and one task to accomplish. The mob designates a ‘driver,’ who simply writes down the code the mob decides is appropriate. The group also moves at the speed of the mob, with the intention that no one is left behind.

ActiveRecord Scope: What is it and how to use it

Since starting my adventure into programming the term scope has collected more meaning for me, specifically how variables cannot be called outside of their scope. This blog post isn’t about that type of scope though, rather it’s about the Rails Active Record scope.

#BlogBuddiesForLife

A few months ago I started a book club focused on giving newbies like myself a space to read books to help us develop and share experiences as they related to the readings. When I started this I didn’t fully realize what I was doing, or how much I would get out of it, but I feel that I’m not only a better developer for it, but also a better person.