Migrations: default value where there shouldn't be one!

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 :wink: )

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?
=> false

m
=> #<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?
=> false

m
=> #<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

On Dec 5, 1:49 pm, Joshua M. [email protected]
wrote:

I started script/console locally and tried to create a new MusicArtist.

Just one question. Did you consider naming this class ‘Musician’ ?

Or even just Artist?

Because you’re needlessly specifying :null => false on these fields it’s
going to set it at 0. If you don’t specify :null => false the columns
will
be “creator_id”=>nil as I’m assuming you’re expecting.

On Dec 6, 2007 2:08 PM, kevin cline [email protected] wrote:

On Dec 5, 1:49 pm, Joshua M. [email protected]
wrote:

I started script/console locally and tried to create a new MusicArtist.

Just one question. Did you consider naming this class ‘Musician’ ?


Ryan B.

Nobody got an idea? :frowning: I’m really desperate about this…

Seems like it’s just a diference between mysql 5 and previous versions:
http://dev.mysql.com/doc/refman/5.0/en/data-type-defaults.html

Fred

The model name is absolutely correct. But I investigated the problem
further and tracked it down to the following:

I’m having a problem. It seems that MySQL on my development machine
interprets an SQL query different to MySQL on my production machine.

Local (keep an eye on the default value of creator_id!):

mysql> show create table music_artists;
±--------------+
CREATE TABLE music_artists (
creator_id int(11) NOT NULL default ‘0’,
) ENGINE=InnoDB DEFAULT CHARSET=latin1
±--------------+
1 row in set (0.00 sec)

mysql> ALTER TABLE music_artists MODIFY creator_id INTEGER(11) NOT NULL;
Query OK, 17 rows affected (0.07 sec)
Records: 17 Duplicates: 0 Warnings: 0

mysql> show create table music_artists;
±--------------+
CREATE TABLE music_artists (
creator_id int(11) NOT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=latin1
±--------------+
1 row in set (0.00 sec)

My MySQL version is:

192:~/Sites/projects/psyguideorg josh$ mysql --version
mysql Ver 14.12 Distrib 5.0.17, for apple-darwin8.2.0 (powerpc) using
readline 5.0

And now exactly the same on my remote machine:

mysql> show create table music_artists;
±--------------+
CREATE TABLE music_artists (
creator_id int(11) NOT NULL default ‘0’,
) ENGINE=InnoDB DEFAULT CHARSET=latin1
±--------------+
1 row in set (0.00 sec)

mysql> ALTER TABLE music_artists MODIFY creator_id INTEGER(11) NOT NULL;
Query OK, 34 rows affected (0.16 sec)
Records: 34 Duplicates: 0 Warnings: 0

mysql> show create table music_artists;
±--------------+
CREATE TABLE music_artists (
creator_id int(11) NOT NULL default ‘0’,
) ENGINE=InnoDB DEFAULT CHARSET=latin1
±--------------+
1 row in set (0.00 sec)

Here the MySQL version is:

[[email protected] psyguideorg]# mysql --version
mysql Ver 14.7 Distrib 4.1.20, for redhat-linux-gnu (i386) using
readline 4.3

Why does the remote MySQL keep the default value ‘0’ but my local
version doesn’t? What do I have to do to ensure that on both machines
the default values are dropped? Are there other typical
“un-similarities”?

Thanks a lot for help,
Josh

And another thought: I guessed that when using migrations I could use
any supported database I like, and (quote from Spanky Ham) “nothing
could possibly go wrong”?! Isn’t that the case though? :frowning:

Frederick C. wrote:

Seems like it’s just a diference between mysql 5 and previous versions:
http://dev.mysql.com/doc/refman/5.0/en/data-type-defaults.html

Fred

Thank you very much, this alreay explains quite a lot!

I guess we stumbled over an inconsistency here in Rails’ migrations:

[[email protected] psyguideorg]# rake db:migrate RAILS_ENV=production
(in /var/rails/psyguideorg)
== AddCreatorAndOwner: migrating

– add_column(:music_artists, :creator_id, :integer,
{:on_delete=>:restrict, :null=>true, :references=>:members})
-> 0.1563s
– execute(“UPDATE music_artists SET creator_id = 3”)
-> 0.0117s
– change_column(:music_artists, :creator_id, :integer, {:null=>false})
-> 0.0724s
[[email protected] psyguideorg]# mysql -u psyguide -p
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 72866 to server version: 4.1.20

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.

mysql> use psyguide_production;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show create table music_artists;
±--------------±--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
±--------------±--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| music_artists | 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 |
±--------------±--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

You see what I mean? Although I’m explicitly not setting a default
value, it sets one for me! This really isn’t fun… what can I do
against it?

Well it seems that this really is a bug in MySQL… found different
topics about this, e.g.

http://bugs.mysql.com/bug.php?id=5986

I guess I’ll upgrade MySQL to the newest version and hope it will work
then…

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs