Contact: Database failure. Please refer to suitecrm.log for details

pgr · 23 December 2022 11:46

It would help if you posted your SuiteCRM version, PHP version, MySQL version (did you recently upgrade to 8.0?)

Maybe you’re getting this known issue

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

The telling sign would be this message somewhere in your logs:

`MySQL error 1250: Table ‘contacts’ from one of the SELECTs cannot be used in global ORDER clause ````