SQL error loading main page

[marcuscps]

Q	A
Bug report?	yes
Feature request?	no
Support request?	yes

I followed the Installation guide for Linux, and everything worked fine, until I tried to access the main URL.
I get the following SQL error:

An exception occurred while executing 'SELECT DISTINCT id_1 FROM (SELECT '' AS sclr_0, l0_.id AS id_1, l0_.price AS price_2, l0_.certified AS certified_3, l0_.average_rating AS average_rating_4, l0_.createdAt AS createdAt_5, l1_.title AS title_6, l1_.description AS description_7, l1_.slug AS slug_8, l1_.locale AS locale_9, l1_.id AS id_10, l2_.id AS id_11, l3_.id AS id_12, l3_.lft AS lft_13, l3_.lvl AS lvl_14, l3_.rgt AS rgt_15, l3_.root AS root_16, l4_.name AS name_17, l4_.locale AS locale_18, l4_.id AS id_19, l5_.id AS id_20, l5_.name AS name_21, u6_.id AS id_22, u6_.first_name AS first_name_23, u7_.id AS id_24, u7_.name AS name_25, l8_.id AS id_26, l8_.country AS country_27, l8_.city AS city_28, l8_.route AS route_29, g9_.id AS id_30, g9_.lat AS lat_31, g9_.lng AS lng_32, l4_.locale AS locale_33, u7_.position AS position_34, l1_.locale AS locale_35, l0_.status AS status_36, l0_.createdAt AS createdAt_37 FROM listing l0_ LEFT JOIN listing_translation l1_ ON l0_.id = l1_.translatable_id LEFT JOIN listing_listing_category l2_ ON l0_.id = l2_.listing_id LEFT JOIN listing_category l3_ ON l2_.listing_category_id = l3_.id LEFT JOIN listing_category_translation l4_ ON l3_.id = l4_.translatable_id AND (l4_.locale = ?) LEFT JOIN listing_image l5_ ON l0_.id = l5_.listing_id LEFT JOIN `user` u6_ ON l0_.user_id = u6_.id LEFT JOIN user_image u7_ ON u6_.id = u7_.user_id AND (u7_.position = 1) LEFT JOIN listing_location l8_ ON l0_.location_id = l8_.id LEFT JOIN geo_coordinate g9_ ON l8_.coordinate_id = g9_.id WHERE l1_.locale = ? AND l0_.status = ? ORDER BY l5_.position asc, u7_.position asc) dctrn_result ORDER BY createdAt_5 DESC LIMIT 6' with params ["en", "en", 2]:

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

AFAIK, this SQL statement is indeed incorrect.
MySQL version: mysql Ver 14.14 Distrib 5.7.20, for Linux (x86_64)

Thanks

[cocolabssas]

It seems to be relative to a doctrine bug doctrine/orm#4846.

A not tested solution until doctrine update would be to disable mysql ONLY_FULL_GROUP_BY mode.

Here a method:

Get a comma-separated list of the mysql modes enabled: SELECT @@sql_mode

Then set sql-mode to this list without the ONLY_FULL_GROUP_BY option by editing the /etc/mysql/mysql.cnf:

[mysqld]
sql-mode="[comma-separated list of modes enabled without ONLY_FULL_GROUP_BY]"

Then restart mysql:

sudo service mysql restart

[marcuscps]

Thanks for the answer, @cocolabssas .
Any plans for a doctrine update? This solution doesn't sound ideal.

[cocolabssas]

@marcuscps i updated my previous answer to be more specific.

[marcuscps]

Awesome! :) I appreciate it @cocolabssas

[cocolabssas]

Thanks :)

[websemantics]

This seems to fix the issue,

• Change doctrine/orm from "^2.5" to "^2.6" in composer.json
• Run composer upgrade seem to have fixed the issue