Question:
I have MySQL error 3065.
Query Error Query: SELECT DISTINCT p. *, P.id, p.ext, p.hash, p.status FROM photos_photo p JOIN photos_album_photos ap ON p.id = ap.photo_id WHERE ap.album_id = 1 AND p.status = 1 AND p.url IS NOT NULL AND LENGTH (TRIM (p.url))> 0 AND p.parent_id = 0 ORDER BY ap.sort ASC LIMIT 500
Error: 3065 Message: Expression # 1 of ORDER BY clause is not in SELECT list, references column 'ss.ap.sort' which is not in SELECT list; this is incompatible with DISTINCT
The software developer is silent, tell me where to dig? There was a transfer from one VPS to another script of the online store. the database is imported using sypex dumper and standard mysqldump – the effect does not change, the earlier databases have the same effect.
It costs mysqld 5.7.12-0ubuntu1 . There used to be an older version. I think the problem is in MySQL.
Answer:
The solution was really in ONLY_FULL_GROUP_BY. I'll tell you how I won
mysql -u root -p
mysql> SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode, "ONLY_FULL_GROUP_BY,", ""));
mysql> SET SESSION sql_mode=(SELECT REPLACE(@@sql_mode, "ONLY_FULL_GROUP_BY,", ""));
thereby we change the value to the previous one, but with the exception of the substring "only_full_group_by,". You can check the result and make sure that the mode is turned off with the command:
mysql> SELECT @@sql_mode;