ONLY_FULL_GROUP_BY error

[hxsllc]

Omeka S encountered an error

Doctrine\DBAL\Exception\DriverException
An exception occurred while executing 'SELECT LOWER(MIN(value.value)) AS "identifier", MIN(value.resource_id) AS "id" FROM value value LEFT JOIN resource resource ON value.resource_id = resource.id WHERE (value.type = "literal") AND (value.property_id = ?) AND (resource.resource_type = ?) AND (value.value = ?) GROUP BY "identifier" ORDER BY "id" ASC, value.id ASC' with params [10, "Omeka\Entity\Item", "vtl_02619_01"]: SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #2 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'dcashion_os2.value.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

From:
https://stackoverflow.com/questions/41887460/select-list-is-not-in-group-by-clause-and-contains-nonaggregated-column-inc/41887524

There is a system variable "ONLY_FULL_GROUP_BY" in MySql engine.
From Mysql Version 5.7.5 : ONLY_FULL_GROUP_BY SQL mode is enabled by default
Before Version 5.7.5 : ONLY_FULL_GROUP_BY was not enabled by default.

If the ONLY_FULL_GROUP_BY SQL mode is enabled (which it is by default from version 5.7.5), MySQL rejects queries for which the select list, HAVING condition, or ORDER BY list refer to non-aggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on them.

[hxsllc]

Details:

PDOException: SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #2 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'dcashion_os2.value.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by in /home/dcashion/metascripta.org/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:112
Stack trace:
#0 /home/dcashion/metascripta.org/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php(112): PDOStatement->execute(NULL)
#1 /home/dcashion/metascripta.org/vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(1292): Doctrine\DBAL\Driver\PDOStatement->execute()
#2 /home/dcashion/metascripta.org/modules/CleanUrl/src/View/Helper/GetResourcesFromIdentifiers.php(193): Doctrine\DBAL\Connection->executeQuery('SELECT LOWER(MI...', Array)
#3 [internal function]: CleanUrl\View\Helper\GetResourcesFromIdentifiers->__invoke(Array, 'items')
#4 /home/dcashion/metascripta.org/vendor/laminas/laminas-view/src/Renderer/PhpRenderer.php(396): call_user_func_array(Object(CleanUrl\View\Helper\GetResourcesFromIdentifiers), Array)
#5 /home/dcashion/metascripta.org/modules/CleanUrl/src/View/Helper/GetResourceFromIdentifier.php(20): Laminas\View\Renderer\PhpRenderer->__call('getResourcesFro...', Array)
#6 /home/dcashion/metascripta.org/modules/CleanUrl/src/Router/Http/CleanRoute.php(597): CleanUrl\View\Helper\GetResourceFromIdentifier->__invoke('VTL_02619_01', 'items')
#7 /home/dcashion/metascripta.org/modules/CleanUrl/src/Router/Http/CleanRoute.php(199): CleanUrl\Router\Http\CleanRoute->getResourceIdFromParams(Array, Array)
#8 /home/dcashion/metascripta.org/vendor/laminas/laminas-router/src/Http/Part.php(146): CleanUrl\Router\Http\CleanRoute->match(Object(Laminas\Http\PhpEnvironment\Request), 0, Array)
#9 /home/dcashion/metascripta.org/vendor/laminas/laminas-router/src/Http/TreeRouteStack.php(317): Laminas\Router\Http\Part->match(Object(Laminas\Http\PhpEnvironment\Request), 0, Array)
#10 /home/dcashion/metascripta.org/application/src/Mvc/Status.php(77): Laminas\Router\Http\TreeRouteStack->match(Object(Laminas\Http\PhpEnvironment\Request))
#11 /home/dcashion/metascripta.org/application/src/Mvc/Status.php(90): Omeka\Mvc\Status->getRouteMatch()
#12 /home/dcashion/metascripta.org/application/src/Mvc/Status.php(118): Omeka\Mvc\Status->getRouteParam('ADMIN')
#13 /home/dcashion/metascripta.org/modules/Search/Module.php(255): Omeka\Mvc\Status->isAdminRequest()
#14 /home/dcashion/metascripta.org/modules/Search/Module.php(82): Search\Module->addRoutes()
#15 /home/dcashion/metascripta.org/vendor/laminas/laminas-eventmanager/src/EventManager.php(321): Search\Module->onBootstrap(Object(Laminas\Mvc\MvcEvent))
#16 /home/dcashion/metascripta.org/vendor/laminas/laminas-eventmanager/src/EventManager.php(170): Laminas\EventManager\EventManager->triggerListeners(Object(Laminas\Mvc\MvcEvent))
#17 /home/dcashion/metascripta.org/vendor/laminas/laminas-mvc/src/Application.php(160): Laminas\EventManager\EventManager->triggerEvent(Object(Laminas\Mvc\MvcEvent))
#18 /home/dcashion/metascripta.org/application/src/Mvc/Application.php(40): Laminas\Mvc\Application->bootstrap(Array)
#19 /home/dcashion/metascripta.org/index.php(19): Omeka\Mvc\Application::init(Array)
#20 {main}

Next Doctrine\DBAL\Driver\PDO\Exception: SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #2 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'dcashion_os2.value.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by in /home/dcashion/metascripta.org/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDO/Exception.php:18
Stack trace:
#0 /home/dcashion/metascripta.org/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php(114): Doctrine\DBAL\Driver\PDO\Exception::new(Object(PDOException))
#1 /home/dcashion/metascripta.org/vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(1292): Doctrine\DBAL\Driver\PDOStatement->execute()
#2 /home/dcashion/metascripta.org/modules/CleanUrl/src/View/Helper/GetResourcesFromIdentifiers.php(193): Doctrine\DBAL\Connection->executeQuery('SELECT LOWER(MI...', Array)
#3 [internal function]: CleanUrl\View\Helper\GetResourcesFromIdentifiers->__invoke(Array, 'items')
#4 /home/dcashion/metascripta.org/vendor/laminas/laminas-view/src/Renderer/PhpRenderer.php(396): call_user_func_array(Object(CleanUrl\View\Helper\GetResourcesFromIdentifiers), Array)
#5 /home/dcashion/metascripta.org/modules/CleanUrl/src/View/Helper/GetResourceFromIdentifier.php(20): Laminas\View\Renderer\PhpRenderer->__call('getResourcesFro...', Array)
#6 /home/dcashion/metascripta.org/modules/CleanUrl/src/Router/Http/CleanRoute.php(597): CleanUrl\View\Helper\GetResourceFromIdentifier->__invoke('VTL_02619_01', 'items')
#7 /home/dcashion/metascripta.org/modules/CleanUrl/src/Router/Http/CleanRoute.php(199): CleanUrl\Router\Http\CleanRoute->getResourceIdFromParams(Array, Array)
#8 /home/dcashion/metascripta.org/vendor/laminas/laminas-router/src/Http/Part.php(146): CleanUrl\Router\Http\CleanRoute->match(Object(Laminas\Http\PhpEnvironment\Request), 0, Array)
#9 /home/dcashion/metascripta.org/vendor/laminas/laminas-router/src/Http/TreeRouteStack.php(317): Laminas\Router\Http\Part->match(Object(Laminas\Http\PhpEnvironment\Request), 0, Array)
#10 /home/dcashion/metascripta.org/application/src/Mvc/Status.php(77): Laminas\Router\Http\TreeRouteStack->match(Object(Laminas\Http\PhpEnvironment\Request))
#11 /home/dcashion/metascripta.org/application/src/Mvc/Status.php(90): Omeka\Mvc\Status->getRouteMatch()
#12 /home/dcashion/metascripta.org/application/src/Mvc/Status.php(118): Omeka\Mvc\Status->getRouteParam('ADMIN')
#13 /home/dcashion/metascripta.org/modules/Search/Module.php(255): Omeka\Mvc\Status->isAdminRequest()
#14 /home/dcashion/metascripta.org/modules/Search/Module.php(82): Search\Module->addRoutes()
#15 /home/dcashion/metascripta.org/vendor/laminas/laminas-eventmanager/src/EventManager.php(321): Search\Module->onBootstrap(Object(Laminas\Mvc\MvcEvent))
#16 /home/dcashion/metascripta.org/vendor/laminas/laminas-eventmanager/src/EventManager.php(170): Laminas\EventManager\EventManager->triggerListeners(Object(Laminas\Mvc\MvcEvent))
#17 /home/dcashion/metascripta.org/vendor/laminas/laminas-mvc/src/Application.php(160): Laminas\EventManager\EventManager->triggerEvent(Object(Laminas\Mvc\MvcEvent))
#18 /home/dcashion/metascripta.org/application/src/Mvc/Application.php(40): Laminas\Mvc\Application->bootstrap(Array)
#19 /home/dcashion/metascripta.org/index.php(19): Omeka\Mvc\Application::init(Array)
#20 {main}

Next Doctrine\DBAL\Exception\DriverException: An exception occurred while executing 'SELECT LOWER(MIN(value.value)) AS "identifier", MIN(value.resource_id) AS "id" FROM value value LEFT JOIN resource resource ON value.resource_id = resource.id WHERE (value.type = "literal") AND (value.property_id = ?) AND (resource.resource_type = ?) AND (value.value = ?) GROUP BY "identifier" ORDER BY "id" ASC, value.id ASC' with params [10, "Omeka\Entity\Item", "vtl_02619_01"]:

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #2 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'dcashion_os2.value.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by in /home/dcashion/metascripta.org/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php:128
Stack trace:
#0 /home/dcashion/metascripta.org/vendor/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php(182): Doctrine\DBAL\Driver\AbstractMySQLDriver->convertException('An exception oc...', Object(Doctrine\DBAL\Driver\PDO\Exception))
#1 /home/dcashion/metascripta.org/vendor/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php(159): Doctrine\DBAL\DBALException::wrapException(Object(Doctrine\DBAL\Driver\PDO\MySQL\Driver), Object(Doctrine\DBAL\Driver\PDO\Exception), 'An exception oc...')
#2 /home/dcashion/metascripta.org/vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(2214): Doctrine\DBAL\DBALException::driverExceptionDuringQuery(Object(Doctrine\DBAL\Driver\PDO\MySQL\Driver), Object(Doctrine\DBAL\Driver\PDO\Exception), 'SELECT LOWER(MI...', Array)
#3 /home/dcashion/metascripta.org/vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(1301): Doctrine\DBAL\Connection->handleExceptionDuringQuery(Object(Doctrine\DBAL\Driver\PDO\Exception), 'SELECT LOWER(MI...', Array, Array)
#4 /home/dcashion/metascripta.org/modules/CleanUrl/src/View/Helper/GetResourcesFromIdentifiers.php(193): Doctrine\DBAL\Connection->executeQuery('SELECT LOWER(MI...', Array)
#5 [internal function]: CleanUrl\View\Helper\GetResourcesFromIdentifiers->__invoke(Array, 'items')
#6 /home/dcashion/metascripta.org/vendor/laminas/laminas-view/src/Renderer/PhpRenderer.php(396): call_user_func_array(Object(CleanUrl\View\Helper\GetResourcesFromIdentifiers), Array)
#7 /home/dcashion/metascripta.org/modules/CleanUrl/src/View/Helper/GetResourceFromIdentifier.php(20): Laminas\View\Renderer\PhpRenderer->__call('getResourcesFro...', Array)
#8 /home/dcashion/metascripta.org/modules/CleanUrl/src/Router/Http/CleanRoute.php(597): CleanUrl\View\Helper\GetResourceFromIdentifier->__invoke('VTL_02619_01', 'items')
#9 /home/dcashion/metascripta.org/modules/CleanUrl/src/Router/Http/CleanRoute.php(199): CleanUrl\Router\Http\CleanRoute->getResourceIdFromParams(Array, Array)
#10 /home/dcashion/metascripta.org/vendor/laminas/laminas-router/src/Http/Part.php(146): CleanUrl\Router\Http\CleanRoute->match(Object(Laminas\Http\PhpEnvironment\Request), 0, Array)
#11 /home/dcashion/metascripta.org/vendor/laminas/laminas-router/src/Http/TreeRouteStack.php(317): Laminas\Router\Http\Part->match(Object(Laminas\Http\PhpEnvironment\Request), 0, Array)
#12 /home/dcashion/metascripta.org/application/src/Mvc/Status.php(77): Laminas\Router\Http\TreeRouteStack->match(Object(Laminas\Http\PhpEnvironment\Request))
#13 /home/dcashion/metascripta.org/application/src/Mvc/Status.php(90): Omeka\Mvc\Status->getRouteMatch()
#14 /home/dcashion/metascripta.org/application/src/Mvc/Status.php(118): Omeka\Mvc\Status->getRouteParam('ADMIN')
#15 /home/dcashion/metascripta.org/modules/Search/Module.php(255): Omeka\Mvc\Status->isAdminRequest()
#16 /home/dcashion/metascripta.org/modules/Search/Module.php(82): Search\Module->addRoutes()
#17 /home/dcashion/metascripta.org/vendor/laminas/laminas-eventmanager/src/EventManager.php(321): Search\Module->onBootstrap(Object(Laminas\Mvc\MvcEvent))
#18 /home/dcashion/metascripta.org/vendor/laminas/laminas-eventmanager/src/EventManager.php(170): Laminas\EventManager\EventManager->triggerListeners(Object(Laminas\Mvc\MvcEvent))
#19 /home/dcashion/metascripta.org/vendor/laminas/laminas-mvc/src/Application.php(160): Laminas\EventManager\EventManager->triggerEvent(Object(Laminas\Mvc\MvcEvent))
#20 /home/dcashion/metascripta.org/application/src/Mvc/Application.php(40): Laminas\Mvc\Application->bootstrap(Array)
#21 /home/dcashion/metascripta.org/index.php(19): Omeka\Mvc\Application::init(Array)
#22 {main}

[mcoonen]

I'm hitting a similar error as @hxsllc when working with CleanUrl in combination with ARK identifiers in the dcterms:identifier field.

My error:

Doctrine\DBAL\Exception\DriverException
An exception occurred while executing 'SELECT value.value, MIN(value.resource_id), resource.resource_type, value.id FROM value value INNER JOIN resource resource ON resource.id = value.resource_id WHERE (value.property_id = 10) AND (value.type = "literal") AND (value.value = ?) GROUP BY value.resource_id, resource.resource_type ORDER BY value.resource_id ASC, value.id ASC LIMIT 1' with params ["ark:\/99999\/b1pzAho"]: SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #4 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'OmekaTest.value.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Versions used

Omeka version: 3.2.0
CleanUrl version: 3.17.4.4

To reproduce the error

• Make sure there is no default site set in Global settings
• Change something to the site settings of any site, it does not matter which one. For instance: change the site's title
• Now go to any site's frontend and browse items. You'll notice that the item URLs are now based on the item ID (e.g. https://<omeka_fqdn>/s//item/1234) instead of the ARK ID.
• Manually enter an ARK URL, e.g. https://<omeka_fqdn>/s//ark:/99999/b1pzAho and you will end up at a HTTP 500 page with the Doctrine\DBAL\Exception\DriverException

To workaround the issue

• Go to global settings and set a default site
• Go to Modules - CleanUrl - Configure
• Check Skip "s/site-slug/" for default site and press Submit
• Uncheck Skip "s/site-slug/" for default site and press Submit
• Go to global settings and unset the default site
  ARK URLs should now be working again.

Fix

It seems like a bug that needs fixing. I will have a look when I have some time, but any hint in the proper direction would be welcome @Daniel-KM