Disable ONLY_FULL_GROUP_BY mode in mysql docker container

Question

I have a big problem when I want to make a view.

CREATE VIEW AnneeBillet (ANNEE_BILLETS, CHIFFRES)
AS SELECT YEAR(FIN_RESERVATION), sum(TYPE.PRIX*NOMBRE)
FROM TYPE, BILLET
where TYPE.TYPE = BILLET.TYPE;

I have this error :

In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'parc.BILLET.FIN_RESERVATION'; this is incompatible with sql_mode=only_full_group_by

I have already tried many solutions like putting

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

But I'm with a docker container and I've seen that I had to modify directly the configuration file to have what I want.

Then, I found this post on stackoverflow but I can't do

--sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''))

in my docker-compose because I have an error like this :

mysql_1 | 2021-04-27T07:59:53.283400Z 0 [ERROR] [MY-000077] [Server] /usr/sbin/mysqld: Error while setting value '(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''))' to 'sql_mode'.

Please help me, I'm fed up with this problem.

Answer 1

If you permanently want to disable only_full_group_by on startup, you can add:

sql-mode= "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"

To your my.ini or my.cnf under '[mysqld]'.

Also if you get the error:

"Error while setting value 'STRICT_TRANS_TABLES,​NO_ZERO_IN_DATE,​NO_ZERO_DATE,​ERROR_FOR_DIVISION_BY_ZERO,​NO_ENGINE_SUBSTITUTION' to 'sql_mode'"

as I did, you can get around this issue, by first clearing all sql-modes by putting sql-mode="" above:

sql-mode= "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"

Answer 2

Just add GROUP BY YEAR(FIN_RESERVATION) to the end of your query or change it to MIN(YEAR(FIN_RESERVATION)) - you can also use max. If you didn't do these things and instead changed the mode MySQL would simply arbitrarily pick one of the year values anyway

Only full group by is a good thing