MySQL error 1250: Table 'contacts' from one of the SELECTs cannot be used in global ORDER clause

SuiteCRM Forum - English Language SuiteCRM General Discussion
1

In accounts the contacts and opportunities are not listed and instead there is the following error: Database failure. Please refer to suitecrm.log for details. In the log there are MySQL error 1250: Table ‘contacts’ from one of the SELECTs cannot be used in global ORDER clause errors for the related tables.

On the internet I have found this, which leads me to think there is a bug with the version 7 and MySQL 8.0.31

If you’re getting an error that reads “ERROR 1250 (42000): Table ‘…’ from one of the SELECTs cannot be used in global ORDER clause” when using the UNION clause in a MySQL query, it’s probably because you’re qualifying a column name with its table name.

This doesn’t work in MySQL.

To fix this issue, either remove the table name or use a column alias.

2

I am getting the same error running version 7.12.6. Was fine until I think maybe a cPanel up date over the weekend. Now getting the error: MySQL error 1250: Table ‘contacts’ from one of the SELECTs cannot be used in global ORDER clause. Nothing else has changed with configuration or server settings that I am aware of.

Seems to be happening with a number of different queries.
Many of the features work but some don’t.

3

Tried a restore from a backup I know was working fine. Same error, so it must be a recent cPanel/MySQL update.

4

Upgraded to 7.12.7, still the same error

5

+1 following I’m getting the same error with all contact relationships (well for sure in cases and opportunities, accounts and anywhere I’ve checked). It just started happening for me this morning. I haven’t made any changes or done any updates.

6

OK, so not a fix, but a work around. The issue seems to be with the “name” sort. It’s the “order by” clause that’s the problem, it must be calling the the table name. If you click on the “Account” column for example in the subpanel, it will sort by “Account” and not experience the error. It’s only sort by “name” that causes the issue. It’s not a fix, but will get you by.

sort problem
sort problem1654×191 17.9 KB

working sort problem
working sort problem1613×282 37.3 KB

7

Note: I’m also having the same issue with Project Tasks subpanel. The only sort that seems to work is “assigned to” The rest have the same error.

8

I tried different sorts, but that does not help me unfortunately

9

@svdkar try adding another column to the subpanel just for giggles and see if you can sort by it. Longshot, but might get you by. For example in my Project Tasks, the only column I could sort by and not get the database failure was “assigned to”.

10

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

1 Like
11

@svdkar I know glad it worked for you. Wondering if there is some setting in MySQL to change that will allow for this syntax. It obviously worked before today. It seems to be rampant throughout all the subpanel views. Fixing the code for all of this would be a huge task (I’m not a dev but I can imagine).

12

Opened an issue on Github:
https://github.com/salesagility/SuiteCRM/issues/9788

1 Like
13

This seems to be limited to subpanels when trying to sort by the header. I’ve also reached out to a MySQL dev to see if there is a switch command to remove the changes.

14

Check the link to issue on Github. This issue is solved by commenting out one line.

1 Like
15

This solves most of the issues, but Products and Services Purchased and History still have issues

16

That really helped.
image

1 Like
17

Edit file /data/SugarBean.php

the code to edit is near line 930

            //Put the query into the final_query
            $query = $subquery['select'] . " " . $subquery['from'] . " " . $subquery['where'];
            if (!$first) {
                $query = ' UNION ALL ( ' . $query . ' )';
                $final_query_rows .= " UNION ALL ";
            } else {
                **//$query = '(' . $query . ')';**  COMENT HERE
                $first = false;
            }
2 Likes
18

I setup SuiteCRM and get the same issue in sub query.
But I notice there is some different between the old version.
The file located in …/public/legacy/data/SugarBean.php.
The line number is 939
Put the line to annotation and solve the problem.