# Migrations: default value where there shouldn't be one! **URL:** \ **Category:** Rails\ **Created:** [December 5, 2007, 8:49pm UTC](https://www.ruby-forum.com/t/migrations-default-value-where-there-shouldnt-be-one/122238 "2007-12-05T20:49:00Z")\ **Posts on this page:** 9\ **Page:** 1
**Author:** ![josh](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/45deac/32.png) [@josh](https://www.ruby-forum.com/u/josh)\ **Post date:** [December 5, 2007, 8:49pm UTC](https://www.ruby-forum.com/t/migrations-default-value-where-there-shouldnt-be-one/122238/1 "2007-12-05T20:49:00Z")
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 > > =\> #\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 > > =\> #\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=#\ > @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=#\ 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 > > =\> #\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 > > =\> #\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=#\ > @base=#\, @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 ---
**Author:** ![josh](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/45deac/32.png) [@josh](https://www.ruby-forum.com/u/josh)\ **Post date:** [December 6, 2007, 4:39am UTC](https://www.ruby-forum.com/t/migrations-default-value-where-there-shouldnt-be-one/122238/2 "2007-12-06T04:39:11Z")
On Dec 5, 1:49 pm, Joshua M. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote: > I started script/console locally and tried to create a new MusicArtist. Just one question. Did you consider naming this class ‘Musician’ ? ---
**Author:** ![josh](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/45deac/32.png) [@josh](https://www.ruby-forum.com/u/josh)\ **Post date:** [December 6, 2007, 5:02am UTC](https://www.ruby-forum.com/t/migrations-default-value-where-there-shouldnt-be-one/122238/3 "2007-12-06T05:02:35Z")
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 [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote: > On Dec 5, 1:49 pm, Joshua M. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) > 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. ---
**Author:** ![josh](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/45deac/32.png) [@josh](https://www.ruby-forum.com/u/josh)\ **Post date:** [December 6, 2007, 3:45pm UTC](https://www.ruby-forum.com/t/migrations-default-value-where-there-shouldnt-be-one/122238/4 "2007-12-06T15:45:37Z")
Nobody got an idea? ☹ I’m really desperate about this… ---
**Author:** ![josh](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/45deac/32.png) [@josh](https://www.ruby-forum.com/u/josh)\ **Post date:** [December 6, 2007, 6:07pm UTC](https://www.ruby-forum.com/t/migrations-default-value-where-there-shouldnt-be-one/122238/5 "2007-12-06T18:07:33Z")
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](http://dev.mysql.com/doc/refman/5.0/en/data-type-defaults.html) Fred ---
**Author:** ![josh](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/45deac/32.png) [@josh](https://www.ruby-forum.com/u/josh)\ **Post date:** [December 6, 2007, 9:13am UTC](https://www.ruby-forum.com/t/migrations-default-value-where-there-shouldnt-be-one/122238/6 "2007-12-06T09:13:48Z")
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 ---
**Author:** ![josh](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/45deac/32.png) [@josh](https://www.ruby-forum.com/u/josh)\ **Post date:** [December 6, 2007, 9:57pm UTC](https://www.ruby-forum.com/t/migrations-default-value-where-there-shouldnt-be-one/122238/7 "2007-12-06T21:57:18Z")
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? ☹ ---
**Author:** ![josh](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/45deac/32.png) [@josh](https://www.ruby-forum.com/u/josh)\ **Post date:** [December 6, 2007, 9:43pm UTC](https://www.ruby-forum.com/t/migrations-default-value-where-there-shouldnt-be-one/122238/8 "2007-12-06T21:43:30Z")
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](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? ---
**Author:** ![josh](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/45deac/32.png) [@josh](https://www.ruby-forum.com/u/josh)\ **Post date:** [December 7, 2007, 12:22am UTC](https://www.ruby-forum.com/t/migrations-default-value-where-there-shouldnt-be-one/122238/9 "2007-12-07T00:22:38Z")
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](http://bugs.mysql.com/bug.php?id=5986) I guess I’ll upgrade MySQL to the newest version and hope it will work then…