why is migration failing?

rubyonrails-talk
1

I have the following migration to add a new column

class AddLocationToTicket < ActiveRecord::Migration    def self.up      add_column :tickets, :location, :string, :null => false, :limit => 30    end

   def self.down      remove_column :tickets, :location    end end

I get the following when I run rake db:migrate, which doesn't make sense because nowhere am i specifing a default value!

== AddLocationToTicket: migrating

2

You're kidding right? That doesn't make sense?!

You're adding a column with no default value (so, that's a default value of "NULL") to a table which presumably already has data in it. And you're saying that the column should *not* allow NULL, even though you don't specify a default value. So the existing columns will all have NULL as the value for the field you just created, except you tell the DB that NULL is not allowed - so there's an exception.

Seems to make sense to me.

Either: delete all the existing data (assuming it's test/development data) before running the migration or specify a default value for the field...

3

I don't have any existing data. I do

rake db:migrate VERSION=0 rake db:migrate

What do you mean? how is that any different from the following which works fine!

class CreateTickets < ActiveRecord::Migration    def self.up      create_table :tickets do |t|        t.string :location, :null => false, :limit => 30

       t.timestamps      end    end

   def self.down      drop_table :tickets    end end

4

I don't have any existing data. I do

rake db:migrate VERSION=0 rake db:migrate

What do you mean? how is that any different from the following which works fine!

As ever, more information about the problem makes it more interesting.

Are you *sure* none of the other migrations create rows in the tickets table?

What if, for the curiosity of it, you alter the CreateTickets migration thus:

def self.up create_table :tickets do |t| # all your other columns here...

 t\.timestamps

end

   add_column :tickets, :location, :string, :null => false, :limit => 30 end

Does that work? (there's definitely no rows...) or does it fail with the same exception?

5

class CreateTickets < ActiveRecord::Migration def self.up create_table :tickets do |t| t.string :location, :null => false, :limit => 30

   t\.timestamps
 end

end

Well sqlite might consider creating a table to be different to adding a column to an existing table. The Sqlite3 docs for alter table do specifically say that you must provide a non null default when adding a non null column (while you are not setting a default, the default default is null)

Fred

While you're not specifying a default value, the default default value is NULL

Fred

6

I tried your suggestion, I get the same error. As Fred stated about sqlite3, you can't add a new field without specifying a default value for a non-null field. I was under the impression if the table was empty with no rows you should be able to do this.

It kind of sucks, because rather than using migration, I have to go back to my previous migration and just add the new field into the model so it works the way I want it without having to specify a non null value.

Since I am the only one working on this "sample" app, it's not a big deal and all source is under GIT control, so not a big deal. still I wanted to get into the habit of using migration.

7

I tried your suggestion, I get the same error. As Fred stated about sqlite3, you can't add a new field without specifying a default value for a non-null field. I was under the impression if the table was empty with no rows you should be able to do this.

Nice gotcha.... maybe it would be better for you to change from SQLite to something else? MySQL? PostgreSQL? Seeing as you're not going to deploy to SQLite, I've never understood the point of developing in it :-/

Since I am the only one working on this "sample" app, it's not a big deal and all source is under GIT control, so not a big deal. still I wanted to get into the habit of using migration.

It's a good habit; and if you can help it, you really *don't* want to get into the bad habit of editing migrations that have already been run (even if you are the only developer).

8

I guess out of laziness as I ramp-up my Rails skills I've stuck to using sqlite3, but I'll take your advice and give PostgreSQL a go. I agree it's better to develop good habits and stick with them. Also I have to get use to working with a production DB.

Doing a mental erase of my last comment about me falling back to editing old migrations by hand =P

9

My advice to you is to use mysql instead of sqlite and see the results as it is a sample app. If not, try to be a little through about sqlite before using. moreover mysql and postgre are widely used DBs and will help you in future.