Mysql 5.7 requires order by clause in select DISTINCT statement (Paginator)

[Devolicious]

Hi,

There seems to be an issue with the Paginator. In mysql 5.7 it is required to have the order by fields to be present in the select statement when using DISTINCT. I have situated where the problem at:
https://github.com/doctrine/doctrine2/blob/master/lib/Doctrine/ORM/Tools/Pagination/LimitSubqueryOutputWalker.php#L276

By preserving the order by clause as described in issue #2630 it now fails in mysql 5.7. I tested by simply adding the ordered fields to the select statement and everything worked again.

[mvrhov]

This just means, that the mysql 5.7 s now working the same as other databases. IMO, the best fix would be to always preserve the order by.

[Devolicious]

Yes, the preserve should stay. Here is an example of what happens.

SELECT DISTINCT id FROM (subquery) dctrn_result ORDER BY last_name DESC;

This select fails in mysql 5.7 but when adding the last_name to the select statement it runs without any problems

Working example:

SELECT DISTINCT id, last_name FROM (subquery) dctrn_result ORDER BY last_name DESC;

[tolry]

Having the same issue after upgrading to Ubuntu 16.04. Ordering by anything other than the id, will produce this error, when using the paginator.

[diegocpires]

I changed the code to works for me.

What i changed?
https://github.com/doctrine/doctrine2/blob/master/lib/Doctrine/ORM/Tools/Pagination/LimitSubqueryOutputWalker.php#L377

I added this line
$sqlIdentifier += $this->refactorOrderForSelect($orderBy);

And i added this method

private function refactorOrderForSelect($orderBy)
{
    // Removing ASC or DESC
    foreach($orderBy as $order) {
        $order = preg_replace(array("/( ASC){0,1}/","/( DESC){0,1}/"), array("",""), $order);
        $orderSelect[] = $order;
    }
    return $orderSelect;
}

Doing this the fields in order by clause are added to select statement.

I know its a poor code. So i did not create a PR.
But, its a way to close this issue!

[keichinger]

@diego-mazzaro I'd call that a hack rather than a solution to close this issue.

It may work for you but it doesn't fix the underlying issue(s) that may cause more problems in the future. Fixing problems with hacks will lead to more hacks and unmaintainable code, which nobody likes since ORMs can be a huge PITA anyway.

[diegocpires]

Yes @cH40z-Lord, i agree... I Just post this hack because I need my system works as it is not released a new version of Doctrine ...
If it is useless , forgiveness. I just tried to help.

[mickadoo]

I found a similar problem reported here and @jradwan's solution of adding

[mysqld]
sql-mode=""

to /etc/mysql/my.cnf fixed the

"ORDER BY clause is not in SELECT list, references column 'dctrn_result.title_1' which is not in SELECT list; this is incompatible with DISTINCT"

error for me.

[ericlery]

On /etc/mysql/my.cnf, in the mysqld section, add sql-mode = ""

So it will look like

[mysqld]
...
sql-mode = ""
...

[apfelbox]

As this obviously is an issue with invalid generated SQL, I think a bugfix is needed? (see #5930 for an example of an invalid select statement)

Relaxing all SQL modes in MySQL so that even (in MySQL 5.7+) invalid SQL works without errors is no solution imho (not even temporarily).

Edit: @mickadoo @ericlery instead of disabling all SQL modes, you probably only should disable the mode that is causing the problem, called ONLY_FULL_GROUP_BY. Furthermore I wouldn't disable it for the whole server, but just for the connections that actually need this.
You can do it on startup in symfony using the postConnect event from doctrine (see http://stackoverflow.com/questions/23921117/disable-only-full-group-by/37508414#37508414 for the query to do it).

[teohhanhui]

Duplicate of #4846

[Boulboulouboule]

Thanks ! This worked for me :
[mysqld] sql-mode=""

This is a hack, but will be sufficient waiting for the PR

[tomasfejfar]

Please be aware, that by setting [mysqld] sql-mode="" you're disabling safe-mode.

That means you're opening up to multitude of issues. To name a few - invalid dates, zero dates, NULL on division by zero. See the full list of available SQL modes for more information.

Ideally only disable the one offending feature as @apfelbox mentioned above.

[tomasfejfar]

@Devolicious could you update your first message with the warning message above or link to it? People seem to be blindly using the workaround without knowing the consequences :/ Not many people will scroll down here :(

[ptlis]

Is there any intention to fix this issue? mySQL 5.7 has been out for > 2.5 years!

The (awful) solution I'm using can be found in this gist. I tried a number of permutations of SQL modes to try to keep some data-integrity guarantees but in the end gave up - be warned that if you use the above solution you've significantly weakened mySQL's already weak protections against garbage data.

[snoek09]

@ptlis this is currently being fixed in #6143

[Ocramius]

Handled in #6143

[fagacil]

Any example please to disable after postConnect in doctrine 2.5?

    /**
     * Triggers after connection to Database
     *
     * @param ConnectionEventArgs $args
     * @return \Doctrine\DBAL\Connection
     */
    public function postConnect(ConnectionEventArgs $args)
    {
        $connectionParams = $args->getConnection()->getParams();
        $connectionParams['driverOptions'] = [
            PDO::MYSQL_ATTR_INIT_COMMAND => "SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''))"
        ];
        $evm = new EventManager();
        return DriverManager::getConnection($connectionParams, null, $evm);

This still throws

Caused by
PDOException: SQLSTATE[HY000]: General error: 3065 Expression #1 of ORDER BY clause is
 not in SELECT list, references column 'dctrn_result.created_at_11' which is not in SELECT list; 
this is incompatible with DISTINCT

And this also does not helped

    /**
     * Triggers after connection to Database
     *
     * @param ConnectionEventArgs $args
     * @return \Doctrine\DBAL\Connection
     */
    public function postConnect(ConnectionEventArgs $args)
    {
        $connectionParams = $args->getConnection()->getParams();
        $connectionParams['driverOptions'] = [
            PDO::MYSQL_ATTR_INIT_COMMAND => "SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''))",
            PDO::MYSQL_ATTR_INIT_COMMAND => "SET session.sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''))",
            PDO::MYSQL_ATTR_INIT_COMMAND => "SET global.sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''))",
            PDO::MYSQL_ATTR_INIT_COMMAND => "SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''))",
            PDO::MYSQL_ATTR_INIT_COMMAND => "SET SESSION sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''))",
        ];
        $driver = new Doctrine\DBAL\Driver\PDOMySql\Driver;
        return new Doctrine\DBAL\Connection($connectionParams, $driver);

and this...

mysql.conf

[mysqld]
user=mysql
default-storage-engine = INNODB
sql-mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

Our query

 $queryBuilder = $this->entityManager
                ->createQueryBuilder()
                ->select('a')
                ->addOrderBy('a.createAt', 'ASC')
                ->from(User::class, 'a');
   return $this->paginate($queryBuilder->getQuery(), $pagination->limit(), $pagination->page());

Also tried with DQL,

 $queryBuilder = $this->createQueryBuilder()
                ->select('a')
                ->addOrderBy('a.createAt', 'ASC')
                 ->setLimit(5) // smth like that
                  ->setPage(1)
                ->from(User::class, 'a');
 return new Doctrine\ORM\Tools\Pagination\Paginator($queryBuilder, true);

Spent about 6 hours already. Using php 7.0, doctrine/dbal 2.5, docker image.

Anyone clear solution?

this not helped too, maybe coz docker

[mysqld]
...
sql-mode = ""
...

also trying to fix overhere

https://github.com/doctrine/doctrine2/blob/master/lib/Doctrine/ORM/Tools/Pagination/LimitSubqueryOutputWalker.php#L377

by this code

private function refactorOrderForSelect($orderBy)
{
    // Removing ASC or DESC
    foreach($orderBy as $order) {
        $order = preg_replace(array("/( ASC){0,1}/","/( DESC){0,1}/"), array("",""), $order);
        $orderSelect[] = $order;
    }
    return $orderSelect;
}

does not help

running this in docker mysql container does not helped

  mysql> set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
    mysql> set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
    mysql> exit;

same to
SET sql_mode = ''

removing line of addOrderBy helped, but we have to sort it in passed order, like some variable $fieldName and $sortDirection

Still got this cool error

Caused by
PDOException: SQLSTATE[HY000]: General error: 3065 Expression #1 of ORDER BY clause is not in SELECT list, references column 'dctrn_result.created_at_11' which is not in SELECT list; this is incompatible with DISTINCT

also tried to add in /etc/mysql/my.conf

.....
#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

[mysqld]
sql-mode=""

not helped

Holly crap .

  database:
    image: mysql:5.7
    env_file:
      - ./stack/environment/development/database.env
    ports:
     - 8307:3306
    command: mysqld --sql_mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
    volumes:
     - data:/var/lib/mysql
     #- ./stack/mysql/config/my.cnf:/etc/mysql/my.cnf

Solved the issue, because container

To see what values a running MySQL server is using, type
'mysqladmin variables' instead of 'mysqld --verbose --help'.
root@594de3cb146d:/etc/mysql# mysqld --verbose --help

mysqld: [Warning] World-writable config file '/etc/mysql/my.cnf' is ignored. <--- NOTE THAT IGNORED

mysqld  Ver 5.7.21 for Linux on x86_64 (MySQL Community Server (GPL))
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Because of that, container configs has

.....
sporadic-binlog-dump-fail                                    FALSE
sql-mode                                                     ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
ssl                                                          TRUE
ssl-ca                                                       (No default value)
.....

Spent about 10 hours to fix one line of that . We do not know why it is ignored. If anyone has ideas, we would be glad to hear.

[Ocramius]

@fagacil just upgrade the ORM, really.

[zzxCNCZ]

Any example please to disable after postConnect in doctrine 2.5?

    /**
     * Triggers after connection to Database
     *
     * @param ConnectionEventArgs $args
     * @return \Doctrine\DBAL\Connection
     */
    public function postConnect(ConnectionEventArgs $args)
    {
        $connectionParams = $args->getConnection()->getParams();
        $connectionParams['driverOptions'] = [
            PDO::MYSQL_ATTR_INIT_COMMAND => "SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''))"
        ];
        $evm = new EventManager();
        return DriverManager::getConnection($connectionParams, null, $evm);

This still throws

Caused by
PDOException: SQLSTATE[HY000]: General error: 3065 Expression #1 of ORDER BY clause is
 not in SELECT list, references column 'dctrn_result.created_at_11' which is not in SELECT list; 
this is incompatible with DISTINCT

And this also does not helped

    /**
     * Triggers after connection to Database
     *
     * @param ConnectionEventArgs $args
     * @return \Doctrine\DBAL\Connection
     */
    public function postConnect(ConnectionEventArgs $args)
    {
        $connectionParams = $args->getConnection()->getParams();
        $connectionParams['driverOptions'] = [
            PDO::MYSQL_ATTR_INIT_COMMAND => "SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''))",
            PDO::MYSQL_ATTR_INIT_COMMAND => "SET session.sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''))",
            PDO::MYSQL_ATTR_INIT_COMMAND => "SET global.sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''))",
            PDO::MYSQL_ATTR_INIT_COMMAND => "SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''))",
            PDO::MYSQL_ATTR_INIT_COMMAND => "SET SESSION sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''))",
        ];
        $driver = new Doctrine\DBAL\Driver\PDOMySql\Driver;
        return new Doctrine\DBAL\Connection($connectionParams, $driver);

and this...

mysql.conf

[mysqld]
user=mysql
default-storage-engine = INNODB
sql-mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

Our query

 $queryBuilder = $this->entityManager
                ->createQueryBuilder()
                ->select('a')
                ->addOrderBy('a.createAt', 'ASC')
                ->from(User::class, 'a');
   return $this->paginate($queryBuilder->getQuery(), $pagination->limit(), $pagination->page());

Also tried with DQL,

 $queryBuilder = $this->createQueryBuilder()
                ->select('a')
                ->addOrderBy('a.createAt', 'ASC')
                 ->setLimit(5) // smth like that
                  ->setPage(1)
                ->from(User::class, 'a');
 return new Doctrine\ORM\Tools\Pagination\Paginator($queryBuilder, true);

Spent about 6 hours already. Using php 7.0, doctrine/dbal 2.5, docker image.

Anyone clear solution?

this not helped too, maybe coz docker

[mysqld]
...
sql-mode = ""
...

also trying to fix overhere

https://github.com/doctrine/doctrine2/blob/master/lib/Doctrine/ORM/Tools/Pagination/LimitSubqueryOutputWalker.php#L377

by this code

private function refactorOrderForSelect($orderBy)
{
    // Removing ASC or DESC
    foreach($orderBy as $order) {
        $order = preg_replace(array("/( ASC){0,1}/","/( DESC){0,1}/"), array("",""), $order);
        $orderSelect[] = $order;
    }
    return $orderSelect;
}

does not help

running this in docker mysql container does not helped

  mysql> set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
    mysql> set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
    mysql> exit;

same to
SET sql_mode = ''

removing line of addOrderBy helped, but we have to sort it in passed order, like some variable $fieldName and $sortDirection

Still got this cool error

Caused by
PDOException: SQLSTATE[HY000]: General error: 3065 Expression #1 of ORDER BY clause is not in SELECT list, references column 'dctrn_result.created_at_11' which is not in SELECT list; this is incompatible with DISTINCT

also tried to add in /etc/mysql/my.conf

.....
#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

[mysqld]
sql-mode=""

not helped

Holly crap .

  database:
    image: mysql:5.7
    env_file:
      - ./stack/environment/development/database.env
    ports:
     - 8307:3306
    command: mysqld --sql_mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
    volumes:
     - data:/var/lib/mysql
     #- ./stack/mysql/config/my.cnf:/etc/mysql/my.cnf

Solved the issue, because container

To see what values a running MySQL server is using, type
'mysqladmin variables' instead of 'mysqld --verbose --help'.
root@594de3cb146d:/etc/mysql# mysqld --verbose --help

mysqld: [Warning] World-writable config file '/etc/mysql/my.cnf' is ignored. <--- NOTE THAT IGNORED

mysqld  Ver 5.7.21 for Linux on x86_64 (MySQL Community Server (GPL))
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Because of that, container configs has

.....
sporadic-binlog-dump-fail                                    FALSE
sql-mode                                                     ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
ssl                                                          TRUE
ssl-ca                                                       (No default value)
.....

Spent about 10 hours to fix one line of that . We do not know why it is ignored. If anyone has ideas, we would be glad to hear.

thank you very much. i got the same problem too when i use mysql 8 by docker-compose. it won't work if i set the sql_mode by edit my.cnf. it should set the parm in docker-compose yml file before build container.

[teohhanhui]

#5622 (comment)