“非法的校对"运行Rails测试套件时来自MySql的错误 [英] "Illegal mix of collations" error from MySql while running rails test suite

问题描述

我最近清理了我的一个旧的Ruby on Rails项目.过去，通过所有测试从来没有任何问题，但是现在有一个测试给了我以下错误:

I've recently dusted off an old Ruby on Rails project of mine. In the past, I've never had any problems getting all the tests to pass, but now there is one test that gives me the following error:

ActiveRecord :: StatementInvalid:Mysql :: Error:#HY000操作'='的排序规则(latin1_swedish_ci，IMPLICIT)和(utf8_general_ci，COERCIBLE)的非法混合:选择*从卡中(cards.l1_description ='是'AND卡.l2_word ='')

ActiveRecord::StatementInvalid: Mysql::Error: #HY000Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '=': SELECT * FROM cards WHERE (cards.l1_description = '是' AND cards.l2_word = '')

所以我去测试数据库，问:

So I go to my test db and ask:

mysql> use flashcard_test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show full columns from cards;
+----------------+--------------+-------------------+------+-----+---------+----------------+---------------------------------+---------+
| Field          | Type         | Collation         | Null | Key | Default | Extra          | Privileges                      | Comment |
+----------------+--------------+-------------------+------+-----+---------+----------------+---------------------------------+---------+
| id             | int(11)      | NULL              | NO   | PRI | NULL    | auto_increment | select,insert,update,references |         |
| l2_word        | varchar(255) | latin1_swedish_ci | YES  |     | NULL    |                | select,insert,update,references |         |
| l1_description | text         | latin1_swedish_ci | YES  |     | NULL    |                | select,insert,update,references |         |
| l1_id          | int(11)      | NULL              | YES  |     | NULL    |                | select,insert,update,references |         |
| l2_id          | int(11)      | NULL              | YES  |     | NULL    |                | select,insert,update,references |         |
+----------------+--------------+-------------------+------+-----+---------+----------------+---------------------------------+---------+
5 rows in set (0.01 sec)

如您所见，排序规则是latin1_swedish_ci，大概是"utf8_general_ci"，那么我的问题就可以解决.幸运的是，我的开发数据库已经可以了，所以我去了

And as you can see, the collation is latin1_swedish_ci, and presumably if it were "utf8_general_ci", my problems would be solved. Thankfully, my development database is already okay, so I go and

rake db:test:clone_structure

并返回MySql并再次在测试数据库中检查

and back to MySql and check again in the test db

mysql> show full columns from cards;
+----------------+--------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+
| Field          | Type         | Collation       | Null | Key | Default | Extra          | Privileges                      | Comment |
+----------------+--------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+
| id             | int(11)      | NULL            | NO   | PRI | NULL    | auto_increment | select,insert,update,references |         |
| l2_word        | varchar(255) | utf8_general_ci | YES  |     | NULL    |                | select,insert,update,references |         |
| l1_description | text         | utf8_general_ci | YES  |     | NULL    |                | select,insert,update,references |         |
| l1_id          | int(11)      | NULL            | YES  |     | NULL    |                | select,insert,update,references |         |
| l2_id          | int(11)      | NULL            | YES  |     | NULL    |                | select,insert,update,references |         |
+----------------+--------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+
5 rows in set (0.00 sec)

啊，所以现在一切都很好，所以我再次

Ah, so now everything is looking good, so once again I

rake test

但是我又遇到了同样的问题，当我检查测试数据库时，我发现排序规则列已重置为latin1_swedish_ci.

But I get the same problem all over again, and when I check my test db, I find that the collation column has been reset to latin1_swedish_ci.

我不太了解rake测试的工作原理，但是我的工作假设是它使用schema.rb重新创建数据库.现在，在我的一次迁移中，我已经

I do not understand very well how rake test works, but my working hypothesis is that it recreates the DB using schema.rb. Now, in one of my migrations, I've got

 class CreateCards < ActiveRecord::Migration
   def self.up
     create_table :cards, :options => "DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci"  do |t|
       t.column :english_word, :string
       t.column :chinese_description, :text
     end
   end

   def self.down
     drop_table :cards
   end
 end

这显然已经解决了整理问题. (我还有另一个迁移，分别将english_word和chinese_description分别重命名为l2_word和l1_description.)但是，此信息尚未将其放入schema.rb.而且显然地，MySql已经决定假定我想要latin1_swedish_ci.

And this apparently has taken care of the collate problem there. (I've got another migration which renames english_word and chinese_description to l2_word and l1_description, respectively.) But this information has not made it into schema.rb. And somehow, apparently, MySql has decided to assume that I want latin1_swedish_ci.

因此，总而言之，我认为我需要做的是以某种方式编辑某些内容，以便使用utf8_general_ci排序规则，然后我的问题就会消失(对吗?).但是我无法弄清楚如何在"rake测试"执行此操作时使代码运行.有人可以帮忙吗?

So, to summarize, what I think I need to do is somehow edit something so that I'll be using the utf8_general_ci collation, and then my problems will go away (right?). But I cannot figure out how to make the code that gets run when you "rake test" do this. Can anybody help?

对于它的价值，测试和开发数据库都创建为

For what it's worth, both the test and development databases were created as

create database flashcard_test default character set utf8 default collate utf8_general_ci;

和

create database flashcard_development default character set utf8 default collate utf8_general_ci;

我的database.yml有

And my database.yml has

development:
  adapter: mysql
  database: flashcard_development
  username: root
  password: 
  encoding: utf8

test:
  adapter: mysql
  database: flashcard_test
  username: root
  password: 
  encoding: utf8
  collation: utf8_general_ci

http://nhw.pl/wp/2008/09/16/mysql-collat​​e-setting-in-rails-application 似乎表明此问题与RoR和MySql之间的连接有关，但我没有运气那里的建议.

http://nhw.pl/wp/2008/09/16/mysql-collate-setting-in-rails-application seems to suggest that this problem has something to do with the connection between RoR and MySql, but I haven't had any luck with the suggestions there.

推荐答案

将排序规则:utf8_general_ci添加到您的database.yml文件中，就像完成此操作一样.尝试使用"rake RAILS_ENV = test db:migrate:reset db:fixtures load"重新创建测试数据库-警告这将清除除Fixtures之外的所有数据.

Adding the collation: utf8_general_ci to your database.yml file like you have done should do the trick. Try recreating the test database using "rake RAILS_ENV=test db:migrate:reset db:fixtures load" - warning this will clear all data you have there beyond the fixtures.

对我有用.要验证查看数据库，表和列上的排序规则，可以执行以下操作:

That worked for me. To verify see the collation on the database, tables, and columns you can execute the following:

-- Database Collations:
SELECT schema_name,default_character_set_name,default_collation_name 
FROM information_schema.SCHEMATA 
WHERE schema_name not IN ('mysql');

-- Table Collations:
SELECT T.table_schema, T.table_name, T.TABLE_COLLATION, CCSA.CHARACTER_SET_NAME 
FROM information_schema.`TABLES` T,
 information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSA
WHERE CCSA.collation_name = T.table_collation
AND T.table_schema not IN ('mysql');

-- Column Collations:
SELECT table_schema, table_name, column_name, collation_name, character_set_name
FROM information_schema.`COLUMNS` C
WHERE C.table_schema not IN ('mysql')
ORDER BY 1,2,4;

测试数据库中的所有内容现在都应具有在database.yml中指定的排序规则.

Everything in your test database should now have the collation specified in database.yml.

这篇关于“非法的校对"运行Rails测试套件时来自MySql的错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！