This project is archived and is in readonly mode.

#2883 ✓committed
awendt

Diacritics break validates_uniqueness_of on MySQL

Reported by awendt | July 8th, 2009 @ 01:26 PM | in 2.3.4

Consider this Person model on Rails 2.3.2:

class Person < ActiveRecord::Base

validates_uniqueness_of :name

end

This lets me create two people with the name 'andré', and the second person gets an ActiveRecord::StatementInvalid. The same code on Sqlite works fine.

This is because the MySQL adapter issues

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

while Sqlite gets

SELECT "people".id FROM "people" WHERE ("people"."name" = 'andré') LIMIT 1

Code to reproduce is on http://github.com/awendt/validates_uniqueness_with_diacritics

SHOW CREATE TABLE people:

CREATE TABLE people (
id int(11) NOT NULL auto_increment, name varchar(255) default NULL, created_at datetime default NULL, updated_at datetime default NULL, PRIMARY KEY (id) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8

Character set and collation seem fine. This is on OSX.

Comments and changes to this ticket

  • Francesc Esplugas

    Francesc Esplugas July 8th, 2009 @ 04:35 PM

    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.

  • awendt

    awendt July 9th, 2009 @ 08:30 AM

    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.

  • awendt

    awendt July 13th, 2009 @ 07:32 PM

    • Title changed from “validates_uniqueness_of fails with diacritics on MySQL” to “Diacritics break validates_uniqueness_of on MySQL”
  • Kirk

    Kirk July 13th, 2009 @ 11:15 PM

    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">]
>>
  • awendt

    awendt July 17th, 2009 @ 09:24 PM

    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;
    I get no records because of the BINARY operator. This is what Rails issues when validating uniqueness. I *do* get a record with 
    mysql> SELECT `people`.id FROM `people` WHERE (`people`.`name` = 'andré') LIMIT 1;
    So it's no wonder the validation doesn't work as expected...
  • Emmanuel

    Emmanuel July 30th, 2009 @ 10:36 PM

    I have this issue too with Rail 2.3.3 and MySQL.

  • CancelProfileIsBroken

    CancelProfileIsBroken August 2nd, 2009 @ 11:14 AM

    • Tag changed from “2-3-stable, activerecord, adapters, mysql, validate_uniqueness_of” to “2-3-stable, activerecord, adapters, bugmash, mysql, validate_uniqueness_of”
  • Leandro Heuert

    Leandro Heuert August 8th, 2009 @ 05:41 PM

    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

  • awendt

    awendt August 8th, 2009 @ 06:55 PM

    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;

  • Hugo Peixoto

    Hugo Peixoto August 8th, 2009 @ 10:30 PM

    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.

  • Jeremy Kemper

    Jeremy Kemper August 8th, 2009 @ 11:41 PM

    • State changed from “new” to “verified”
    • Milestone changed from “2.x” to “2.3.4”

    Thanks Hugo!

  • Jeremy Kemper

    Jeremy Kemper August 9th, 2009 @ 01:30 AM

    Note that a fix is still needed :)

  • Rizwan Reza

    Rizwan Reza August 9th, 2009 @ 01:44 AM

    • Tag changed from “2-3-stable, activerecord, adapters, bugmash, mysql, validate_uniqueness_of” to “2-3-stable, 3.0, activerecord, adapters, bugmash, mysql, validate_uniqueness_of, verified”

    verified

    +1 This issue is present in 2-3-stable.

    Will be submitting a test case for master now.

  • Rizwan Reza

    Rizwan Reza August 9th, 2009 @ 02:01 AM

    verified patch

    +1 this issue is also present in master.

    The attached test case is for master.

  • Hugo Peixoto

    Hugo Peixoto August 9th, 2009 @ 02:25 AM

    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?

  • Hugo Peixoto

    Hugo Peixoto August 9th, 2009 @ 02:53 AM

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

  • Jeremy Kemper

    Jeremy Kemper August 9th, 2009 @ 03:11 AM

    • Assigned user set to “Jeremy Kemper”
    • State changed from “verified” to “committed”

    2-3-stable: ac9f9a9c3e61e977a1614f484648d9334d0f67ed

    master: ac9f9a9c3e61e977a1614f484648d9334d0f67ed

  • Jeremy Kemper

    Jeremy Kemper August 9th, 2009 @ 03:11 AM

    • Tag changed from “2-3-stable, 3.0, activerecord, adapters, bugmash, mysql, validate_uniqueness_of, verified” to “2-3-stable, 3.0, activerecord, adapters, mysql, validate_uniqueness_of, verified”
  • Kieran P

    Kieran P August 9th, 2009 @ 04:12 AM

    • Assigned user cleared.
    • Tag changed from “2-3-stable, 3.0, activerecord, adapters, mysql, validate_uniqueness_of, verified” to “2-3-stable, 3.0, activerecord, adapters, bugmash, mysql, validate_uniqueness_of, verified”

    +1 support for the patch, however.... it depends on the encoding of your database. All new databases are created with utf-8 on my machine (as defined in my /opt/local/etc/mysql5/my.cnf file) and as such, the test in this ticket did not cause failures when running the mysql activerecord test suite.

    % cat /opt/local/etc/mysql5/my.cnf
[client]
default-character-set=utf8
[mysqld]
init-connect = SET NAMES utf8
character-set-server = utf8


mysql> SHOW VARIABLES LIKE 'character_set%';
+--------------------------+-----------------------------------------+
| Variable_name            | Value                                   |
+--------------------------+-----------------------------------------+
| character_set_client     | utf8                                    | 
| character_set_connection | utf8                                    | 
| character_set_database   | utf8                                    | 
| character_set_filesystem | binary                                  | 
| character_set_results    | utf8                                    | 
| character_set_server     | utf8                                    | 
| character_set_system     | utf8                                    | 
| character_sets_dir       | /opt/local/share/mysql5/mysql/charsets/ | 
+--------------------------+-----------------------------------------+
8 rows in set (0.00 sec)

    That said, the patch to fix this did not affect already working system, so to avoid inconsistencies like this, it would be good to force a database type to utf8 (anyone working with translations in a database probably already does this).

  • Kieran P

    Kieran P August 9th, 2009 @ 04:15 AM

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

  • Kieran P

    Kieran P August 9th, 2009 @ 04:17 AM

    • Assigned user set to “Jeremy Kemper”
    • Tag changed from “2-3-stable, 3.0, activerecord, adapters, bugmash, mysql, validate_uniqueness_of, verified” to “2-3-stable, 3.0, activerecord, adapters, mysql, validate_uniqueness_of, verified”

    Sorry about that. Started the message over an hour ago and had to go afk. Nice to see it was committed.

  • Repository

    Repository August 9th, 2009 @ 05:31 AM

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

    [#2883 state:committed]

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

  • Repository

    Repository August 9th, 2009 @ 05:31 AM

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

    [#2883 state:committed]

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

  • Repository

    Repository August 9th, 2009 @ 07:56 PM

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

    [#2883 state:committed]

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

  • Repository

    Repository August 9th, 2009 @ 07:56 PM

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

    [#2883 state:committed]

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

Create your profile

Help contribute to this project by taking a few moments to create your personal profile. Create your profile »

<h2 style="font-size: 14px">Tickets have moved to Github</h2>

The new ticket tracker is available at <a href="https://github.com/rails/rails/issues">https://github.com/rails/rails/issues</a>

People watching this ticket

Attachments

Tags

Referenced by

Pages