<p>Answering my own question:
We ended up configuring MySQL as Routh suggests here:
<a href="https://craftcms.stackexchange.com/questions/12084/getting-this-sql-error-group-by-incompatible-with-sql-mode-only-full-group-by/12106#">Getting this SQL Error: GROUP BY incompatible with sql_mode=only_full_group_by</a> </p>

<p>Problem solved.</p>

CC BY-SA 3.0
2017-02-03T17:15:16.407Z
2017-02-03T17:15:16.407Z
2017-04-13T12:51:00.160Z
userAW
<p>I have 2 Craft sites that share the same Craft codebase. For both sites, i was getting the following error:</p>

<pre><code>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
</code></pre>

<p>I found <a href="https://craftcms.stackexchange.com/questions/12084/getting-this-sql-error-group-by-incompatible-with-sql-mode-only-full-group-by/12106#">this Craft Stack Exchange solution</a> 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.</p>

<hr>

<p><strong>O/S:</strong></p>

<p>O/S: GNU/Linux</p>

<p>Issue: Ubuntu 16.04.1 LTS</p>

<p>Kernel Release: 4.4.0-43-generic</p>

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

<p><strong>Apache:</strong></p>

<p>Server version: Apache/2.4.18 (Ubuntu)</p>

<p>Server built:   2016-07-14T12:32:26</p>

<p><strong>MySQL:</strong></p>

<p>mysql  Ver 14.14 Distrib 5.7.15, for Linux (i686) using  EditLine wrapper</p>

<p><strong>PHP:</strong></p>

<p>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</p>

<p><strong>Craft:</strong></p>

<p>define('CRAFT_VERSION', '2.6');</p>

<p>define('CRAFT_BUILD', '2949');</p>

<p>define('CRAFT_SCHEMA_VERSION', '2.6.7');</p>

<p>define('CRAFT_RELEASE_DATE', '1475689624');</p>

<p>define('CRAFT_MIN_BUILD_REQUIRED', '2570');</p>

<p>define('CRAFT_MIN_BUILD_URL', '<a href="https://download.craftcdn.com/craft/2.1/2.1.2570/Craft-2.1.2570.zip" rel="nofollow noreferrer">https://download.craftcdn.com/craft/2.1/2.1.2570/Craft-2.1.2570.zip</a>');</p>

<p>define('CRAFT_TRACK', 'stable');</p>

<p><strong>Craft's db.php:</strong></p>

<pre><code>&lt;?php

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

                // The database username to connect with.
                'user' =&gt; '&lt;redacted&gt;',

                // The database password to connect with.
                'password' =&gt; '&lt;redacted&gt;',
        ),

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

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

                // Work-around for newer version of MySQL
                'initSQLs' =&gt; 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' =&gt; array(
                // The name of the database to select.
                'database' =&gt; 'craftPc',

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

                // Work-around for newer version of MySQL
                'initSQLs' =&gt; 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';"),
        ),
); ?&gt;
</code></pre>

CC BY-SA 3.0
2016-10-21T18:25:49.307Z
Is `devappsPc` the one that's broken?  Are you accessing it via `http://devappsPc`?
Brad Bell
CC BY-SA 3.0
2016-10-21T18:59:34.947Z
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
CC BY-SA 3.0
2016-10-21T20:21:49.343Z
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
CC BY-SA 3.0
2016-10-26T13:51:03.933Z
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
CC BY-SA 3.0
2016-10-26T17:52:43.970Z
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
CC BY-SA 3.0
2016-10-26T23:37:13.597Z
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
CC BY-SA 3.0
2016-10-27T13:19:48.300Z
We ended up just using the other suggestion on the referred question and configured MySQL as Routh suggests. Problem solved. Thank you.
userAW
CC BY-SA 3.0
2016-10-27T16:49:14.337Z
Glad you're sorted... would you mind adding what worked for you as an official answer?
Brad Bell
CC BY-SA 3.0
2017-02-03T17:09:20.507Z
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
<mysql>
What's the difference? SQL_MODE error in one site and not the other
# What's the difference? SQL_MODE error in one site and not the other

(Asked by: username_0 on 2016-10-21)

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:
 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' => '',

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

        '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';"),
        ),
); ?>

**Tags:** mysql

> **Comments:**
> **username_1**: Is `devappsPc` the one that's broken?  Are you accessing it via `http://devappsPc`?  
> **username_0**: 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.  
> **username_1**: 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  
> **username_0**: 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!  
> **username_1**: 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.  
> **username_0**: that's strange, since adding that piece DID fix the 'devappsCraft' site, but not the 'devappsPc' site. I'll have to ask the sysadmin.  
> **username_0**: We ended up just using the other suggestion on the referred question and configured MySQL as Routh suggests. Problem solved. Thank you.  
> **username_1**: Glad you're sorted... would you mind adding what worked for you as an official answer?  
> **username_0**: sorry, as I've learned more about environment variables: 'devappsPc' and 'devappsCraft' are CRAFT_ENVIRONMENT variables as defined in their respective index.php files.  

---

## 1 answers

### Answer by username_0 on 2017-02-03. Score: 1 | Accepted answer

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](https://craftcms.stackexchange.com/questions/12084/getting-this-sql-error-group-by-incompatible-with-sql-mode-only-full-group-by/12106#)

Problem solved.


---

