[Sguerso]

Hi all,

TL;DR if you upgraded your mysql to newer version and you're having issues with actors "link" you need to disable "only_full_group_by" option from your mysql sql_mode.

I started having issues on my Kodi environment after upgrading the mysql vm to ubuntu 16.04, long story short clicking on actors name in movies information didn't let me see anymore the movies list with that actor, returing just a "no results" error.

Looking at queries executed on mysql server i found that the "click" on the gui run:

Code:

select * from movie_view LEFT JOIN actor_link ON actor_link.media_id=movie_view.idMovie AND actor_link.media_type='movie' LEFT JOIN actor a ON a.actor_id=actor_link.actor_id LEFT JOIN director_link ON director_link.media_id=movie_view.idMovie AND director_link.media_type='movie' LEFT JOIN actor d ON d.actor_id=director_link.actor_id WHERE a.name='Russell Crowe' OR d.name='Russell Crowe' GROUP BY movie_view.idMovie

Executing that query directly on the server returned this error:

Code:

RROR 1055 (42000): Expression #34 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'movie_view.resumeTimeInSeconds' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

I found that the option "only_full_group_by" is enabled by default starting from mysql 5.7.5.

Disabling that option solved the issue, you can try it "live" without changing your configuration doing:

Code:

mysql -u <user> -p
SELECT @@sql_mode;
#will return some configuration including ONLY_FULL_GROUP_BY

than you can easily copy the list of options removing the one we don't want anymore and paste it in:

Code:

SET sql_mode = '<list of options>';

If everything in your environment works you can change the configuration permanently using this guide.

I'd like to thanks everyone from these threads: mysql logging and disable only_full_group_by.


Regards,

S