Rails Ruby on Rails → 2.x

Comments and changes to this ticket

• You flagged this item as spam.
  

  Clemens Kofler July 10th, 2008 @ 01:57 PM

  • Tag set to “activerecord, mysql”

  I tried it out using Rails 2.1 with the following migration:

  class CreateUsers < ActiveRecord::Migration
    def self.up
      create_table(:users, :options => "ENGINE=MyISAM") do |t|
        t.string :name
  
        t.timestamps
      end
    end
  
    def self.down
      drop_table :users
    end
  end
  

  This produces the following SQL:

  CREATE TABLE `users` (
    `id` int(11) NOT NULL auto_increment,
    `name` varchar(255) default NULL,
    `created_at` datetime default NULL,
    `updated_at` datetime default NULL,
    PRIMARY KEY  (`id`)
  ) ENGINE=MyISAM DEFAULT CHARSET=utf8
  

  In short: I can't see the bug you described.

• You flagged this item as spam.
  

  Sebastian July 10th, 2008 @ 02:28 PM

  The problem is the SQL creating the schema_migrations table:

  CREATE TABLE `schema_migrations` (`version` varchar(255) NOT NULL) ENGINE=InnoDB
  

  executed before any of the migrations, if the schema_migrations table does not yet exist:

  $ mysql -u USER --password=PASS DB -e "show tables"
  $ rake db:migrate
  (in /path/to/rails/app)
  rake aborted!
  Mysql::Error: Can't create table './DB/schema_migrations.frm' (errno: 135): CREATE TABLE `schema_migrations` (`version` varchar(255) NOT NULL) ENGINE=InnoDB
  
  (See full trace by running task with --trace)
  $ 
  

  So, the schema_migrations table is created by activerecord and has nothing to do with migrations added to db/migrate.

  What's missing is a way of controlling which MySQL engine to use for the schema_migrations table.

  Better?

• You flagged this item as spam.
  

  Clemens Kofler July 10th, 2008 @ 02:49 PM

  Ah, I see. Yeah, that seems logical. I think I know how to fix this. I'll try to come up with a patch that you can then test.

• You flagged this item as spam.
  

  Clemens Kofler July 10th, 2008 @ 03:19 PM

  • Tag changed from “activerecord, mysql” to “activerecord, enhancement, mysql, patch”

  Here it goes.

  The patch doesn't break any exiting tests so I guess it should be fine. I have tested it and it looks good to me - of course, I have InnoDB enabled in my sys but all tables (including the schema_version) where created with MyISAM.

  What you need to do is just add the "engine" option in your database.yml and set it to MyISAM. So for example your data for the dev environment would look like this:

  development:
    adapter: mysql
    engine: MyISAM
    database: some_table
    username: some_user
    password: some_password
  

  This uses SET STORAGE_ENGINE when the connection is established (like encoding uses SET NAMES) to set the default storage engine to whatever you define. If you want to manually override it in a migration, you can still use create_table(:whatever, :options => "ENGINE=BDB") etc.

  Feel free to test-drive it and leave a comment if something doesn't work. Since it doesn't break any existing functionality (at least not according to the tests), I guess this could maybe make it into core.

  • MySQLConfigStorageEngine.diff 2.4 KB

• You flagged this item as spam.
  

  Sebastian July 10th, 2008 @ 04:21 PM

  Sorry, my first post here, which patch?

  Is it in git? Or just not attached here?

• You flagged this item as spam.
  

  Clemens Kofler July 10th, 2008 @ 04:30 PM

  In the right column, there's a section called "attachments". The patch is called "MySQLConfigStorageEngine.diff". If you need help applying it, you can refer to http://rails.lighthouseapp.com/p....

  If you don't have git or don't want to use git to apply the patch, you can easily just copy the lines that are affected by it because it's a relatively small patch - 4 new lines of code (including the documentation) and removing one method. Just open the .diff in your text editor to see what I've changed. If you need help, feel free to contact me in the #rubyonrails channel on IRC (nickname is clemensk).

• 

  josh October 28th, 2008 @ 04:24 PM

  • State changed from “new” to “stale”

  Staling out, please reopen if this is still a problem.

• You flagged this item as spam.
  

  Sebastian November 11th, 2008 @ 12:29 PM

  Not sure, but did the patch [1] Clemens provided go into Rails?

  That did solve the problem, how do I make sure it goes into Rails?

  [1] http://rails.lighthouseapp.com/a...

• You flagged this item as spam.
  

  Clemens Kofler November 11th, 2008 @ 12:33 PM

  I think I talked to Pratik back then (or was it Koz?) who told me that Rails fucks up in a thousand weird ways when used with MyISAM. So I guess applying this patch would not really be a good idea.

  If it works for you, you could probably just use the code in an initializer to monkeypatch it in.

• You flagged this item as spam.
  

  Sebastian November 12th, 2008 @ 03:03 PM

  It works for me (so far).

  Does is make sense to update the activerecord documentation? Like adding a note saying that the Rails 'system tables' will only work with MyISAM?

• 

  Ryan Bigg October 9th, 2010 @ 09:59 PM

  • Tag cleared.
  • Importance changed from “” to “Low”

  Automatic cleanup of spam.

• 

  Jeff Kreeftmeijer November 7th, 2010 @ 04:57 PM

  Automatic cleanup of spam.