1

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?

2

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

| improve this answer | |
  • Thanks buddy! I'd not seen this before. Seems to have done the trick. – richardpixel Aug 22 '19 at 15:46

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.