Rails - Dec 2007 - 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 ;-) )

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
-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

On Dec 5, 1:49 pm, Joshua Muheim
<rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org>
wrote:
> I started script/console locally and tried to create a new MusicArtist.
Just one question.  Did you consider naming this class
''Musician'' ?
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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
<kevin.cline-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
>
> On Dec 5, 1:49 pm, Joshua Muheim
<rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org>
> wrote:
> > I started script/console locally and tried to create a new
MusicArtist.
>
> Just one question.  Did you consider naming this class
''Musician'' ?
> >
>

-- 
Ryan Bigg

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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:

[root@qvs010 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
-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Nobody got an idea? :-( I''m really desperate about this...
-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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
On 6 Dec 2007, at 14:45, Joshua Muheim wrote:
>
> Nobody got an idea? :-( I''m really desperate about this...
> --  
> Posted via http://www.ruby-forum.com/.
>
> >

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Frederick Cheung 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:

[root@qvs010 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
[root@qvs010 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?
-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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? :-(
-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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...
-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---