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

Created on 20 Jan 2016 · 20Comments · Source: doctrine/orm

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.

Bug

Source

Devolicious

👍32

Most helpful comment

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.

mickadoo on 16 Jun 2016

👍20 👎14 ❤4 🎉4

All 20 comments

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.

mvrhov on 20 Jan 2016

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;

Devolicious on 20 Jan 2016

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.

tolry on 26 Apr 2016

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!

diegocpires on 17 May 2016

@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.

keichinger on 17 May 2016

👍4

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.

diegocpires on 18 May 2016

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.

mickadoo on 16 Jun 2016

👍20 👎14 ❤4 🎉4

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

So it will look like

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

ericlery on 9 Jul 2016

👎5 👍5

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).

apfelbox on 11 Jul 2016

👍18 😄1

Duplicate of #4846

teohhanhui on 18 Jul 2016

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

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

Boulboulouboule on 14 Nov 2016

👎7

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 on 6 Jun 2017

👍11

@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 :(

tomasfejfar on 6 Jun 2017

👍9

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.

ptlis on 4 Jul 2017

👎1

@ptlis this is currently being fixed in https://github.com/doctrine/doctrine2/pull/6143

snoek09 on 4 Jul 2017

Handled in https://github.com/doctrine/doctrine2/pull/6143

Ocramius on 22 Jul 2017

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.

fagacil on 21 Mar 2018

👍1

@fagacil just upgrade the ORM, really.

Ocramius on 27 Mar 2018

👍5