What's the difference? SQL_MODE error in one site and not the other

Question

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: https://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
that's strange, since adding that piece DID fix the 'devappsCraft' site, but not the 'devappsPc' site. I'll have to ask the sysadmin. — userAW, Oct 26 '16 at 23:37
We ended up just using the other suggestion on the referred question and configured MySQL as Routh suggests. Problem solved. Thank you. — userAW, Oct 27 '16 at 13:19
Glad you're sorted... would you mind adding what worked for you as an official answer? — Brad Bell, Oct 27 '16 at 16:49
sorry, as I've learned more about environment variables: 'devappsPc' and 'devappsCraft' are CRAFT_ENVIRONMENT variables as defined in their respective index.php files. — userAW, Feb 03 '17 at 17:09

score 1 · Accepted Answer · edited Apr 13 '17 at 12:51

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.

edited Apr 13 '17 at 12:51

Community

1

answered Feb 03 '17 at 17:15

userAW

176
9

What's the difference? SQL_MODE error in one site and not the other

1 Answers1