Mysql and non null text columns

rubyonrails-core
1

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

2

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

3

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

4

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

5

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!

6

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.

7

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

8

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.