Why is migration failing?

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

– add_column(:tickets, :location, :string, {:null=>false, :limit=>30})
rake aborted!
An error has occurred, this and all later migrations canceled:

SQLite3::SQLException: Cannot add a NOT NULL column with default value
NULL: ALTER TABLE “ticke
ts” ADD “location” varchar(30) NOT NULL

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


Kind Regards,
Rajinder Y. | DevMentor.org | Do Good! ~ Share Freely

GNU/Linux: 2.6.35-22-generic
Kubuntu x86_64 10.10 | KDE 4.5.1
Ruby 1.9.2p0 | Rails 3.0.1

On 7 November 2010 10:37, Rajinder Y. [email protected] wrote:

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

SQLite3::SQLException: Cannot add a NOT NULL column with default value NULL:
ALTER TABLE “ticke
ts” ADD “location” varchar(30) NOT NULL

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…

On 10-11-07 05:44 AM, Michael P. wrote:

You’re adding a column with no default value (so, that’s a default
before running the migration
or
specify a default value for the field…

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


Kind Regards,
Rajinder Y. | DevMentor.org | Do Good! ~ Share Freely

GNU/Linux: 2.6.35-22-generic
Kubuntu x86_64 10.10 | KDE 4.5.1
Ruby 1.9.2p0 | Rails 3.0.1

On 7 November 2010 10:56, Rajinder Y. [email protected] wrote:

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?

On Nov 7, 10:56am, Rajinder Y. [email protected] wrote:

On 10-11-07 05:44 AM, Michael P. wrote:

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

On 10-11-07 07:03 AM, Michael P. wrote:

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?

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.


Kind Regards,
Rajinder Y. | DevMentor.org | Do Good! ~ Share Freely

GNU/Linux: 2.6.35-22-generic
Kubuntu x86_64 10.10 | KDE 4.5.1
Ruby 1.9.2p0 | Rails 3.0.1

On 10-11-07 04:32 PM, Michael P. wrote:

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

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


Kind Regards,
Rajinder Y. | DevMentor.org | Do Good! ~ Share Freely

GNU/Linux: 2.6.35-22-generic
Kubuntu x86_64 10.10 | KDE 4.5.1
Ruby 1.9.2p0 | Rails 3.0.1

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.

On 7 November 2010 21:09, Rajinder Y. [email protected] wrote:

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