I have a problem with MySQL and Django on Docker: "sql_mode=only_full_group_by"

Question

"Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'database_name.i.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by"

Hi, sorry for me English. I have a problem with MySQL and Django when I dockerize my project. I'm new with this technologies. This is part of my docker-compose file:

db_mysql:
    image: mysql:8
    container_name: db_mysql
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: password
      MYSQL_DATABASE: database_name
      MYSQL_USER: user
      MYSQL_PASSWORD: password
    ports:
      - '3306:3306'
    expose:
      - '3306'
    volumes:
      - dbdata:/var/lib/mysql

The problem appears when I deploy my project and enter a section. The message I showed initially appears.

I tried to apply what is mentioned in this post, but the problem persists. SELECT list is not in GROUP BY clause and contains nonaggregated column .... incompatible with sql_mode=only_full_group_by

I did the following: I entered the container of my db:

docker exec -it db_mysql bash

I logged in to MySQL with the root user:

mysql -u root -p

I placed the command to change the value of SQL_MODE:

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

I checked that the change has been made:

SELECT @@sql_mode;

I exited the container and restarted it with:

docker restart db_mysql

When I go back to the project, the problem persists.

The post says that I must restart MySQL for the changes to be preserved. But when I try to run restart from the container, it tells me that the service command does not exist:

service mysqld restart

You must fix your incorrect query instead of editing the SQL Mode setting. — Akina, Oct 16 '22 at 18:14
But I deploy the same project without docker and with MySQL in XAMPP and everything works fine. — Alonso, Oct 16 '22 at 19:04
This means that your XAMPP uses old (5.x) version of MySQL whereas in Docker container you have MySQL of version 8+. Of course you may change SQL Mode (see Bill's answer) but I think that making the queries correct is the only safe/correct solution. — Akina, Oct 16 '22 at 19:26

score 1 · Answer 1 · answered Oct 16 '22 at 19:07

You have read incorrect advice. Restarting the mysqld service resets any changes you have made with SET GLOBAL. The configuration reverts to the defaults.

If you want to set global variables automatically when the docker container starts, you can do one of the following:

Pass options as arguments to the command for the container
Set options in a configuration file, and reference that file in your container

Read https://ahelpme.com/software/docker/edit-mysql-options-in-docker-or-docker-compose-mysql/ for a guide on how to do both of these methods.

That said, I agree with the comment above that you should not disable ONLY_FULL_GROUP_BY. It's important to prevent invalid queries.

I have a problem with MySQL and Django on Docker: "sql_mode=only_full_group_by"

1 Answers1