Widget Who's Online cause error "sql_mode=only_full_group_by"

[jolichter]

Summary

Widget: "Who's Online" cause an error "sql_mode=only_full_group_by" since mySQL 5.7+

Step to reproduce

• several test with new installed MODX 2.6.1
• no error if I deactivate the Widget Who's Online (widget.grid-online.php)

Observed behavior

Each time I click on the dashboard, the error log is generated this error:

[2017-12-12 12:12:12] (ERROR @ /home/html/modx/core/xpdo/om/xpdoobject.class.php : 240) Error 42000 executing statement: 
Array
(
    [0] => 42000
    [1] => 1055
    [2] => Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'modx.modManagerLog.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
)

Expected behavior

no error ;-)

Environment

• MODX 2.6.1
• Linux Ubuntu 16.04
• php 7.0.22
• mySQL 5.7.20

[Jako]

Could you try to change this line https://github.com/modxcms/revolution/blob/689ca8beab67771f3ef95aa857da941d07590b22/core/model/modx/processors/security/user/getonline.class.php#L24 to

    $query->groupby('modManagerLog.user');

[jolichter]

No, I get exactly the same error. However this is working: $query->groupby('id');

Thanks for the hint! Maybe you have an idea why it with 'user' doesn't working. I guess to group the result with the 'id' is also good?

[Jako]

I guess to group the result with the 'id' is also good?

No, then you have double entries for the same user, since you are grouping by modManagerLog.id and if one user has done more than one logged actions in the manager …

[Jako]

But since the error is a bit different than expected: Change the lines https://github.com/modxcms/revolution/blob/689ca8beab67771f3ef95aa857da941d07590b22/core/model/modx/processors/security/user/getonline.class.php#L24-L25

to

    $query->groupby('modManagerLog.user');
    $query->select($this->modx->getSelectColumns('modManagerLog','modManagerLog','', array('user')));
    $query->select($this->modx->getSelectColumns('modManagerLog','modManagerLog','', array('user'), true));

[jolichter]

Hi Jako, thanks for your support. I tried it, but also brings the same mistake.

my print screen Who's Online when I 'grouping by id'

Sorry, I can't understand. I have no double entries. For my understanding is a grouping by UserID or Username the same. Sorry, maybe I don't understand that correctly and on the wrong track?

Update: I got it! The list should not show all my recent activities. 'Who's Online' means 'Who's Online' was probably a long night ;-)

[Jako]

Is the error away with the patch in https://github.com/modxcms/revolution/issues/13730#issuecomment-354512819?

[jolichter]

Unfortunately not, when I exclude the groupby I get no error but the Who's Online table shows all entries like the print screen before (as you already mentioned):

#$query->groupby('modManagerLog.user');
$query->select($this->modx->getSelectColumns('modManagerLog','modManagerLog','', array('user')));
$query->select($this->modx->getSelectColumns('modManagerLog','modManagerLog','', array('user'), true));

If I understand MySQL 5.7 Handling of GROUP BY correctly is the fault that the nonaggregated id column in the select list is not named in the GROUP BY clause.

I get no error if I try: $query->groupby('modManagerLog.user, modManagerLog.id'); That's not the solution, because the result is the same as exclude that line.

Maybe it is possible to use groupby with MAX('occurred') for each user?

[devlanda]

I can confirm this issue. The same error is also triggered in the submitted forms overview of FormIt.

MODX v2.6.1 MySQL v5.7.20 PHP v7.1.13

[sepiariver]

@Jako did you mean:

$query->groupby('modManagerLog.user');
$query->select($this->modx->getSelectColumns('modManagerLog','modManagerLog','', array('user'), true));

?

and @jolichter could you try the above? If that doesn't work could you also try

$query->groupby('user');
$query->select($this->modx->getSelectColumns('modManagerLog','modManagerLog','',array('user','classKey','item'),true));

MySQL 5.7.x seems to be pretty fussy about these types of statements...

[Jako]

@sepiariver I just tried to solve that error message Expression #1 of SELECT list is not in GROUP BY clause by ordering the modManagerLog.user column in Expression #1 of the select list. But modManagerLog should be a xPDOSimpleObject and SELECT modManagerLog.id could be added to the SELECT list before that select query for some reason.

[sepiariver]

@jako I think you’re right. Excluding modManagerLog.id seems to return a server error.

However excluding modManagerLog.user seems ok because User.id is selected automatically.

Also looks like if you call getSelectColumns and then subsequently call it again with exclude = true it won’t exclude as expected.

[sepiariver]

In the end it might require a sub-query...?

[sepiariver]

$query->groupby('modManagerLog.user, User.username, User.id'); might also work. Sorry can't test it at the moment but will try later. @jolichter curious to see if it works on your setup...

[jolichter]

@sepiariver Thanks for your support. I have changed the SQL mode on my homepage: SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY','')); I know, this is a workaround and not a solution. Therefore I tested it on my PC with the same conditions (php / mySQL version) and get the same error.

[jolichter]

btw: on my local home server I changed from mySQL 5.7.21 to mariadb-server (10.0.33-MariaDB-0ubuntu0.16.04.1) and get no error.

[bezumkin]

Confirm this issue on MySQL 5.7.20 with default sql_mode.

Working on possible fix.

[Ibochkarev]

Adding the next block to the config core/config/config.inc.php should solve this problem:

$driver_options = array (
  PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true,
  PDO::MYSQL_ATTR_INIT_COMMAND => "SET sql_mode = 'TRADITIONAL'"
);

[alroniks]

Seems solved by #13835 which is merged.