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)

just execute

ALTER TABLE table_name MODIFY column text CHARACTER SET utf8

this will do the trick



Witold Rugowski wrote:
> 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)