Mysql 5.7 issues around ansi

sunnysideup · 19 July 2018 07:04

Silverstripe Version:

SS4.1

Question:

We have often run into issues, especially when upgrading old code, but also with new code (e.g. tractorcow/fluent), where the mysql errors because group by / distinct does not follow strict sql rules (ONLY_FULL_GROUP_BY mode) introduced in mysql 5.7

I have now created a micro module to fix this:

github.com

sunnysideup/silverstripe-mysql-5-7-fix/blob/master/_config.php

<?php

// see: https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html#sqlmode_ansi
// and: https://dev.mysql.com/doc/refman/5.6/en/sql-mode.html#sqlmode_only_full_group_by

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

Is this dangerous / legit?

Is there anything that may cause troubles using this module (apart from the code not being standard SQL - which I dont really care about since we always run mysql anyway - and because the module is for mysql)?

lerni · 30 August 2018 15:40

I’ve made a composer-patch module to fix this with 3.x

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

Well I don’t think it’s dangerous since it ever was that way but best praxis would be to fix the queries to be ANSI compatible and as far as I know this is done with in fluent 4.x Merge pull request #415 from creative-commoners/pulls/4.1/allow-sorting · tractorcow/silverstripe-fluent@252e489 · GitHub