Hi,
We''ve been having some problems with mysql 5 and not null text columns
(versions 5.0.67, 5.0.51a and 5.0.51b)
Mysql reports the columns as having a default of null (the column is
actually a not null) which causes activerecord to try and insert nulls
where it can''t which makes things implode. If you do insert into foos
values() then you do get an empty string inserted in the relevant
column, so in that sense the column default is the empty string.
Mysql is a bit funny about text columns and defaults (it won''t let you
set one, but still seems to behave as if there is one), and there are
other places where it''s funny with defaults (eg
missing_default_forged_as_empty_string)
If the column isn''t marked as not null then the default is actually
null. The behaviour with blobs is the same
It seems to me that MysqlColumn#extract_default could be patched to
def extract_default(default)
if type == :binary || type == :text
if default.blank?
null ? nil : ''''
else
raise ArgumentError, "#{type} columns cannot have a default
value: #{default.inspect}"
end
elsif missing_default_forged_as_empty_string?(default)
nil
else
super
end
end
Does this sound reasonable? Have others run into this ?
Fred
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Core" group.
To post to this group, send email to rubyonrails-core@googlegroups.com
To unsubscribe from this group, send email to
rubyonrails-core+unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en
-~----------~----~----~----~------~----~------~--~---
On Tue, Sep 9, 2008 at 9:47 AM, Frederick Cheung <frederick.cheung@gmail.com> wrote:> We''ve been having some problems with mysql 5 and not null text columns > (versions 5.0.67, 5.0.51a and 5.0.51b) > > Mysql reports the columns as having a default of null (the column is > actually a not null) which causes activerecord to try and insert nulls > where it can''t which makes things implode. If you do insert into foos > values() then you do get an empty string inserted in the relevant > column, so in that sense the column default is the empty string. > > Mysql is a bit funny about text columns and defaults (it won''t let you > set one, but still seems to behave as if there is one), and there are > other places where it''s funny with defaults (eg > missing_default_forged_as_empty_string) > > If the column isn''t marked as not null then the default is actually > null. The behaviour with blobs is the same > > It seems to me that MysqlColumn#extract_default could be patched to > > def extract_default(default) > if type == :binary || type == :text > if default.blank? > null ? nil : '''' > else > raise ArgumentError, "#{type} columns cannot have a default > value: #{default.inspect}" > end > elsif missing_default_forged_as_empty_string?(default) > nil > else > super > end > end > > Does this sound reasonable? Have others run into this ?Yes, sounds good. We do our best to ''just work'' with MySQL and this quirk shouldn''t be an exception. Ideally we''d omit unassigned not-null attributes from the INSERT in the first place. jeremy --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Core" group. To post to this group, send email to rubyonrails-core@googlegroups.com To unsubscribe from this group, send email to rubyonrails-core+unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/rubyonrails-core?hl=en -~----------~----~----~----~------~----~------~--~---
On 9 Sep 2008, at 17:58, Jeremy Kemper wrote:>> >> >> Does this sound reasonable? Have others run into this ? > > Yes, sounds good. We do our best to ''just work'' with MySQL and this > quirk shouldn''t be an exception. >Cool. I''ll patchify it when I''m satisfied that this fix doesn''t screw anything up> Ideally we''d omit unassigned not-null attributes from the INSERT in > the first place.Fred --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Core" group. To post to this group, send email to rubyonrails-core@googlegroups.com To unsubscribe from this group, send email to rubyonrails-core+unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/rubyonrails-core?hl=en -~----------~----~----~----~------~----~------~--~---
On 9 Sep 2008, at 18:09, Frederick Cheung wrote:> > On 9 Sep 2008, at 17:58, Jeremy Kemper wrote: >>> >>> >>> Does this sound reasonable? Have others run into this ? >> >> Yes, sounds good. We do our best to ''just work'' with MySQL and this >> quirk shouldn''t be an exception. >> > > Cool. I''ll patchify it when I''m satisfied that this fix doesn''t > screw anything up >http://rails.lighthouseapp.com/projects/8994-ruby-on-rails/tickets/1043-mysql-textblob-column-peculiarity One existing test conflicted with the new behaviour, but I do very much believe that changing the behaviour is right - mysql is just weird in this particular case. Tests pass with mysql/sqlite3/postgres Fred> >> Ideally we''d omit unassigned not-null attributes from the INSERT in >> the first place. > > > > Fred--~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Core" group. To post to this group, send email to rubyonrails-core@googlegroups.com To unsubscribe from this group, send email to rubyonrails-core+unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/rubyonrails-core?hl=en -~----------~----~----~----~------~----~------~--~---
I''m seeing a similar issue with mysql 5.0.51. I have a rails 1.2.6 app
deployed on a webhost agains mysql 5.0.27 and it works fine. however
on two different linux boxes, both running mysql 5.0.51, I get the
following error:
Mysql::Error: Column ''title'' cannot be null: INSERT INTO boats
(`updated_at`, `kind`, `title`, `length_string`, `description`,
`length`, `hulltype`, `make`, `year`, `published_at`, `user_id`,
`builder`, `description_uses_textile`, `image_id`, `model`,
`specifications`, `created_at`, `state`, `style`) VALUES(''2008-10-03
16:24:28'', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 4,
NULL, 0, NULL, NULL, ''[]'', ''2008-10-03
16:24:28'', ''new'', NULL);
Think this is the same issue?
Thanks, I''m at my wits end with this, I can''t figure out why
it works
on the web host with the SAME sql from AR, but not on a dev box.
Thanks!
On Sep 14, 7:14 am, Frederick Cheung <frederick.che...@gmail.com>
wrote:> On 9 Sep 2008, at 18:09, Frederick Cheung wrote:
>
>
>
> > On 9 Sep 2008, at 17:58, Jeremy Kemper wrote:
>
> >>> Does this sound reasonable? Have others run into this ?
>
> >> Yes, sounds good. We do our best to ''just work''
with MySQL and this
> >> quirk shouldn''t be an exception.
>
> > Cool. I''ll patchify it when I''m satisfied that this
fix doesn''t
> > screw anything up
>
> http://rails.lighthouseapp.com/projects/8994-ruby-on-rails/tickets/10...
>
> One existing test conflicted with the new behaviour, but I do very
> much believe that changing the behaviour is right - mysql is just
> weird in this particular case. Tests pass with mysql/sqlite3/postgres
>
> Fred
>
>
>
> >> Ideally we''d omit unassigned not-null attributes from the
INSERT in
> >> the first place.
>
> > Fred
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Core" group.
To post to this group, send email to rubyonrails-core@googlegroups.com
To unsubscribe from this group, send email to
rubyonrails-core+unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en
-~----------~----~----~----~------~----~------~--~---
Jeff wrote:> I''m seeing a similar issue with mysql 5.0.51. I have a rails 1.2.6 app > deployed on a webhost agains mysql 5.0.27 and it works fine. however > on two different linux boxes, both running mysql 5.0.51, I get the > following error: > > Mysql::Error: Column ''title'' cannot be null: INSERT INTO boats > (`updated_at`, `kind`, `title`, `length_string`, `description`, > `length`, `hulltype`, `make`, `year`, `published_at`, `user_id`, > `builder`, `description_uses_textile`, `image_id`, `model`, > `specifications`, `created_at`, `state`, `style`) VALUES(''2008-10-03 > 16:24:28'', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 4, > NULL, 0, NULL, NULL, ''[]'', ''2008-10-03 16:24:28'', ''new'', NULL); > > Think this is the same issue? > Thanks, I''m at my wits end with this, I can''t figure out why it works > on the web host with the SAME sql from AR, but not on a dev box.Your problem is just an issue caused by behavioral differences in mysql versions, it hasn''t got anything to do with rails. -- Cheers, Koz --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Core" group. To post to this group, send email to rubyonrails-core@googlegroups.com To unsubscribe from this group, send email to rubyonrails-core+unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/rubyonrails-core?hl=en -~----------~----~----~----~------~----~------~--~---
On 4 Oct 2008, at 03:39, Jeff wrote:> > I''m seeing a similar issue with mysql 5.0.51. I have a rails 1.2.6 app > deployed on a webhost agains mysql 5.0.27 and it works fine. however > on two different linux boxes, both running mysql 5.0.51, I get the > following error: > > Mysql::Error: Column ''title'' cannot be null: INSERT INTO boats > (`updated_at`, `kind`, `title`, `length_string`, `description`, > `length`, `hulltype`, `make`, `year`, `published_at`, `user_id`, > `builder`, `description_uses_textile`, `image_id`, `model`, > `specifications`, `created_at`, `state`, `style`) VALUES(''2008-10-03 > 16:24:28'', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 4, > NULL, 0, NULL, NULL, ''[]'', ''2008-10-03 16:24:28'', ''new'', NULL); > > Think this is the same issue?Quite possibly. The problem I encountered was that mysql''s output made rails think the column has a default null, and so rails would try and insert a null into the column (which would fail since the column was not null). Rails derives column defaults from the output from SHOW FIELDS, I found that on 5.0.51 it was show null as the column default. If that matches up with what you''ve seen then it''s probably the same thing I ran into. Fred> > Thanks, I''m at my wits end with this, I can''t figure out why it works > on the web host with the SAME sql from AR, but not on a dev box. > > Thanks! > > On Sep 14, 7:14 am, Frederick Cheung <frederick.che...@gmail.com> > wrote: >> On 9 Sep 2008, at 18:09, Frederick Cheung wrote: >> >> >> >>> On 9 Sep 2008, at 17:58, Jeremy Kemper wrote: >> >>>>> Does this sound reasonable? Have others run into this ? >> >>>> Yes, sounds good. We do our best to ''just work'' with MySQL and this >>>> quirk shouldn''t be an exception. >> >>> Cool. I''ll patchify it when I''m satisfied that this fix doesn''t >>> screw anything up >> >> http://rails.lighthouseapp.com/projects/8994-ruby-on-rails/tickets/ >> 10... >> >> One existing test conflicted with the new behaviour, but I do very >> much believe that changing the behaviour is right - mysql is just >> weird in this particular case. Tests pass with mysql/sqlite3/postgres >> >> Fred >> >> >> >>>> Ideally we''d omit unassigned not-null attributes from the INSERT in >>>> the first place. >> >>> Fred > > >--~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Core" group. To post to this group, send email to rubyonrails-core@googlegroups.com To unsubscribe from this group, send email to rubyonrails-core+unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/rubyonrails-core?hl=en -~----------~----~----~----~------~----~------~--~---
This turned out to be a change in behaviour in MySQL. Downgrading from 5.0.51a to 5.0.27 solved the problem but that left me unsatisified. I had moved the database from production to the dev box via mysqldump, letting it handle the schema creation. That was the issue. On the production box, I dumped the schema using rake, and used the resulting schema.rb to recreate the schema on the development machine. I used mysqldump to move the data. Doing it this way allowed me to continue to use mysql 5.0.51a on the development box. The reason? Rail''s schema dumper sets the empty string as then default value for string columns, while mysql was setting a default of NULL, which broke the inserts. On Oct 4, 12:55 pm, Frederick Cheung <frederick.che...@gmail.com> wrote:> On 4 Oct 2008, at 03:39, Jeff wrote: > > > > > I''m seeing a similar issue with mysql 5.0.51. I have a rails 1.2.6 app > > deployed on a webhost agains mysql 5.0.27 and it works fine. however > > on two different linux boxes, both running mysql 5.0.51, I get the > > following error: > > > Mysql::Error: Column ''title'' cannot be null: INSERT INTO boats > > (`updated_at`, `kind`, `title`, `length_string`, `description`, > > `length`, `hulltype`, `make`, `year`, `published_at`, `user_id`, > > `builder`, `description_uses_textile`, `image_id`, `model`, > > `specifications`, `created_at`, `state`, `style`) VALUES(''2008-10-03 > > 16:24:28'', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 4, > > NULL, 0, NULL, NULL, ''[]'', ''2008-10-03 16:24:28'', ''new'', NULL); > > > Think this is the same issue? > > Quite possibly. The problem I encountered was that mysql''s output made > rails think the column has a default null, and so rails would try and > insert a null into the column (which would fail since the column was > not null). > Rails derives column defaults from the output from SHOW FIELDS, I > found that on 5.0.51 it was show null as the column default. If that > matches up with what you''ve seen then it''s probably the same thing I > ran into. > > Fred > > > > > Thanks, I''m at my wits end with this, I can''t figure out why it works > > on the web host with the SAME sql from AR, but not on a dev box. > > > Thanks! > > > On Sep 14, 7:14 am, Frederick Cheung <frederick.che...@gmail.com> > > wrote: > >> On 9 Sep 2008, at 18:09, Frederick Cheung wrote: > > >>> On 9 Sep 2008, at 17:58, Jeremy Kemper wrote: > > >>>>> Does this sound reasonable? Have others run into this ? > > >>>> Yes, sounds good. We do our best to ''just work'' with MySQL and this > >>>> quirk shouldn''t be an exception. > > >>> Cool. I''ll patchify it when I''m satisfied that this fix doesn''t > >>> screw anything up > > >>http://rails.lighthouseapp.com/projects/8994-ruby-on-rails/tickets/ > >> 10... > > >> One existing test conflicted with the new behaviour, but I do very > >> much believe that changing the behaviour is right - mysql is just > >> weird in this particular case. Tests pass with mysql/sqlite3/postgres > > >> Fred > > >>>> Ideally we''d omit unassigned not-null attributes from the INSERT in > >>>> the first place. > > >>> Fred--~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Core" group. To post to this group, send email to rubyonrails-core@googlegroups.com To unsubscribe from this group, send email to rubyonrails-core+unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/rubyonrails-core?hl=en -~----------~----~----~----~------~----~------~--~---