<p>this is a MySQL/i version issue on Craft 2 sites.
You'll need to add this to your database config (<code>craft/config/db.php</code>):</p>

<pre><code>'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';")
</code></pre>

<p>As per discussion at <a href="https://craftcms.stackexchange.com/a/12106/992">https://craftcms.stackexchange.com/a/12106/992</a></p>

CC BY-SA 4.0
2019-08-22T15:46:50.857Z
Thanks buddy! I'd not seen this before. Seems to have done the trick.
richardpixel
CC BY-SA 4.0
2019-08-22T12:16:08.667Z
2019-08-22T12:16:08.667Z
Cole Henley
<p>Just tried to get a local site working again but getting all sorts of issues.</p>

<p>I have updated the website online and downloaded the database, installed locally. I have also the <code>craft/app</code> folder to my local setup. I'm running MAMP Pro and this site is set to PHP 7.0.32.</p>

<p>I'm getting the following errors on the front-end locally and '/admin':</p>

<pre><code>CDbCommand failed to execute the SQL statement: SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #45 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'richard_craft.structureelements.root' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by. The SQL statement executed was: SELECT `elements`.`id`, `elements`.`type`, `elements`.`enabled`, `elements`.`archived`, `elements`.`dateCreated`, `elements`.`dateUpdated`, `elements_i18n`.`slug`, `elements_i18n`.`uri`, `elements_i18n`.`enabled` AS `localeEnabled`, `content`.`id` AS `contentId`, `content`.`title`, `content`.`field_address`, `content`.`field_awardTitle`, `content`.`field_body`, `content`.`field_btnTxt`, `content`.`field_flag`, `content`.`field_detailsTitle`, `content`.`field_downloadsTitle`, `content`.`field_faqsShortTitle`, `content`.`field_footnote`, `content`.`field_faqsTitle`, `content`.`field_headline`, `content`.`field_intro`, `content`.`field_phone`, `content`.`field_photosTitle`, `content`.`field_relatedProductsTitle`, `content`.`field_searchProductsTitle`, `content`.`field_seoDescription`, `content`.`field_seoFooterCode`, `content`.`field_seoKeywords`, `content`.`field_seoRobots`, `content`.`field_seoTitle`, `content`.`field_subHeading`, `content`.`field_retailerType`, `content`.`field_video`, `content`.`field_videoTitle`, `content`.`field_website`, `content`.`field_websiteBy`, `content`.`field_youSearchedTitle`, `entries`.`sectionId`, `entries`.`typeId`, `entries`.`authorId`, `entries`.`postDate`, `entries`.`expiryDate`, `structureelements`.`root`, `structureelements`.`lft`, `structureelements`.`rgt`, `structureelements`.`level`
FROM `craft_elements` `elements`
JOIN `craft_elements_i18n` `elements_i18n` ON elements_i18n.elementId = elements.id
JOIN `craft_content` `content` ON content.elementId = elements.id
JOIN `craft_entries` `entries` ON entries.id = elements.id
JOIN `craft_sections` `sections` ON sections.id = entries.sectionId
LEFT JOIN `craft_structures` `structures` ON structures.id = sections.structureId
LEFT JOIN `craft_structureelements` `structureelements` ON (structureelements.structureId = structures.id) AND (structureelements.elementId = entries.id)
WHERE ((((elements_i18n.locale = :locale) AND (content.locale = :locale)) AND (elements.archived = 0)) AND (`entries`.`sectionId` IN ('4', '6', '12', '5', '1', '8', '9', '3', '10', '11', '7'))) AND (`entries`.`sectionId` IN ('4', '12', '5', '8', '3', '10'))
GROUP BY `elements`.`id`
ORDER BY `elements`.`dateCreated` DESC LIMIT 10. Bound with :locale='en_gb'
</code></pre>

<blockquote>
  <p>/Users/richardwiggins/Dropbox (Personal)/Sites/Milenco/craft/app/framework/db/CDbCommand.php(543)</p>
</blockquote>

<pre><code>531         {
532             if($this-&gt;_connection-&gt;enableProfiling)
533                 Yii::endProfile('system.db.CDbCommand.query('.$this-&gt;getText().$par.')','system.db.CDbCommand.query');
534 
535             $errorInfo=$e instanceof PDOException ? $e-&gt;errorInfo : null;
536             $message=$e-&gt;getMessage();
537             Yii::log(Yii::t('yii','CDbCommand::{method}() failed: {error}. The SQL statement executed was: {sql}.',
538                 array('{method}'=&gt;$method, '{error}'=&gt;$message, '{sql}'=&gt;$this-&gt;getText().$par)),CLogger::LEVEL_ERROR,'system.db.CDbCommand');
539 
540             if(YII_DEBUG)
541                 $message.='. The SQL statement executed was: '.$this-&gt;getText().$par;
542 
543             throw new CDbException(Yii::t('yii','CDbCommand failed to execute the SQL statement: {error}',
544                 array('{error}'=&gt;$message)),(int)$e-&gt;getCode(),$errorInfo);
545         }
546     }
547 
548     /**
549      * Builds a SQL SELECT statement from the given query specification.
550      * @param array $query the query specification in name-value pairs. The following
551      * query options are supported: {@link select}, {@link distinct}, {@link from},
552      * {@link where}, {@link join}, {@link group}, {@link having}, {@link order},
553      * {@link limit}, {@link offset} and {@link union}.
554      * @throws CDbException if "from" key is not present in given query parameter
555      * @return string the SQL statement
</code></pre>

<p>Can anyone help?</p>

<php><mysql><craft2>
Local DB SQL 'CDbException' Errors on Craft 2 site
# Local DB SQL 'CDbException' Errors on Craft 2 site

(Asked by: username_0 on 2019-08-22)

Just tried to get a local site working again but getting all sorts of issues.
I have updated the website online and downloaded the database, installed locally. I have also the craft/app folder to my local setup. I'm running MAMP Pro and this site is set to PHP 7.0.32.
I'm getting the following errors on the front-end locally and '/admin':
CDbCommand failed to execute the SQL statement: SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #45 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'richard_craft.structureelements.root' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by. The SQL statement executed was: SELECT `elements`.`id`, `elements`.`type`, `elements`.`enabled`, `elements`.`archived`, `elements`.`dateCreated`, `elements`.`dateUpdated`, `elements_i18n`.`slug`, `elements_i18n`.`uri`, `elements_i18n`.`enabled` AS `localeEnabled`, `content`.`id` AS `contentId`, `content`.`title`, `content`.`field_address`, `content`.`field_awardTitle`, `content`.`field_body`, `content`.`field_btnTxt`, `content`.`field_flag`, `content`.`field_detailsTitle`, `content`.`field_downloadsTitle`, `content`.`field_faqsShortTitle`, `content`.`field_footnote`, `content`.`field_faqsTitle`, `content`.`field_headline`, `content`.`field_intro`, `content`.`field_phone`, `content`.`field_photosTitle`, `content`.`field_relatedProductsTitle`, `content`.`field_searchProductsTitle`, `content`.`field_seoDescription`, `content`.`field_seoFooterCode`, `content`.`field_seoKeywords`, `content`.`field_seoRobots`, `content`.`field_seoTitle`, `content`.`field_subHeading`, `content`.`field_retailerType`, `content`.`field_video`, `content`.`field_videoTitle`, `content`.`field_website`, `content`.`field_websiteBy`, `content`.`field_youSearchedTitle`, `entries`.`sectionId`, `entries`.`typeId`, `entries`.`authorId`, `entries`.`postDate`, `entries`.`expiryDate`, `structureelements`.`root`, `structureelements`.`lft`, `structureelements`.`rgt`, `structureelements`.`level`
FROM `craft_elements` `elements`
JOIN `craft_elements_i18n` `elements_i18n` ON elements_i18n.elementId = elements.id
JOIN `craft_content` `content` ON content.elementId = elements.id
JOIN `craft_entries` `entries` ON entries.id = elements.id
JOIN `craft_sections` `sections` ON sections.id = entries.sectionId
LEFT JOIN `craft_structures` `structures` ON structures.id = sections.structureId
LEFT JOIN `craft_structureelements` `structureelements` ON (structureelements.structureId = structures.id) AND (structureelements.elementId = entries.id)
WHERE ((((elements_i18n.locale = :locale) AND (content.locale = :locale)) AND (elements.archived = 0)) AND (`entries`.`sectionId` IN ('4', '6', '12', '5', '1', '8', '9', '3', '10', '11', '7'))) AND (`entries`.`sectionId` IN ('4', '12', '5', '8', '3', '10'))
GROUP BY `elements`.`id`
ORDER BY `elements`.`dateCreated` DESC LIMIT 10. Bound with :locale='en_gb'


/Users/richardwiggins/Dropbox (Personal)/Sites/Milenco/craft/app/framework/db/CDbCommand.php(543)

531         {
532             if($this->_connection->enableProfiling)
533                 Yii::endProfile('system.db.CDbCommand.query('.$this->getText().$par.')','system.db.CDbCommand.query');
534 
535             $errorInfo=$e instanceof PDOException ? $e->errorInfo : null;
536             $message=$e->getMessage();
537             Yii::log(Yii::t('yii','CDbCommand::{method}() failed: {error}. The SQL statement executed was: {sql}.',
538                 array('{method}'=>$method, '{error}'=>$message, '{sql}'=>$this->getText().$par)),CLogger::LEVEL_ERROR,'system.db.CDbCommand');
539 
540             if(YII_DEBUG)
541                 $message.='. The SQL statement executed was: '.$this->getText().$par;
542 
543             throw new CDbException(Yii::t('yii','CDbCommand failed to execute the SQL statement: {error}',
544                 array('{error}'=>$message)),(int)$e->getCode(),$errorInfo);
545         }
546     }
547 
548     /**
549      * Builds a SQL SELECT statement from the given query specification.
550      * @param array $query the query specification in name-value pairs. The following
551      * query options are supported: {@link select}, {@link distinct}, {@link from},
552      * {@link where}, {@link join}, {@link group}, {@link having}, {@link order},
553      * {@link limit}, {@link offset} and {@link union}.
554      * @throws CDbException if "from" key is not present in given query parameter
555      * @return string the SQL statement

Can anyone help?

**Tags:** php, mysql, craft2


---

## 1 answers

### Answer by username_1 on 2019-08-22. Score: 5 | Accepted answer

this is a MySQL/i version issue on Craft 2 sites.
You'll need to add this to your database config (craft/config/db.php):
'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';")

As per discussion at https://craftcms.stackexchange.com/a/12106/992

> **Comments:**
> **username_0**: Thanks buddy! I'd not seen this before. Seems to have done the trick.  

---

