How to use migrations to convert engine type to InnoDB?

rubyonrails-talk
1

I've been looking through some references but can't seem to find the right syntax and commands.

I basically just want to have a migration to convert my db and all my tables to InnoDB. Does anyone know a tutorial or the migration syntax to do this? Thanks in advance :slight_smile:

2

http://dev.mysql.com/doc/refman/5.0/en/storage-engines.html

To convert a table from one storage engine to another, use an ALTER TABLE statement that indicates the new engine:

ALTER TABLE t ENGINE = INNODB;

You can specify the engine in a migration for creating a table as:

class CreateUsersTable < ActiveRecord::Migration    def self.up      create_table(:users,                   :options => 'ENGINE=InnoDB') do |t|        t.column :first_name, :string, :limit => 30, :null => false        t.column :last_name, :string, :limit => 30, :null => false        t.column :phone, :string, :limit => 24      end    end

   def self.down      drop_table(:users)    end end

If you need to make a migration to change the engine later, you can do something like this:

class ChangeUsersTableEngine < ActiveRecord::Migration    def self.table_engine(table, engine='InnoDB')      execute "ALTER TABLE `#{table}` ENGINE = #{engine}"    end

   def self.up      table_engine :users, 'InnoDB'    end

   def self.down      table_engine :users, 'MyISAM'    end end

You probably want to change the default storage engine in your my.cnf file.

-Rob

Rob Biedenharn http://agileconsultingllc.com Rob@AgileConsultingLLC.com

3

Rob,

Greatly appreciate, this is what I needed. Thanks.