Upgrade from 7.12.4 to 7.12.7 Database failure

toddh · 26 October 2022 16:51

Upgraded from 7.12.4 yesterday to 7.12.7. Upgraded appeared to run normally.
We are on
Ubuntu 20.04.5
PHP 7.4
MySQL 8.0.31
Apache 2.4.41

Today I added a new Contact but the Account does not show the new Contact. It says
“Database failure. Please refer to suitecrm.log for details”

The log shows the following:
Tue Oct 25 08:32:16 2022 [340685][74637fbf-996c-2f1a-ec39-5903df3135eb][FATAL] Mysqli_query failed.
Tue Oct 25 08:32:16 2022 [340685][74637fbf-996c-2f1a-ec39-5903df3135eb][FATAL] Error retrieving Account 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, contacts.primary_address_city , contacts.primary_address_state , 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 accounts_contacts ON contacts.id=accounts_contacts.contact_id AND accounts_contacts.account_id=‘222807ea-f0d7-4d24-cb3b-5dcacdc267bf’ AND accounts_contacts.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

I also installed Elasticsearch and enabled the cron jobs on the host yesterday.

Thanks,

Todd

minkatec · 26 October 2022 21:13

We’re running version 7.6.6 on Ubuntu 20.04 LTS.
Yesterday, we upgraded to MySQL 8.0.31 via apt-get and it broke our installation. When clicking on detail for a particular Account, the ajax pop-up was broken. When disabling ajax for that module, we would get just the basic information about the Account, but no Tasks, Meetings, Calls, Notes, etc. When creating a test call, could not associate to an Account. SugarBean.php throwing mysql errors. I didn’t have time to debug further. I simply reverted the VM to a snapshot and we’re back up and running. Thank god I took a snapshot.

pgr · 27 October 2022 09:29

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

toddh · 1 November 2022 19:14

The solution in the link provided work.

The solution being to comment out line 930 in the /data/SugarBean.php file like so.

//$query = ‘(’ . $query . ‘)’;