This project is archived and is in readonly mode.

#590 ✓stale
Sebastian

allow setting :options in initialize_schema_migrations_table

Reported by Sebastian | July 10th, 2008 @ 01:27 PM | in 2.x

I am using Rails 2.1/mysql-5.0.54 on a host supporting only the MyISAM engine.

Now, when I run

rake db:migrate VERSION=0

for the first time, rake tries creating the @@@schema_migrations@@@ table and fails with

(in /path/to/rails/app)
rake aborted!
Mysql::Error: Can't create table './dbname/schema_migrations.frm' (errno: 135): CREATE TABLE `schema_migrations` (`version` varchar(255) NOT NULL) ENGINE=InnoDB

(See full trace by running task with --trace)

because @@@activerecord/lib/active_record/connection_adapters/mysql_adapter.rb@@@ does not allow setting @@@:options => "ENGINE=MyISAM" }@@@ as I did in the migrations.

Comments and changes to this ticket

  • Clemens Kofler

    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.

  • Sebastian

    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?

  • Clemens Kofler

    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.

  • Clemens Kofler

    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.

  • Sebastian

    Sebastian July 10th, 2008 @ 04:21 PM

    Sorry, my first post here, which patch?

    Is it in git? Or just not attached here?

  • Clemens Kofler

    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

    josh October 28th, 2008 @ 04:24 PM

    • State changed from “new” to “stale”

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

  • Sebastian

    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...

  • Clemens Kofler

    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.

  • Sebastian

    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

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

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

    Automatic cleanup of spam.

  • Jeff Kreeftmeijer

Create your profile

Help contribute to this project by taking a few moments to create your personal profile. Create your profile »

<h2 style="font-size: 14px">Tickets have moved to Github</h2>

The new ticket tracker is available at <a href="https://github.com/rails/rails/issues">https://github.com/rails/rails/issues</a>

Attachments

Pages