85 3 true 2009-07-08T13:26:48+01:00 38023 2009-08-31T00:00:00+01:00 41987 2883 validates_uniqueness_of-fails-with-diacritics-on-mysql 16 8994 committed 2-3-stable 3.0 activerecord adapters mysql validate_uniqueness_of verified 2009-08-09T19:56:43+01:00 17393 Repository awendt Jeremy Kemper http://rails.lighthouseapp.com/projects/8994/tickets/2883 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: +--------+---------------------------------------------------------- | Table | 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. 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: +--------+---------------------------------------------------------- | Table | 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. <div><p>Consider this Person model on Rails 2.3.2:</p> <p>class Person &lt; ActiveRecord::Base</p> <pre> <code>validates_uniqueness_of :name </code> </pre> <p>end</p> <p>This lets me create two people with the name 'andr&eacute;', and the second person gets an ActiveRecord::StatementInvalid. The same code on Sqlite works fine.</p> <p>This is because the MySQL adapter issues</p> <p>SELECT <code>people</code>.id FROM <code>people</code> WHERE (<code>people</code>.<code>name</code> = BINARY 'andr&eacute;') LIMIT 1</p> <p>while Sqlite gets</p> <p>SELECT "people".id FROM "people" WHERE ("people"."name" = 'andr&eacute;') LIMIT 1</p> <p>Code to reproduce is on <a href="http://github.com/awendt/validates_uniqueness_with_diacritics.%3C/p"> http://github.com/awendt/validat...</a>&gt;</p> <p>SHOW CREATE TABLE people:</p> <p> +--------+---------------------------------------------------------- | Table | Create Table| +--------+---------------------------------------------------------- | people | CREATE TABLE <code>people</code> ( <code>id</code> int(11) NOT NULL auto_increment, <code>name</code> varchar(255) default NULL, <code>created_at</code> datetime default NULL, <code>updated_at</code> datetime default NULL, PRIMARY KEY (<code>id</code>) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 +--------+----------------------------------------------------------</p> <p>Character set and collation seem fine. This is on OSX.</p></div> 0 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. <div><p>Consider this Person model on Rails 2.3.2:</p> <p><code>class Person &lt; ActiveRecord::Base</code></p> <p><code>validates_uniqueness_of :name</code></p> <p><code>end</code></p> <p>This lets me create two people with the name 'andr&eacute;', and the second person gets an ActiveRecord::StatementInvalid. The same code on Sqlite works fine.</p> <p>This is because the MySQL adapter issues</p> <p>SELECT <code>people</code>.id FROM <code>people</code> WHERE (<code>people</code>.<code>name</code> = BINARY 'andr&eacute;') LIMIT 1</p> <p>while Sqlite gets</p> <p>SELECT "people".id FROM "people" WHERE ("people"."name" = 'andr&eacute;') LIMIT 1</p> <p>Code to reproduce is on <a href= "http://github.com/awendt/validates_uniqueness_with_diacritics">http://github.com/awendt/validates_uniqueness_with_diacritics</a></p> <p>SHOW CREATE TABLE people:</p> <p>CREATE TABLE <code>people</code> (<br> <code>id</code> int(11) NOT NULL auto_increment, <code>name</code> varchar(255) default NULL, <code>created_at</code> datetime default NULL, <code>updated_at</code> datetime default NULL, PRIMARY KEY (<code>id</code>) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8</p> <p>Character set and collation seem fine. This is on OSX.</p></div> false 2009-07-08T13:26:48+01:00 38023 --- {} 9903 2883 validates_uniqueness_of-fails-with-diacritics-on-mysql 0 8994 new 2-3-stable activerecord adapters mysql validate_uniqueness_of 2009-07-08T13:29:26+01:00 38023 awendt awendt http://rails.lighthouseapp.com/projects/8994/tickets/2883 2.x 0 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. <div><p>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.</p></div> false 2009-07-08T16:35:17+01:00 38023 --- {} 9903 2883 validates_uniqueness_of-fails-with-diacritics-on-mysql 0 8994 new 2-3-stable activerecord adapters mysql validate_uniqueness_of 2009-07-08T16:35:20+01:00 31975 Francesc Esplugas awendt http://rails.lighthouseapp.com/projects/8994/tickets/2883 2.x 0 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. <div><p>Maybe I wasn't quite clear. The strange behavior is that the validation actually passes.</p> <p>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.</p> <p>I don't expect the validation let the value slip through and then realize on inserting that the DB rejects the value (ActiveRecord::StatementInvalid).</p> <p>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.</p></div> false 2009-07-09T08:30:32+01:00 38023 --- {} 9903 2883 validates_uniqueness_of-fails-with-diacritics-on-mysql 0 8994 new 2-3-stable activerecord adapters mysql validate_uniqueness_of 2009-07-09T08:30:33+01:00 38023 awendt awendt http://rails.lighthouseapp.com/projects/8994/tickets/2883 2.x 0 false 2009-07-13T19:32:39+01:00 38023 --- :title: validates_uniqueness_of fails with diacritics on MySQL 9903 2883 validates_uniqueness_of-fails-with-diacritics-on-mysql 0 8994 new 2-3-stable activerecord adapters mysql validate_uniqueness_of 2009-07-13T19:32:45+01:00 38023 awendt awendt http://rails.lighthouseapp.com/projects/8994/tickets/2883 2.x 0 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">] >> @@@ <div><p>When using script/console, I can't get the save command to fail; perhaps the problem is with RSpec, and not with ActiveRecord.</p> <pre> <code>~/proj/avuwd$ script/console Loading development environment (Rails 2.3.2) &gt;&gt; Person.find(:all) =&gt; [] &gt;&gt; p1=Person.new(:name=&gt;'Joe') =&gt; #&lt;Person id: nil, name: "Joe", created_at: nil, updated_at: nil&gt; &gt;&gt; p1.save =&gt; true &gt;&gt; p2=Person.new(:name=&gt;'Joe') =&gt; #&lt;Person id: nil, name: "Joe", created_at: nil, updated_at: nil&gt; &gt;&gt; p2.save =&gt; false &gt;&gt; p2.errors =&gt; #&lt;ActiveRecord::Errors:0x9b0c404 @base=#&lt;Person id: nil, name: "Joe", created_at: nil, updated_at: nil&gt;, @errors={"name"=&gt;["has already been taken"]}&gt; &gt;&gt; Person.find(:all) =&gt; [#&lt;Person id: 3, name: "Joe", created_at: "2009-07-13 22:05:55", updated_at: "2009-07-13 22:05:55"&gt;] &gt;&gt; p3=Person.new(:name=&gt;'andr&eacute;') =&gt; #&lt;Person id: nil, name: "andr&eacute;", created_at: nil, updated_at: nil&gt; &gt;&gt; p3.save =&gt; true &gt;&gt; p4=Person.new(:name=&gt;'andr&eacute;') =&gt; #&lt;Person id: nil, name: "andr&eacute;", created_at: nil, updated_at: nil&gt; &gt;&gt; p4.save =&gt; false &gt;&gt; p4.errors =&gt; #&lt;ActiveRecord::Errors:0x97cb664 @base=#&lt;Person id: nil, name: "andr&eacute;", created_at: nil, updated_at: nil&gt;, @errors={"name"=&gt;["has already been taken"]}&gt; &gt;&gt; Person.find(:all) =&gt; [#&lt;Person id: 3, name: "Joe", created_at: "2009-07-13 22:05:55", updated_at: "2009-07-13 22:05:55"&gt;, #&lt;Person id: 4, name: "andr\xC3\xA9", created_at: "2009-07-13 22:07:41", updated_at: "2009-07-13 22:07:41"&gt;] &gt;&gt;</code> </pre></div> false 2009-07-13T23:15:29+01:00 38023 --- {} 9903 2883 validates_uniqueness_of-fails-with-diacritics-on-mysql 0 8994 new 2-3-stable activerecord adapters mysql validate_uniqueness_of 2009-07-13T23:15:33+01:00 53327 Kirk awendt http://rails.lighthouseapp.com/projects/8994/tickets/2883 2.x 0 This is weird, to say the least: <pre><code> $ sc 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:0xb7553900 @errors={"name"=>["has already been taken"]}, @base=#<Person id: nil, name: "Joe", created_at: nil, updated_at: nil>> >> Person.find(:all) => [#<Person id: 3, name: "Joe", created_at: "2009-07-17 20:14:52", updated_at: "2009-07-17 20:14:52">] >> 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 => true </code></pre> 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?: <pre><code>mysql> SELECT `people`.id FROM `people` WHERE (`people`.`name` = BINARY 'andré') LIMIT 1;</pre></code> I get no records because of the BINARY operator. This is what Rails issues when validating uniqueness. I *do* get a record with <pre><code>mysql> SELECT `people`.id FROM `people` WHERE (`people`.`name` = 'andré') LIMIT 1;</pre></code> So it's no wonder the validation doesn't work as expected... <div><p>This is weird, to say the least:</p> <pre> <code> $ sc Loading development environment (Rails 2.3.2) &gt;&gt; Person.find(:all) =&gt; [] &gt;&gt; p1=Person.new(:name=&gt;'Joe') =&gt; # &gt;&gt; p1.save =&gt; true &gt;&gt; p2=Person.new(:name=&gt;'Joe') =&gt; # &gt;&gt; p2.save =&gt; false &gt;&gt; p2.errors =&gt; #["has already been taken"]}, @base=#&gt; &gt;&gt; Person.find(:all) =&gt; [#] &gt;&gt; p3=Person.new(:name=&gt;'andr&eacute;') =&gt; # &gt;&gt; p3.save =&gt; true &gt;&gt; p4=Person.new(:name=&gt;'andr&eacute;') =&gt; # &gt;&gt; p4.save =&gt; true</code> </pre> <p>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?:</p> <pre> <code>mysql&gt; SELECT `people`.id FROM `people` WHERE (`people`.`name` = BINARY 'andr&eacute;') LIMIT 1;</code> </pre> I get no records because of the BINARY operator. This is what Rails issues when validating uniqueness. I *do* get a record with <pre> <code>mysql&gt; SELECT `people`.id FROM `people` WHERE (`people`.`name` = 'andr&eacute;') LIMIT 1;</code> </pre> So it's no wonder the validation doesn't work as expected...</div> false 2009-07-17T21:24:28+01:00 38023 --- {} 9903 2883 validates_uniqueness_of-fails-with-diacritics-on-mysql 0 8994 new 2-3-stable activerecord adapters mysql validate_uniqueness_of 2009-07-17T21:24:30+01:00 38023 awendt awendt http://rails.lighthouseapp.com/projects/8994/tickets/2883 2.x 0 I have this issue too with Rail 2.3.3 and MySQL. <div><p>I have this issue too with Rail 2.3.3 and MySQL.</p></div> false 2009-07-30T22:36:03+01:00 38023 --- {} 9903 2883 validates_uniqueness_of-fails-with-diacritics-on-mysql 0 8994 new 2-3-stable activerecord adapters mysql validate_uniqueness_of 2009-07-30T22:36:05+01:00 64587 Emmanuel awendt http://rails.lighthouseapp.com/projects/8994/tickets/2883 2.x 0 false 2009-08-02T11:14:12+01:00 38023 --- :tag: 2-3-stable activerecord adapters mysql validate_uniqueness_of 9903 2883 validates_uniqueness_of-fails-with-diacritics-on-mysql 0 8994 new 2-3-stable activerecord adapters bugmash mysql validate_uniqueness_of 2009-08-02T11:14:15+01:00 7211 Mike Gunderloy awendt http://rails.lighthouseapp.com/projects/8994/tickets/2883 2.x 0 As Kirk said, I can't reproduce the errors on Rails 2.3.2 and Rails 2.3.3 too. <code> >> Person.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:0x27983d8 @base=#<Person id: nil, name: "Joe", created_at: nil, updated_at: nil>, @errors={"name"=>["has already been taken"]}> >> 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 </code> <div><p>As Kirk said, I can't reproduce the errors on Rails 2.3.2 and Rails 2.3.3 too.</p> <p><code><br></code></p> <blockquote> <blockquote> <p>Person.all =&gt; [] p1 = Person.new(:name =&gt; 'Joe') =&gt; # p1.save =&gt; true p2 = Person.new(:name =&gt; 'Joe') =&gt; # p2.save =&gt; false p2.errors =&gt; #&lt;ActiveRecord::Errors:0x27983d8 @base=#, @errors={"name"=&gt;["has already been taken"]}&gt; p3 = Person.new(:name =&gt; 'andr&eacute;') =&gt; # p3.save =&gt; true p4 = Person.new(:name =&gt; 'andr&eacute;') =&gt; # p4.save =&gt; false</p> </blockquote> </blockquote></div> false 2009-08-08T17:41:42+01:00 38023 --- {} 9903 2883 validates_uniqueness_of-fails-with-diacritics-on-mysql 0 8994 new 2-3-stable activerecord adapters bugmash mysql validate_uniqueness_of 2009-08-08T17:41:47+01:00 20230 Leandro Heuert awendt http://rails.lighthouseapp.com/projects/8994/tickets/2883 2.x 0 Leandro, neither you nor Kirk have responded how many records you get with these two statements: <code> 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; </code> <div><p>Leandro, neither you nor Kirk have responded how many records you get with these two statements:</p> <p><code><br> mysql&gt; SELECT</code> people.id FROM <code>people</code> WHERE (<code>people</code>.<code>name</code> = BINARY 'andr&eacute;') LIMIT 1;<br> mysql&gt; SELECT <code>people</code>.id FROM <code>people</code> WHERE (<code>people</code>.<code>name</code> = 'andr&eacute;') LIMIT 1;<br></p></div> false 2009-08-08T18:55:10+01:00 38023 --- {} 9903 2883 validates_uniqueness_of-fails-with-diacritics-on-mysql 0 8994 new 2-3-stable activerecord adapters bugmash mysql validate_uniqueness_of 2009-08-08T18:55:15+01:00 38023 awendt awendt http://rails.lighthouseapp.com/projects/8994/tickets/2883 2.x 0 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. <div><p>I verified that this issue occurs in 2-3-stable. I've attached a test that currently fails when I run:</p> <p>rake test_mysql TEST=test/cases/validations_test.rb</p> <p>on the activerecord folder.</p></div> false 2009-08-08T22:30:52+01:00 38023 --- {} 9903 2883 validates_uniqueness_of-fails-with-diacritics-on-mysql 0 8994 new 2-3-stable activerecord adapters bugmash mysql validate_uniqueness_of 2009-08-08T22:30:54+01:00 30376 Hugo Peixoto awendt http://rails.lighthouseapp.com/projects/8994/tickets/2883 2.x 1 Thanks Hugo! <div><p>Thanks Hugo!</p></div> false 2009-08-08T23:41:53+01:00 38023 --- :state: new :milestone: 9903 41987 2883 validates_uniqueness_of-fails-with-diacritics-on-mysql 0 8994 verified 2-3-stable activerecord adapters bugmash mysql validate_uniqueness_of 2009-08-08T23:41:59+01:00 85 Jeremy Kemper awendt http://rails.lighthouseapp.com/projects/8994/tickets/2883 2.3.4 1 Note that a fix is still needed :) <div><p>Note that a fix is still needed :)</p></div> false 2009-08-09T01:30:28+01:00 38023 --- {} 41987 2883 validates_uniqueness_of-fails-with-diacritics-on-mysql 0 8994 verified 2-3-stable activerecord adapters bugmash mysql validate_uniqueness_of 2009-08-09T01:30:29+01:00 85 Jeremy Kemper awendt http://rails.lighthouseapp.com/projects/8994/tickets/2883 2.3.4 1 verified +1 This issue is present in 2-3-stable. Will be submitting a test case for master now. <div><p>verified</p> <p>+1 This issue is present in 2-3-stable.</p> <p>Will be submitting a test case for master now.</p></div> false 2009-08-09T01:44:54+01:00 38023 --- :tag: 2-3-stable activerecord adapters bugmash mysql validate_uniqueness_of 41987 2883 validates_uniqueness_of-fails-with-diacritics-on-mysql 0 8994 verified 2-3-stable 3.0 activerecord adapters bugmash mysql validate_uniqueness_of verified 2009-08-09T01:44:56+01:00 65556 Rizwan Reza awendt http://rails.lighthouseapp.com/projects/8994/tickets/2883 2.3.4 1 verified patch +1 this issue is also present in master. The attached test case is for master. <div><p>verified patch</p> <p>+1 this issue is also present in master.</p> <p>The attached test case is for master.</p></div> false 2009-08-09T02:01:17+01:00 38023 --- {} 41987 2883 validates_uniqueness_of-fails-with-diacritics-on-mysql 0 8994 verified 2-3-stable 3.0 activerecord adapters bugmash mysql validate_uniqueness_of verified 2009-08-09T02:01:23+01:00 65556 Rizwan Reza awendt http://rails.lighthouseapp.com/projects/8994/tickets/2883 2.3.4 2 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? <div><p>OK, I found the problem. But I need to be advised on how to proceed.</p> <p>The "&eacute;" characters on the test case I provided (provode? :p) are in UTF-8.<br> The databases are created using the default charset, which is latin1_swedish_ci.<br> This somehow causes the comparisons to fail.</p> <p>I created the test databases with the following commands:</p> <pre> <code>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;</code> </pre> <p>and ran the tests. It worked. So, should I change the database creation call on the Rakefile?</p></div> false 2009-08-09T02:25:46+01:00 38023 --- {} 41987 2883 validates_uniqueness_of-fails-with-diacritics-on-mysql 0 8994 verified 2-3-stable 3.0 activerecord adapters bugmash mysql validate_uniqueness_of verified 2009-08-09T02:25:49+01:00 30376 Hugo Peixoto awendt http://rails.lighthouseapp.com/projects/8994/tickets/2883 2.3.4 2 OK, I did what I mentioned in the previous comment. I've attached a patch. <div><p>OK, I did what I mentioned in the previous comment. I've attached a patch.</p></div> false 2009-08-09T02:53:52+01:00 38023 --- {} 41987 2883 validates_uniqueness_of-fails-with-diacritics-on-mysql 0 8994 verified 2-3-stable 3.0 activerecord adapters bugmash mysql validate_uniqueness_of verified 2009-08-09T02:53:55+01:00 30376 Hugo Peixoto awendt http://rails.lighthouseapp.com/projects/8994/tickets/2883 2.3.4 85 3 2-3-stable: ac9f9a9c3e61e977a1614f484648d9334d0f67ed master: ac9f9a9c3e61e977a1614f484648d9334d0f67ed <div><p>2-3-stable: ac9f9a9c3e61e977a1614f484648d9334d0f67ed</p> <p>master: ac9f9a9c3e61e977a1614f484648d9334d0f67ed</p></div> true 2009-08-09T03:11:24+01:00 38023 --- :state: verified :assigned_user: 41987 2883 validates_uniqueness_of-fails-with-diacritics-on-mysql 0 8994 committed 2-3-stable 3.0 activerecord adapters bugmash mysql validate_uniqueness_of verified 2009-08-09T03:11:27+01:00 85 Jeremy Kemper awendt Jeremy Kemper http://rails.lighthouseapp.com/projects/8994/tickets/2883 2.3.4 85 3 true 2009-08-09T03:11:30+01:00 38023 --- :tag: 2-3-stable 3.0 activerecord adapters bugmash mysql validate_uniqueness_of verified 41987 2883 validates_uniqueness_of-fails-with-diacritics-on-mysql 0 8994 committed 2-3-stable 3.0 activerecord adapters mysql validate_uniqueness_of verified 2009-08-09T03:11:33+01:00 85 Jeremy Kemper awendt Jeremy Kemper http://rails.lighthouseapp.com/projects/8994/tickets/2883 2.3.4 3 +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). <div><p>+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.</p> <pre> <code>% cat /opt/local/etc/mysql5/my.cnf [client] default-character-set=utf8 [mysqld] init-connect = SET NAMES utf8 character-set-server = utf8 mysql&gt; 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)</code> </pre> <p>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).</p></div> true 2009-08-09T04:12:24+01:00 38023 --- :tag: 2-3-stable 3.0 activerecord adapters mysql validate_uniqueness_of verified :assigned_user: 85 41987 2883 validates_uniqueness_of-fails-with-diacritics-on-mysql 0 8994 committed 2-3-stable 3.0 activerecord adapters bugmash mysql validate_uniqueness_of verified 2009-08-09T04:12:27+01:00 26790 Kieran P awendt http://rails.lighthouseapp.com/projects/8994/tickets/2883 2.3.4 3 (that my.cnf file was misformatted, see http://pastie.org/577142) <div><p>(that my.cnf file was misformatted, see <a href= "http://pastie.org/577142">http://pastie.org/577142</a>)</p></div> true 2009-08-09T04:15:50+01:00 38023 --- {} 41987 2883 validates_uniqueness_of-fails-with-diacritics-on-mysql 0 8994 committed 2-3-stable 3.0 activerecord adapters bugmash mysql validate_uniqueness_of verified 2009-08-09T04:15:52+01:00 26790 Kieran P awendt http://rails.lighthouseapp.com/projects/8994/tickets/2883 2.3.4 85 3 Sorry about that. Started the message over an hour ago and had to go afk. Nice to see it was committed. <div><p>Sorry about that. Started the message over an hour ago and had to go afk. Nice to see it was committed.</p></div> true 2009-08-09T04:17:32+01:00 38023 --- :tag: 2-3-stable 3.0 activerecord adapters bugmash mysql validate_uniqueness_of verified :assigned_user: 41987 2883 validates_uniqueness_of-fails-with-diacritics-on-mysql 0 8994 committed 2-3-stable 3.0 activerecord adapters mysql validate_uniqueness_of verified 2009-08-09T04:17:37+01:00 26790 Kieran P awendt Jeremy Kemper http://rails.lighthouseapp.com/projects/8994/tickets/2883 2.3.4 85 3 (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/ac9f9a9c3e61e977a1614f484648d9334d0f67ed <div><p>(from <a href= "/projects/8994/changesets/ac9f9a9c3e61e977a1614f484648d9334d0f67ed" title= "Changeset [ac9f9a9c3e61e977a1614f484648d9334d0f67ed]">[ac9f9a9c3e61e977a1614f484648d9334d0f67ed]</a>) MySQL: fix diacritic uniqueness test by setting the default character set and collation to utf8/utf8_unicode_ci</p> <p>[<a href="/projects/8994/tickets/2883" title= "Ticket #2883">#2883</a> state:committed]</p> <p>Signed-off-by: Jeremy Kemper <a>jeremy@bitsweat.net</a><br> <a href= "http://github.com/rails/rails/commit/ac9f9a9c3e61e977a1614f484648d9334d0f67ed"> http://github.com/rails/rails/commit/ac9f9a9c3e61e977a1614f484648d9...</a></p></div> true 2009-08-09T05:31:14+01:00 38023 --- {} 41987 2883 validates_uniqueness_of-fails-with-diacritics-on-mysql 0 8994 committed 2-3-stable 3.0 activerecord adapters mysql validate_uniqueness_of verified 2009-08-09T05:31:16+01:00 17393 Repository awendt Jeremy Kemper http://rails.lighthouseapp.com/projects/8994/tickets/2883 2.3.4 85 3 (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/d8041538dd1fed038c1ad30ddca5c9ce8254ee30 <div><p>(from <a href= "/projects/8994/changesets/d8041538dd1fed038c1ad30ddca5c9ce8254ee30" title= "Changeset [d8041538dd1fed038c1ad30ddca5c9ce8254ee30]">[d8041538dd1fed038c1ad30ddca5c9ce8254ee30]</a>) MySQL: fix diacritic uniqueness test by setting the default character set and collation to utf8/utf8_unicode_ci</p> <p>[<a href="/projects/8994/tickets/2883" title= "Ticket #2883">#2883</a> state:committed]</p> <p>Signed-off-by: Jeremy Kemper <a>jeremy@bitsweat.net</a><br> <a href= "http://github.com/rails/rails/commit/d8041538dd1fed038c1ad30ddca5c9ce8254ee30"> http://github.com/rails/rails/commit/d8041538dd1fed038c1ad30ddca5c9...</a></p></div> true 2009-08-09T05:31:14+01:00 38023 --- {} 41987 2883 validates_uniqueness_of-fails-with-diacritics-on-mysql 0 8994 committed 2-3-stable 3.0 activerecord adapters mysql validate_uniqueness_of verified 2009-08-09T05:31:15+01:00 17393 Repository awendt Jeremy Kemper http://rails.lighthouseapp.com/projects/8994/tickets/2883 2.3.4 85 3 (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/a8286af3c335eeb80245db2ffbcfc6c06fb6fc60 <div><p>(from <a href= "/projects/8994/changesets/a8286af3c335eeb80245db2ffbcfc6c06fb6fc60" title= "Changeset [a8286af3c335eeb80245db2ffbcfc6c06fb6fc60]">[a8286af3c335eeb80245db2ffbcfc6c06fb6fc60]</a>) Added a uniqueness validation test that uses diacritics.</p> <p>[<a href="/projects/8994/tickets/2883" title= "Ticket #2883">#2883</a> state:committed]</p> <p>Signed-off-by: Jeremy Kemper <a href= "mailto:jeremy@bitsweat.net">jeremy@bitsweat.net</a><br> <a href= "http://github.com/rails/rails/commit/a8286af3c335eeb80245db2ffbcfc6c06fb6fc60"> http://github.com/rails/rails/commit/a8286af3c335eeb80245db2ffbcfc6...</a></p></div> true 2009-08-09T19:56:41+01:00 38023 --- {} 41987 2883 validates_uniqueness_of-fails-with-diacritics-on-mysql 0 8994 committed 2-3-stable 3.0 activerecord adapters mysql validate_uniqueness_of verified 2009-08-09T19:56:42+01:00 17393 Repository awendt Jeremy Kemper http://rails.lighthouseapp.com/projects/8994/tickets/2883 2.3.4 85 3 (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/ff60ec469fd85db450c9c2c7eb8f9d9254e45988 <div><p>(from <a href= "/projects/8994/changesets/ff60ec469fd85db450c9c2c7eb8f9d9254e45988" title= "Changeset [ff60ec469fd85db450c9c2c7eb8f9d9254e45988]">[ff60ec469fd85db450c9c2c7eb8f9d9254e45988]</a>) Added a uniqueness validation test that uses diacritics.</p> <p>[<a href="/projects/8994/tickets/2883" title= "Ticket #2883">#2883</a> state:committed]</p> <p>Signed-off-by: Jeremy Kemper <a href= "mailto:jeremy@bitsweat.net">jeremy@bitsweat.net</a><br> <a href= "http://github.com/rails/rails/commit/ff60ec469fd85db450c9c2c7eb8f9d9254e45988"> http://github.com/rails/rails/commit/ff60ec469fd85db450c9c2c7eb8f9d...</a></p></div> true 2009-08-09T19:56:42+01:00 38023 --- {} 41987 2883 validates_uniqueness_of-fails-with-diacritics-on-mysql 0 8994 committed 2-3-stable 3.0 activerecord adapters mysql validate_uniqueness_of verified 2009-08-09T19:56:43+01:00 17393 Repository awendt Jeremy Kemper http://rails.lighthouseapp.com/projects/8994/tickets/2883 2.3.4 f8ca7d375284b9fe24a01a7abdd7f8fc7b22ff4e text/plain 2009-08-08T22:30:52+01:00 validates_uniqueness_with_diacritics.diff 239183 2117 30376 http://rails.lighthouseapp.com/attachments/239183/validates_uniqueness_with_diacritics.diff 1e20855f90a4acf778267ee758a8f0f059426568 text/plain 2009-08-09T02:01:17+01:00 test-diacritics-fail.diff 239229 1308 65556 http://rails.lighthouseapp.com/attachments/239229/test-diacritics-fail.diff 2265919762e7410b243ac5d32be6c9105220c614 text/plain 2009-08-09T02:53:52+01:00 2883-default-activerecord-validations-charset-and-collation.diff 239233 1249 30376 http://rails.lighthouseapp.com/attachments/239233/2883-default-activerecord-validations-charset-and-collation.diff