Error when trying to sort by title/creator

Omeka Classic Troubleshooting
1

I’m getting this error when I try to sort items by either title or creator:

2016-12-10T11:12:12-08:00 ERR (3): Zend_Db_Statement_Mysqli_Exception: Mysqli prepare error: Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'omeka.et_sort.text' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by in /var/www/html/omeka/application/libraries/Zend/Db/Statement/Mysqli.php:77
Stack trace:
#0 /var/www/html/omeka/application/libraries/Zend/Db/Statement.php(115): Zend_Db_Statement_Mysqli->_prepare('SELECT `items`....')
#1 /var/www/html/omeka/application/libraries/Zend/Db/Adapter/Mysqli.php(388): Zend_Db_Statement->__construct(Object(Zend_Db_Adapter_Mysqli), 'SELECT `items`....')
#2 /var/www/html/omeka/application/libraries/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Adapter_Mysqli->prepare('SELECT `items`....')
#3 /var/www/html/omeka/application/libraries/Omeka/Db.php(79): Zend_Db_Adapter_Abstract->query('SELECT `items`....', Array)
#4 /var/www/html/omeka/application/libraries/Omeka/Db/Table.php(648): Omeka_Db->__call('query', Array)
#5 /var/www/html/omeka/application/libraries/Omeka/Db/Table.php(281): Omeka_Db_Table->fetchObjects(Object(Omeka_Db_Select))
#6 /var/www/html/omeka/application/controllers/helpers/Db.php(59): Omeka_Db_Table->findBy(Array, 10, 1)
#7 /var/www/html/omeka/application/libraries/Omeka/Controller/AbstractActionController.php(128): Omeka_Controller_Action_Helper_Db->__call('findBy', Array)
#8 /var/www/html/omeka/application/controllers/ItemsController.php(183): Omeka_Controller_AbstractActionController->browseAction()
#9 /var/www/html/omeka/application/libraries/Zend/Controller/Action.php(516): ItemsController->browseAction()
#10 /var/www/html/omeka/application/libraries/Zend/Controller/Dispatcher/Standard.php(308): Zend_Controller_Action->dispatch('browseAction')
#11 /var/www/html/omeka/application/libraries/Zend/Controller/Front.php(954): Zend_Controller_Dispatcher_Standard->dispatch(Object(Zend_Controller_Request_Http), Object(Zend_Controller_Response_Http))
#12 /var/www/html/omeka/application/libraries/Zend/Application/Bootstrap/Bootstrap.php(105): Zend_Controller_Front->dispatch()
#13 /var/www/html/omeka/application/libraries/Zend/Application.php(384): Zend_Application_Bootstrap_Bootstrap->run()
#14 /var/www/html/omeka/application/libraries/Omeka/Application.php(79): Zend_Application->run()
#15 /var/www/html/omeka/index.php(23): Omeka_Application->run()
#16 {main}
2

Hi, I guess you probably have a newer version of MYSQL, which has "compatibility issue with Omeka 2.3.1 and 2.4, specifically with the default ONLY_FULL_GROUP_BY behaviour since MySQL 5.7.5. "
as stated and explained in the following:


Sort items by title with MySQL 5.7.10

Probable Bottom Line: You will have to change your “sql_mode” in MYSQL configuration.
Be sure sql_mode DOES NOT INCLUDE “ONLY_FULL_GROUP_BY”.

As mikesname commented on Apr 15:
Workaround Omeka problem with ONLY_FULL_GROUP_BY
sql_mode=“STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION”

If you don’t know much of MYSQL configuration, I would follow caa’s advice commented on Jun 28:
“
On Ubuntu 16.04 LTS and probably Debian Jessie, you have to add the workaround to /etc/mysql/mysql.conf.d/mysqld.cnf
just under the [mysqld] line.
Restart MySQL with
service mysql restart if needed.
”

I hope this helps.

3

@nrucks basically has it right: this is an issue that crops up with people using very recent versions of MySQL.

It will be fixed in Omeka 2.5, due out very soon. In the interim the sql_mode workaround will work also.

1 Like