by eggsurplus

Control what your users can access and save time, money, and frustrations. Lock down sensitive data in SugarCRM or SuiteCRM to specific groups or teams. Supports unlimited assigned users, unlimited group assignments to records, custom layouts for each group, login/sudo capabilities and much more.

Free 30 day trial
Try it Now

By clicking you consent to share your profile with the developer

#118 - Installation breaks every module

Closed Bug? created by mberniz 9 years ago

After installing on fresh 6.5.12 it breaks the accounts/contacts/opportunities main view of modules. I have done the repair, rebuild modules and relationships.

Log says this:

Fri Apr 26 11:19:35 2013 [1290][572cb3bd-c001-66d6-9473-51793c2ae412][FATAL] Error retrieving SecurityGroup list: Query Failed: SELECT securitygroups.* , LTRIM(RTRIM(CONCAT(IFNULL(jt0.first_name,''),' ',IFNULL(jt0.last_name,'')))) modified_by_name , jt0.created_by modified_by_name_owner , 'Users' modified_by_name_mod , LTRIM(RTRIM(CONCAT(IFNULL(jt1.first_name,''),' ',IFNULL(jt1.last_name,'')))) created_by_name , jt1.created_by created_by_name_owner , 'Users' created_by_name_mod , LTRIM(RTRIM(CONCAT(IFNULL(jt2.first_name,''),' ',IFNULL(jt2.last_name,'')))) assigned_user_name , jt2.created_by assigned_user_name_owner , 'Users' assigned_user_name_mod , LTRIM(RTRIM(CONCAT(IFNULL(jt3.first_name,''),' ',IFNULL(jt3.last_name,'')))) modified_user_name , jt3.created_by modified_user_name_owner , 'Users' modified_user_name_mod FROM securitygroups LEFT JOIN users jt0 ON AND jt0.deleted=0

AND jt0.deleted=0 LEFT JOIN users jt1 ON AND jt1.deleted=0

AND jt1.deleted=0 LEFT JOIN users jt2 ON AND jt2.deleted=0

AND jt2.deleted=0 LEFT JOIN users jt3 ON AND jt3.deleted=0

AND jt3.deleted=0 where securitygroups.deleted=0 ORDER BY MySQL error 1267: Illegal mix of collations (utf8_spanish_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='

  1. eggsurplus member avatar

    eggsurplus Provider Affiliate

    9 years ago

    This appears to be unrelated to SecuritySuite (although it could be argued an issue with SugarCRM installers). You have some tables defined as having a utf8_spanish_ci collation with some of the columns being utf8_general_ci (or vice versa). The fix is to alter the problem columns directly in the database so that the column collations match the table collations. See for more info on this MySQL oddity.

  2. eggsurplus member avatar

    eggsurplus Provider Affiliate

    9 years ago

    A little more info; when checking the columns above check for "securitygroups" table. If that fixes the problem then you may also need to address securitygroups_acl_roles, securitygroups_audit, securitygroups_default, securitygroups_message, securitygroups_records, and securitygroups_users. I've never seen this specific issue in the 7 years that this has been around so I'm very interested in helping find out the cause. It seems like it would be specific to installs that have set the collation of the database to something other than the expected utf8_general_ci.

  3. mberniz member avatar


    9 years ago

    Thank you very much for the help. Changing the inconsistent collation in tables and columns made it. No clue how they became utf8_spanish_ci. ¿Is it possible sugar is ignoring default collation for tables and columns while ss is not? (at time of table creation, i mean)



    • eggsurplus member avatar

      eggsurplus Provider Affiliate

      9 years ago

      No problem. I wonder if the original installer went off of MySQL defaults for your server, but then the Module Loader used the SugarCRM default collation. Very odd issue and I don't really have a solid answer to that one. Let me know if you need anything else.

  4. mberniz member avatar


    9 years ago

    Thank you very much for your help. That made it, fixing the inconsistent utf8_spanish_ci tables and columns. Not sure what can have cause that. ¿is it possible sugarcrm is ignoring default collation while ss is not? (at time of tables creation i mean)

This case is public. Please leave out any sensitive information such as URLs, passwords, etc.
Saving Comment Saving Comment...
  • "The perfect solution for managing security. Very stable and easy to implement. Must-have add-on for every CE implementation!"

    Read More Reviews