Sort items by title with MySQL 5.7.10:

[mikesname]

I've just ran into a MySQL 5.7 compatibility issue with Omeka 2.3.1 and 2.4, specifically with the default ONLY_FULL_GROUP_BY behaviour since MySQL 5.7.5. Specifically, sorting items by title (at say /items/browse?sort_field=Dublin+Core%2CTitle) results in this error:

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

exception 'Zend_Db_Statement_Mysqli_Exception' with message '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/omeka/application/libraries/Zend/Db/Statement/Mysqli.php:77
Stack trace:
#0 /var/www/omeka/application/libraries/Zend/Db/Statement.php(115): Zend_Db_Statement_Mysqli->_prepare('SELECT `items`....')
#1 /var/www/omeka/application/libraries/Zend/Db/Adapter/Mysqli.php(388): Zend_Db_Statement->__construct(Object(Zend_Db_Adapter_Mysqli), 'SELECT `items`....')
#2 /var/www/omeka/application/libraries/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Adapter_Mysqli->prepare('SELECT `items`....')
#3 [internal function]: Zend_Db_Adapter_Abstract->query(Object(Omeka_Db_Select), Array)
#4 /var/www/omeka/application/libraries/Omeka/Db.php(79): call_user_func_array(Array, Array)
#5 /var/www/omeka/application/libraries/Omeka/Db/Table.php(648): Omeka_Db->__call('query', Array)
#6 /var/www/omeka/application/libraries/Omeka/Db/Table.php(648): Omeka_Db->query(Object(Omeka_Db_Select), Array)
#7 /var/www/omeka/application/libraries/Omeka/Db/Table.php(281): Omeka_Db_Table->fetchObjects(Object(Omeka_Db_Select))
#8 [internal function]: Omeka_Db_Table->findBy(Array, 10, 1)
#9 /var/www/omeka/application/controllers/helpers/Db.php(59): call_user_func_array(Array, Array)
#10 /var/www/omeka/application/libraries/Omeka/Controller/AbstractActionController.php(128): Omeka_Controller_Action_Helper_Db->__call('findBy', Array)
#11 /var/www/omeka/application/libraries/Omeka/Controller/AbstractActionController.php(128): Omeka_Controller_Action_Helper_Db->findBy(Array, 10, 1)
#12 /var/www/omeka/application/controllers/ItemsController.php(183): Omeka_Controller_AbstractActionController->browseAction()
#13 /var/www/omeka/application/libraries/Zend/Controller/Action.php(516): ItemsController->browseAction()
#14 /var/www/omeka/application/libraries/Zend/Controller/Dispatcher/Standard.php(308): Zend_Controller_Action->dispatch('browseAction')
#15 /var/www/omeka/application/libraries/Zend/Controller/Front.php(954): Zend_Controller_Dispatcher_Standard->dispatch(Object(Zend_Controller_Request_Http), Object(Zend_Controller_Response_Http))
#16 /var/www/omeka/application/libraries/Zend/Application/Bootstrap/Bootstrap.php(105): Zend_Controller_Front->dispatch()
#17 /var/www/omeka/application/libraries/Zend/Application.php(382): Zend_Application_Bootstrap_Bootstrap->run()
#18 /var/www/omeka/application/libraries/Omeka/Application.php(79): Zend_Application->run()
#19 /var/www/omeka/index.php(23): Omeka_Application->run()
#20 {main}

I'll try and add more details tomorrow when I get a chance to investigate further.

[zerocrates]

I thought there would be a problem with this once I'd initially heard about the 5.7 mode changes... Omeka is definitely dependent on the old nonstandard MySQL handling of GROUP BY in several places.

Fixing the queries or manually setting the SQL mode ourselves on startup are really the only options here, I think. I've tried to avoid setting the mode in the past.

[mikesname]

Setting the mode on startup sounds good to me. Currently I've worked around this by doing:

SET GLOBAL sql_mode=(SELECT REPLACE(@@GLOBAL.sql_mode,'ONLY_FULL_GROUP_BY',''));

[anuragji]

@mikesname Where did you implement this change?

[mikesname]

@anuragji by setting the sql_mode global in a root session, and adding this to /etc/my.cnf so it sticks on restarts:

# 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"

There may well be a better way to do it!

[anuragji]

Thanks @mikesname - while there may be better ways, this one worked for me :)

[zerocrates]

I'm somewhat reluclant to go with the workaround, but I think nevertheless it's a good idea to include it in a 2.4 bugfix, as this is just going to get more relevant as more hosts upgrade MySQL and fixing the actual queries won't happen fast.

[anuragji]

Also since it requires modifying the MySQL configuration itself, rather then being resolved within Omeka, it brings it's own set of related issues.

[zerocrates]

The Omeka-side solution would use this: https://github.com/omeka/Omeka/blob/master/application/libraries/Omeka/Test/Resource/Db.php#L86

Currently that's just being used to make sure we test against strict modes and things like that for the automated tests, but we could set it (even using that exact mode) in normal usage, too, to guarantee the mode's always the same for all users.

[zerocrates]

Let me know how that works for you.

[caa]

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.

[daedaluscrow]

I can verify that caa's advice solves the problem on Ubuntu 16.04.