1

I have an AJAX GET-request which should return to me the list of categories but I have got an SQL error SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column

My request URl is next: http://my-domain.com/admin/api/categories?flat=true&sortBy=depth&sortOrder=asc&fields=name,id

What is the best way to solve this problem? Should I change mysql .cnf file or it's can be fixed in the code?

Pawel Novikov
  • 465
  • 1
  • 11
  • 26
  • The general GROUP BY rule says: "If a GROUP BY clause is specified, each column reference in the SELECT list must either identify a grouping column or be the argument of a set function." – jarlh Oct 06 '17 at 08:57
  • yes, but sql-query is generated in vendor, so I can't change vendors code. That's why I'm asking what the best solution of this problem in Sulu – Pawel Novikov Oct 06 '17 at 09:00
  • 1
    MySQL has a compatibility mode to allow those invalid GROUP BY's. Perhaps you can set it? – jarlh Oct 06 '17 at 09:04

1 Answers1

1

OK, the problem was in MySQL compatibility.

So, I just change mysql.cnf file by adding following lines: [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

Pawel Novikov
  • 465
  • 1
  • 11
  • 26