Ruby Forum Ruby on Rails > MySQL connection collation

Hi!
I have ran on problem with collations. In database.yml I have set:
encoding: utf8, but when in console run:

>> ActiveRecord::Base.connection.collation
=> "latin1_swedish_ci"

It is Rails 2.1, mysql gem 2.7, mysql 5.1.22. This leads to errors like:
Mysql::Error: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and
(utf8_general_ci,COERCIBLE) for operation 'like': SELECT * FROM `tags`
WHERE (name LIKE '. UTF-8 STRING.')  LIMIT 1


Table are set for UTF-8 and server is run with
mysql_args=" --character-set-server=utf8
--collation-server=utf8_general_ci"

Any ideas?

Have you looked at this specific table to see it's collation and
encoding?

On 3 set, 21:07, Witold Rugowski <rails-mailing-l...@andreas-s.net>

Maurício Linhares wrote:
> Have you looked at this specific table to see it's collation and
> encoding?
 Yes, of course:
mysqldump -d --create-options -u root -p DATABASE tags

DROP TABLE IF EXISTS `tags`;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `tags` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_tags_on_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=51 DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;


Problem is that connection collation is set wrong. I did ugly hack and 
at end of environment.rb I have added:

ActiveRecord::Base.connection.execute "SET collation_database = 
'utf8_general_ci' "
ActiveRecord::Base.connection.execute "SET collation_connection = 
'utf8_general_ci' "


And it works:
>> ActiveRecord::Base.connection.collation
=> "utf8_general_ci"


--
Witold Rugowski
Ruby on Rails freelancer
http://nhw.pl/wp/ (EN blog) http://nhw.pl/pl (PL blog)