malcek
14 December 2022 14:16
6
We have use Cpanel copy and mostly work without problem. Only SuiteCrm has minor problems which was solved by my support and @outrightsystems
I had to add accounts for the sort in studio and now if selecting account I can see the missing lists for all sub-panels
nasty bug
BUG report:
opened 03:33PM - 12 Oct 22 UTC
Type: Bug
Status:Fix Proposed
Priority:Important
Area: Module
Area: Databases
<!--- Please be aware that as of the 31st January 2022 we no longer support 7.10… .x.
New issues referring to 7.10.x will only be valid if applicable to 7.12.x and above.
If your issue is still applicable in 7.12.x, please create the issue following the template below -->
#### 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.
When I sort on "Accounts" it works:
#### 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
and FIX:
salesagility:hotfix-7.13.x ← hbartel:master
opened 08:02PM - 06 Dec 22 UTC
Fix Issue https://github.com/salesagility/SuiteCRM/issues/9788
Description
C… hanges 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.
I hope that somebody would find this solution useful.