Database exception with Craft 3.1.23

[howells]

Hi - this seems to be related to an older issue that was resolved, but has just started erroring on updating to Craft 3.1.23, with Similar 1.0.5:

Syntax error or access violation: 1055 Expression #4 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'bergmeyer.structureelements.structureId' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Any ideas?

[svale]

Hi! I also ran into this one last week. In a rush (literally while boarding a flight :-), I quickly pushed this fix to my fork (https://github.com/svale/craft-similar/commit/3c06b751e246c7e5ae5ce10942684b076cdb7eb4).

I'll PR that patch, but I have to admit I haven't really tried to understand what is happening in the SQL here.

[khalwat]

Fixed in https://github.com/nystudio107/craft-similar/commit/3c06b751e246c7e5ae5ce10942684b076cdb7eb4

[brianlarson]

I'm still running into this issue with all latest versions of Craft, Similar and other deps. Using MySQL v5.7.22 with Laravel Valet and PHP 7.4.6. I'd do a PR if my brain was bigger! 🤯.

PDOException: SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'link.elements_sites.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by in /Users/brianlarson/Sites/link/vendor/yiisoft/yii2/db/Command.php:1293

Full stacktrace here.

For testing I'm using the example from the Similar docs with only a needed tweak to my tags handle (entry.blogTags instead of entry.tags):

{% set similarEntriesByTags = craft.similar.find({ element: entry, context: entry.blogTags }) %}
<ul>
    {% for similarEntry in similarEntriesByTags %}
        <li>{{ similarEntry.title }} ({{ similarEntry.count }} tags in common)</li>
    {% endfor %}
</ul>