# How to use migrations to convert engine type to InnoDB?

**URL:** <https://www.ruby-forum.com/t/how-to-use-migrations-to-convert-engine-type-to-innodb/83896>\
**Category:** Rails\
**Created:** [January 30, 2007, 6:13pm UTC](https://www.ruby-forum.com/t/how-to-use-migrations-to-convert-engine-type-to-innodb/83896 "2007-01-30T18:13:20Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![marstoni](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/e9bcb4/32.png) [@marstoni](https://www.ruby-forum.com/u/marstoni)\
**Post date:** [January 30, 2007, 6:13pm UTC](https://www.ruby-forum.com/t/how-to-use-migrations-to-convert-engine-type-to-innodb/83896/1 "2007-01-30T18:13:20Z")

</div>

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 🙂

---

<div class="post-metadata">

**Author:** ![marstoni](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/e9bcb4/32.png) [@marstoni](https://www.ruby-forum.com/u/marstoni)\
**Post date:** [January 30, 2007, 6:46pm UTC](https://www.ruby-forum.com/t/how-to-use-migrations-to-convert-engine-type-to-innodb/83896/2 "2007-01-30T18:46:28Z")

</div>

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 🙂

[http://dev.mysql.com/doc/refman/5.0/en/storage-engines.html](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](http://agileconsultingllc.com)  
removed\_email\_address@domain.invalid

---

<div class="post-metadata">

**Author:** ![marstoni](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/e9bcb4/32.png) [@marstoni](https://www.ruby-forum.com/u/marstoni)\
**Post date:** [January 30, 2007, 8:27pm UTC](https://www.ruby-forum.com/t/how-to-use-migrations-to-convert-engine-type-to-innodb/83896/3 "2007-01-30T20:27:43Z")

</div>

Rob,

Greatly appreciate, this is what I needed. Thanks.