MySQL connection collation

rubyonrails-talk
1

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?

2

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

3

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"

4

just execute

ALTER TABLE table_name MODIFY column text CHARACTER SET utf8

this will do the trick

Witold Rugowski wrote: