Broken Gallery plugin after updating server to MYSQL 5.7

Summary

With MYSQL 5.6 reaching EOL we were forced to upgrade our hosting machine to the 5.7 version.

This operation broke the Gallery plugin, which i’m not very fond of, but is used in a lot of older websites prior to switching almost completely to ContentBlocks for content management.

Step to reproduce

  • From the Components menu go to Gallery and open any gallery
  • Where the images should be shown it’s an error message that no file satisfies the search filter.

Observed behavior

Looking around the web i’ve found a post that states to add this line:

$this->pdo->exec("SET SESSION sql_mode = 'NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';");

to /core/xpdo/om/mysql/connect.inc.php

This actually resolves the issue (screenshot is the same gallery shown earlier, after applying the patch)

Expected behavior

The core editing solutions feels very hacky and not really future-proof and I’m wondering if there’s a better approach, considering we have dozens of websites suffering from this issues and applying this patch manually on every one is pretty time consuming.

Environment

MODX versions varying from 2.6.5 to 2.7.3
Apache 2.4.46
MySQL 5.7

Yeah… please don’t change core files like that.

What that line does is change your SQL mode temporarily. To change that the proper way, change your mysql configuration:

Some hosts/control panels may offer an interface to do so instead of having to edit the my.cnf directly.

It would also be worthwhile to make sure details of the problem (i.e. what SQL mode is your server currently using that doesn’t work with Gallery, what errors does it cause in the MODX error log) are logged on Github. Perhaps the server upgrade also changed collations and you’re running into https://github.com/modxcms/Gallery/issues/85 or you’d need to create a new issue.

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.

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)',

I did try the AlbumItems.rank solution but it didn’t seem to work.
Anyway we updated the MYSQL ini file at a server level and the issue is resolved across all sites on the machine :slight_smile:

I havent been using the Gallery plugin (not a big fan of it) in ages, but we just had to support our oldest projects.