Fluent extension causes Errors with MySQL version >= 5.7.5

[bummzack]

The problem arises when the FluentExtension uses COALESCE on colums that should be sorted.

Say you have a translatable DataObject Article, with a Title field and you want to sort by Title. It works in MySQL 5.6, but generates the following error on MySQL 5.7.13:

Article::get()->sort('Title'); // using the localized column name doesn't help either

Expression #1 of ORDER BY clause is not in SELECT list, references column 'Article.Title_en_US' which is not in SELECT list; this is incompatible with DISTINCT

I've looked around for the same issue and it seems to be caused by the following default setting: https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_only_full_group_by

Multiple sources state, that it can be fixed by setting sql-mode to a custom value that doesn't include ONLY_FULL_GROUP_BY, but I was unable to make it work locally. I'm assuming that a lot of people don't have full control over their mysql-server settings (depending on hosting-provider), so I guess this would be better fixed in code?

The only workaround so far seems to be to disable the coalescing of fields, by setting the sortable field to be nullable. Eg.

Article:
  extensions:
    - FluentExtension
  translate:
    - Title
    - Content
nullable_fields:
  - Title # adding this makes the error go away!

[tractorcow]

This is an error I've seen in recent months, and as it's a serious blocker to upgrade I'm going to raise this to critical. I'm not sure how difficult this will be to resolve... it may be necessary to remove "distinct" for certain queries (which is something that framework should NOT be doing in the first place, but that's another story).

[tractorcow]

Related: silverstripe/silverstripe-framework#4923

[guyvanbael]

any updates on this? I have an upcoming project in 6 languages for which i'd like to use this module.

[tractorcow]

Are you able to use mysql 5.6 for your project? Otherwise you can turn off ONLY_FULL_GROUP_BY on your mysql 5.7

[guyvanbael]

last project i had trouble with this, so i asked my hosting partner to set up 5.6 which solved the issue, but i can imagine that this is not the case with every hoster.

[bummzack]

Small update on this: Turning off ONLY_FULL_GROUP_BY is not an option, sadly. SilverStripe sets sql_mode to ANSI, which will always activate ONLY_FULL_GROUP_BY. See manual

This means, the module should be considered incompatible with MySQL 5.7+

[dhensby]

related: silverstripe/silverstripe-framework#5451

[kinglozzer]

Just been discussing this on Slack, so adding another workaround that worked for me on an SS3 project a while back:

        $pages = SiteTree::get()->filter(array(
            'ParentID' => $this->owner->ID
        ));

        // Jumping through hoops to sort by Title in current locale...
        $pages = $pages->alterDataQuery(function (DataQuery $dataQuery) {
            $field = 'Title';
            $alias = "{$field}_Sort";
            $expression = $this->getSortExpressionForField($field, $dataQuery);

            // Hack - DataQuery::selectField() is protected in SilverStripe 3
            // $filtersQuery->selectField($selectSQL, $alias);
            $reflectionMethod = new ReflectionMethod($dataQuery, 'selectField');
            $reflectionMethod->setAccessible(true);
            $reflectionMethod->invoke($dataQuery, $expression, $alias);

            $dataQuery->sort($alias, 'ASC');
        });

        return $pages;

[sunnysideup]

It worked for us:

https://github.com/sunnysideup/silverstripe-mysql-5-7-fix (see _config.php file)

[lerni]

I've made a composer-patch module to fix this with 3.x
https://github.com/lerni/silverstripe3-mysql57-fluent

Just discovered https://github.com/SpliffSplendor/silverstripe-mysqlfixer - this should do the same as lerni/silverstripe3-mysql57-fluent but per Injector - not patching.

[tractorcow]

I would accept https://github.com/sunnysideup/silverstripe-mysql-5-7-fix/blob/master/_config.php as a PR to core fluent 3.x branch.

[lerni]

@tractorcow Fluent 3.x requires SS ~3.2 but AFAIK the "config-fix" you're suggesting doesn't work with SS 3.x. Probable adding a note to https://github.com/tractorcow/silverstripe-fluent/blob/3.8/readme.md#requirements link: MySQL <5.7.5 cause of changed ANSI mode https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sql-mode-combo

[tractorcow]

Sorry, my mistake (I should read better!). The ss3 patch is a patch to core, not to _config.php. The ss4 patch only can be done via _config.php.

In that case we could add the _config.php fix to the current fluent version.

Regarding ss3 maybe we just recommend your patch for mysql 5.7 instead.

[lerni]

@tractorcow Is this still an issue with SS/Fluent 4 after the merge of 252e489 ?

[tractorcow]

It may still be an issue for some queries. I'm not 100% convinced it's gone.

[obj63mc]

Just wanted to leave a note, that with v4.3.x and Fluent 4 I am still getting this issue. Was able to work around with https://github.com/sunnysideup/silverstripe-mysql-5-7-fix/blob/master/_config.php

Example setup - SS4.3x, Elemental, Fluent 4 -

1. New block which extends a base element which has the FluentVersionedExtension Applied to it

    <?php

    namespace Swift\Objects\Blocks\Hero;

    use SilverStripe\Forms\FieldList;
    use SilverStripe\Forms\GridField\GridField;
    use SilverStripe\Forms\GridField\GridFieldConfig_RecordEditor;
    use DNADesign\Elemental\Models\BaseElement;
    use UndefinedOffset\SortableGridField\Forms\GridFieldSortableRows;

    class HeroElementHolder extends BaseElement{
        private static $table_name = 'sw_HeroElementHolder';
        private static $singular_name = 'hero elements block';
        private static $plural_name = 'hero elements blocks';
        private static $description = 'Add a Hero Element/Slider';

        private static $has_many = [
            'HeroElements' => HeroElement::class
        ];

        private static $owns = [
            'HeroElements'
        ];

        private static $translate = [
            'HeroElements'
        ];


        public function getCMSFields()
        {
            $this->beforeUpdateCMSFields(function (FieldList $fields) {
                $conf = GridFieldConfig_RecordEditor::create(10);
                $conf->addComponent(new GridFieldSortableRows('SortOrder'));
                $fields->addFieldToTab('Root.HeroElements', new GridField('HeroElements', 'Hero Elements', $this->HeroElements(), $conf));
            });
            return parent::getCMSFields();
        }

        public function inlineEditable()
        {
            return false;
        }

        public function getType()
        {
            return 'Hero Slider';
        }
    }

2. Element that block holds - DataObject that is Versioned and has FluentVersionedExtension and a default sort 'SortOrder ASC'

    <?php

    namespace Swift\Objects\Blocks\Hero;

    use SilverStripe\Forms\FieldList;
    use SilverStripe\ORM\DataObject;
    use SilverStripe\Assets\File;
    use SilverStripe\Versioned\Versioned;
    use TractorCow\Fluent\Extension\FluentVersionedExtension;

    class HeroElement extends DataObject{

        private static $table_name = 'sw_HeroElement';
        private static $singular_name = 'hero element';
        private static $plural_name = 'hero elements';
        private static $description = 'Hero Element Slide';

        private static $db = [
            'Content' => 'HTMLText',
            'SortOrder' => 'Int'
        ];

        private static $has_one = [
            'HeroElementHolder' => HeroElementHolder::class,
            'BackgroundVisual' => File::class
        ];

        private static $extensions = [
            Versioned::class, FluentVersionedExtension::class
        ];

        private static $owns = [
            'BackgroundVisual'
        ];

        private static $translate = [
            'Content', 'SortOrder', 'BackgroundVisual'
        ];

        private static $default_sort = "SortOrder ASC";

        public function getCMSFields()
        {
            $this->beforeUpdateCMSFields(function (FieldList $fields) {
                $fields->fieldByName('Root.Main.BackgroundVisual')->setAllowedExtensions(['mp4','jpg','jpeg','png','gif']);

            });
            return parent::getCMSFields();
        }

        public function getIsVideo(){
            if($this->BackgroundVisual() && $this->BackgroundVisual()->exists() && $this->BackgroundVisual()->getExtension() == 'mp4'){
                return true;
            }

            return false;
        }

        public function getIsImage(){
            if($this->BackgroundVisual() && $this->BackgroundVisual()->exists() && $this->BackgroundVisual()->getExtension() != 'mp4'){
                return true;
            }

            return false;
        }
    }

3. Error message returned -

[Emergency] Uncaught SilverStripe\ORM\Connect\DatabaseException: Couldn't run query: SELECT DISTINCT "sw_HeroElement_Live"."ClassName", "sw_HeroElement_Live"."LastEdited", "sw_HeroElement_Live"."Created", "sw_HeroElement_Live"."Version", CASE WHEN "sw_HeroElement_Localised_en_US"."ID" IS NOT NULL THEN "sw_HeroElement_Localised_en_US"."Content" ELSE "sw_HeroElement_Live"."Content" END AS "Content", CASE WHEN "sw_HeroElement_Localised_en_US"."ID" IS NOT NULL THEN "sw_HeroElement_Localised_en_US"."SortOrder" ELSE "sw_HeroElement_Live"."SortOrder" END AS "SortOrder", "sw_HeroElement_Live"."HeroElementHolderID", "sw_HeroElement_Live"."BackgroundVisualID", "sw_HeroElement_Live"."ID", CASE WHEN "sw_HeroElement_Live"."ClassName" IS NOT NULL THEN "sw_HeroElement_Live"."ClassName" ELSE 'Swift\\Objects\\Blocks\\Hero\\HeroElement' END AS "RecordClassName", 'en_US' AS "Locale", CASE WHEN "sw_HeroElement_Localised_en_US"."ID" IS NOT NULL THEN 'en_US' ELSE NULL END AS "SourceLocale" FROM "sw_HeroElement_Live" LEFT JOIN "sw_HeroElement_Localised_Live" AS "sw_HeroElement_Localised_en_US" ON "sw_HeroElement_Live"."ID" = "sw_HeroElement_Localised_en_US"."RecordID" AND "sw_HeroElement_Localised_en_US"."Locale" = ? WHERE ("sw_HeroElement_Live"."HeroElementHolderID" = ?) ORDER BY "sw_HeroElement_Live"."SortOrder" ASC Expression #1 of ORDER BY clause is not in SELECT list, references column 'SS_swift_qa.sw_HeroElement_Live.SortOrder' which is not in SELECT list; this is incompatible with DISTINCT

[andreaslindahl]

I'm experiencing this issue with Silverstripr 4.5. Is there an easy fix?

[obj63mc]

@andreaslindahl, check out @sunnysideup workaround above - basically you can add the following to your _config.php in the root of your app folder.

use SilverStripe\ORM\DB;
DB::query("SET SESSION sql_mode='REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE';");

[andreaslindahl]

@obj63mc Excellent, thanks!

[guyvanbael]

Is the latest Silverstripe and Fluent compatible with mysql 5.7 or do i still have to add the fix that @obj63mc suggests? I have 3 older SS3 websites on a hoster that has upgraded mysql to 5.7 so i probable have to upgrade the sites to SS4 and the latest fluent. But since this issue is still open....
and since mysql5.6 is EOL...

[lerni]

@guyvanbael - I recently saw this in asset-admin on a live environment with fluent 4.4.5 & SS 4.5.x. But it just happened on this particular server for some reason even though other environments also use latest MySQL.

[tractorcow]

Maybe we should add @obj63mc 's fix with a config flag (E.g. fluent_mysql_compatibility or something).

[tractorcow]

Let's just get it in #617

[tractorcow]

Same PR but for 4.4 branch #618

[tractorcow]

Maybe I'll find some more time to fix the "make sure sorted columns" are included in the queried columns...

[mikey-harveycameron]

I'm stuck on this one, the sql_mode trick doesn't work.
Dies when I'm using Symbiote\GridFieldExtensions\GridFieldAddExistingSearchHandler to search for pages, can't apply the (COALESCE("SiteTree_Localised_en_NZ"."Title", "SiteTree"."Title") LIKE ?) part of the query. So sad.

Hmm it's also reported here:
symbiote/silverstripe-gridfieldextensions#356

Looking at my SQL it seems to be inserting an empty clause into the query, perhaps that is the real culprit?

WHERE () AND (COALESCE("SiteTree_Localised_en_NZ"."Title", "SiteTree"."Title") LIKE ?)

[mikey-harveycameron]

It looks like this line sometimes generates an empty value:

silverstripe-fluent/src/Extension/FluentExtension.php

Line 583 in 91b834f

$localisedPredicate = str_replace($conditionSearch, $conditionReplace, $predicate);

The empty value in $localisedPredicate is then used as a key for an array of 'where' clauses. Not sure why it's empty but if we just skip these empty keys the problem is resolved.