I have a problem with MySQL and Django on Docker: "sql_mode=only_full_group_by"
"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 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.