Unable to assign users or roles to security group (Database Error) (SuiteCRM 8.2)

Good Afternoon All,

  I am unable to assign roles OR users to security groups (SuiteCRM>Admin>Security Suite Management). Anytime I try, I receive the error seen in the screenshot. The suiteCRM.log is as follows:

Tue Feb 7 14:28:06 2023 [10168][1][FATAL] Mysqli_query failed.
Tue Feb 7 14:28:06 2023 [10168][1][FATAL] Error retrieving SecurityGroup list: Query Failed: (SELECT acl_roles.id , acl_roles.name , acl_roles.description , acl_roles.created_by , ‘aclroles’ panel_name FROM acl_roles INNER JOIN securitygroups_acl_roles ON acl_roles.id=securitygroups_acl_roles.role_id AND securitygroups_acl_roles.securitygroup_id=‘42781c6d-0456-6af1-57a0-63e2a5ae2ac8’ AND securitygroups_acl_roles.deleted=0

where acl_roles.deleted=0) ORDER BY acl_roles.name asc LIMIT 0,10: MySQL error 1250: Table ‘acl_roles’ from one of the SELECTs cannot be used in global ORDER clause
Tue Feb 7 14:28:06 2023 [10168][1][FATAL] [ListView::processUnionBeans] . Database failure. Please refer to suitecrm.log for details.
Tue Feb 7 14:28:17 2023 [16356][1][FATAL] Mysqli_query failed.
Tue Feb 7 14:28:17 2023 [16356][1][FATAL] Query Failed: SELECT favorites.*
FROM favorites
JOIN config ON ( config.id = favorites.parent_id )
WHERE favorites.assigned_user_id = ‘1’
AND favorites.parent_type = ‘Administration’
AND favorites.deleted = 0
AND config.deleted = 0
ORDER BY favorites.date_entered DESC LIMIT 0,10: MySQL error 1054: Unknown column ‘config.deleted’ in ‘where clause’
Tue Feb 7 14:28:50 2023 [10168][1][FATAL] Mysqli_query failed.
Tue Feb 7 14:28:50 2023 [10168][1][FATAL] Query Failed: SELECT favorites.*
FROM favorites
JOIN config ON ( config.id = favorites.parent_id )
WHERE favorites.assigned_user_id = ‘1’
AND favorites.parent_type = ‘Administration’
AND favorites.deleted = 0
AND config.deleted = 0
ORDER BY favorites.date_entered DESC LIMIT 0,10: MySQL error 1054: Unknown column ‘config.deleted’ in ‘where clause’
Tue Feb 7 14:29:37 2023 [572][1][FATAL] Mysqli_query failed.
Tue Feb 7 14:29:37 2023 [572][1][FATAL] Error retrieving SecurityGroup list: Query Failed: (SELECT acl_roles.id , acl_roles.name , acl_roles.description , acl_roles.created_by , ‘aclroles’ panel_name FROM acl_roles INNER JOIN securitygroups_acl_roles ON acl_roles.id=securitygroups_acl_roles.role_id AND securitygroups_acl_roles.securitygroup_id=‘42781c6d-0456-6af1-57a0-63e2a5ae2ac8’ AND securitygroups_acl_roles.deleted=0

where acl_roles.deleted=0) ORDER BY acl_roles.name asc LIMIT 0,10: MySQL error 1250: Table ‘acl_roles’ from one of the SELECTs cannot be used in global ORDER clause
Tue Feb 7 14:29:37 2023 [572][1][FATAL] [ListView::processUnionBeans] . Database failure. Please refer to suitecrm.log for details.
Tue Feb 7 14:29:55 2023 [10168][1][FATAL] Mysqli_query failed.
Tue Feb 7 14:29:55 2023 [10168][1][FATAL] Error retrieving SecurityGroup list: Query Failed: (SELECT acl_roles.id , acl_roles.name , acl_roles.description , acl_roles.created_by , ‘aclroles’ panel_name FROM acl_roles INNER JOIN securitygroups_acl_roles ON acl_roles.id=securitygroups_acl_roles.role_id AND securitygroups_acl_roles.securitygroup_id=‘42781c6d-0456-6af1-57a0-63e2a5ae2ac8’ AND securitygroups_acl_roles.deleted=0

where acl_roles.deleted=0) ORDER BY acl_roles.name asc LIMIT 0,10: MySQL error 1250: Table ‘acl_roles’ from one of the SELECTs cannot be used in global ORDER clause
Tue Feb 7 14:29:55 2023 [10168][1][FATAL] [ListView::processUnionBeans] . Database failure. Please refer to suitecrm.log for details.
Tue Feb 7 14:30:16 2023 [11540][1][FATAL] Mysqli_query failed.
Tue Feb 7 14:30:16 2023 [11540][1][FATAL] Error retrieving ACLRole list: Query Failed: (SELECT securitygroups.id , securitygroups.name , securitygroups.description , securitygroups.assigned_user_id , ‘securitygroups’ panel_name FROM securitygroups INNER JOIN securitygroups_acl_roles ON securitygroups.id=securitygroups_acl_roles.securitygroup_id AND securitygroups_acl_roles.role_id=‘7b6dd089-b7f8-2feb-af62-63e2a5ebcdf5’ AND securitygroups_acl_roles.deleted=0

where securitygroups.deleted=0) ORDER BY securitygroups.name asc LIMIT 0,10: MySQL error 1250: Table ‘securitygroups’ from one of the SELECTs cannot be used in global ORDER clause
Tue Feb 7 14:30:16 2023 [11540][1][FATAL] [ListView::processUnionBeans] . Database failure. Please refer to suitecrm.log for details.
Tue Feb 7 14:33:21 2023 [16356][1][FATAL] Mysqli_query failed.
Tue Feb 7 14:33:21 2023 [16356][1][FATAL] Error retrieving SecurityGroup list: Query Failed: (SELECT acl_roles.id , acl_roles.name , acl_roles.description , acl_roles.created_by , ‘aclroles’ panel_name FROM acl_roles INNER JOIN securitygroups_acl_roles ON acl_roles.id=securitygroups_acl_roles.role_id AND securitygroups_acl_roles.securitygroup_id=‘42781c6d-0456-6af1-57a0-63e2a5ae2ac8’ AND securitygroups_acl_roles.deleted=0

where acl_roles.deleted=0) ORDER BY acl_roles.name asc LIMIT 0,10: MySQL error 1250: Table ‘acl_roles’ from one of the SELECTs cannot be used in global ORDER clause
Tue Feb 7 14:33:21 2023 [16356][1][FATAL] [ListView::processUnionBeans] . Database failure. Please refer to suitecrm.log for details.
Tue Feb 7 14:33:35 2023 [16356][1][FATAL] Mysqli_query failed.
Tue Feb 7 14:33:35 2023 [16356][1][FATAL] Error retrieving SecurityGroup list: Query Failed: (SELECT users.id , users.first_name , users.last_name , LTRIM(RTRIM(CONCAT(IFNULL(users.first_name,‘’),’ ‘,IFNULL(users.last_name,’')))) as full_name, users.user_name , users.phone_work , users.created_by , ‘users’ panel_name FROM users INNER JOIN securitygroups_users ON users.id=securitygroups_users.user_id AND securitygroups_users.securitygroup_id=‘42781c6d-0456-6af1-57a0-63e2a5ae2ac8’ AND securitygroups_users.deleted=0

where users.deleted=0) ORDER BY users.user_name asc LIMIT 0,10: MySQL error 1250: Table ‘users’ from one of the SELECTs cannot be used in global ORDER clause
Tue Feb 7 14:33:35 2023 [16356][1][FATAL] [ListView::processUnionBeans] . Database failure. Please refer to suitecrm.log for details.

You’re using an unsupported PHP version, if I’m not mistaken.

Hey, thanks for the reply!!! I’m using 8.0, which I believe is supported according to the matrix

Folks,

Getting the same error from Both SuiteCRM 7 and 8 using any flavor of PHP8.0

Alright, after some digging on the forms, I found in SuiteCRM 7XXX, folks had to comment out Line 930 on SuiteCRM>Data>sugarbean.php.

Well, if you apply this same fix on line 939 of SuiteCRM>Public>Legacy>Sugarbean.php it also corrects the issue with SuiteCRM 8.2.

2023-02-07_16-44-14

The screenshot is from SuiteCRM7, but is the same fix for 8.2, just line 939 instead of 930.

Hope this can help other folks.

This is fixed in the latest releases.

If you look for it in Github you’ll find an Issue that dealt with this recently.

Hey, good point. Where is the SuiteCRM area of GitHub?

I have exactly the same issue but the solution didn’t work for me. the line you have mentioned SuiteCRM>Public>Legacy>Sugarbean.php is actually located at SuiteCRM>Public>Legacy>Data>Sugarbean.php but still didn’t work for me.

1 Like

There are actually two places you need to fix in that file: Fix salesagility#9788 - Database Failures in Subpanels throughout SuiteCRM (MySQL Error) by hbartel · Pull Request #9843 · salesagility/SuiteCRM · GitHub

If i upgrade the PHP from 8.0 to 8.1 will the error go away. Also do you know simple step by step how to fix this error in my 8.4 beta

I applied the fix to the SugarBean.php and I am all set. Gratitude to all you guys to share this.