SuiteCRM 8.5 SQL Errors and Access Authorisation errors

ggraynoth · 7 January 2024 19:27

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 contacts.id , 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.id = contacts_cstm.id_c INNER JOIN accounts_contacts ON contacts.id=accounts_contacts.contact_id 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.

pgr · 7 January 2024 20:06

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.

chris001 · 7 January 2024 21:05

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!

github.com/salesagility/SuiteCRM

Comment by clemente-raposo to Fix salesagility#9788 - Database Failures in Subpanels throughout SuiteCRM (MySQL Error)

salesagility:hotfix-7.13.x ← hbartel:master

@pgorod @pstevens71 @hbartel @chris001 Some more data: Tested with mysql vers…ions 8.0.31, 8.0.34, 8.1.0, 8.2.0. Only 8.0.31 fails. Adding info about the tests below. Feedback is welcome ### Testing with mysql 8.0.31 **docker setup** image from mysql dockerhub ``` mysql8031: image: mysql:8.0.31 container_name: mysql8031 restart: always ports: - "3310:3306" volumes: - ./mysql8031:/var/lib/mysql environment: - MYSQL_ROOT_PASSWORD=<pass> - MYSQL_DATABASE=<db> - MYSQL_USER=<user> - MYSQL_PASSWORD=<pass> ``` **adminer** ![image](https://github.com/salesagility/SuiteCRM/assets/1140849/dcd0a6c8-074f-4c64-b9a5-2ec3da1daee4) **result** ![image](https://github.com/salesagility/SuiteCRM/assets/1140849/4138c301-ce5b-44a6-afd9-5cb499f26062) ### Testing with mysql 8.0.34 image from mysql dockerhub ``` mysql80: image: mysql:8.0 container_name: mysql80 restart: always ports: - "3309:3306" volumes: - ./mysql80:/var/lib/mysql environment: - MYSQL_ROOT_PASSWORD=<pass> - MYSQL_DATABASE=<db> - MYSQL_USER=<user> - MYSQL_PASSWORD=<pass> ``` **adminer** ![image](https://github.com/salesagility/SuiteCRM/assets/1140849/b7312a57-38a7-46b3-a165-476ef6f0dce8) **result** ![image](https://github.com/salesagility/SuiteCRM/assets/1140849/e1742c46-849b-4a0f-aef7-9299d2d28187) ### Testing with mysql 8.1.0 image from mysql dockerhub ``` mysql8: image: mysql:8.1 container_name: mysql8 restart: always ports: - "3308:3306" volumes: - ./mysql8:/var/lib/mysql environment: - MYSQL_ROOT_PASSWORD=<pass> - MYSQL_DATABASE=<db> - MYSQL_USER=<user> - MYSQL_PASSWORD=<pass> ``` **adminer** ![image](https://github.com/salesagility/SuiteCRM/assets/1140849/47636dd5-c1ee-4a18-a5b5-6c04ca43491d) **result** ![image](https://github.com/salesagility/SuiteCRM/assets/1140849/169ff5f0-5c7c-4cc8-a877-b3f4db4d2b89) ### Testing with mysql 8.2.0 image from mysql dockerhub ``` mysql82: image: mysql:8.2 container_name: mysql82 restart: always ports: - "3312:3306" volumes: - ./mysql82:/var/lib/mysql environment: - MYSQL_ROOT_PASSWORD=<pass> - MYSQL_DATABASE=<db> - MYSQL_USER=<user> - MYSQL_PASSWORD=<pass> ``` **adminer** ![image](https://github.com/salesagility/SuiteCRM/assets/1140849/e8a8fa12-deca-4857-b7eb-820d5587465c) **result** ![image](https://github.com/salesagility/SuiteCRM/assets/1140849/a86b7021-4013-4adc-b56d-9a273b2e401d)

ggraynoth · 7 January 2024 21:33

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.

ggraynoth · 8 January 2024 18:39

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

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.id = opportunities_cstm.id_c LEFT JOIN users jt0 ON opportunities.modified_user_id=jt0.id AND jt0.deleted=0

AND jt0.deleted=0 LEFT JOIN users jt1 ON opportunities.created_by=jt1.id AND jt1.deleted=0

AND jt1.deleted=0 LEFT JOIN users jt2 ON opportunities.assigned_user_id=jt2.id AND jt2.deleted=0

AND jt2.deleted=0 LEFT JOIN accounts_opportunities jtl3 ON opportunities.id=jtl3.opportunity_id AND jtl3.deleted=0

LEFT JOIN accounts accounts ON accounts.id=jtl3.account_id AND accounts.deleted=0
AND accounts.deleted=0 LEFT JOIN campaigns jt4 ON opportunities.campaign_id=jt4.id 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.