Collation Error - how to overcome - Joomla/Civicrm

[stevegjacobs]

I tried to create a join between the Joomla users table and a civicrm table. On saving, I got this error:

Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '=' SQL=SELECT SQL_CALC_FOUND_ROWS DISTINCT `jos_users`.`id` AS `jos_users___id`,
`jos_users`.`id` AS `jos_users___id_raw`,
`jos_users`.`name` AS `jos_users___name`,
`jos_users`.`name` AS `jos_users___name_raw`,
`jos_users`.`username` AS `jos_users___username`,
`jos_users`.`username` AS `jos_users___username_raw`,
`jos_users`.`email` AS `jos_users___email`,
`jos_users`.`email` AS `jos_users___email_raw`,
`jos_users`.`password` AS `jos_users___password`,
`jos_users`.`password` AS `jos_users___password_raw`,
`jos_users`.`block` AS `jos_users___block`,
`jos_users`.`block` AS `jos_users___block_raw`,
`jos_users`.`sendEmail` AS `jos_users___sendEmail`,
`jos_users`.`sendEmail` AS `jos_users___sendEmail_raw`,
`jos_users`.`registerDate` AS `jos_users___registerDate`,
`jos_users`.`registerDate` AS `jos_users___registerDate_raw`,
`jos_users`.`lastvisitDate` AS `jos_users___lastvisitDate`,
`jos_users`.`lastvisitDate` AS `jos_users___lastvisitDate_raw`,
`jos_users`.`activation` AS `jos_users___activation`,
`jos_users`.`activation` AS `jos_users___activation_raw`,
`jos_users`.`params` AS `jos_users___params`,
`jos_users`.`params` AS `jos_users___params_raw`,
`jos_users`.`lastResetTime` AS `jos_users___lastResetTime`,
`jos_users`.`lastResetTime` AS `jos_users___lastResetTime_raw`,
`jos_users`.`resetCount` AS `jos_users___resetCount`,
`jos_users`.`resetCount` AS `jos_users___resetCount_raw`,
`jos_users`.`otpKey` AS `jos_users___otpKey`,
`jos_users`.`otpKey` AS `jos_users___otpKey_raw`,
`jos_users`.`otep` AS `jos_users___otep`,
`jos_users`.`otep` AS `jos_users___otep_raw`,
`civicrm_uf_match`.`id` AS `civicrm_uf_match___id`,
`civicrm_uf_match`.`id` AS `civicrm_uf_match___id_raw`,
`civicrm_uf_match`.`domain_id` AS `civicrm_uf_match___domain_id`,
`civicrm_uf_match`.`domain_id` AS `civicrm_uf_match___domain_id_raw`,
`civicrm_uf_match`.`uf_id` AS `civicrm_uf_match___uf_id`,
`civicrm_uf_match`.`uf_id` AS `civicrm_uf_match___uf_id_raw`,
`civicrm_uf_match`.`uf_name` AS `civicrm_uf_match___uf_name`,
`civicrm_uf_match`.`uf_name` AS `civicrm_uf_match___uf_name_raw`,
`civicrm_uf_match`.`contact_id` AS `civicrm_uf_match___contact_id`,
`civicrm_uf_match`.`contact_id` AS `civicrm_uf_match___contact_id_raw`,
`civicrm_uf_match`.`language` AS `civicrm_uf_match___language`,
`civicrm_uf_match`.`language` AS `civicrm_uf_match___language_raw`,
`jos_users`.`id` AS slug
, `jos_users`.`id` AS `__pk_val`

FROM `jos_users`
INNER JOIN `civicrm_uf_match` AS `civicrm_uf_match` ON `civicrm_uf_match`.`uf_name` = `jos_users`.`email` LIMIT 0, 10

I am not sure what to do about this error. It appears that Joomla and Civicrm have installed a mix of collations utf8_general, utf8_unicode, and at least one Latin_swedish table. This is all in a new install of latest version of both products

 

[stevegjacobs]

Here is a couple of solutions proposed by mysql forums. Any way to do this in Fabrik?
Your solution is to specify a shared collation for the two columns within the query. Here are some example uses of the COLLATE clause:
SELECT*COLLATE latin1_general_ci FROMtableORDERBYkey;SELECT*FROMtableORDERBYkeyCOLLATE latin1_general_ci;
Another option is to use the BINARY operator which is simply a shorthand version of COLLATE. Your solution might look something like this:
SELECT*FROMtableWHERE BINARY a = BINARY b;
Or,
SELECT*FROMtableORDERBY BINARY a;

 

[troester]

What is the collation of your database?

 

[stevegjacobs]

It was Latin_swedish, but I have changed it to utf8_general_ci. Most tables were installed as utf8, but the jxx_users table was latin.

 

[troester]

A change of DB collation does only affect tables created after the change.
If you say "latin" where does "...Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT)" come from?
Are the civicrm tables utf8_unicode?

Maybe creating a new DB with utf8_general_ci and then doing a new installation (Joomla+civicrm) will help.

 

[rob]

I tested and I see the same thing.
I've updated the code in github, so that you can now edit your list and under the advanced section, select 'Force Query Collation' to uft_general_ci, which will append

COLLATE uft_general_ci

to the end of the list's query, after saving the list the query should run.

For info on how to update from github see my signature, and for reference this is the commit that adds the option https://github.com/Fabrik/fabrik/commit/85461eb24c96d51e4af6c561895ed4ec0f7af3eb