MySQL error 1267: Illegal mix of collations

[Marty]

Hi guys, i am adding on a custom field within the accounts module, this custom field will be a unique identifier for each individual account. When i create this field and then go to the accounts tab the following error message appears:

" Error running count query for Account List: Query Failed:SELECT count(*) FROM accounts LEFT JOIN users ON accounts.assigned_user_id=users.id LEFT JOIN accounts_cstm ON accounts.id = accounts_cstm.id_c where accounts.deleted=0 ::MySQL error 1267: Illegal mix of collations (latin1_general_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '=' "

When i delete the custom field it start working again, creating custom fields within the contacts module didnt cause this problem.

I have checked through all the mysql details for the tables and can find no reference to a collation being set to latin1_swedish_ci, they are all set to either latin1_general_ci or default.

PLEASE PLEASE help me on this as it is driving me insane.

[sadek]

I know this is an old one, but it does occationally come up, and I have just added a solution to this problem to the sugarcrm.com wiki.

Please see http://www.sugarcrm.com/wiki/index.p...g_to_Sugar_4.5

-Sadek

[hfgmis]

I noticed that before I added a custom field to accounts the accounts_cstm table did not exist. The problem is when the table is created the Character Set was not set correctly. You have to change it in two differant places within the table setup as well.

In MySQL Administrator edit the accounts_cstm table, select Table Options
I had to change the Character Set as follows:
Charset: utf8
Collation: utf8_general_ci

Then on the Colmuns and Indices tab, select the first field then click on the Colmun details tab on the lower part of the screen. On the right side you will again see Charset and Collate, change these to be the same as above. You have to do this for each of the custom fields in the table plus the id field.

Once I did this and applied my changes the account section began working, custom fields and all.