Sort in YML or Interface suddenly causes SQL error

[iamromeo]

Easy Admin version: version1.17.4
Symfony version: 3.3.9

Note: All was working OK until today in the morning (when I run composer install again and the last was yesteday)

Now when I am clicking on any sorting arrow or if I have

list: sort: ['updateDate','DESC'] (or anything else)

I get this error:

PDOException  PDOException  DriverException  Twig_Error_Runtime
HTTP 500 Internal Server Error
An exception has been thrown during the rendering of a template ("An exception occurred while executing 'SELECT DISTINCT id_0 FROM (SELECT g0_.id AS id_0, g0_.name AS name_1, g0_.description AS description_2 FROM genres g0_) dctrn_result ORDER BY name_1 DESC LIMIT 200 OFFSET 0':

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

Twig_Error_Runtime
in vendor/javiereguiluz/easyadmin-bundle/src/Resources/views/default/list.html.twig (line 131)

[javiereguiluz]

Thanks for reporting this ... but I can't reproduce it!! 😱 Using the EasyAdmin Demo application with 1.17.4 works as expected. It's strange that none of our tests have caught this bug.

If anyone else is suffering this issue, could you please create a reproducer? Thanks!

[iamromeo]

@javiereguiluz is there anything more I can provide to help out? It's interesting because it works OK on my Local but not on my production server.

[artgris]

Hello, I had same issue as @iamromeo with a 5.7 mysql version. I fixed problem by disabling ONLY_FULL_GROUP_BY in mysql mode.

[iamromeo]

I can confirm that my live server used mysql 5.7

ii  libapache2-mod-php               1:7.1+52+deb.sury.org~xenial+1             all          server-side, HTML-embedded scripting language (Apache 2 module) (default)
ii  libapache2-mod-php7.1            7.1.6-1~ubuntu16.04.1+deb.sury.org+1       amd64        server-side, HTML-embedded scripting language (Apache 2 module)
ii  mysql-client-5.7                 5.7.19-0ubuntu0.16.04.1                    amd64        MySQL database client binaries
ii  mysql-client-core-5.7            5.7.19-0ubuntu0.16.04.1                    amd64        MySQL database core client binaries
ii  mysql-common                     5.7.19-0ubuntu0.16.04.1                    all          MySQL database common files, e.g. /etc/mysql/my.cnf
ii  mysql-server                     5.7.19-0ubuntu0.16.04.1                    all          MySQL database server (metapackage depending on the latest version)
ii  mysql-server-5.7                 5.7.19-0ubuntu0.16.04.1                    amd64        MySQL database server binaries and system database setup
ii  mysql-server-core-5.7            5.7.19-0ubuntu0.16.04.1                    amd64        MySQL database server binaries
ii  php                              1:7.1+52+deb.sury.org~xenial+1             all          server-side, HTML-embedded scripting language (default)
ii  php-apcu                         5.1.8+4.0.11-1+deb.sury.org~xenial+1       amd64        APC User Cache for PHP
ii  php-common                       1:35ubuntu6                                all          Common files for PHP packages
ii  php-gd                           1:7.1+52+deb.sury.org~xenial+1             all          GD module for PHP [default]
ii  php-mysql                        1:7.1+52+deb.sury.org~xenial+1             all          MySQL module for PHP [default]
ii  php7.1                           7.1.6-1~ubuntu16.04.1+deb.sury.org+1       all          server-side, HTML-embedded scripting language (metapackage)
ii  php7.1-cli                       7.1.6-1~ubuntu16.04.1+deb.sury.org+1       amd64        command-line interpreter for the PHP scripting language
ii  php7.1-common                    7.1.6-1~ubuntu16.04.1+deb.sury.org+1       amd64        documentation, examples and common module for PHP
ii  php7.1-curl                      7.1.6-1~ubuntu16.04.1+deb.sury.org+1       amd64        CURL module for PHP
ii  php7.1-gd                        7.1.6-1~ubuntu16.04.1+deb.sury.org+1       amd64        GD module for PHP
ii  php7.1-json                      7.1.6-1~ubuntu16.04.1+deb.sury.org+1       amd64        JSON module for PHP
ii  php7.1-mbstring                  7.1.6-1~ubuntu16.04.1+deb.sury.org+1       amd64        MBSTRING module for PHP
ii  php7.1-mysql                     7.1.6-1~ubuntu16.04.1+deb.sury.org+1       amd64        MySQL module for PHP
ii  php7.1-opcache                   7.1.6-1~ubuntu16.04.1+deb.sury.org+1       amd64        Zend OpCache module for PHP
ii  php7.1-readline                  7.1.6-1~ubuntu16.04.1+deb.sury.org+1       amd64        readline module for PHP
ii  php7.1-xml                       7.1.6-1~ubuntu16.04.1+deb.sury.org+1       amd64        DOM, SimpleXML, WDDX, XML, and XSL module for PHP
ii  php7.1-zip                       7.1.6-1~ubuntu16.04.1+deb.sury.org+1       amd64        Zip module for PHP

[iamromeo]

@artgris how can I disable ONLY_FULL_GROUP_BY ?

[javiereguiluz]

@iamromeo this command should work, according to StackOverflow:

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

[artgris]

@iamromeo , @javiereguiluz command works perfectly 👍

[iamromeo]

@javiereguiluz yes command worked great - no issue at this point. Have you by any chance found why this might was an issue?

[sylvaindeloux]

Same here : happened when I add sort on a DATETIME field

[javiereguiluz]

I've found this Doctrine issue: doctrine/orm#5622 which was fixed as a bug in doctrine/orm#6143 and probably released in their 2.5.7 version https://github.com/doctrine/doctrine2/releases/tag/v2.5.7.

So, let's close this as a Doctrine bug that can be solved by upgrading the doctrine2 dependency.

[Julien-Marcou]

I actually have doctrine/orm 2.5.11 with mysql 5.7.19, I updated EasyAdminBundle from 1.17.3 to 1.17.4 (nothing else) and since that, I have the same fatal error when I'm trying to sort a list of entities :

[...]
        Question:
          class: AppBundle\Entity\Question
          templates:
              list: '::easy_admin/custom/list-sortable.html.twig'
          list:
            sortable: {entity: 'question', property: 'questionnaire'}
            sort: ['position', 'ASC']
[...]

An exception has been thrown during the rendering of a template ("An exception occurred while executing 'SELECT DISTINCT id_0 FROM (SELECT q0_.id AS id_0, q0_.obligatoire AS obligatoire_1, q0_.nb_reponse AS nb_reponse_2, q0_.visible AS visible_3, q0_.libelle AS libelle_4, q0_.affiche_separateur AS affiche_separateur_5, q0_.cle_unique AS cle_unique_6, q0_.groupe_question AS groupe_question_7, q0_.position AS position_8 FROM question q0_ WHERE q0_.questionnaire_id = 7) dctrn_result ORDER BY position_8 ASC LIMIT 15 OFFSET 0':

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

Reverting EasyAdminBundle to 1.17.3 fixed the problem.