MySQL connection collation

Rails
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?

On 3 set, 21:07, Witold R. [email protected]

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”

–
Witold R.
Ruby on Rails freelancer
nhw.pl (EN blog) NetManiac (PL blog)

4

just execute

ALTER TABLE table_name MODIFY column text CHARACTER SET utf8

this will do the trick

Witold R. 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 R.
Ruby on Rails freelancer
nhw.pl (EN blog) NetManiac (PL blog)