MySQL 5.7 - DISTINCT Pagination Query and GROUP BY

[abssimon]

As soon as I use Group By, I get an error

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of
SELECT list is not in GROUP BY clause and contains nonaggregated column
'symfony.s0_.id' which is not functionally dependent on columns in
GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

I have installed

doctrine/annotations                v1.6.1  Docblock Annotations Parser
doctrine/cache                      v1.8.0  Caching library offering an obj...
doctrine/collections                v1.6.1  PHP Doctrine Collections librar...
doctrine/common                     v2.10.0 PHP Doctrine Common project is ...
doctrine/data-fixtures              v1.3.1  Data Fixtures for all Doctrine ...
doctrine/dbal                       v2.9.2  Powerful PHP database abstracti...
doctrine/doctrine-bundle            1.10.2  Symfony DoctrineBundle
doctrine/doctrine-cache-bundle      1.3.5   Symfony Bundle for Doctrine Cache
doctrine/doctrine-fixtures-bundle   3.1.0   Symfony DoctrineFixturesBundle
doctrine/doctrine-migrations-bundle v2.0.0  Symfony DoctrineMigrationsBundle
doctrine/event-manager              v1.0.0  Doctrine Event Manager component
doctrine/inflector                  v1.3.0  Common String Manipulations wit...
doctrine/instantiator               1.2.0   A small, lightweight utility to...
doctrine/lexer                      v1.0.1  Base library for a lexer that c...
doctrine/migrations                 v2.0.0  PHP Doctrine Migrations project...
doctrine/orm                        v2.6.3  Object-Relational-Mapper for PHP
doctrine/persistence                v1.1.0  The Doctrine Persistence projec...
doctrine/reflection                 v1.0.0  Doctrine Reflection component

and

knplabs/knp-components              v1.3.10 Knplabs component library
knplabs/knp-paginator-bundle        v3.0.0  Paginator bundle for Symfony to...

The full error message is

An exception occurred while executing 'SELECT DISTINCT s0_.id AS id_0 FROM stock s0_ LEFT JOIN storage_fee s1_ ON s0_.storage_fee_id = s1_.id LEFT JOIN stock_meta_data s2_ ON s0_.stock_meta_data_id = s2_.id GROUP BY s2_.master_sku LIMIT 20':

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'symfony.s0_.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Is there a work around for "SELECT DISTINCT s0_.id AS id_0" when using group by?

Thank you & Best Regards

[abssimon]

I think I have solved this problem using paginator with, distinct = false and and wrap-queries = true

$pagination = $paginator->paginate($query, $page, 50, ['distinct' => false, 'wrap-queries' => true]);

But I am not quite sure. In the config there is a note, that distinct is useful, especially with Group by

distinct: true # ensure distinct results, useful when ORM queries are using GROUP BY statements

I would be grateful for any help.

Best Regards

[nicolasmure]

Hello,

You can configure your mysql server to disable the ONLY_FULL_GROUP_BY sql mode.
This can be done at runtime, log into your mysql server with a suer having sufficient rights and run

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

Also, you can set this change in your /etc/mysql/my.cnf config file (or any other config file in conf.d) :

[mysqld]
# ... any other opts
sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

[spolischook]

got this bug on mysql but on MariaDb ver 10.1.38 it works well

[Amunak]

And can we, uh, maybe get a proper fix anyway?

[garak]

@Amunak feel free to open a Pull Request

[gmormorromrom]

I have an example configure .env
DATABASE_URL=mysql://login:[email protected]:3306/exampledb?serverVersion=mariadb-10.4.14