websearchadmin module is not compatible with mysql 5.7.11

[dimaip]

It fails with this stack trace: https://gist.github.com/anonymous/66e93990878ae924284497327f53df32

See mysql error when executed from console:

mysql> SELECT DISTINCT(id_field) FROM collection_field_fieldvalue WHERE type='seo' AND id_collection=4 ORDER by score desc;
ERROR 3065 (HY000): Expression #1 of ORDER BY clause is not in SELECT list, references column 'invenio.collection_field_fieldvalue.score' which is not in SELECT list; this is incompatible with DISTINCT

[tiborsimko]

Note: works with MySQL 5.6, does not work with MySQL 5.7.

We have a few more places:

$ git grep 'DISTINCT.*ORDER'
modules/webstat/lib/webstat_engine.py:    for dat in run_sql("SELECT DISTINCT(status) FROM crcILLREQUEST ORDER BY status ASC"):
modules/webstat/lib/webstat_engine.py:    for dat in run_sql("SELECT DISTINCT(loan_period) FROM crcITEM ORDER BY loan_period ASC"):
modules/websubmit/lib/websubmitadmin_dblayer.py:    q = """SELECT DISTINCT(chname) FROM sbmCHECKS ORDER BY chname ASC"""
modules/websubmit/lib/websubmitadmin_dblayer.py:    q = """SELECT DISTINCT(param) FROM sbmFUNDESC ORDER BY param ASC"""
modules/websubmit/lib/websubmitadmin_dblayer.py:    q = """SELECT DISTINCT(name) FROM sbmPARAMETERS ORDER BY name ASC"""
modules/websubmit/lib/websubmitadmin_dblayer.py:    q = """SELECT DISTINCT(name) FROM sbmFIELDDESC ORDER BY name"""

as well as other possible further multiline occurrences.

[dimaip]

@tiborsimko no, those other places have no problem: the column, by which you order is present among the select fields.

[tiborsimko]

Yeah, I meant that as a reminder to check for other possible occurrences, when fixing this one.

Thanks for checking already :smile: