Broken Gallery plugin after updating server to MYSQL 5.7

halftrainedharry · February 2, 2021, 8:41pm

At least for the given specific example (the images not showing), the reason it fails is sql-mode “ONLY_FULL_GROUP_BY” in combination with this query.

github.com

modxcms/Gallery/blob/4e14230c591b69bc9b1e3938f70390478849a03d/core/components/gallery/processors/mgr/item/getlist.class.php#L58


    $c->innerJoin('galAlbum','Album',array(
        'Album.id = AlbumItems.album',
    ));
    $c->leftJoin('galTag','Tags');
    return $c;
}

public function prepareQueryAfterCount(xPDOQuery $c) {
    $c->select($this->modx->getSelectColumns('galItem','galItem'));
    $c->select(array(
        'AlbumItems.rank',
        'album' => 'Album.id',
        '(
            SELECT GROUP_CONCAT(Tags.tag) FROM '.$this->modx->getTableName('galTag').' AS Tags
            WHERE Tags.item = galItem.id
        ) AS tags'
    ));
    $c->groupBy('id');
    return $c;
}

Error: “contains nonaggregated column ‘AlbumItems.rank’ which is not functionally dependent on columns in GROUP BY clause”

Possible solution: Changing the line 'AlbumItems.rank', to this.

'rank' => 'MAX(AlbumItems.rank)',