How to use migrations to convert engine type to InnoDB?

Rails
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

On Jan 30, 2007, at 10:22 AM, Marston A. wrote:

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 B. http://agileconsultingllc.com
[email protected]

3

Rob,

Greatly appreciate, this is what I needed. Thanks.