1

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.

|improve this question
1

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!

|improve this answer

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.