Managed to fix this myself. The last thing thing I did before the crash was to add an image to album Y, remove it from album X and also add it to album Z (which I created as a new album 'on the fly' using 'creation mode' in the Add album dialog) then clicked on Save.

I suspected the error message

"The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay"

was misleading mainly because at this time there are only 525 photos (images) and 25 albums (categories) in my gallery. But even if there was some malformed SQL query embedded in the php code creating a cartesian join, 525 x 25 would still only produce 13,125 rows (if only the photos and albums tables were joined) which is diddly-squat for a MySQL database. So  I don't really believe that message and unfortunately I don't know any php to go digging any deeper into other aspects of the error message.

But the reported error did at least lead me to take a look in the database to see if I could find any obvious data integrity issue. I couldn't but I decided to manually back out my last action step by step in the hope it would fix the problem and that worked at the very first step, which was to hack the image_category table to delete the record linking the photo to album Z.

Adding a photo to more than one album is supported by Piwigo so I have absolutely no idea why deleting that one image-category link should fix it.

So I'm happy I have fixed it but would still like to know the root cause. I see my original post got viewed quite a few times but nobody posted any replies. It would be really helpful if a member of the Piwigo team could post their thoughts on possible causes.

I changed the title of my original post to give a clearer description of the issue.

In the meantime I also tried adding a photo to more than one album via the Edit photo icon when viewing the photo in the gallery. This too resulted in the error message so I guess that rules out the possibility the problem is related to edits via the batch manager.

This all doesn't make much sense

I agree. The query doesn't do a JOIN.  In researchinhg the error message I found this posted on another web site:

Before MySQL 8.0.31, max_join_size also applies to single-table queries: MySQL 8.0.30 and earlier: Do not permit statements that probably need to examine more than max_join_size rows (for single-table statements) or row combinations (for multiple-table statements) or that are likely to do more than max_join_size disk seeks.

But I'm not sure if that's relevant because I cannot tie up the referenced MySQL version 8.0.31 with the version I am running ( 5.5.5-10.11.10-MariaDB-cll-lve).

As of today I have only 536 records in the piwis3_images table  and 539 records in the piwis3_image_category table which suggests that one or two images do in fact appear in more than one album (I need to dig a little deeper on which ones in case that gives me any clues).

There are no orphans but that said, at the moment the issue is not occuring because as mentioned in my earlier post I fixed it by deleting one of the records in the piwis3_image_category table for the photo I was editng when the problem  occurred.

I can't get to it till tomorrow I will reproduce the behaviour and then check for orphans.  Please post re any other diagnostics I can try.

Continuing my attempts to debug this issue but the problem seems to have got worse. Now I get an error directly I try to go to batch manager after uploading a set of images. Up till now the error occurred when editing data in batch manager.

Here's the current sequence of steps I followed to reproduce the error:


1. Uploaded 11 files to an album called Events
2. Clicked on  "Manage this set of 11 photos" after successful upload
3. Error message displayed:

Fatal error: Uncaught mysqli_sql_exception: The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay in /home/fellfixe/public_html/piwigo/include/dblayer/functions_mysqli.inc.php:132 Stack trace: #0 /home/fellfixe/public_html/piwigo/include/dblayer/functions_mysqli.inc.php(132): mysqli->query() #1 /home/fellfixe/public_html/piwigo/admin/include/functions.php(3335): pwg_query() #2 /home/fellfixe/public_html/piwigo/admin.php(304): count_orphans() #3 {main} thrown in /home/fellfixe/public_html/piwigo/include/dblayer/functions_mysqli.inc.php on line 132

Note: this is similar to but different from the original error message and also doesn't include the offending SQL statement. The original error message was:

Warning:  [mysql error 1104] The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay

SELECT
    COUNT(DISTINCT(image_id))
  FROM piwis3_image_category
; in /home/fellfixe/public_html/piwigo/include/dblayer/functions_mysqli.inc.php on line 847


Fatal error: Uncaught Error: Call to a member function fetch_row() on bool in /home/fellfixe/public_html/piwigo/include/dblayer/functions_mysqli.inc.php:216 Stack trace: #0 /home/fellfixe/public_html/piwigo/admin/include/functions.php(3335): pwg_db_fetch_row(false) #1 /home/fellfixe/public_html/piwigo/admin.php(304): count_orphans() #2 {main} thrown in /home/fellfixe/public_html/piwigo/include/dblayer/functions_mysqli.inc.php on line 216

(Perhaps the different message is because I upgraded my PHP version from 7.4.33 to 8.3.15 in case this was the possible cause of the error).

4. Checked in piwis3_images - all 11 images were there
5. Checked in piwis3_image_category - all 11 images were linked to only one album
6. Checked in piwis3_caddie - no rows in table (which seems strange and significant given at step 2 I opted to manage the uploaded images)
7. Checked in piwis3_tags - the keywords in the images' metadata were there

8. Deleted the 11 records added to piwis3_image_category

9. In Piwigo tab clicked on the Back button, the gallery home page was displayed, clicked on Admin - the Admin Dashboard was displayed with warning of 11 orphans in Header

10. Clicked on Photos > Orphans and chose action to move all 11 images to For Indexing album - the error recurred.

I'm almost out of ideas as to what else I can try to identify the problem. Is there any logging I can switch on and inspect to see if it gives any other clues?

Can any of the Piwigo development team familiar with the upload and batch manager code describe what should happen under the covers at each step in the above sequence to maybe give me a clue as to anything else I can check?

I was about to give up on this (and on Piwigo) but decided to do another search of the forum. Should have thought much earlier of searching for MAX_JOIN_SIZE. Doing so led me to these two topics:

https://piwigo.org/forum/viewtopic.php?id=33307 (First post in topic July 2021)
https://piwigo.org/forum/viewtopic.php?id=33820 (First post in topic August 2024)

Those posts reveal the problem appears to be with the count_orphans function called at line 304 (Piwigo 15.3.0) in admin.php. So commenting out the following lines fixed it for me:


//if ($page['nb_photos_total'] < 100000) // 100k is already a big gallery
//{
//  $page['nb_orphans'] = count_orphans();
//}

Some of the above-referenced posts suggest commenting out more lines than that but not being a programmer myself I prefer to keep my amateur hacking to the bare minimum for simplicity.

So although that has fixed the problem I, and others who reported it, still have no idea what the root cause is. We all seem to have observed it occurring under completely different circumstances. Given that the problem seems to have been around for at least 3.5 years it is a little surprising that many more people haven't run into it and a fix implemented. (That's just an observation, not a complaint).

Last edited by strutha (2025-02-01 05:15:18)