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

mysql error 3065: ORDER BY -vs- SELECT list #509

New Issue
Closed
opened 2026-10-05 00:01:41 +02:00 by adam · 1 comment
adam commented 2026-10-05 00:01:41 +02:00
Owner
Copy link
Copy source

I've just downloaded Piwigo 2.8.2 to run it in a Docker environment. However, I get mysql errors that look like this:

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

SELECT DISTINCT(image_id)
	FROM piwigo_image_category
		INNER JOIN piwigo_images ON id = image_id
	WHERE
		category_id = 11

	ORDER BY date_available DESC, file ASC, id ASC
; in /scripts/pics.binfalse.de/include/dblayer/functions_mysqli.inc.php on line 832


Fatal error: Uncaught Error: Call to a member function fetch_assoc() on boolean in /scripts/pics.binfalse.de/include/dblayer/functions_mysqli.inc.php:893 Stack trace: #0 /scripts/pics.binfalse.de/include/section_init.inc.php(318): query2array('\nSELECT DISTINC...', NULL, 'image_id') #1 /scripts/pics.binfalse.de/index.php(27): include('/scripts/pics.b...') #2 {main} thrown in /scripts/pics.binfalse.de/include/dblayer/functions_mysqli.inc.php on line 893

The docker environment consists of

  • an nginx:latest container that mounts the Piwigo sources to /usr/share/nginx/html (ro)
  • a mysql:latest container, that is linked to the nginx container
  • a php:fpm container that also knows about the piwigo sources and has mysqli installed

We're talking about this PHP

  • PHP Version => 7.0.11
  • Configure Command => './configure' '--with-config-file-path=/usr/local/etc/php' '--with-config-file-scan-dir=/usr/local/etc/php/conf.d' '--disable-cgi' '--enable-ftp' '--enable-mbstring' '--enable-mysqlnd' '--with-curl' '--with-libedit' '--with-openssl' '--with-zlib' '--enable-fpm' '--with-fpm-user=www-data' '--with-fpm-group=www-data'
  • PHP API => 20151012
  • PHP Extension => 20151012
  • Zend Extension => 320151012
  • Zend Extension Build => API320151012,NTS
  • PHP Extension Build => API20151012,NTS
  • Client API library version => mysqlnd 5.0.12-dev - 20150407 - Id: 241ae00989d1995ffcbbf63d579943635faf9972
  • Version => mysqlnd 5.0.12-dev - 20150407 - Id: 241ae00989d1995ffcbbf63d579943635faf9972

I've been able to fix the issues by adding the ORDER BY elements to the SELECT list in piwigo/include/section_init.inc.php:

309c309
< SELECT DISTINCT(image_id), date_available, file, id
---
> SELECT DISTINCT(image_id)
446c446
< SELECT DISTINCT(id), date_available
---
> SELECT DISTINCT(id)
485c485
< SELECT DISTINCT(id), hit
---
> SELECT DISTINCT(id)

However, I'm not sure if that's the proper way to go..!?
Do you have any comments? Maybe you want to include the changes to your code base?

I've just downloaded Piwigo 2.8.2 to run it in a Docker environment. However, I get mysql errors that look like this: Warning: [mysql error 3065] Expression #1 of ORDER BY clause is not in SELECT list, references column 'picsdb.piwigo_images.date_available' which is not in SELECT list; this is incompatible with DISTINCT SELECT DISTINCT(image_id) FROM piwigo_image_category INNER JOIN piwigo_images ON id = image_id WHERE category_id = 11 ORDER BY date_available DESC, file ASC, id ASC ; in /scripts/pics.binfalse.de/include/dblayer/functions_mysqli.inc.php on line 832 Fatal error: Uncaught Error: Call to a member function fetch_assoc() on boolean in /scripts/pics.binfalse.de/include/dblayer/functions_mysqli.inc.php:893 Stack trace: #0 /scripts/pics.binfalse.de/include/section_init.inc.php(318): query2array('\nSELECT DISTINC...', NULL, 'image_id') #1 /scripts/pics.binfalse.de/index.php(27): include('/scripts/pics.b...') #2 {main} thrown in /scripts/pics.binfalse.de/include/dblayer/functions_mysqli.inc.php on line 893 The docker environment consists of * an nginx:latest container that mounts the Piwigo sources to `/usr/share/nginx/html` (ro) * a mysql:latest container, that is linked to the nginx container * a php:fpm container that also knows about the piwigo sources and has `mysqli` installed We're talking about this PHP * PHP Version => 7.0.11 * Configure Command => './configure' '--with-config-file-path=/usr/local/etc/php' '--with-config-file-scan-dir=/usr/local/etc/php/conf.d' '--disable-cgi' '--enable-ftp' '--enable-mbstring' '--enable-mysqlnd' '--with-curl' '--with-libedit' '--with-openssl' '--with-zlib' '--enable-fpm' '--with-fpm-user=www-data' '--with-fpm-group=www-data' * PHP API => 20151012 * PHP Extension => 20151012 * Zend Extension => 320151012 * Zend Extension Build => API320151012,NTS * PHP Extension Build => API20151012,NTS * Client API library version => mysqlnd 5.0.12-dev - 20150407 - $Id: 241ae00989d1995ffcbbf63d579943635faf9972 $ * Version => mysqlnd 5.0.12-dev - 20150407 - $Id: 241ae00989d1995ffcbbf63d579943635faf9972 $ I've been able to fix the issues by adding the `ORDER BY` elements to the `SELECT` list in `piwigo/include/section_init.inc.php`: 309c309 < SELECT DISTINCT(image_id), date_available, file, id --- > SELECT DISTINCT(image_id) 446c446 < SELECT DISTINCT(id), date_available --- > SELECT DISTINCT(id) 485c485 < SELECT DISTINCT(id), hit --- > SELECT DISTINCT(id) However, I'm not sure if that's the proper way to go..!? Do you have any comments? Maybe you want to include the changes to your code base? <!-- github-sync-id:190454930 -->
adam closed this issue 2026-10-05 00:01:41 +02:00
adam commented 2026-10-05 00:01:42 +02:00
Author
Owner
Copy link
Copy source

https://github.com/Piwigo/Piwigo/issues/376#issuecomment-160817041

https://github.com/Piwigo/Piwigo/issues/376#issuecomment-160817041 <!-- github-comment-id:261705942 -->
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#509