CiviCRM Community Forums (archive)

There seem to be numerous threads regarding the ajax quick search not working on Joomla.
I have recently upgraded and tried everything I can find related to deleting templates_c, config.IDS, clearing caches, resetting menus, etc
Is there a general problem or is unique to each site - any help gratefully received

After a bit of digging I have now found the ajax query fails as below
....com_civicrm/civicrm/api/v3/Contact.php(763): CRM_Core_DAO::executeQuery("\n        SELECT DISTINCT(id), data, sort_name , email, phone, state_province...")

Thrown error is
...com_civicrm/civicrm/packages/DB/mysql.php(898): DB_common->raiseError(-1, NULL, NULL, NULL, "1271 ** Illegal mix of collations for operation 'UNION'")

I have checked the table collations just in case - but they are all fine.

What I now need to know is how to get a copy of the sql that is actually being generated within the api call as enabling debugging and trace does not seem to work for this

Found some time to look at this and seem to have fixed it - for my site anyway.

Here's how:

1. After trying smart search, the log file in folder ../media/civicrm/ConfigAndLog/ included the following:
#6 .../administrator/components/com_civicrm/civicrm/packages/DB/mysql.php(898): DB_common->raiseError(-1, NULL, NULL, NULL, "1271 ** Illegal mix of collations for operation 'UNION'")

2. Added the line:
define( 'CIVICRM_DEBUG_LOG_QUERY', 1 );
to my ../administrator/components/com_civicrm/civicrm.setting.php file, to log mysql statements.
[thanks to jorich_2000 for this tip]

3. After trying the smart search again, the log file now included:
$Query = string(1668) "
        SELECT DISTINCT(id), data, sort_name , email, phone, state_province, country
        FROM   (
            ( SELECT 0 as exactFirst, cc.id as id, CONCAT_WS( ' :: ', sort_name, email, phone, phone_ext, street_address, city, ste.name, coy.name ) as data , sort_name, email, phone, phone_ext, street_address, city, ste.name as state_province, coy.name as country
            FROM   civicrm_contact cc LEFT JOIN civicrm_email eml ON ( cc.id = eml.contact_id AND eml.is_primary = 1 )  LEFT JOIN civicrm_phone phe ON ( cc.id = phe.contact_id AND phe.is_primary = 1 )  LEFT JOIN civicrm_address sts ON ( cc.id = sts.contact_id AND sts.is_primary = 1)   LEFT JOIN civicrm_state_province ste ON ( sts.state_province_id = ste.id  )   LEFT JOIN civicrm_country coy ON ( sts.country_id = coy.id  )
   
     
     WHERE ( cc.id = '1')
    LIMIT 0, 10 )
    UNION
    ( SELECT 1 as exactFirst, cc.id as id, CONCAT_WS( ' :: ', sort_name, email, phone, phone_ext, street_address, city, ste.name, coy.name ) as data , sort_name, email, phone, phone_ext, street_address, city, ste.name as state_province, coy.name as country
    FROM   civicrm_contact cc LEFT JOIN civicrm_email eml ON ( cc.id = eml.contact_id AND eml.is_primary = 1 )  LEFT JOIN civicrm_phone phe ON ( cc.id = phe.contact_id AND phe.is_primary = 1 )  LEFT JOIN civicrm_address sts ON ( cc.id = sts.contact_id AND sts.is_primary = 1)   LEFT JOIN civicrm_state_province ste ON ( sts.state_province_id = ste.id  )   LEFT JOIN civicrm_country coy ON ( sts.country_id = coy.id  )
   
     
     WHERE ( cc.id = '1')
    ORDER BY sort_name
    LIMIT 0, 10 )
) t
ORDER BY exactFirst, sort_name
LIMIT    0, 10
    "

4. Ran the following mysql query against the database to view the collation details of the database tables:
show table status;
Noted that for the tables referred to in the above mysql query, the tables and columns had a mixture of collations. Some with utf8_unicode_ci others with utf8_general_ci and some with DEFAULT.

5. Ran the same query:
show table status;
on a brand new Joomla!/CiviCRM site that I recently created on the same server, using the latest versions of Joomla! and CiviCRM.
Smart search *is* working fine on the new site.
Noted that are was no mix of collations, all are: utf8_unicode_ci.

6. Noted that the collation for the civicrm_email table was different for the two sites. So decided to try changing the collation on live database to be the same as that on the new (working) database.
Changed the collation for the table and columns (including existing data) for the civicrm_email table, using the following mysql query:
alter table civicrm_email convert to character set utf8 collate utf8_unicode_ci;
[thanks again to jorich_2000 for pinpointing the email table as the potential problem]

7. Tested smart search again - seems to be fixed!

Just a post to document that this solution also worked for me, using Joomla 2.5.20 and going from CiviCRM 4.3.7 to 4.4.5.  Thanks to all who posted!