Disable ONLY_FULL_GROUP_BY mode in mysql docker container

user_15573066 0
Viewed 5060

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.

docker-compose docker mysql
3 Answers

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

user_14106640

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

You can't use an sql expression as the value of --sql_mode. Run SET SESSION sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY','')); SELECT @@sql_mode; in your server and use the value it shows with --sql_mode.

ysth_173890
Related