Hi all
(First of all: the following post is not that long as it might seem,
it has quite much code excerpts that won’t be of interest… so thanks
for reading it anyway 
)
I have a very strange problem. I just uploaded my application on my
server. I added the fields owner_id and creator_id to the table
music_artists. Then I tried to create a new music artist on the server,
but got an error page. I investigated the problem and came to the
insight that my local database table differs from the remote one! But a
small example first:
I started script/console locally and tried to create a new MusicArtist.
m = MusicArtist.new
=> #<MusicArtist:0x318817c @attributes={“name”=>nil, “updated_at”=>nil,
“creator_id”=>nil, “url”=>nil, “lock_version”=>0, “description”=>nil,
“owner_id”=>nil, “origin_country_id”=>nil, “created_at”=>nil},
@new_record=true>m.valid?
=> falsem
=> #<MusicArtist:0x318817c @attributes={“name”=>nil, “updated_at”=>nil,
“creator_id”=>nil, “url”=>nil, “lock_version”=>0, “description”=>nil,
“owner_id”=>nil, “origin_country_id”=>nil, “created_at”=>nil},
@new_record=true, @errors=#<ActiveRecord::Errors:0x3183244
@errors={“name”=>[“is too long (maximum is 100 characters)”, “can’t be
blank”], “creator_id”=>[“can’t be blank”], “owner_id”=>[“can’t be
blank”], “origin_country_id”=>[“can’t be blank”]},
@base=#<MusicArtist:0x318817c …>
You can see that creator_id and owner_id are nil by default. That’s what
I expect it to be.
MySQL tells me that I have the following table structure:
CREATE TABLE music_artists (
id int(11) NOT NULL auto_increment,
name varchar(100) NOT NULL default ‘’,
origin_country_id int(11) default NULL,
description text,
created_at datetime NOT NULL default ‘0000-00-00 00:00:00’,
updated_at datetime NOT NULL default ‘0000-00-00 00:00:00’,
lock_version int(11) NOT NULL default ‘0’,
url varchar(100) default NULL,
creator_id int(11) NOT NULL,
owner_id int(11) NOT NULL,
PRIMARY KEY (id),
UNIQUE KEY name_unique (name),
KEY origin_country_id (origin_country_id),
KEY creator_id (creator_id),
KEY owner_id (owner_id),
CONSTRAINT music_artists_ibfk_1 FOREIGN KEY (origin_country_id)
REFERENCES countries (id),
CONSTRAINT music_artists_ibfk_2 FOREIGN KEY (creator_id)
REFERENCES members (id),
CONSTRAINT music_artists_ibfk_3 FOREIGN KEY (owner_id) REFERENCES
members (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
My schema.rb contains:
create_table “music_artists”, :force => true do |t|
t.column “name”, :string, :limit => 100,
:null => false
t.column “origin_country_id”, :integer
t.column “description”, :text
t.column “created_at”, :datetime,
:null => false
t.column “updated_at”, :datetime,
:null => false
t.column “lock_version”, :integer, :default => 0,
:null => false
t.column “url”, :string, :limit => 100
t.column “creator_id”, :integer,
:null => false
t.column “owner_id”, :integer,
:null => false
end
Now let’s go remote.
The same example in script/console:
m = MusicArtist.new
=> #<MusicArtist:0x40b928cc @attributes={“name”=>nil, “updated_at”=>nil,
“creator_id”=>0, “url”=>nil, “lock_version”=>0, “description”=>nil,
“owner_id”=>0, “origin_country_id”=>nil, “created_at”=>nil},
@new_record=true>m.valid?
=> falsem
=> #<MusicArtist:0x40b928cc @attributes={“name”=>nil, “updated_at”=>nil,
“creator_id”=>0, “url”=>nil, “lock_version”=>0, “description”=>nil,
“owner_id”=>0, “origin_country_id”=>nil, “created_at”=>nil},
@new_record=true, @errors=#<ActiveRecord::Errors:0x40b4769c
@base=#<MusicArtist:0x40b928cc …>, @errors={“name”=>[“is too long
(maximum is 100 characters)”, “can’t be blank”],
“origin_country_id”=>[“can’t be blank”]}>>
But here I get a value 0 for creator_id and owner_id! That’s not what
I expected!
MySQL tells me I’m having the following table structure:
CREATE TABLE music_artists (
id int(11) NOT NULL auto_increment,
name varchar(100) NOT NULL default ‘’,
origin_country_id int(11) default NULL,
description text,
created_at datetime NOT NULL default ‘0000-00-00 00:00:00’,
updated_at datetime NOT NULL default ‘0000-00-00 00:00:00’,
lock_version int(11) NOT NULL default ‘0’,
url varchar(100) default NULL,
creator_id int(11) NOT NULL default ‘0’,
owner_id int(11) NOT NULL default ‘0’,
PRIMARY KEY (id),
UNIQUE KEY name_unique (name),
KEY origin_country_id (origin_country_id),
KEY creator_id (creator_id),
KEY owner_id (owner_id),
CONSTRAINT music_artists_ibfk_1 FOREIGN KEY (origin_country_id)
REFERENCES countries (id),
CONSTRAINT music_artists_ibfk_2 FOREIGN KEY (creator_id)
REFERENCES members (id),
CONSTRAINT music_artists_ibfk_3 FOREIGN KEY (owner_id) REFERENCES
members (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
You can see, that creator_id and owner_id have default value of 0 here,
which is NOT correct!
My remote schema.rb contains:
create_table “music_artists”, :force => true do |t|
t.column “name”, :string, :limit => 100,
:null => false
t.column “origin_country_id”, :integer
t.column “description”, :text
t.column “created_at”, :datetime,
:null => false
t.column “updated_at”, :datetime,
:null => false
t.column “lock_version”, :integer, :default => 0,
:null => false
t.column “url”, :string, :limit => 100
t.column “creator_id”, :integer,
:null => false
t.column “owner_id”, :integer,
:null => false
end
Here you can see, that this default values seem to come out of nowhere!
The migration should not add them!
So where could they have come from? I have no idea and feel completely
helpless… of course, I could just correct this manually, but that’s
not what I want, because it would make my confidence in migrations much
smaller…
Any idea? Maybe because I’m using different MySQL versions?
Thanks a lot for any help!
Josh
I’m really desperate about this…