adam/Piwigo
1
0 Fork 0
mirror of https://github.com/Piwigo/Piwigo.git synced 2026-10-31 06:02:00 +01:00
Code Issues 657 Packages Projects Releases Wiki Activity

[MySQL 5.7] ORDER BY clause is not in SELECT list #369

New Issue
Closed
opened 2026-10-04 23:54:52 +02:00 by adam · 28 comments
adam commented 2026-10-04 23:54:52 +02:00
Owner
Copy link
Copy source

I get an error 3065 regardless of which ordering option I select in configuration. I narrowed it down to:

mysql> SELECT DISTINCT(image_id)
-> FROM piwigo_image_category
-> INNER JOIN piwigo_images ON id = image_id
-> WHERE
-> category_id = 6178
->
-> ORDER BY piwigo_images.file ASC;
ERROR 3065 (HY000): Expression #1 of ORDER BY clause is not in SELECT list, references column 'piwigo.piwigo_images.file' which is not in SELECT list; this is incompatible with DISTINCT

The above is from mysql - the browser reports a similar error. To get past this, I changed section_init.inc.php and added piwigo_images.file to the select line to force it for my site.

SELECT DISTINCT(image_id),piwigo_images.file
FROM '.IMAGE_CATEGORY_TABLE.'
INNER JOIN '.IMAGES_TABLE.' ON id = image_id
WHERE
'.$where_sql.'
'.$forbidden.'
'.$conf['order_by'].'
;';

Server version: 5.7.9 MySQL Community Server (GPL)
Piwigo 2.7.4

I get an error 3065 regardless of which ordering option I select in configuration. I narrowed it down to: mysql> SELECT DISTINCT(image_id) -> FROM piwigo_image_category -> INNER JOIN piwigo_images ON id = image_id -> WHERE -> category_id = 6178 -> -> ORDER BY piwigo_images.file ASC; ERROR 3065 (HY000): Expression #1 of ORDER BY clause is not in SELECT list, references column 'piwigo.piwigo_images.file' which is not in SELECT list; this is incompatible with DISTINCT The above is from mysql - the browser reports a similar error. To get past this, I changed section_init.inc.php and added piwigo_images.file to the select line to force it for my site. SELECT DISTINCT(image_id),piwigo_images.file FROM '.IMAGE_CATEGORY_TABLE.' INNER JOIN '.IMAGES_TABLE.' ON id = image_id WHERE '.$where_sql.' '.$forbidden.' '.$conf['order_by'].' ;'; Server version: 5.7.9 MySQL Community Server (GPL) Piwigo 2.7.4 <!-- github-sync-id:119505159 -->
adam closed this issue 2026-10-04 23:54:52 +02:00
adam commented 2026-10-04 23:54:53 +02:00
Author
Owner
Copy link
Copy source

Hi @rp1428

so MySQL 5.7 has added such a constraint... (I think PostrgreSQL already had it). We will have to manage it seriously!

As you can see on the SQL query you have found, the order_by is a variable, so we will have to select rows as a variable...

Hi @rp1428 so MySQL 5.7 has added such a constraint... (I think PostrgreSQL already had it). We will have to manage it seriously! As you can see on the SQL query you have found, the order_by is a variable, so we will have to select rows as a variable... <!-- github-comment-id:160657983 -->
adam commented 2026-10-04 23:54:53 +02:00
Author
Owner
Copy link
Copy source

much simpler solution : do not query with DISTINCT and remove duplicates in PHP

much simpler solution : do not query with DISTINCT and remove duplicates in PHP <!-- github-comment-id:160658716 -->
adam commented 2026-10-04 23:54:54 +02:00
Author
Owner
Copy link
Copy source

@mistic100 of course, much simpler. And I doubt it would make things much slower (fetching some ids should be fast)

@mistic100 of course, much simpler. And I doubt it would make things much slower (fetching some ids should be fast) <!-- github-comment-id:160660026 -->
adam commented 2026-10-04 23:54:55 +02:00
Author
Owner
Copy link
Copy source

I think for large galleries it will definitely be slower (I'm thinking of
flat view for example).

And I'm definitely sure there are many other places...

Do you know if this is going to be the future mysql standard or just some
kind of strict sql option?
On Nov 30, 2015 4:24 PM, "Pierrick Le Gall" notifications@github.com
wrote:

@mistic100 https://github.com/mistic100 of course, much simpler. And I
doubt it would make things much slower (fetching some ids should be fast)

—
Reply to this email directly or view it on GitHub
https://github.com/Piwigo/Piwigo/issues/376#issuecomment-160660026.

I think for large galleries it will definitely be slower (I'm thinking of flat view for example). And I'm definitely sure there are many other places... Do you know if this is going to be the future mysql standard or just some kind of strict sql option? On Nov 30, 2015 4:24 PM, "Pierrick Le Gall" notifications@github.com wrote: > @mistic100 https://github.com/mistic100 of course, much simpler. And I > doubt it would make things much slower (fetching some ids should be fast) > > — > Reply to this email directly or view it on GitHub > https://github.com/Piwigo/Piwigo/issues/376#issuecomment-160660026. <!-- github-comment-id:160664103 -->
adam commented 2026-10-04 23:54:56 +02:00
Author
Owner
Copy link
Copy source

Researching, I came across:

As of MySQL 5.7.5, the default SQL mode includes ONLY_FULL_GROUP_BY. (Before 5.7.5, MySQL does not detect functional dependency and ONLY_FULL_GROUP_BY is not enabled by default. For a description of pre-5.7.5 behavior, see the MySQL 5.6 Reference Manual.)

And my database has:
mysql> SELECT @@global.sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@global.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 |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

So perhaps this is the culprit, the default is ONLY_FULL_GROUP_BY. I can try changing this tonight to see if it removes the issue.

Researching, I came across: As of MySQL 5.7.5, the default SQL mode includes ONLY_FULL_GROUP_BY. (Before 5.7.5, MySQL does not detect functional dependency and ONLY_FULL_GROUP_BY is not enabled by default. For a description of pre-5.7.5 behavior, see the MySQL 5.6 Reference Manual.) And my database has: mysql> SELECT @@global.sql_mode; +-------------------------------------------------------------------------------------------------------------------------------------------+ | @@global.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 | +-------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) So perhaps this is the culprit, the default is ONLY_FULL_GROUP_BY. I can try changing this tonight to see if it removes the issue. <!-- github-comment-id:160674739 -->
adam commented 2026-10-04 23:54:56 +02:00
Author
Owner
Copy link
Copy source

It makes me think of http://piwigo.org/bugs/view.php?id=3205 which shows that MySQL 5.7 has new default settings, with more restrictions. But I doubt hosting providers will keep these default settings, or else many web applications will be broken.

It makes me think of http://piwigo.org/bugs/view.php?id=3205 which shows that MySQL 5.7 has new default settings, with more restrictions. But I doubt hosting providers will keep these default settings, or else many web applications will be broken. <!-- github-comment-id:160701006 -->
adam commented 2026-10-04 23:54:57 +02:00
Author
Owner
Copy link
Copy source

I set sql-mode="" in /etc/my.cnf and that resolved the issue for me.

This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus

I set sql-mode="" in /etc/my.cnf and that resolved the issue for me. --- This email has been checked for viruses by Avast antivirus software. https://www.avast.com/antivirus <!-- github-comment-id:160817041 -->
adam commented 2026-10-04 23:54:58 +02:00
Author
Owner
Copy link
Copy source

I just ran into this after upgrading my server today to Ubuntu 16.04, which came with MySQL 5.7.11-0ubuntu6. When I try to browse my albums I get:

Warning:  [mysql error 3065] Expression #1 of ORDER BY clause is not in SELECT list, references column 'piwigo.images.date_creation' which is not in SELECT list; this is incompatible with DISTINCT

SELECT DISTINCT(image_id)
  FROM image_category
    INNER JOIN images ON id = image_id
  WHERE
    category_id = 1
AND (category_id NOT IN (228,269,327,331,351,387,394,396,397,398,399,400,401,402,403,404,405,406,407,408,409,410,411,412,413,414,415,416,417,418,419,420,421,422,423,424,425,426,427,428,429,430,431,432,433,434,435,436,437,438,439,440,441,442,443,444,445,446,447,448,449,450,451,452,453,454,455,456,457,458,459,460,461,462,463,464,465,466,467,468,469,470,471,472,473,474,475,476,477,478,479,480,481,482,483,484,485,486,487,488,489,490,491,492,493,494,495,496,497,498,499,500,501,502,503,504,505,506,507,508,509,510,511,512,513,514,515,516,517,518,519,520,521,522,523,524,525,526,527,528,529,530,531,532,533,534,535,536,537,538,539,540,541,542,543,544,545,546,547,548,549,550,551,552,553,554,555,556,557,558,559,560,561,562, in /usr/local/piwigo/include/dblayer/functions_mysqli.inc.php on line 832


Fatal error: Call to a member function fetch_assoc() on boolean in /usr/local/piwigo/include/dblayer/functions_mysqli.inc.php on line 893
I just ran into this after upgrading my server today to Ubuntu 16.04, which came with MySQL 5.7.11-0ubuntu6. When I try to browse my albums I get: ``` Warning: [mysql error 3065] Expression #1 of ORDER BY clause is not in SELECT list, references column 'piwigo.images.date_creation' which is not in SELECT list; this is incompatible with DISTINCT SELECT DISTINCT(image_id) FROM image_category INNER JOIN images ON id = image_id WHERE category_id = 1 AND (category_id NOT IN (228,269,327,331,351,387,394,396,397,398,399,400,401,402,403,404,405,406,407,408,409,410,411,412,413,414,415,416,417,418,419,420,421,422,423,424,425,426,427,428,429,430,431,432,433,434,435,436,437,438,439,440,441,442,443,444,445,446,447,448,449,450,451,452,453,454,455,456,457,458,459,460,461,462,463,464,465,466,467,468,469,470,471,472,473,474,475,476,477,478,479,480,481,482,483,484,485,486,487,488,489,490,491,492,493,494,495,496,497,498,499,500,501,502,503,504,505,506,507,508,509,510,511,512,513,514,515,516,517,518,519,520,521,522,523,524,525,526,527,528,529,530,531,532,533,534,535,536,537,538,539,540,541,542,543,544,545,546,547,548,549,550,551,552,553,554,555,556,557,558,559,560,561,562, in /usr/local/piwigo/include/dblayer/functions_mysqli.inc.php on line 832 Fatal error: Call to a member function fetch_assoc() on boolean in /usr/local/piwigo/include/dblayer/functions_mysqli.inc.php on line 893 ``` <!-- github-comment-id:212138715 -->
adam commented 2026-10-04 23:54:59 +02:00
Author
Owner
Copy link
Copy source

Adding

[mysqld]
sql-mode=""

to my.cnf resolved my issue. The problem seems to be related to the default ONLY_FULL_GROUP_BY setting in mysql 5.7.

Adding ``` [mysqld] sql-mode="" ``` to my.cnf resolved my issue. The problem seems to be related to the default ONLY_FULL_GROUP_BY setting in mysql 5.7. <!-- github-comment-id:212146953 -->
adam commented 2026-10-04 23:54:59 +02:00
Author
Owner
Copy link
Copy source

That's exactly what was said above :-)

That's exactly what was said above :-) <!-- github-comment-id:212308734 -->
adam commented 2026-10-04 23:55:00 +02:00
Author
Owner
Copy link
Copy source

Yep, I know. I was just confirming that workaround since this issue is still marked as open.

Yep, I know. I was just confirming that workaround since this issue is still marked as open. <!-- github-comment-id:212400271 -->
adam commented 2026-10-04 23:55:01 +02:00
Author
Owner
Copy link
Copy source

This still seems to be an issue (fresh install on ubuntu 16.04), I can confirm @jradwan 's fix works.

This still seems to be an issue (fresh install on ubuntu 16.04), I can confirm @jradwan 's fix works. <!-- github-comment-id:221706880 -->
adam commented 2026-10-04 23:55:02 +02:00
Author
Owner
Copy link
Copy source

^ Same here, on Ubuntu 16.04. The "sql-mode=""" fix helped me as well though.

^ Same here, on Ubuntu 16.04. The "sql-mode=""" fix helped me as well though. <!-- github-comment-id:223166639 -->
adam commented 2026-10-04 23:55:03 +02:00
Author
Owner
Copy link
Copy source

Add this:

[mysqld]
sql-mode=""

To:
/etc/mysql/my.cnf

Worked for me as well, on Ubuntu 16.04

Add this: ``` [mysqld] sql-mode="" ``` To: `/etc/mysql/my.cnf` Worked for me as well, on Ubuntu 16.04 <!-- github-comment-id:231621150 -->
adam commented 2026-10-04 23:55:04 +02:00
Author
Owner
Copy link
Copy source

Works for me on Debian 8, but I guess this is just a temporary work around?

Works for me on Debian 8, but I guess this is just a temporary work around? <!-- github-comment-id:231663036 -->
adam commented 2026-10-04 23:55:04 +02:00
Author
Owner
Copy link
Copy source

This workaround http://piwigo.org/forum/viewtopic.php?pid=163884#p163884 seems interesting.

This workaround http://piwigo.org/forum/viewtopic.php?pid=163884#p163884 seems interesting. <!-- github-comment-id:232425042 -->
adam commented 2026-10-04 23:55:05 +02:00
Author
Owner
Copy link
Copy source

Add this:

[mysqld]
sql-mode=""

To:
/etc/mysql/my.cnf

Worked for me as well, on Ubuntu 16.04

Add this: [mysqld] sql-mode="" To: /etc/mysql/my.cnf Worked for me as well, on Ubuntu 16.04 <!-- github-comment-id:255315428 -->
adam commented 2026-10-04 23:55:06 +02:00
Author
Owner
Copy link
Copy source

Only Wamp Server
Add code at bottom in my.ini file:

[mysqld]
sql-mode=""

Folder Path:
C:\wamp\bin\mysql\mysql5.7.14\my.ini

It is working in window 7 and 8.

Only Wamp Server Add code at bottom in my.ini file: **[mysqld] sql-mode=""** Folder Path: C:\wamp\bin\mysql\mysql5.7.14\my.ini It is working in window 7 and 8. <!-- github-comment-id:300713529 -->
adam commented 2026-10-04 23:55:07 +02:00
Author
Owner
Copy link
Copy source

For me, its simpler to fix the "offending" SQL statement. This is an easy change from:

SELECT DISTINCT YEAR(my_date) as 'years' FROM my_tableorder bymy_date;

to:

SELECT DISTINCT YEAR(my_date) as 'years' FROM my_table order by YEAR(my_date);

Just match the select with the order by and you're good to go.

For me, its simpler to fix the _"offending"_ SQL statement. This is an easy change from: `SELECT DISTINCT YEAR(`my_date`) as 'years' FROM `my_table` order by `my_date`;` to: `SELECT DISTINCT YEAR(`my_date`) as 'years' FROM `my_table` order by YEAR(`my_date`);` Just match the select with the order by and **you're good to go**. <!-- github-comment-id:304884573 -->
adam commented 2026-10-04 23:55:07 +02:00
Author
Owner
Copy link
Copy source

The problem was fixed with b87094ac2f

The problem was fixed with b87094ac2f502b31b8650c371c01b74b894df50e <!-- github-comment-id:305174298 -->
adam commented 2026-10-04 23:55:08 +02:00
Author
Owner
Copy link
Copy source

Another solution:
mysql > SET @@global.sql_mode=(SELECT REPLACE(@@global.sql_mode, 'ONLY_FULL_GROUP_BY', ''));

Another solution: `mysql > SET @@global.sql_mode=(SELECT REPLACE(@@global.sql_mode, 'ONLY_FULL_GROUP_BY', ''));` <!-- github-comment-id:309242371 -->
adam commented 2026-10-04 23:55:09 +02:00
Author
Owner
Copy link
Copy source

*Another solution:
mysql > SET @@global.sql_mode=(SELECT REPLACE(@@global.sql_mode, 'ONLY_FULL_GROUP_BY', ''));

This work for me.

*The problem was fixed with b87094a = Result Error 500

  • Cannot start mysql
    [mysqld]
    sql-mode=""
*Another solution: mysql > SET @@global.sql_mode=(SELECT REPLACE(@@global.sql_mode, 'ONLY_FULL_GROUP_BY', '')); This work for me. *The problem was fixed with b87094a = Result Error 500 * Cannot start mysql [mysqld] sql-mode="" <!-- github-comment-id:335062433 -->
adam commented 2026-10-04 23:55:09 +02:00
Author
Owner
Copy link
Copy source

DON'T USE:

[mysqld]
sql-mode=""

From documentation:
The default SQL mode in MySQL 5.7 includes these modes: ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, and NO_ENGINE_SUBSTITUTION.

By setting sql-mode='', you'd loose MySQL strictness - allowing you to put 60 chars long string in a varchar(50), truncating it and being completely silent about it.

I don't care about the other settings so I ended up with:

sql-mode="STRICT_ALL_TABLES"
**DON'T USE:** ``` [mysqld] sql-mode="" ``` From [documentation](https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html): _The default SQL mode in MySQL 5.7 includes these modes: ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, and NO_ENGINE_SUBSTITUTION._ By setting `sql-mode=''`, you'd loose MySQL strictness - allowing you to put 60 chars long string in a varchar(50), truncating it and being completely silent about it. I don't care about the other settings so I ended up with: ``` sql-mode="STRICT_ALL_TABLES" ``` <!-- github-comment-id:336738303 -->
adam commented 2026-10-04 23:55:10 +02:00
Author
Owner
Copy link
Copy source

Hey gang,

MySQL version: Server version: 5.7.23-0ubuntu0.16.04.1 (Ubuntu)

Listen I hope it's not too late, but as many of you I wouldn't like to temper with the workaround, I confirm it's working but I would rather fix my query to work. So from the simple query which was earlier:

SELECT rmc_description 
FROM   rad_mammo_calendars 
WHERE  rmc_year = 2018 
ORDER  BY rmc_id

I got the error: Error Code: 3065 Expression #1 of ORDER BY clause is not in SELECT list, references column 'rirads_wrdp1.rad_mammo_calendars.rmc_id' which is not in SELECT list; this is incompatible with DISTINCT

So I rewrote it with the subquery.

My solution would be:

SELECT a.rmc_description
FROM
(
    SELECT rmc_description, rmc_id
    FROM rad_mammo_calendars
    WHERE rmc_year = 2018
) a
ORDER BY a.rmc_id

I hope it will help somebody.

Hey gang, MySQL version: `Server version: 5.7.23-0ubuntu0.16.04.1 (Ubuntu)` Listen I hope it's not too late, but as many of you I wouldn't like to temper with the workaround, I confirm it's working but I would rather fix my query to work. So from the simple query which was earlier: ``` SELECT rmc_description FROM rad_mammo_calendars WHERE rmc_year = 2018 ORDER BY rmc_id ``` I got the error: `Error Code: 3065 Expression #1 of ORDER BY clause is not in SELECT list, references column 'rirads_wrdp1.rad_mammo_calendars.rmc_id' which is not in SELECT list; this is incompatible with DISTINCT` So I rewrote it with the subquery. My solution would be: ``` SELECT a.rmc_description FROM ( SELECT rmc_description, rmc_id FROM rad_mammo_calendars WHERE rmc_year = 2018 ) a ORDER BY a.rmc_id ``` I hope it will help somebody. <!-- github-comment-id:421137840 -->
adam commented 2026-10-04 23:55:11 +02:00
Author
Owner
Copy link
Copy source

I edited this file /etc/mysql/my.cnf appending the 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'

Compared with the default sql_mode, I just removed 'ONLY_FULL_GROUP_BY' mode and it fixed the problem for me.

I edited this file `/etc/mysql/my.cnf` appending the 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' ``` Compared with the default sql_mode, I just removed 'ONLY_FULL_GROUP_BY' mode and it fixed the problem for me. <!-- github-comment-id:469640604 -->
adam commented 2026-10-04 23:55:12 +02:00
Author
Owner
Copy link
Copy source

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

For people using MAMP (free)(MacOS): Steps -> Stop your servers -> you might need to create a the file my.conf in your /MAMP/conf/ directory if its not already there and put this lines and then try starting servers..

Hope it helps.

This worked for me: [mysqld] sql-mode="" . For people using MAMP (free)(MacOS): Steps -> Stop your servers -> you might need to create a the file my.conf in your /MAMP/conf/ directory if its not already there and put this lines and then try starting servers.. Hope it helps. <!-- github-comment-id:477796395 -->
adam commented 2026-10-04 23:55:13 +02:00
Author
Owner
Copy link
Copy source

Adding

[mysqld]
sql-mode=""

to my.cnf resolved my issue. The problem seems to be related to the default ONLY_FULL_GROUP_BY setting in mysql 5.7.

working 4 me to

> Adding > > ``` > [mysqld] > sql-mode="" > ``` > > to my.cnf resolved my issue. The problem seems to be related to the default ONLY_FULL_GROUP_BY setting in mysql 5.7. working 4 me to <!-- github-comment-id:705825108 -->
adam commented 2026-10-04 23:55:13 +02:00
Author
Owner
Copy link
Copy source

Hello,

Editing my.cnf or using SET @@global.sql_mode=(SELECT REPLACE(@@global.sql_mode, 'ONLY_FULL_GROUP_BY', '')); is only applicable when having full right on your mysql instance :/

For exemple, on the new DB services on OVH, it's not usable.

It seems that there is a solution here : https://github.com/Piwigo/Piwigo/issues/376#issuecomment-421137840 but it's not yet implemented :(

Hello, Editing my.cnf or using SET @@global.sql_mode=(SELECT REPLACE(@@global.sql_mode, 'ONLY_FULL_GROUP_BY', '')); is only applicable when having full right on your mysql instance :/ For exemple, on the new DB services on OVH, it's not usable. It seems that there is a solution here : https://github.com/Piwigo/Piwigo/issues/376#issuecomment-421137840 but it's not yet implemented :( <!-- github-comment-id:972096644 -->
Sign in to join this conversation.
No Branch/Tag Specified
Labels
No items
No labels
Milestone
No items
No Milestone
Projects
Clear projects
No projects
Assignees
Clear assignees
adam (Adam Wojcieszonek)
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: adam/Piwigo#369