Weird behavior sorting on subpanels

SuiteCRM Forum - English Language
1

We had an issue, and have restored back to a working version. However now on any of the subpanels, if we click on any of the headers to sort by the column, it results in an unexpected error and all of the data on the UI within the subpanel disappears. Refresh - data’s back, but sorting results in error every time.

Only error I see is
MySQL error 1250: Table ‘svcs_servicenotes’ from one of the SELECTs cannot be used in global ORDER clause

Query seems to work if I strip it out and run it on my own though. Does seem to be wrapped in paranths which it doesn’t like?

Any help would be appreciated!

2

I saw that SQL error very recently here in the Forums. Maybe you restored into a new MySQL version, and it’s causing issues.

Check the Compatibility Matrix :: SuiteCRM Documentation for v8

And perhaps consider downgrading your stack just a bit.

I assume you already did all relevant Repairs

3

SQL version is good… Leaning towards we recovered only part of a linked record… trying to figure out where now.

4

and we tried all relevant repairs.
To expand a bit - our restore was a copy of the database from Sunday at 2AM .

5

I don’t think it has to do with your restore. This issue just popped up for myself and seems like a bunch of other people this morning without doing any upgrades or restores. I think MySQL version maybe got updated in Cpanel or something (my guess). The issue is the ORDERBY clause in the MySQL. for example ORDERBY contacts.firstname where contacts is the name of the table. The documentation for this error in MySQL says not to include the table name and only include ORDERBY firstname. There is another thread for this just opened and I also opened an issue on Github.

https://github.com/salesagility/SuiteCRM/issues/9788

I’m wondering if there is a setting in MySQL to allow this syntax. It seemed to work fine before this morning.

6

Yeah, I narrowed it down to what appears to be a minor MySQL upgrade to 8.0.31. Looks specifically related to the optimizer changes
https://dev.mysql.com/doc/refman/8.0/en/switchable-optimizations.html

it is now wrapping a query such as

(SELECT table1.columna, table2.columnA, from table1 inner join table2 on blahblah)
ORDER BY table1.columna

if you run the query in MySQL workbench on a non 8.0.31 version - it errors… remove the parenths and it’s fine… Hoping to dig a bit into the optimization switches and see if I can shut if off… If anyone else gets there first - please advise!

1 Like
7

You can try to set both as False
‘sort_order’ => false,
‘sort_by’ => false,

8

Thats a bit of a lift considering this effects any module that is a subpanel…has this path been tested on v8.x.x?

9

I am using 7.10.x and we are using custom panels
custom/modules/Leads/Ext/Layoutdefs

Not tested on 8.x

10

Did this resolve the issue for you? Are users still able to click the header to sort the data?

11

Just a bump on this one. Did anyone have any luck finding a fix for this? PS - I did add a bug on github and someone from MySQL added this as a bug in there system too. Apparently, they made some changes in 8.0.31 to handle parenthesis in the query better, which unfortunately caused this issue.

12

Nothing I’ve seen yet, hoping to get some time to dig into it this week at some point and see. I was hoping someone would have a MySQL optimization Switch available to revert the changes they made to parenthesized query optimizations… but nothing yet.

1 Like
13

Hey all, someone on the Github issue found the line to fix!!!

In SuiteCRM/data/SugarBean.php

Line 930 is $query = ‘(’ . $query . ‘)’;

just comment out like:

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

all it does is put the parenthesis around which are causing the problem.

1 Like
14

Confirmed and working!!! Thank you!