spliffs/mysqlfixer

Fix problems with MySQL-DB > 5.7.5 and some SilverStripe modules

MySQL Fixer Module

This is intended to fix problem Expression #1 of ORDER BY clause is not in SELECT list.....which is not in SELECT list; this is incompatible with DISTINCT caused by some modules., _(*1)

The distinct error occures with MySQL >5.7.5 where the option ONLY_FULL_GROUP_BY became part of the combination on __ANSI__ (which also includes 'REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE') used by SilverStripe for sql_mode., _(*2)

( See also here https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sql-mode-combo ), _(*3)

This module introduces the MySQL57Database class which extends the MySQLDatabase class, or better overwrites the connect() method using the sql_mode 'REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE' and instead of to 'ANSI'., _(*4)

Maintainer Contact

Spliff Splendor , _(*5)

Requirements

• SilverStripe 3.3 or newer

Installation

• If using composer, run composer require spliffs/mysqlfixer.
• Otherwise, download, unzip and copy the 'mysqlfixer' folder to your project root so that it becomes a sibling of framework/.

Configuration

Add this to your _config.php (right after 'require_once("conf/ConfigureFromEnv.php");') or in your _ss_environment.php where you configure your database., _(*6)

Sample/Excerpt _ss_environment.php

// DB config
define('SS_DATABASE_CLASS', 'MySQL57Database');`
define('SS_DATABASE_SERVER',   'mysql5.7.20.local');
define('SS_DATABASE_USERNAME', 'dbuser');
define('SS_DATABASE_PASSWORD', 'top-secret-password');
define('SS_DATABASE_NAME',   'ss_mysite');

Sample mysite/_config.php

<?php
global $project;
$project = 'mysite';

global $database;
$database = 'SS_mysite';

require_once("conf/ConfigureFromEnv.php");

global $databaseConfig;

$databaseConfig = array(
    "type" => 'MySQL57Database',
    "server" => 'mysql5.7.20.local',
    "username" => 'dbuser',
    "password" => 'top-secret-password',
    "database" => 'ss_mysite',
);

SSViewer::set_theme('simple');
SiteTree::enable_nested_urls();

Open Issues