SuiteCRM 8.5 SQL Errors and Access Authorisation errors

SuiteCRM - 8.5
PHP - 8.1.25
MYSQL - 8.0

I believe these are all within the compatibility matrix.

Have just upgraded to 8.5 from 8.2.4 which went smoothly.

I have an authorisation Login error, which doesn’t actually stop me from accessing anything, so this is more a nuisance than a major problem,but would like to fix if poss - seems only to be in Chrome. We have no external modules configured, this is vanilla SuiteCRM with only internal customisation via Studio.

The big issue is accessing main module data. So Accounts, Contacts, Opportunities in particular.

On accessing any given record, the screen displays the error as per the deprecated screenshot below for Accounts. “Error occured while retrieving records”

SuiteCRM log containst the following FATALs’ in relation to the Account Query;

Sun Jan 7 13:34:29 2024 [2445541][1][FATAL] Mysqli_query failed.
Sun Jan 7 13:34:29 2024 [2445541][1][FATAL] Error retrieving Account list: Query Failed: (SELECT , LTRIM(RTRIM(CONCAT(IFNULL(contacts.first_name,‘’),’ ‘,IFNULL(contacts.last_name,’')))) as name, contacts.title , contacts.phone_work , contacts.first_name , contacts.last_name , contacts.salutation , contacts.assigned_user_id , ‘contacts’ panel_name FROM contacts LEFT JOIN contacts_cstm ON = contacts_cstm.id_c INNER JOIN accounts_contacts ON AND accounts_contacts.account_id=‘101c46b2-0133-9295-4f8f-64aeb895394b’ 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
Sun Jan 7 13:34:29 2024 [2445541][1][FATAL] [SubpanelDataPort::fetch] . Database failure. Please refer to suitecrm.log for details.

However, you can still view the record and update it . This is an area I am struggling with as it seems like a compatibility issue but the versions are all within the 8.5 matrix. i also have a few niggling “Database failure. Please refer to suitecrm.log for details.” dotted around, which take me back to the very early days if V8 of SuiteCRM which I think required a few silly changes to sort incorrect paths, so I will go over the internal change log for these but the one above is baffling me.

Any help gratefully received.

This is a known issue Database Failures in Subpanels throughout SuiteCRM (MySQL Error) · Issue #9788 · salesagility/SuiteCRM · GitHub

My recommendation is not to apply that patch, but rather to upgrade MySQL. Even a minor upgrade will probably get you out of trouble.

That’s right.

You should upgrade your MySQL to 8.0.33 or newer (8.2, etc.). The bug causing this issue was in MySQL 8.0.31, not in SuiteCRM 8.x!

Many thanks both. This now makes perfect sense. I searched in the forum but should have looked in Github.

So in a way it is a compatibility issue albeit due to a bug in the DB, which I would never have found without a lot of trial and error, I suspect.

I’ll do the upgrade in the morning and let you know how it goes.

So, where I am with this is that I couldn’t upgrade the SQL database, as other apps on the host are also using this version, one of which is our main website, so not keen to take that down if I went ahead and then replicated it on the prod system :slight_smile:

So, just for now, I applied the patch , which does fix the issue, although Opportunities still generates a fatal in the log, without the access error being displayed and seems to work OK otherwise but I will need to fix. However, looking at the log entry, see below, this also seems to be a feature of the same fault - MySQL version incompatibility, so i am going to leave this alone until I can upgrade and then re-test.

I will have to get the compatibility of our website checked before I upgrade the database. It is Wordpress based so I expect it will be fine, so once I can get this verified, I will go ahead and do the upgrade and revert the code back to the original sugarbeans.php and re-test. This will probably be a few days.

Here is theOpportunities error from the log. I have put the key element in bold

Mon Jan 8 13:14:49 2024 [1156396][1][FATAL] Mysqli_query failed.
Mon Jan 8 13:14:49 2024 [1156396][1][FATAL] Query Failed: SELECT opportunities.sales_stage, SUM(opportunities.amount_usdollar) as amount_usdollar FROM opportunities LEFT JOIN opportunities_cstm ON = opportunities_cstm.id_c LEFT JOIN users jt0 ON AND jt0.deleted=0

AND jt0.deleted=0 LEFT JOIN users jt1 ON AND jt1.deleted=0

AND jt1.deleted=0 LEFT JOIN users jt2 ON AND jt2.deleted=0

AND jt2.deleted=0 LEFT JOIN accounts_opportunities jtl3 ON AND jtl3.deleted=0

LEFT JOIN accounts accounts ON AND accounts.deleted=0
AND accounts.deleted=0 LEFT JOIN campaigns jt4 ON AND jt4.deleted=0

AND jt4.deleted=0 where opportunities.deleted=0 AND opportunities.amount_usdollar is not null AND opportunities.sales_stage is not null GROUP BY opportunities.sales_stage DESC: MySQL error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘DESC’ at line 12

I’ll keep you posted and thank you so much for your help.