SUITECRM Database failure After Assigning Role Management

gowtham.s · 2 November 2022 11:04

Hello Team,

I am facing one issue in suitecrm role management.

After creating roles and assigning a user to the role which is resulting in Database Failure. I don’t know how to resolve this.

Can anyone suggest me how to clear the database failure & assign roles to users.

LOG REPORT:

Database failure. Please refer to suitecrm.log for details.
Wed Nov 2 10:47:30 2022 [504775][1][FATAL] Mysqli_query failed.
Wed Nov 2 10:47:30 2022 [504775][1][FATAL] Error retrieving ACLRole 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 acl_roles_users ON users.id=acl_roles_users.user_id AND acl_roles_users.role_id=‘125fdbae-1df5-d34a-dd6f-63624a30361b’ AND acl_roles_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

chirag_biz309 · 2 November 2022 12:25

Hello @gowtham.s

I hope this is helpful.

github.com/salesagility/SuiteCRM

Database Failures in Subpanels throughout SuiteCRM (MySQL Error)

opened 03:33PM - 12 Oct 22 UTC

pstevens71

Area: Module

#### Issue Database error in subpanels with the error: ERROR 1250 (42000): Table ‘…’ from one of the SELECTs cannot be used in global ORDER clause. The issue pertains to use the Table name in the sort clause. This seemed to work fine until today (Oct. 10. 2022). So must have been a recent update in mySQL that no longer permits this syntax. I was able to get the subpanel to display contacts again by sorting on a different column. Example below (screenshots) is in the Cases module and Contacts Subpanel. However, this issue is throughout SuiteCRM and not just this module, and not just contacts. ![sort problem](https://user-images.githubusercontent.com/29544935/195383235-d940e040-4b12-495b-8b7f-bb7f0afa4496.png) When I sort on "Accounts" it works: ![working sort problem](https://user-images.githubusercontent.com/29544935/195383318-aff4c101-e63a-44e1-8fd7-2713a6eb00e2.png) #### Expected Behavior The contact in the subpanel should display without error. #### Actual Behavior Contacts in subpanel display with database error. ```Wed Oct 12 14:08:16 2022 [16187][1][FATAL] Error retrieving Case list: Query Failed: (SELECT contacts.id , contacts.first_name , contacts.last_name , contacts.salutation , LTRIM(RTRIM(CONCAT(IFNULL(contacts.first_name,''),' ',IFNULL(contacts.last_name,'')))) as name, ' ' account_name , ' ' account_id , contacts.phone_work , contacts.assigned_user_id , 'contacts' panel_name FROM contacts LEFT JOIN contacts_cstm ON contacts.id = contacts_cstm.id_c INNER JOIN contacts_cases ON contacts.id=contacts_cases.contact_id AND contacts_cases.case_id='9e5568fd-3fd7-5130-0a83-6345f29aafeb' AND contacts_cases.deleted=0 where contacts.deleted=0) ORDER BY contacts.last_name, contacts.first_name asc LIMIT 0,10: MySQL error 1250: Table 'contacts' from one of the SELECTs cannot be used in global ORDER clause ``` #### Possible Fix As a work around, I can sort by another column to avoid the error. The fix is to remove the table name from "sort by" I think, but this is throughout all the subpanels not sure where to start. the ORDERBY contacts.last_name I think is what's causing the problem and should be just ORDERBY last_name. #### Steps to Reproduce 1. Go into any module that has Contacts subpanel, see it doesn't work. Also Project Tasks have same issue so its more than just contacts. See screen shots provided. #### Context This is a huge issue, I imagine affects every installation on MySQL 8.0.31 #### Your Environment * SuiteCRM Version used: 7.12.7 * Browser name and version (e.g. Chrome Version 51.0.2704.63 (64-bit)): Chrome * Environment name and version (e.g. MySQL, PHP 7): MySQL 8.0.31 * Operating System and version (e.g Ubuntu 16.04): WHM/Cpanel, Centoi v7.9.2009

pstevens · 2 November 2022 13:17

The role management sounds like a different issue, (in terms of the fix is specifically for the subpanel issue) but may be related to bracket issue in the latest version of mysql as well. The error generated looks the same.

hvb · 2 November 2022 18:24

This is exactly the same issue and the fix is the same.
Note the error in the subpanned at the bottom of the screenshot.

This issue impacts subpannes in every module as all the supannels use SugarBean.php when pulling data. Where the user first encounters the error is dependent on what part of the CRM they use after the MySQL environment on their server is updated.

To fix:
In SuiteCRM/data/SugarBean.php
Comment out line 876 & 930

Line 876: // $final_query = ‘(’ . $tmp_final_query . ‘)’;
…
Line 930: // $query = ‘(’ . $query . ‘)’;

pstevens · 2 November 2022 18:57

Cool fix multiple problems with the same fix!

gowtham.s · 3 November 2022 06:59

@hvb

It works now thanks for the solution it is resolved