Docker MySQL8 - aggregated query without GROUP BY

Question

I am using Docker to generate a MySQL 8 container, as well PHP 7 and phpMyAdmin.

I was having an issue with phpMyAdmin when running a query with aggregated functions. I thought maybe there's something wrong with phpMyAdmin.

So I took it to another database application, called Navicat. I created a successful connection to the Docker MySQL 8 container, and when I attempted to run the same query, I got the same error. As follows:

In aggregated query without GROUP BY, expression #2 of SELECT list contains nonaggregated column 'myDb.mytable.uid'; 
this is incompatible with sql_mode=only_full_group_by

This leads me to believe that the issue has to be within my Docker setup. Here is the docker-compose.yml file:

version: "3.7"
services:
  www:
    build: .
    ports:
     - "8001:80"
    volumes:
     - ./www:/var/www/html/
    links:
     - db
    networks:
     - default
  db:
    image: mysql:8.0
    command: --default-authentication-plugin=mysql_native_password
    ports:
     - "3306:3306"
    environment:
     MYSQL_DATABASE: myDb
     MYSQL_USER: user
     MYSQL_PASSWORD: test
     MYSQL_ROOT_PASSWORD: test
    volumes:
     - ./dump:/docker-entrypoint-initdb.d
    networks:
     - default
  phpmyadmin:
    image: phpmyadmin/phpmyadmin
    links:
     - db:db
    ports:
     - 8000:80
    environment:
     MYSQL_USER: user
     MYSQL_PASSWORD: test
     MYSQL_ROOT_PASSWORD: test
volumes:
 mysql-data:
 persistent:

Here is my Dockerfile:

FROM php:7.0.30-apache
RUN docker-php-ext-install pdo pdo_mysql

I have seen several posts that tell me to update the sql_mode, using the following command:

SET sql_mode = ''

But zero results are returned, and I still get the same aggregated error.

I attempted this query:

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

But I only get the following results:

Access denied; you need (at least one of) the SUPER or SYSTEM_VARIABLES_ADMIN privilege(s) for this operation

It has to be something I can do in my docker-compose.yml file that will allow me to run the query without getting the aggregated error.

If not, how can I successfully run a query with an aggregated function without error?

Sources I have attempted:

Disable ONLY_FULL_GROUP_BY

SELECT list is not in GROUP BY clause and contains nonaggregated column .... incompatible with sql_mode=only_full_group_by

Edit

Here is a simple query I just tested that produced the error in question:

SELECT
  uid,
  COUNT(pol)
FROM
  myTable

Answer 1

SELECT
  uid,
  COUNT(pol)
FROM
  myTable

This query is returning a single uid, at random (more precisely, indeterminately), along with the number of non-null pol values in the entire table (so, 1 row result). It is giving you the warning because the uid can be different each and every time you run the query. (Even though if you run it repeatedly it may look like the same value, it could change at any time.)

Likely, you want something like this:

SELECT
  uid,
  COUNT(pol)
FROM
  myTable
GROUP BY uid

This will return one row for each uid, and the number of non-null pol values for that uid.

Your original query is not well defined (different results for the same query and data set), which is why you are seeing the error, which exists in the first place to protect you. The fact that a different server doesn't give the error is likely a result of a different sql_mode or a different MySQL version.