How to use migrations to convert engine type to InnoDB?

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:

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

Rob,

Greatly appreciate, this is what I needed. Thanks.