1

I have 2 Craft sites that share the same Craft codebase. For both sites, i was getting the following error:

Internal Server Error CDbCommand failed to execute the SQL statement: SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #18 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'creighton_mma_craft.structureelements.root' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

I found this Craft Stack Exchange solution and implemented Brad's second suggestion ('initSQLs' array) on both sites (see config/db.php below). It seems to have fixed it in the site called 'craft' but not the site called 'pc'. Though the question referenced above says that he can reach his control panel, i cannot reach the site or the control panel. What am I doing wrong? As far as I know, there are no differences between the 2 sites. All configuration details listed below.


O/S:

O/S: GNU/Linux

Issue: Ubuntu 16.04.1 LTS

Kernel Release: 4.4.0-43-generic

Kernel Version: #63-Ubuntu SMP Wed Oct 12 13:50:36 UTC 2016

Apache:

Server version: Apache/2.4.18 (Ubuntu)

Server built: 2016-07-14T12:32:26

MySQL:

mysql Ver 14.14 Distrib 5.7.15, for Linux (i686) using EditLine wrapper

PHP:

PHP 7.0.8-0ubuntu0.16.04.3 (cli) ( NTS ) Copyright (c) 1997-2016 The PHP Group Zend Engine v3.0.0, Copyright (c) 1998-2016 Zend Technologies with Zend OPcache v7.0.8-0ubuntu0.16.04.3, Copyright (c) 1999-2016, by Zend Technologies

Craft:

define('CRAFT_VERSION', '2.6');

define('CRAFT_BUILD', '2949');

define('CRAFT_SCHEMA_VERSION', '2.6.7');

define('CRAFT_RELEASE_DATE', '1475689624');

define('CRAFT_MIN_BUILD_REQUIRED', '2570');

define('CRAFT_MIN_BUILD_URL', 'https://download.craftcdn.com/craft/2.1/2.1.2570/Craft-2.1.2570.zip');

define('CRAFT_TRACK', 'stable');

Craft's db.php:

<?php

return array(
        '*' => array(
                // The database server name or IP address. Usually this is 'localhost' or '127.0.0.1'.
                'server' => 'localhost',

                // The database username to connect with.
                'user' => '<redacted>',

                // The database password to connect with.
                'password' => '<redacted>',
        ),

        'devappsCraft' => array(
                // The name of the database to select.
                'database' => 'craft',

                // The prefix to use when naming tables. This can be no more than 5 characters.
                'tablePrefix' => 'path',

                // Work-around for newer version of MySQL
                'initSQLs' => array("SET SESSION sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';"),
        ),

        'devappsPc' => array(
                // The name of the database to select.
                'database' => 'craftPc',

                // The prefix to use when naming tables. This can be no more than 5 characters.
                'tablePrefix' => 'pc',

                // Work-around for newer version of MySQL
                'initSQLs' => array("SET SESSION sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';"),
        ),
); ?>
  • Is devappsPc the one that's broken? Are you accessing it via http://devappsPc? – Brad Bell Oct 21 '16 at 18:25
  • sorry, typo. yes, devappsPc is the broken one. i am accessing it via the siteroot/pc. the devappsCraft site is accessed the same way by siteroot/craft. – userAW Oct 21 '16 at 18:59
  • Are you you doing logic elsewhere to define a devappsPc key? By default multi-environment keys work off of the domain name: craftcms.com/docs/multi-environment-configs – Brad Bell Oct 21 '16 at 20:21
  • not sure what you mean. 'devappsPc' is the portion of the domain that relates to that site. the URL is 'example.com/devappsPc' while devappsCraft site's URL is 'example.com/devappsCraft'. We've been using this configuration for a while and had no problems....? Thanks for your responsiveness! – userAW Oct 26 '16 at 13:51
  • Multi-environment keys work off of the $_SERVER['SERVER_NAME'] global variable, which (assuming your web server is configured properly) is going to be the domain name minus any protocol (http, https). i.e. example.com. So Craft is never by default going to look at any path after the domain name to match a multi-environment config key. – Brad Bell Oct 26 '16 at 17:52
1

Answering my own question: We ended up configuring MySQL as Routh suggests here: Getting this SQL Error: GROUP BY incompatible with sql_mode=only_full_group_by

Problem solved.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.