Mysql and non null text columns

[Frederick Cheung-2]

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@...
To unsubscribe from this group, send email to rubyonrails-core+unsubscribe@...
For more options, visit this group at http://groups.google.com/group/rubyonrails-core?hl=en
-~----------~----~----~----~------~----~------~--~---

[Jeremy Kemper]

On Tue, Sep 9, 2008 at 9:47 AM, Frederick Cheung
<frederick.cheung@...> 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@...
To unsubscribe from this group, send email to rubyonrails-core+unsubscribe@...
For more options, visit this group at http://groups.google.com/group/rubyonrails-core?hl=en
-~----------~----~----~----~------~----~------~--~---

[Frederick Cheung-2]

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@...
To unsubscribe from this group, send email to rubyonrails-core+unsubscribe@...
For more options, visit this group at http://groups.google.com/group/rubyonrails-core?hl=en
-~----------~----~----~----~------~----~------~--~---

[Frederick Cheung-2]

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@...
To unsubscribe from this group, send email to rubyonrails-core+unsubscribe@...
For more options, visit this group at http://groups.google.com/group/rubyonrails-core?hl=en
-~----------~----~----~----~------~----~------~--~---

[Jeff Paquette-2]

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...@...>
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@...
To unsubscribe from this group, send email to rubyonrails-core+unsubscribe@...
For more options, visit this group at http://groups.google.com/group/rubyonrails-core?hl=en
-~----------~----~----~----~------~----~------~--~---

[Michael Koziarski]

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@...
To unsubscribe from this group, send email to rubyonrails-core+unsubscribe@...
For more options, visit this group at http://groups.google.com/group/rubyonrails-core?hl=en
-~----------~----~----~----~------~----~------~--~---

[Frederick Cheung-2]

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...@...>
> 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@...
To unsubscribe from this group, send email to rubyonrails-core+unsubscribe@...
For more options, visit this group at http://groups.google.com/group/rubyonrails-core?hl=en
-~----------~----~----~----~------~----~------~--~---

[Jeff Paquette-2]

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...@...>
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...@...>
> > 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@...
To unsubscribe from this group, send email to rubyonrails-core+unsubscribe@...
For more options, visit this group at http://groups.google.com/group/rubyonrails-core?hl=en
-~----------~----~----~----~------~----~------~--~---