Hi, welcome to Word of Mike, my little corner of the internet. I am a Software/Web Developer working in North Yorkshire. I mainly write about programming but my other passion is politics so beware. (click to hide)

2013-04-30 11:03:31 UTC

Removing/ Rewriting All Indexes in Rails Migration


I had a situation today where I had to sort out a mess of indexes. I wanted to clear all current indexes on our has_and_belongs_to_many (or has_many through) join tables and add only the composite unique index on the foreign keys.

I found that ActiveRecord has an IndexDefinition type struct and that 

ActiveRecord::Base.connection.indexes(:table_name)
gives us all our indexes for the given table.

The next problem was that I'm adding a unique key to a table which didn't previously have one, so there could be all kinds of mess in that table, duplicate rows and such, so we need to clear that. I found another SO post which I modified a bit to come up with the migration code:

# Get rid of duplicates rows that will stop us adding our index
execute "CREATE TABLE #{t}2 LIKE #{t}"
execute "INSERT INTO #{t}2 SELECT * FROM #{t} GROUP BY #{c.join(", ")}"
execute "DROP TABLE #{t}"
execute "RENAME TABLE #{t}2 TO #{t}"

So, using these techniques, my complete migration looked something like:

class AddIndexesOnJoinTables < ActiveRecord::Migration
  def change
    tables_columns = {
      table1: [:foreign_key_1, :foreign_key_2],
      table2: [:foreign_key_3, :foreign_key_4],
      table3: [:foreign_key_5, :foreign_key_6],
      table4: [:foreign_key_7, :foreign_key_8]
    }
    tables_columns.each do |t,c|

      # Get rid of duplicates rows that will stop us adding our index
      execute "CREATE TABLE #{t}2 LIKE #{t}"
      execute "INSERT INTO #{t}2 SELECT * FROM #{t} GROUP BY #{c.join(", ")}"
      execute "DROP TABLE #{t}"
      execute "RENAME TABLE #{t}2 TO #{t}"

      # Get existing indexes and remove them
      indices = indexes(t)
      indices.each do |i|
        remove_index(t, i.columns)
      end
      # Add our new one
      begin
        add_index(t, c, unique: true)
      rescue ArgumentError => e
        if e.message.include? "the limit is 64 characters"
          add_index(t,c, unique: true, name: Time.now.to_i)
        end
      end
    end
  end
end

The only other addition I haven't mentioned yet is the little begin rescue end block. For whatever reason, Rails and the mysql2 adapter still generate and try to add indexes with names that are far too long, resulting in the exception:

An error has occurred, all later migrations canceled:

#<ArgumentError: Index name 'index_medical_conditions_students_on_student_id_and_medical_condition_id' on table 'medical_conditions_students' is too long; the limit is 64 characters>

The solution is to pass the add_index method a name parameter, but seeing as we're generating all our indexes in a loop, this is a pain in the arse. We'll either need to name them all, or introduce some extra logic. I didn't want to do either of those so I fudged it a little. The rescue block catches the ArgumentError, checks that it's because of our index length (by looking at the message) and if so, adds the index again using a timestamp as the index name (to avoid conflicts). Not perfect by any means but seems to do the job for me.

Tidy!