Target List Error Message

pgr · 6 December 2022 09:52

Hi @rbprimat, welcome to the Community!

You’re replying to a 6 years old thread, did you notice that?

You’re facing a 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

Which has a fix:

github.com/hbartel/SuiteCRM

Fix salesagility#9788 - Database Failures in Subpanels throughout SuiteCRM (MySQL Error)

hbartel:master ← hbartel:Fix-Error-#9788

opened 08:42PM - 27 Oct 22 UTC

hbartel

+0 -2

Fix Issue [9788](https://github.com/salesagility/SuiteCRM/issues/9788) ## De…scription Changes implemented in MySQL 8.0.31 cause SQL queries that pull data for subpannels to fail. If the outer ( ) is removed from the query, it will complete normally. The code that adds the outer ( ) to the query is located on lines 876 and 930 of Suite CRM/data/SugarBean.php. By removing these lines the query if fixed. 876: $final_query = '(' . $tmp_final_query . ')'; 930: $query = '(' . $query . ')'; ''' ## Motivation and Context If the MySQL environment is updated to MySQL 8.0.31 the subpannels that depend on these SQL queries will fail. ## How To Test Update MySQL to 8.0.31 on host server Create Account with linked subpanel data (contacts, opportunities, leads, ect) Subpanels must have data for the error to occur. Open Account detail page & suitecrm.log to see error. Remove specified lines and test ## Types of changes - [X] Bug fix (non-breaking change which fixes an issue) ### Final checklist - [X] My code follows the code style of this project found [here](https://docs.suitecrm.com/community/contributing-code/coding-standards/). - [ ] My change requires a change to the documentation. - [X] I have read the [**How to Contribute**](https://docs.suitecrm.com/community/contributing-code/) guidelines.

… but it’s already merged, I think it should be part of 7.12.8. Are you sure you’re using that version?