MySQL+VBB问题：mysql 错误: mysql 错误: Illegal mix of collations (latin1_bin,IMPLICIT) and (latin1_swedish_

续上篇文章，自从升级 MySQL 到 4.1.8 之后，我的VBB又有了新的问题：

在 faq 搜索时不时会遇到数据库错误，描述如下：

---

数据库错误出现于 vBulletin 3.0.1:
 
Invalid SQL:
                SELECT faqname, faqparent, phrase.text AS title
                FROM faq AS faq
                INNER JOIN phrase AS phrase ON(phrase.phrasetypeid = 7000 AND ph
rase.varname = faq.faqname)
                WHERE phrase.languageid IN(-1, 0, 1)
                        AND (
                        faqparent IN('vb_custom_status', 'vb_update_profile', 'v
b_calendar_how', 'vb_referrals_explain', 'vb_sig_explain', 'vb_user_maintain')
                        OR
                        faqname IN('vb_custom_status', 'vb_update_profile', 'vb_
calendar_how', 'vb_referrals_explain', 'vb_sig_explain', 'vb_user_maintain')
                )
 
mysql 错误: Illegal mix of collations (latin1_bin,IMPLICIT) and (latin1_swedish_
ci,IMPLICIT) for operation '='
 
mysql 错误号: 1267
 
日期: Thursday 06th of January 2005 11:43:59 AM
脚本: http://club.zsnet.com/faq.php?s=&do=search&q=%B8%F6%C8%CB%D7%CA%C1%CF&matc
h=all&titlesonly=0
涉及:
用户名: 未注册
IP 地址:

---

经过 google 搜索，在VBB官方论坛上发现的解决办法：
登录MYSQL后执行这个语句就可以了：
ALTER TABLE `phrase` CHANGE `varname` `varname` VARCHAR( 250 ) NOT NULL;


据说是 MYSQL 的字符 charset 的问题。具体都不甚了解。




有关英文链接：
1。 http://www.vbulletin.com/forum/bugs.php?do=view&bugid=3624
2。 http://www.vbulletin.com/forum/bugs.php?do=view&bugid=3627
3。 http://dev.mysql.com/doc/mysql/en/CHAR.html


下面是英文帖子内容的摘要：
You must have set the server character set/collation different from your connection client character set/collation setting. 
Problem is usually resolved if you change the character set to "latin1" or the collatioin to "latin1_swedish_ci".

---

For now, try this:

Code:

  
  

   
   ALTER TABLE faq CHANGE faqname faqname VARCHAR(250) BINARY NOT NULL 
  
  

latin1_bin is the collation of a field set to binary using the latin1 Charset. The alter query I give will set your faqname field to binary, using whatever charset your database is set to, most likely latin1. If it was utf8, then it would come out as utf8_bin.

Basically, it boils down to it was possible to compare a binary varchar vs. a non binary varchar before 4.1, and now it isn't. It would still be possible to achive this when using runtime Collation conversion on the fields but it is better to get the fields in sync rather than converting them at runtime.

Quote:

As of MySQL 4.1, values in CHAR and VARCHAR columns are sorted and compared according to the collation of the character set assigned to the column. Before MySQL 4.1, sorting and comparison are based on the collation of the server character set;

This bug report is only for the specific issue mentioned. Issues that are going to arise with these new headaches, er features, in 4.1.x are for the forums.

---

Just to ensure you are aware, anyone installing MySQL 4.1 will need to install PHP 5.x because PHP 4.x does not support the changes of MySQL 4.1. It is not advised to stay on PHP 4.x when you are on MySQL 4.1.

The only work around to make MySQL 4.1 work with anything before PHP 5.x, is to make MySQL:
1.) use old passwords (by enabling "old_passwords" option) and,
2.) set default character set to "latin1" and collation to "latin1_swedish_ci".

Also, anyone upgrading from MySQL 4.0 to MySQL 4.1 is required to first backup all data, uninstall MySQL 4.0, then install MySQL 4.1, restore all data. During the restore, if they don't sepcify the default character set specifically, they may accidentally select a different character set causing queries to fail with "Illegal mix of collation" errors. As the client will be using the default character set, while the tables are using another character set.

---