Diacritics break validates_uniqueness_of on MySQL

What do you expect to get if you insert two times the same value and have a uniqueness validation? I don't see any strange behavior.

Maybe I wasn't quite clear. The strange behavior is that the validation actually passes.

If I already have that value in the DB, I expect to get a validation error, as this is the whole purpose of validates_uniqueness_of.

I don't expect the validation let the value slip through and then realize on inserting that the DB rejects the value (ActiveRecord::StatementInvalid).

Please clone the project and have a look at the tags works_on_sqlite and fails_on_mysql. In Sqlite, all tests pass. In MySQL, they don't.

When using script/console, I can't get the save command to fail; perhaps the problem is with RSpec, and not with ActiveRecord.

~/proj/avuwd$ script/console
Loading development environment (Rails 2.3.2)
>> Person.find(:all)
=> []
>> p1=Person.new(:name=>'Joe')
=> #<Person id: nil, name: "Joe", created_at: nil, updated_at: nil>
>> p1.save
=> true
>> p2=Person.new(:name=>'Joe')
=> #<Person id: nil, name: "Joe", created_at: nil, updated_at: nil>
>> p2.save
=> false
>> p2.errors
=> #<ActiveRecord::Errors:0x9b0c404 @base=#<Person id: nil, name: "Joe", created_at: nil, updated_at: nil>, @errors={"name"=>["has already been taken"]}>
>> Person.find(:all)
=> [#<Person id: 3, name: "Joe", created_at: "2009-07-13 22:05:55", updated_at: "2009-07-13 22:05:55">]
>> p3=Person.new(:name=>'andré')
=> #<Person id: nil, name: "andré", created_at: nil, updated_at: nil>
>> p3.save
=> true
>> p4=Person.new(:name=>'andré')
=> #<Person id: nil, name: "andré", created_at: nil, updated_at: nil>
>> p4.save
=> false
>> p4.errors
=> #<ActiveRecord::Errors:0x97cb664 @base=#<Person id: nil, name: "andré", created_at: nil, updated_at: nil>, @errors={"name"=>["has already been taken"]}>
>> Person.find(:all)
=> [#<Person id: 3, name: "Joe", created_at: "2009-07-13 22:05:55", updated_at: "2009-07-13 22:05:55">, #<Person id: 4, name: "andr\xC3\xA9", created_at: "2009-07-13 22:07:41", updated_at: "2009-07-13 22:07:41">]
>>

This is weird, to say the least:

$ sc
Loading development environment (Rails 2.3.2)
>> Person.find(:all)
=> []
>> p1=Person.new(:name=>'Joe')
=> #
>> p1.save
=> true
>> p2=Person.new(:name=>'Joe')
=> #
>> p2.save
=> false
>> p2.errors
=> #["has already been taken"]}, @base=#>
>> Person.find(:all)
=> [#]
>> p3=Person.new(:name=>'andré')
=> #
>> p3.save
=> true
>> p4=Person.new(:name=>'andré')
=> #
>> p4.save
=> true

This is on another system. Both run Rails 2.3.2, one with rspec 1.2.4, the other with 1.2.7. Are you on MySQL and not SQLite? And do you get any records when issuing the following?:

mysql> SELECT `people`.id FROM `people` WHERE (`people`.`name` = BINARY 'andré') LIMIT 1;

mysql> SELECT `people`.id FROM `people` WHERE (`people`.`name` = 'andré') LIMIT 1;

I have this issue too with Rail 2.3.3 and MySQL.

As Kirk said, I can't reproduce the errors on Rails 2.3.2 and Rails 2.3.3 too.

Person.all => [] p1 = Person.new(:name => 'Joe') => # p1.save => true p2 = Person.new(:name => 'Joe') => # p2.save => false p2.errors => #<ActiveRecord::Errors:0x27983d8 @base=#, @errors={"name"=>["has already been taken"]}> p3 = Person.new(:name => 'andré') => # p3.save => true p4 = Person.new(:name => 'andré') => # p4.save => false

Leandro, neither you nor Kirk have responded how many records you get with these two statements:


mysql> SELECT people.id FROM people WHERE (people.name = BINARY 'andré') LIMIT 1;
mysql> SELECT people.id FROM people WHERE (people.name = 'andré') LIMIT 1;

I verified that this issue occurs in 2-3-stable. I've attached a test that currently fails when I run:

rake test_mysql TEST=test/cases/validations_test.rb

on the activerecord folder.

Note that a fix is still needed :)

verified patch

+1 this issue is also present in master.

The attached test case is for master.

OK, I found the problem. But I need to be advised on how to proceed.

The "é" characters on the test case I provided (provode? :p) are in UTF-8.
The databases are created using the default charset, which is latin1_swedish_ci.
This somehow causes the comparisons to fail.

I created the test databases with the following commands:

create database activerecord_unittest DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;
create database activerecord_unittest2 DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;

and ran the tests. It worked. So, should I change the database creation call on the Rakefile?

OK, I did what I mentioned in the previous comment. I've attached a patch.

(that my.cnf file was misformatted, see http://pastie.org/577142)

(from [ac9f9a9c3e61e977a1614f484648d9334d0f67ed]) MySQL: fix diacritic uniqueness test by setting the default character set and collation to utf8/utf8_unicode_ci

[#2883 Diacritics break validates_uniqueness_of on MySQL state:committed]

Signed-off-by: Jeremy Kemper jeremy@bitsweat.net
http://github.com/rails/rails/commit/ac9f9a9c3e61e977a1614f484648d9...

(from [d8041538dd1fed038c1ad30ddca5c9ce8254ee30]) MySQL: fix diacritic uniqueness test by setting the default character set and collation to utf8/utf8_unicode_ci

[#2883 Diacritics break validates_uniqueness_of on MySQL state:committed]

Signed-off-by: Jeremy Kemper jeremy@bitsweat.net
http://github.com/rails/rails/commit/d8041538dd1fed038c1ad30ddca5c9...

(from [a8286af3c335eeb80245db2ffbcfc6c06fb6fc60]) Added a uniqueness validation test that uses diacritics.

[#2883 Diacritics break validates_uniqueness_of on MySQL state:committed]

Signed-off-by: Jeremy Kemper jeremy@bitsweat.net
http://github.com/rails/rails/commit/a8286af3c335eeb80245db2ffbcfc6...

(from [ff60ec469fd85db450c9c2c7eb8f9d9254e45988]) Added a uniqueness validation test that uses diacritics.

[#2883 Diacritics break validates_uniqueness_of on MySQL state:committed]

Signed-off-by: Jeremy Kemper jeremy@bitsweat.net
http://github.com/rails/rails/commit/ff60ec469fd85db450c9c2c7eb8f9d...