[SOLVED] Collation error

13 years 5 months ago #144576 by dankel8
[SOLVED] Collation error was created by dankel8
For the purpose of double checking this problem, I have installed a new installation of Joomla 1.5.2 with sample files. I have activated nothing, made no alterations and the only addition is the com_comprofiler.zip file from the CB_1.2.3 package. When I run the “CB Tools: Check Users database: Results” I get the message:

ERROR: sql query: SELECT u.id FROM #__users u LEFT JOIN #__core_acl_aro a ON a.section_value = 'users' AND a.value = CAST( u.id AS CHAR ) WHERE a.value IS NULL : returned error: Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '=' SQL=SELECT u.id FROM jos_users u LEFT JOIN jos_core_acl_aro a ON a.section_value = 'users' AND a.value = CAST( u.id AS CHAR ) WHERE a.value IS NULL

I checked FAQs and followed the instructions to the best of my limited ability.

The “Collation” column shows “utf8_unicode_ci” for ALL tables.

Fields “Varchar – Text – Tinytext – Longtext” all show utf8_unicode_ci

Should I be looking at something else or can I ignore this problem?

Regards
DK

Post edited by: krileon, at: 2010/11/30 14:33

Please Log in to join the conversation.

13 years 5 months ago #144627 by krileon
Replied by krileon on topic Re:Collation error
The tables _users and _core_acl_aro don't have the same collations. One of them has utf8_general_ci while the other has utf8_unicode_ci. Please double check as they are very similar.


Kyle (Krileon)
Community Builder Team Member
Before posting on forums: Read FAQ thoroughly + Read our Documentation + Search the forums
CB links: Documentation - Localization - CB Quickstart - CB Paid Subscriptions - Add-Ons - Forge
--
If you are a Professional, Developer, or CB Paid Subscriptions subscriber and have a support issue please always post in your respective support forums for best results!
--
If I've missed your support post with a delay of 3 days or greater and are a Professional, Developer, or CBSubs subscriber please send me a private message with your thread and will reply when possible!
--
Please note I am available Monday - Friday from 8:00 AM CST to 4:00 PM CST. I am away on weekends (Saturday and Sunday) and if I've missed your post on or before a weekend after business hours please wait for the next following business day (Monday) and will get to your issue as soon as possible, thank you.
--
My role here is to provide guidance and assistance. I cannot provide custom code for each custom requirement. Please do not inquire me about custom development.

Please Log in to join the conversation.

13 years 4 months ago #147080 by STUCKSHUTTER
Replied by STUCKSHUTTER on topic Re:Collation error
Can you clarify which table to change and how that can be best done?

To clarify: My site has jos_users set as utf8_unicode_ci and jos_core_acl_aro set as utf8_unicode_ci

Which table should be changed to utf8_general_ci since I don't know which to change?

I can find the tables OK and see I can change the field collations but am no MYSQL expert by a long shot so a little help will be much appreciated.

Any idea what made the change to the collation and how to prevent it in the future?(We reinstalled the site on a new server manually with Akeeba BU).

Please Log in to join the conversation.

13 years 4 months ago #147100 by STUCKSHUTTER
Replied by STUCKSHUTTER on topic Re:Collation error
Following up on the previous post: this is puzzling, both tables have exactly the same collation: utf8_unicode_ci on all varchar fields. I have checked and double checked both table and all fields, all other fields are blank.

I am more or less at a loss as to what the next step should be. Is this an error in fact or just an artifact of something else and something I can ignore?

Thanks for any help on this one...

Post edited by: STUCKSHUTTER, at: 2010/11/27 21:59
Attachments:

Please Log in to join the conversation.

13 years 4 months ago #147102 by nant
Replied by nant on topic Re:Collation error
STUCKSHUTTER wrote:

Can you clarify which table to change and how that can be best done?

To clarify: My site has jos_users set as utf8_unicode_ci and jos_core_acl_aro set as utf8_unicode_ci

Which table should be changed to utf8_general_ci since I don't know which to change?

I can find the tables OK and see I can change the field collations but am no MYSQL expert by a long shot so a little help will be much appreciated.

Any idea what made the change to the collation and how to prevent it in the future?(We reinstalled the site on a new server manually with Akeeba BU).


Please post exact error you are getting

Please Log in to join the conversation.

13 years 4 months ago #147104 by STUCKSHUTTER
Replied by STUCKSHUTTER on topic Re:Collation error
This is the error message:

CB Tools: Check Users database: Results

Checking Users Datbase:

All Community Builder comprofiler table rows have links to user table.

All users table rows have links to comprofiler table.

users table has no zero id row.

comprofiler table has no zero id row.

All rows in comprofiler table have user_id columns identical to id columns.

ERROR: sql query: SELECT u.id FROM #__users u LEFT JOIN #__core_acl_aro a ON a.section_value = 'users' AND a.value = CAST( u.id AS CHAR ) WHERE a.value IS NULL : returned error: Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '=' SQL=SELECT u.id FROM jos_users u LEFT JOIN jos_core_acl_aro a ON a.section_value = 'users' AND a.value = CAST( u.id AS CHAR ) WHERE a.value IS NULL

All ACL core_acl_aro table rows have corresponding entries in users table.

All core_acl_aro table rows have ACL entries in core_acl_groups_aro_map table.

All core_acl_aro table rows have ACL entries in core_acl_groups_aro_map table.
Attachments:

Please Log in to join the conversation.

Moderators: beatnantkrileon
Time to create page: 0.227 seconds

Facebook Twitter LinkedIn