CODEWITHSUNDEEP
ruby-on-railsrubyactiverecord
randombits
randombits

Reputation: 48490

Ruby on Rails migration, change table to MyISAM

How does one create a Rails migration properly so that a table gets changed to MyISAM in MySQL? It is currently InnoDB. Running a raw execute statement will change the table, but it won't update db/schema.rb, so when the table is recreated in a testing environment, it goes back to InnoDB and my fulltext searches fail.

How do I go about changing/adding a migration so that the existing table gets modified to MyISAM and schema.rb gets updated so my db and respective test db get updated accordingly?

Upvotes: 5

Views: 4162

Answers (3)

rwold
rwold

Reputation: 2476

You can run any sql in migrations. This worked for me:

class ChangeMapOnlyUsersEngine < ActiveRecord::Migration[5.1]
  def change
    MyModel.connection.execute("ALTER TABLE my_models ENGINE = 'MyISAM';")
  end
end

When I did this in the other direction (InnoDB -> MyISAM) it worked fine, without loss of data so I don't think it's neccesary to create temporary tables or similar. Note that MyISAM doesn't support transactions, so any tests against the database for a corresponding ActiveRecord model will be persisted, with a risk of test pollution.

Upvotes: 0

Dex
Dex

Reputation: 12759

I didn't find a great way to do this. You could change your schema.rb like someone suggested and then run: rake db:schema:load, however, this will overwrite your data.

The way I did it was (assuming you are trying to convert a table called books):

  1. Save the existing data from the CLI: CREATE TABLE tmp SELECT * FROM books;

  2. In your new migration file, drop the books table and recreate it with :options => "ENGINE=MyISAM" like someone said in the comment

  3. Copy the contents back: INSERT INTO books SELECT * FROM tmp

Upvotes: 5

Elad Meidar
Elad Meidar

Reputation: 814

i think that if you change your schema format (config.active_record.schema_format) from :ruby to :sql, all sql will be saved there.

i'd do some tests on a fresh app first if i were you, see how it works.

Upvotes: 3

Related Questions