<p>A little more pouring over the logs came up with the specific "only_full_group_by" MySQL error. This put me back on the trail, which lead me here:
<a href="https://craftcms.stackexchange.com/questions/12084/getting-this-sql-error-group-by-incompatible-with-sql-mode-only-full-group-by">Getting this SQL Error: GROUP BY incompatible with sql_mode=only_full_group_by</a></p>

<p>It appears the most straightforward approach is to add this line to your environment within /craft/config/db.php:</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>Thanks again, Brad Bell!</p>

CC BY-SA 4.0
2018-09-25T16:51:53.347Z
2018-09-25T16:51:53.347Z
Marcus
<p>I've duplicated a site into a new development environment, and everything works as it should other than the Entries section. It throws the AJAX-based "unknown error", originating from a 500 error on getElements action. Attempting to bring up that request directly results in a "Bad Request - The request could not be understood by the server due to malformed syntax." screen. The isolated log from the incident included here:</p>

<pre><code>2018/09/25 16:31:49 [profile] [system.db.CDbCommand.query] begin:system.db.CDbCommand.query(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_body`, `content`.`field_redirectLink`, `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 ('2', '1'))) AND (`entries`.`sectionId` IN ('2', '1'))
GROUP BY `elements`.`id`
ORDER BY `postDate` DESC LIMIT 50. Bound with :locale='en_us')
2018/09/25 16:31:49 [profile] [system.db.CDbCommand.query] end:system.db.CDbCommand.query(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_body`, `content`.`field_redirectLink`, `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 ('2', '1'))) AND (`entries`.`sectionId` IN ('2', '1'))
GROUP BY `elements`.`id`
ORDER BY `postDate` DESC LIMIT 50. Bound with :locale='en_us')
2018/09/25 16:31:49 [error] [system.db.CDbCommand] CDbCommand::fetchAll() failed: SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #19 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'craft_lla.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_body`, `content`.`field_redirectLink`, `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 ('2', '1'))) AND (`entries`.`sectionId` IN ('2', '1'))
GROUP BY `elements`.`id`
ORDER BY `postDate` DESC LIMIT 50. Bound with :locale='en_us'.
in /Users/marcus/Web/linked-learning-datamart/craft/app/services/ElementsService.php (224)
in /Users/marcus/Web/linked-learning-datamart/craft/app/models/ElementCriteriaModel.php (284)
in /Users/marcus/Web/linked-learning-datamart/craft/app/elementtypes/BaseElementType.php (237)
2018/09/25 16:31:49 [error] [exception.CDbException] CDbException: CDbCommand failed to execute the SQL statement: SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #19 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'craft_lla.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_body`, `content`.`field_redirectLink`, `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 ('2', '1'))) AND (`entries`.`sectionId` IN ('2', '1'))
GROUP BY `elements`.`id`
ORDER BY `postDate` DESC LIMIT 50. Bound with :locale='en_us' in /Users/marcus/Web/linked-learning-datamart/craft/app/framework/db/CDbCommand.php:543
Stack trace:
#0 /Users/marcus/Web/linked-learning-datamart/craft/app/framework/db/CDbCommand.php(396): CDbCommand-&gt;queryInternal('fetchAll', Array, Array)
#1 /Users/marcus/Web/linked-learning-datamart/craft/app/services/ElementsService.php(224): CDbCommand-&gt;queryAll()
#2 /Users/marcus/Web/linked-learning-datamart/craft/app/models/ElementCriteriaModel.php(284): Craft\ElementsService-&gt;findElements(Object(Craft\ElementCriteriaModel))
#3 /Users/marcus/Web/linked-learning-datamart/craft/app/elementtypes/BaseElementType.php(237): Craft\ElementCriteriaModel-&gt;find()
#4 /Users/marcus/Web/linked-learning-datamart/craft/app/controllers/ElementIndexController.php(373): Craft\BaseElementType-&gt;getIndexHtml(Object(Craft\ElementCriteriaModel), Array, Array, '*', 'index', true, true)
#5 /Users/marcus/Web/linked-learning-datamart/craft/app/controllers/ElementIndexController.php(105): Craft\ElementIndexController-&gt;_getElementResponseData(true, true)
#6 /Users/marcus/Web/linked-learning-datamart/craft/app/framework/web/actions/CInlineAction.php(49): Craft\ElementIndexController-&gt;actionGetElements()
#7 /Users/marcus/Web/linked-learning-datamart/craft/app/framework/web/CController.php(308): CInlineAction-&gt;runWithParams(Array)
#8 /Users/marcus/Web/linked-learning-datamart/craft/app/framework/web/CController.php(286): CController-&gt;runAction(Object(CInlineAction))
#9 /Users/marcus/Web/linked-learning-datamart/craft/app/framework/web/CController.php(265): CController-&gt;runActionWithFilters(Object(CInlineAction), Array)
#10 /Users/marcus/Web/linked-learning-datamart/craft/app/framework/web/CWebApplication.php(282): CController-&gt;run('getElements')
#11 /Users/marcus/Web/linked-learning-datamart/craft/app/etc/web/WebApp.php(837): CWebApplication-&gt;runController('elementIndex/ge...')
#12 /Users/marcus/Web/linked-learning-datamart/craft/app/etc/web/WebApp.php(288): Craft\WebApp-&gt;_processActionRequest()
#13 /Users/marcus/Web/linked-learning-datamart/craft/app/framework/base/CApplication.php(185): Craft\WebApp-&gt;processRequest()
#14 /Users/marcus/Web/linked-learning-datamart/craft/app/index.php(73): CApplication-&gt;run()
#15 /Users/marcus/Web/linked-learning-datamart/index.php(19): require_once('/Users/marcus/W...')
#16 {main}
REQUEST_URI=/index.php/admin/actions/elementIndex/getElements
HTTP_REFERER=http://lla.local/admin/entries
---
2018/09/25 16:31:49 [trace] [system.CModule] Loading "errorHandler" application component
in /Users/marcus/Web/linked-learning-datamart/craft/app/etc/web/WebApp.php (596)

******************************************************************************************************
</code></pre>

<p>I've tried recopying the working site files and database, but this exact same issue shows up each time. Could it be an environmental issue? Despite tracking it this far, I'm at a loss.</p>

<entries><admin><craft2>
Admin getElements 500 "unknown error"
# Admin getElements 500 "unknown error"

(Asked by: username_0 on 2018-09-25)

I've duplicated a site into a new development environment, and everything works as it should other than the Entries section. It throws the AJAX-based "unknown error", originating from a 500 error on getElements action. Attempting to bring up that request directly results in a "Bad Request - The request could not be understood by the server due to malformed syntax." screen. The isolated log from the incident included here:
2018/09/25 16:31:49 [profile] [system.db.CDbCommand.query] begin:system.db.CDbCommand.query(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_body`, `content`.`field_redirectLink`, `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 ('2', '1'))) AND (`entries`.`sectionId` IN ('2', '1'))
GROUP BY `elements`.`id`
ORDER BY `postDate` DESC LIMIT 50. Bound with :locale='en_us')
2018/09/25 16:31:49 [profile] [system.db.CDbCommand.query] end:system.db.CDbCommand.query(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_body`, `content`.`field_redirectLink`, `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 ('2', '1'))) AND (`entries`.`sectionId` IN ('2', '1'))
GROUP BY `elements`.`id`
ORDER BY `postDate` DESC LIMIT 50. Bound with :locale='en_us')
2018/09/25 16:31:49 [error] [system.db.CDbCommand] CDbCommand::fetchAll() failed: SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #19 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'craft_lla.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_body`, `content`.`field_redirectLink`, `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 ('2', '1'))) AND (`entries`.`sectionId` IN ('2', '1'))
GROUP BY `elements`.`id`
ORDER BY `postDate` DESC LIMIT 50. Bound with :locale='en_us'.
in /Users/marcus/Web/linked-learning-datamart/craft/app/services/ElementsService.php (224)
in /Users/marcus/Web/linked-learning-datamart/craft/app/models/ElementCriteriaModel.php (284)
in /Users/marcus/Web/linked-learning-datamart/craft/app/elementtypes/BaseElementType.php (237)
2018/09/25 16:31:49 [error] [exception.CDbException] CDbException: CDbCommand failed to execute the SQL statement: SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #19 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'craft_lla.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_body`, `content`.`field_redirectLink`, `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 ('2', '1'))) AND (`entries`.`sectionId` IN ('2', '1'))
GROUP BY `elements`.`id`
ORDER BY `postDate` DESC LIMIT 50. Bound with :locale='en_us' in /Users/marcus/Web/linked-learning-datamart/craft/app/framework/db/CDbCommand.php:543
Stack trace:
#0 /Users/marcus/Web/linked-learning-datamart/craft/app/framework/db/CDbCommand.php(396): CDbCommand->queryInternal('fetchAll', Array, Array)
#1 /Users/marcus/Web/linked-learning-datamart/craft/app/services/ElementsService.php(224): CDbCommand->queryAll()
#2 /Users/marcus/Web/linked-learning-datamart/craft/app/models/ElementCriteriaModel.php(284): Craft\ElementsService->findElements(Object(Craft\ElementCriteriaModel))
#3 /Users/marcus/Web/linked-learning-datamart/craft/app/elementtypes/BaseElementType.php(237): Craft\ElementCriteriaModel->find()
#4 /Users/marcus/Web/linked-learning-datamart/craft/app/controllers/ElementIndexController.php(373): Craft\BaseElementType->getIndexHtml(Object(Craft\ElementCriteriaModel), Array, Array, '*', 'index', true, true)
#5 /Users/marcus/Web/linked-learning-datamart/craft/app/controllers/ElementIndexController.php(105): Craft\ElementIndexController->_getElementResponseData(true, true)
#6 /Users/marcus/Web/linked-learning-datamart/craft/app/framework/web/actions/CInlineAction.php(49): Craft\ElementIndexController->actionGetElements()
#7 /Users/marcus/Web/linked-learning-datamart/craft/app/framework/web/CController.php(308): CInlineAction->runWithParams(Array)
#8 /Users/marcus/Web/linked-learning-datamart/craft/app/framework/web/CController.php(286): CController->runAction(Object(CInlineAction))
#9 /Users/marcus/Web/linked-learning-datamart/craft/app/framework/web/CController.php(265): CController->runActionWithFilters(Object(CInlineAction), Array)
#10 /Users/marcus/Web/linked-learning-datamart/craft/app/framework/web/CWebApplication.php(282): CController->run('getElements')
#11 /Users/marcus/Web/linked-learning-datamart/craft/app/etc/web/WebApp.php(837): CWebApplication->runController('elementIndex/ge...')
#12 /Users/marcus/Web/linked-learning-datamart/craft/app/etc/web/WebApp.php(288): Craft\WebApp->_processActionRequest()
#13 /Users/marcus/Web/linked-learning-datamart/craft/app/framework/base/CApplication.php(185): Craft\WebApp->processRequest()
#14 /Users/marcus/Web/linked-learning-datamart/craft/app/index.php(73): CApplication->run()
#15 /Users/marcus/Web/linked-learning-datamart/index.php(19): require_once('/Users/marcus/W...')
#16 {main}
REQUEST_URI=/index.php/admin/actions/elementIndex/getElements
HTTP_REFERER=http://lla.local/admin/entries
---
2018/09/25 16:31:49 [trace] [system.CModule] Loading "errorHandler" application component
in /Users/marcus/Web/linked-learning-datamart/craft/app/etc/web/WebApp.php (596)

******************************************************************************************************

I've tried recopying the working site files and database, but this exact same issue shows up each time. Could it be an environmental issue? Despite tracking it this far, I'm at a loss.

**Tags:** entries, admin, craft2

---

## 1 answers

### Answer by username_0 on 2018-09-25. Score: 1 | Accepted answer

A little more pouring over the logs came up with the specific "only_full_group_by" MySQL error. This put me back on the trail, which lead me here:
Getting this SQL Error: GROUP BY incompatible with sql_mode=only_full_group_by
It appears the most straightforward approach is to add this line to your environment within /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';")

Thanks again, Brad Bell!


---

