Probleme beim Einstellen Sicherheitsgruppen

jordan · 19 April 2023 10:44

Guten Tag,
ich möchte gerade Sicherheitsgruppen einstellen und habe eine Schwierigkeit.
Ich habe die Sicherheitsgruppen erstellt aber wenn ich die Sicherheitsgruppe mit einer Rolle verbinden möchte , bekomme ich einen Fehler “Datenbank Fehler,Bitte sehen Sie in der Datei suitecrm.log nach.”

Ich weiß nicht was das bedeutet und hab keine Idee wie ich das lösen kann.
Wurde jemand schon damit konfrontiert ?
Vielen Dank

crmspace · 19 April 2023 10:53

Hi,
dann mal im Log schauen welche Fehlermeldung genau geworfen wird. Idealerweise hier posten

jordan · 19 April 2023 10:56

Danke für deine Rückmeldung.
Ich habe eine Diagnose gemacht und die entsprechende Folder heruntergeladen.
In dem Folder gibt es mehrere Dateien.,
Was genau soll ich hier posten ?

jordan · 19 April 2023 10:59

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
Wed Apr 19 12:25:34 2023 [518628][3b99d166-60bb-4d51-43fd-639af5836e6b][FATAL] [ListView::processUnionBeans] . Datenbank Fehler. Bitte sehen Sie in der Datei suitecrm.log nach.
Wed Apr 19 12:25:35 2023 [518628][3b99d166-60bb-4d51-43fd-639af5836e6b][FATAL] Mysqli_query failed.
Wed Apr 19 12:25:35 2023 [518628][3b99d166-60bb-4d51-43fd-639af5836e6b][FATAL] Error retrieving User list: Query Failed: (SELECT securitygroups.id , securitygroups.name , ’ ’ securitygroup_noninher_fields , ’ ’ user_id , securitygroups.description , securitygroups.assigned_user_id , ‘securitygroups’ panel_name FROM securitygroups INNER JOIN securitygroups_users ON securitygroups.id=securitygroups_users.securitygroup_id AND securitygroups_users.user_id=‘c4fbf83e-6b78-f653-f83d-608bf2eeaab5’ AND securitygroups_users.deleted=0

where securitygroups.deleted=0) ORDER BY securitygroups.name desc LIMIT 0,10: MySQL error 1250: Table ‘securitygroups’ from one of the SELECTs cannot be used in global ORDER clause
Wed Apr 19 12:25:35 2023 [518628][3b99d166-60bb-4d51-43fd-639af5836e6b][FATAL] [ListView::processUnionBeans] . Datenbank Fehler. Bitte sehen Sie in der Datei suitecrm.log nach.
Wed Apr 19 12:25:42 2023 [518629][3b99d166-60bb-4d51-43fd-639af5836e6b][FATAL] Mysqli_query failed.
Wed Apr 19 12:25:42 2023 [518629][3b99d166-60bb-4d51-43fd-639af5836e6b][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=‘c854dc1e-1d41-b8b4-609c-63a57b65a85e’ AND securitygroups_acl_roles.deleted=0

where securitygroups.deleted=0) ORDER BY securitygroups.name desc LIMIT 0,10: MySQL error 1250: Table ‘securitygroups’ from one of the SELECTs cannot be used in global ORDER clause
Wed Apr 19 12:25:42 2023 [518629][3b99d166-60bb-4d51-43fd-639af5836e6b][FATAL] [ListView::processUnionBeans] . Datenbank Fehler. Bitte sehen Sie in der Datei suitecrm.log nach.
Wed Apr 19 12:35:12 2023 [522784][3b99d166-60bb-4d51-43fd-639af5836e6b][FATAL] Mysqli_query failed.
Wed Apr 19 12:35:12 2023 [522784][3b99d166-60bb-4d51-43fd-639af5836e6b][FATAL] Query Failed: DESCRIBE versions: MySQL error 1146: Table ‘suitecrm.versions’ doesn’t exist
Wed Apr 19 12:35:12 2023 [522784][3b99d166-60bb-4d51-43fd-639af5836e6b][FATAL] Mysqli_query failed.
Wed Apr 19 12:35:12 2023 [522784][3b99d166-60bb-4d51-43fd-639af5836e6b][FATAL] Query Failed: SHOW INDEX FROM versions: MySQL error 1146: Table ‘suitecrm.versions’ doesn’t exist
Wed Apr 19 12:35:12 2023 [522784][3b99d166-60bb-4d51-43fd-639af5836e6b][FATAL] Mysqli_query failed.
Wed Apr 19 12:35:12 2023 [522784][3b99d166-60bb-4d51-43fd-639af5836e6b][FATAL] Query Failed: select * from versions: MySQL error 1146: Table ‘suitecrm.versions’ doesn’t exist

crmspace · 19 April 2023 12:09

mhm…

github.com/salesagility/SuiteCRM

Database Failures in Subpanels throughout SuiteCRM (MySQL Error)

opened 03:33PM - 12 Oct 22 UTC

pstevens71

Type:Bug Status:Fix Proposed Priority:Important Area: Module Area: Databases

#### 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

Könnte das gleiche Issue sein, ich würden den Fix an deiner Stelle kurz testen, die Änderung ist ja schnell eingespielt.

jordan · 19 April 2023 13:15

ich habe mir den Link angeschaut aber alles war bisschen verwirrend.
Ich habe nicht verstanden was man machen sollte , um das Problem zu lösen

crmspace · 19 April 2023 13:18

…/data/SugarBean.php:

Also on line 930

comment out:

$query = '(' . $query . ')';

AND

line: 876 remove the brackets and quotations:

$final_query = '(' . $tmp_final_query . ')';

to

$final_query = $tmp_final_query;

Both changes are required to fix this issue.

jordan · 19 April 2023 13:24

okay das werde ich versuchen
danke