Illegal mix of collations [Solved - not jD Bug]

[yaresa]

hi, i am using joomls 3.3.6 in administrator psge, when i cilick on the jdownloads/downloads link in main menu i see an error message like this,


can you help please.

Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '=' SQL=SELECT COUNT(*) FROM `bslho_jdownloads_files` AS a LEFT JOIN bslho_users AS uc ON uc.id=a.checked_out LEFT JOIN `bslho_jdownloads_files` AS f ON f.file_id = a.other_file_id LEFT JOIN `bslho_languages` AS l ON l.lang_code = a.language LEFT JOIN bslho_viewlevels AS ag ON ag.id = a.access LEFT JOIN bslho_jdownloads_categories AS c ON c.id = a.cat_id LEFT JOIN bslho_jdownloads_categories AS cc ON cc.id = c.parent_id WHERE (a.published = 0 OR a.published = 1) Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '=' SQL=SELECT a.file_id, a.file_title, a.file_alias, a.description, a.file_pic, a.price, a.release, a.cat_id, a.size, a.date_added, a.publish_from, a.publish_to, a.use_timeframe, a.url_download, a.other_file_id, a.extern_file, a.downloads, a.extern_site, a.notes, a.access, a.language, a.checked_out, a.checked_out_time, a.ordering, a.published, a.asset_id,uc.name AS editor,f.url_download AS other_file_name, f.file_title AS other_download_title,l.title AS language_title,ag.title AS access_level,c.title AS category_title, c.parent_id AS category_parent_id,cc.title AS category_title_parent FROM `bslho_jdownloads_files` AS a LEFT JOIN bslho_users AS uc ON uc.id=a.checked_out LEFT JOIN `bslho_jdownloads_files` AS f ON f.file_id = a.other_file_id LEFT JOIN `bslho_languages` AS l ON l.lang_code = a.language LEFT JOIN bslho_viewlevels AS ag ON ag.id = a.access LEFT JOIN bslho_jdownloads_categories AS c ON c.id = a.cat_id LEFT JOIN bslho_jdownloads_categories AS cc ON cc.id = c.parent_id WHERE (a.published = 0 OR a.published = 1) ORDER BY a.file_title asc LIMIT 0, 20 Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '=' SQL=SELECT COUNT(*) FROM `bslho_jdownloads_files` AS a LEFT JOIN bslho_users AS uc ON uc.id=a.checked_out LEFT JOIN `bslho_jdownloads_files` AS f ON f.file_id = a.other_file_id LEFT JOIN `bslho_languages` AS l ON l.lang_code = a.language LEFT JOIN bslho_viewlevels AS ag ON ag.id = a.access LEFT JOIN bslho_jdownloads_categories AS c ON c.id = a.cat_id LEFT JOIN bslho_jdownloads_categories AS cc ON cc.id = c.parent_id WHERE (a.published = 0 OR a.published = 1)

[ColinM]

Hi
Have you just migrated from a jD1.9 or jD2.5 series?
Colin

[rascreek]

I have the same problem. I migrated from jdownloads 1.9.2.11 to 3.2.37.

I even tried a clean install - still the same illegal mix of collations message.
i really hope somebody can help me. thanks in advance, rascreek

[Arno]

Hi.
You use utf8_unicode_ci for collation in your joomla database? Why?
You get the error only when you try to open the Downloads page in backend?

Please compare the pic below with your db setting in phpmyadmin

[gelöscht durch Administrator]

[rascreek]

Hi Arno,
thanks a lot for your quick reply. I am almost going crazy with this problem.  
Why I use it - I don

[rascreek]

sorry my post are always cut. I dont know why. I just cant fully post my message.

[rascreek]

Since i can not post a full message I append a screenshot from my message. Sorry for this!

[gelöscht durch Administrator]

[Arno]

Hi.
Sorry for the temp problems with the editor. We work on it.

To your problem:
when it exist not really important reason to use in your case utf8_unicode_ci, you should change it to utf8_general_ci with help from phpmyadmin. See my pic above.

[Arno]

Sorry, have first now see your pic with your answer above.
Please send me login data for your backend and also for your phpmyadmin (via PM). Then i will see what i can do.
But first create a DB backup.

[rascreek]

Hi Arno, thanks a lot for your help. finally I got rid of the problem. the solution was like you mentioned change utf8 to general. I did exactly like you told me but that wasn´t enough. there was still some utf8_unicode in some places. So I made an sql dump searched for utf8_unicode and replaced it by utf8_general.
That´s it. Thanks for your help and sorry that it took so long to answer. great job arno