Piwigo.org

deemon · 2022-02-23 20:31:46

Hello/Hi/Greetings,

(Here your message)

Something causes piwigo to "hang" and turn the piwigo main page white (sometimes permanently, so only DB maintenance tasks + purge sessions seemed to bring the site back alive). admin.php was still working fine, thats where I did those DB maintenace and purge things.
In logs I found only troublesome thing this (many many times):

mysqld[1149]: 2022-02-23 21:03:42 140397169985280 [Warning] Sort aborted, host: localhost, user: piwigo, thread: 817149, query: SELECT *
mysqld[1149]: FROM images
mysqld[1149]: WHERE id IN (413509,400218,400219,400217,400220,400221,438118,504697,413945,413510,415044,398727,438120,438121,473734,473735,473736,473737,473738,473739,473740,473741,473742,473743,4737.....

once upon time earlier we had also this huge query problem, but we managed to mitigate it by hiding "Related albums" in Configuration > Menus. But this is still hidden, and now again some weird "sort" query. Can I turn this also off somehow? What triggers it?

(Copy here your environment details, found on your Piwigo page [Administration > Tools > Maintenance])

Piwigo 12.2.0 Check for upgrade
Operating system: Linux
PHP: 7.1.33-3+0~20191218.29+debian9~1.gbp18b07c (Show info) [2022-02-23 21:27:43]
MySQL: 5.5.5-10.1.48-MariaDB-1~stretch [2022-02-23 21:27:43]
Graphics Library: External ImageMagick 6.9.7-4
Cache size 200817.05 Mo   calculated 29 minutes ago Refresh

Last edited by deemon (2022-02-23 20:41:30)

deemon · 2022-02-28 07:55:26

anything? what action on user part triggers such a sort query over (almost?) everything, where the query is composed such bad way, that it lists every single image ID in the query, making the query itself possibly megabytes long?

erAck · 2022-02-28 11:30:28

> turn the piwigo main page white

What do you mean by main page? The standard entry page that displays categories without any user interaction? If ~all images are selected that sounds like some plugin's carousel query, but that shouldn't be on the main entry page. Try to deactivate plugins one by one and see when it stops.

deemon · 2022-02-28 13:45:42

One failed query alone does not cause the website to hang, because next day I saw few of them in the log again, but the website was still very much operational, so I have no exact clue, how the hang/crash itself happened -- maybe several of those failed queries at once + some cache rebuild + something else at the same time?

After the "hang" was happened, the webpage for normal visitors was indeed white and nothing could be seen at all.

After I managed to "fix it" with admin user, by purging user sessions and just in case running DB fixes and stuff (just clicked one by one on all: Update albums' information, Update photos' information, Repair and optimize database, Reinitialize integrity check, Purge user cache, Purge sessions) from admin (not quite sure, which exactly fixed it, but page started working again for visitors.), I tried to make this happen again with "normal visitor" and failed to find any link or button or action that could have caused the "hang" or even the bad sort queries then in the syslog -- so it probably was caused by one of the photographers (admin user) while adding images and doing some other "gallery/image manupulation" that broke the website, for visitors.

But I still would like the absurd queries not happening at all, so it can't hang the website again somehow?

erAck · 2022-02-28 17:24:08

As long as it isn't known what triggers these queries it's hard to say how to avoid them..
Anyhow, I did a rough context grep over the sources

Code:

git grep -n -B2 'WHERE id IN' |grep -C1 -i 'FROM .*images' |grep -A2 'SELECT \*'

that came up with

Code:

comments.php-418-SELECT *
comments.php-419-  FROM '.IMAGES_TABLE.'
comments.php:420:  WHERE id IN ('.implode(',', $element_ids).')
--
include/category_cats.inc.php-157-SELECT *
include/category_cats.inc.php-158-  FROM '.IMAGES_TABLE.'
include/category_cats.inc.php:159:  WHERE id IN ('.implode(',', $image_ids).')
--
include/category_cats.inc.php-205-SELECT *
include/category_cats.inc.php-206-  FROM '.IMAGES_TABLE.'
include/category_cats.inc.php:207:  WHERE id IN ('.implode(',', $new_image_ids).')
--
include/category_default.inc.php-30-SELECT *
include/category_default.inc.php-31-  FROM '.IMAGES_TABLE.'
include/category_default.inc.php:32:  WHERE id IN ('.implode(',', $selection).')
--
include/ws_functions/pwg.images.php-643-SELECT *
include/ws_functions/pwg.images.php-644-  FROM '. IMAGES_TABLE .'
include/ws_functions/pwg.images.php:645:  WHERE id IN ('. implode(',', $image_ids) .')
--
include/ws_functions/pwg.tags.php-139-SELECT *
include/ws_functions/pwg.tags.php-140-  FROM '. IMAGES_TABLE .'
include/ws_functions/pwg.tags.php:141:  WHERE id IN ('. implode(',',$image_ids) .')
--
picture.php-477-SELECT *
picture.php-478-  FROM '.IMAGES_TABLE.'
picture.php:479:  WHERE id IN ('.implode(',', $ids).')

but may have missed some of course if further variables are used.

It might be related to a whole bunch of comments being retrieved in comments.php, or the API function ws_images_search() in include/ws_functions/pwg.images.php if the search matches a whole lot of images, or function ws_tags_getImages() in include/ws_functions/pwg.tags.php if the queried tag matches a whole lot of images. Maybe the first/prev/next/last management in picture.php for huge categories or global albums like keyword search if they contain massive amounts of images, though it doesn't look like. Just ideas.

deemon · 2022-03-29 16:49:14

Oops. Probably not the old fetch everything problem.
Didn't find this earlier, but might have been Bootstrap Darkroom theme problem.
Finally found in apache error log this:

There are like 2 errors running in tandem, first php7 warn and then milliseconds later php7 error:

Code:

[Tue Mar 29 02:04:26.334925 2022] [php7:warn] [pid 97606] [client 31.13.103.1:58980] PHP Warning:  [mysql error 1028] Sort aborted: Query execution was interrupted (max_statement_time exceeded)\n\n            SELECT *\n            FROM 
images\n            WHERE id IN (413509,400218,400219,400217,400220,400221,438118,504697,413945,413510,415044,398727,438120,438121,473734,473735,473736,473737,473738,473739,473740,473741,473742,473743,473744,473745,473746,473713,473714,
473721,473722,473723,473724,473725,473726,473727,473728,473729,473715,473730,473731,473732,473733,473716,473717,473718,473719,473720,413188,413946,413473,415045,398728,413189,413474,413947,415046,398729,413190,473192,413475,413948,41504
8,398730,413191,413476,413949,415049,398731,413192,472275,413477,413950,415050,398732,413193,413478,413951,415051,398733,413194,413952,413479,415052,398734,472277,413195,413480,413953,415053,398735,413196,472270,444134,444143,447741,451
869,455573,462159,464697,469088,477462,261388,273000,286508,290488,400575,417692,426809,431728,438432,443374,450238,451041,454911,460859,473190,476063,476363,478423,480799,501233,4978 in /var/www/html/galerii/include/dblayer/functions_m
ysqli.inc.php on line 847

[Tue Mar 29 02:04:26.335574 2022] [php7:error] [pid 97606] [client 31.13.103.1:58980] PHP Fatal error:  Uncaught Error: Call to a member function fetch_assoc() on boolean in /var/www/html/galerii/include/dblayer/functions_mysqli.inc.php
:211\nStack trace:\n#0 /var/www/html/galerii/themes/bootstrap_darkroom/include/themecontroller.php(190): pwg_db_fetch_assoc(false)\n#1 /var/www/html/galerii/include/functions_plugins.inc.php(264): BootstrapDarkroom\\ThemeController->get
AllThumbnailsInCategory()\n#2 /var/www/html/galerii/picture.php(1029): trigger_notify('loc_end_picture')\n#3 {main}\n  thrown in /var/www/html/galerii/include/dblayer/functions_mysqli.inc.php on line 211


[Tue Mar 29 02:04:26.397315 2022] [php7:warn] [pid 97645] [client 31.13.103.11:44414] PHP Warning:  [mysql error 1028] Sort aborted: Query execution was interrupted (max_statement_time exceeded)\n\n            SELECT *\n            FROM
 images\n            WHERE id IN (413509,400218,400219,400217,400220,400221,438118,504697,413945,413510,415044,398727,438120,438121,473734,473735,473736,473737,473738,473739,473740,473741,473742,473743,473744,473745,473746,473713,473714
,473721,473722,473723,473724,473725,473726,473727,473728,473729,473715,473730,473731,473732,473733,473716,473717,473718,473719,473720,413188,413946,413473,415045,398728,413189,413474,413947,415046,398729,413190,473192,413475,413948,4150
48,398730,413191,413476,413949,415049,398731,413192,472275,413477,413950,415050,398732,413193,413478,413951,415051,398733,413194,413952,413479,415052,398734,472277,413195,413480,413953,415053,398735,413196,472270,444134,444143,447741,45
1869,455573,462159,464697,469088,477462,261388,273000,286508,290488,400575,417692,426809,431728,438432,443374,450238,451041,454911,460859,473190,476063,476363,478423,480799,501233,4978 in /var/www/html/galerii/include/dblayer/functions_
mysqli.inc.php on line 847

[Tue Mar 29 02:04:26.397750 2022] [php7:error] [pid 97645] [client 31.13.103.11:44414] PHP Fatal error:  Uncaught Error: Call to a member function fetch_assoc() on boolean in /var/www/html/galerii/include/dblayer/functions_mysqli.inc.ph
p:211\nStack trace:\n#0 /var/www/html/galerii/themes/bootstrap_darkroom/include/themecontroller.php(190): pwg_db_fetch_assoc(false)\n#1 /var/www/html/galerii/include/functions_plugins.inc.php(264): BootstrapDarkroom\\ThemeController->ge
tAllThumbnailsInCategory()\n#2 /var/www/html/galerii/picture.php(1029): trigger_notify('loc_end_picture')\n#3 {main}\n  thrown in /var/www/html/galerii/include/dblayer/functions_mysqli.inc.php on line 211



[Tue Mar 29 02:04:26.404437 2022] [php7:warn] [pid 97694] [client 31.13.103.1:58982] PHP Warning:  [mysql error 1028] Sort aborted: Query execution was interrupted (max_statement_time exceeded)\n\n            SELECT *\n            FROM 
images\n            WHERE id IN (413509,400218,400219,400217,400220,400221,438118,504697,413945,413510,415044,398727,438120,438121,473734,473735,473736,473737,473738,473739,473740,473741,473742,473743,473744,473745,473746,473713,473714,
473721,473722,473723,473724,473725,473726,473727,473728,473729,473715,473730,473731,473732,473733,473716,473717,473718,473719,473720,413188,413946,413473,415045,398728,413189,413474,413947,415046,398729,413190,473192,413475,413948,41504
8,398730,413191,413476,413949,415049,398731,413192,472275,413477,413950,415050,398732,413193,413478,413951,415051,398733,413194,413952,413479,415052,398734,472277,413195,413480,413953,415053,398735,413196,472270,444134,444143,447741,451
869,455573,462159,464697,469088,477462,261388,273000,286508,290488,400575,417692,426809,431728,438432,443374,450238,451041,454911,460859,473190,476063,476363,478423,480799,501233,4978 in /var/www/html/galerii/include/dblayer/functions_m
ysqli.inc.php on line 847

[Tue Mar 29 02:04:26.404863 2022] [php7:error] [pid 97694] [client 31.13.103.1:58982] PHP Fatal error:  Uncaught Error: Call to a member function fetch_assoc() on boolean in /var/www/html/galerii/include/dblayer/functions_mysqli.inc.php
:211\nStack trace:\n#0 /var/www/html/galerii/themes/bootstrap_darkroom/include/themecontroller.php(190): pwg_db_fetch_assoc(false)\n#1 /var/www/html/galerii/include/functions_plugins.inc.php(264): BootstrapDarkroom\\ThemeController->get
AllThumbnailsInCategory()\n#2 /var/www/html/galerii/picture.php(1029): trigger_notify('loc_end_picture')\n#3 {main}\n  thrown in /var/www/html/galerii/include/dblayer/functions_mysqli.inc.php on line 211



[Tue Mar 29 02:04:26.471938 2022] [php7:warn] [pid 98790] [client 31.13.103.13:37754] PHP Warning:  [mysql error 1028] Sort aborted: Query execution was interrupted (max_statement_time exceeded)\n\n            SELECT *\n            FROM
 images\n            WHERE id IN (413509,400218,400219,400217,400220,400221,438118,504697,413945,413510,415044,398727,438120,438121,473734,473735,473736,473737,473738,473739,473740,473741,473742,473743,473744,473745,473746,473713,473714
,473721,473722,473723,473724,473725,473726,473727,473728,473729,473715,473730,473731,473732,473733,473716,473717,473718,473719,473720,413188,413946,413473,415045,398728,413189,413474,413947,415046,398729,413190,473192,413475,413948,4150
48,398730,413191,413476,413949,415049,398731,413192,472275,413477,413950,415050,398732,413193,413478,413951,415051,398733,413194,413952,413479,415052,398734,472277,413195,413480,413953,415053,398735,413196,472270,444134,444143,447741,45
1869,455573,462159,464697,469088,477462,261388,273000,286508,290488,400575,417692,426809,431728,438432,443374,450238,451041,454911,460859,473190,476063,476363,478423,480799,501233,4978 in /var/www/html/galerii/include/dblayer/functions_
mysqli.inc.php on line 847

[Tue Mar 29 02:04:26.472372 2022] [php7:error] [pid 98790] [client 31.13.103.13:37754] PHP Fatal error:  Uncaught Error: Call to a member function fetch_assoc() on boolean in /var/www/html/galerii/include/dblayer/functions_mysqli.inc.ph
p:211\nStack trace:\n#0 /var/www/html/galerii/themes/bootstrap_darkroom/include/themecontroller.php(190): pwg_db_fetch_assoc(false)\n#1 /var/www/html/galerii/include/functions_plugins.inc.php(264): BootstrapDarkroom\\ThemeController->ge
tAllThumbnailsInCategory()\n#2 /var/www/html/galerii/picture.php(1029): trigger_notify('loc_end_picture')\n#3 {main}\n  thrown in /var/www/html/galerii/include/dblayer/functions_mysqli.inc.php on line 211

Anyway, now updated Bootstrap Darkroom also, but the problems remain.

In addition now I have occasionally a lot of new problems in apache error log, that wasn't there before Bootstrap Darkroom update (it shoots off those like 9 times and then nothing for a while and then again ~9 times).

Code:

[Tue Mar 29 17:34:16.722647 2022] [php7:notice] [pid 5718] [client <snip>:59256] PHP Notice:  unserialize(): Error at offset 85 of 255 bytes in /var/www/html/galerii/include/ws_functions/pwg.php on line 456, referer: <snip>
[Tue Mar 29 17:34:16.722712 2022] [php7:notice] [pid 5718] [client <snip>:59256] PHP Notice:  unserialize(): Error at offset 85 of 255 bytes in /var/www/html/galerii/include/ws_functions/pwg.php on line 456, referer: <snip>
[Tue Mar 29 17:34:16.722734 2022] [php7:notice] [pid 5718] [client <snip>:59256] PHP Notice:  unserialize(): Error at offset 85 of 255 bytes in /var/www/html/galerii/include/ws_functions/pwg.php on line 456, referer: <snip>
[Tue Mar 29 17:34:16.722751 2022] [php7:notice] [pid 5718] [client <snip>:59256] PHP Notice:  unserialize(): Error at offset 85 of 255 bytes in /var/www/html/galerii/include/ws_functions/pwg.php on line 456, referer: <snip>
[Tue Mar 29 17:34:16.722759 2022] [php7:notice] [pid 5718] [client <snip>:59256] PHP Notice:  unserialize(): Error at offset 85 of 255 bytes in /var/www/html/galerii/include/ws_functions/pwg.php on line 456, referer: <snip>
[Tue Mar 29 17:34:16.722766 2022] [php7:notice] [pid 5718] [client <snip>:59256] PHP Notice:  unserialize(): Error at offset 85 of 255 bytes in /var/www/html/galerii/include/ws_functions/pwg.php on line 456, referer: <snip>
[Tue Mar 29 17:34:16.722784 2022] [php7:notice] [pid 5718] [client <snip>:59256] PHP Notice:  unserialize(): Error at offset 85 of 255 bytes in /var/www/html/galerii/include/ws_functions/pwg.php on line 456, referer: <snip>
[Tue Mar 29 17:34:16.722801 2022] [php7:notice] [pid 5718] [client <snip>:59256] PHP Notice:  unserialize(): Error at offset 85 of 255 bytes in /var/www/html/galerii/include/ws_functions/pwg.php on line 456, referer: <snip>
[Tue Mar 29 17:34:16.722808 2022] [php7:notice] [pid 5718] [client <snip>:59256] PHP Notice:  unserialize(): Error at offset 85 of 255 bytes in /var/www/html/galerii/include/ws_functions/pwg.php on line 456, referer: <snip>

EDIT: oops, sorry, seems the Bootstrap Darkroom problem is still present (logs with the current version):

Code:

[Tue Mar 29 19:26:11.099710 2022] [php7:warn] [pid 12089] [client 31.13.103.2:49942] PHP Warning:  [mysql error 1028] Sort aborted: Query execution was interrupted (max_statement_time exceeded)\n\n            SELECT *\n            FROM images\n            WHERE id IN (413509,400218,400219,400217,400220,400221,438118,504697,413945,413510,415044,398727,438120,438121,473734,473735,473736,473737,473738,473739,473740,473741,473742,473743,473744,473745,473746,473713,473714,473721,473722,473723,473724,473725,473726,473727,473728,473729,473715,473730,473731,473732,473733,473716,473717,473718,473719,473720,413188,413946,413473,415045,398728,413189,413474,413947,415046,398729,413190,473192,413475,413948,415048,398730,413191,413476,413949,415049,398731,413192,472275,413477,413950,415050,398732,413193,413478,413951,415051,398733,413194,413952,413479,415052,398734,472277,413195,413480,413953,415053,398735,413196,472270,444134,444143,447741,451869,455573,462159,464697,469088,477462,261388,273000,286508,290488,400575,417692,426809,431728,438432,443374,450238,451041,454911,460859,473190,476063,476363,478423,480799,501233,4978 in /var/www/html/galerii/include/dblayer/functions_mysqli.inc.php on line 847
[Tue Mar 29 19:26:11.100038 2022] [php7:error] [pid 12089] [client 31.13.103.2:49942] PHP Fatal error:  Uncaught Error: Call to a member function fetch_assoc() on boolean in /var/www/html/galerii/include/dblayer/functions_mysqli.inc.php:211\nStack trace:\n#0 /var/www/html/galerii/themes/bootstrap_darkroom/include/themecontroller.php(190): pwg_db_fetch_assoc(false)\n#1 /var/www/html/galerii/include/functions_plugins.inc.php(264): BootstrapDarkroom\\ThemeController->getAllThumbnailsInCategory()\n#2 /var/www/html/galerii/picture.php(1029): trigger_notify('loc_end_picture')\n#3 {main}\n  thrown in /var/www/html/galerii/include/dblayer/functions_mysqli.inc.php on line 211
[Tue Mar 29 19:26:21.400191 2022] [php7:warn] [pid 12176] [client 31.13.103.14:43684] PHP Warning:  [mysql error 1028] Sort aborted: Query execution was interrupted (max_statement_time exceeded)\n\n            SELECT *\n            FROM images\n            WHERE id IN (413509,400218,400219,400217,400220,400221,438118,504697,413945,413510,415044,398727,438120,438121,473734,473735,473736,473737,473738,473739,473740,473741,473742,473743,473744,473745,473746,473713,473714,473721,473722,473723,473724,473725,473726,473727,473728,473729,473715,473730,473731,473732,473733,473716,473717,473718,473719,473720,413188,413946,413473,415045,398728,413189,413474,413947,415046,398729,413190,473192,413475,413948,415048,398730,413191,413476,413949,415049,398731,413192,472275,413477,413950,415050,398732,413193,413478,413951,415051,398733,413194,413952,413479,415052,398734,472277,413195,413480,413953,415053,398735,413196,472270,444134,444143,447741,451869,455573,462159,464697,469088,477462,261388,273000,286508,290488,400575,417692,426809,431728,438432,443374,450238,451041,454911,460859,473190,476063,476363,478423,480799,501233,4978 in /var/www/html/galerii/include/dblayer/functions_mysqli.inc.php on line 847
[Tue Mar 29 19:26:21.400516 2022] [php7:error] [pid 12176] [client 31.13.103.14:43684] PHP Fatal error:  Uncaught Error: Call to a member function fetch_assoc() on boolean in /var/www/html/galerii/include/dblayer/functions_mysqli.inc.php:211\nStack trace:\n#0 /var/www/html/galerii/themes/bootstrap_darkroom/include/themecontroller.php(190): pwg_db_fetch_assoc(false)\n#1 /var/www/html/galerii/include/functions_plugins.inc.php(264): BootstrapDarkroom\\ThemeController->getAllThumbnailsInCategory()\n#2 /var/www/html/galerii/picture.php(1029): trigger_notify('loc_end_picture')\n#3 {main}\n  thrown in /var/www/html/galerii/include/dblayer/functions_mysqli.inc.php on line 211
[Tue Mar 29 19:26:21.438947 2022] [php7:warn] [pid 12301] [client 31.13.103.116:43998] PHP Warning:  [mysql error 1028] Sort aborted: Query execution was interrupted (max_statement_time exceeded)\n\n            SELECT *\n            FROM images\n            WHERE id IN (413509,400218,400219,400217,400220,400221,438118,504697,413945,413510,415044,398727,438120,438121,473734,473735,473736,473737,473738,473739,473740,473741,473742,473743,473744,473745,473746,473713,473714,473721,473722,473723,473724,473725,473726,473727,473728,473729,473715,473730,473731,473732,473733,473716,473717,473718,473719,473720,413188,413946,413473,415045,398728,413189,413474,413947,415046,398729,413190,473192,413475,413948,415048,398730,413191,413476,413949,415049,398731,413192,472275,413477,413950,415050,398732,413193,413478,413951,415051,398733,413194,413952,413479,415052,398734,472277,413195,413480,413953,415053,398735,413196,472270,444134,444143,447741,451869,455573,462159,464697,469088,477462,261388,273000,286508,290488,400575,417692,426809,431728,438432,443374,450238,451041,454911,460859,473190,476063,476363,478423,480799,501233,4978 in /var/www/html/galerii/include/dblayer/functions_mysqli.inc.php on line 847
[Tue Mar 29 19:26:21.439238 2022] [php7:error] [pid 12301] [client 31.13.103.116:43998] PHP Fatal error:  Uncaught Error: Call to a member function fetch_assoc() on boolean in /var/www/html/galerii/include/dblayer/functions_mysqli.inc.php:211\nStack trace:\n#0 /var/www/html/galerii/themes/bootstrap_darkroom/include/themecontroller.php(190): pwg_db_fetch_assoc(false)\n#1 /var/www/html/galerii/include/functions_plugins.inc.php(264): BootstrapDarkroom\\ThemeController->getAllThumbnailsInCategory()\n#2 /var/www/html/galerii/picture.php(1029): trigger_notify('loc_end_picture')\n#3 {main}\n  thrown in /var/www/html/galerii/include/dblayer/functions_mysqli.inc.php on line 211
[Tue Mar 29 19:49:16.655949 2022] [php7:warn] [pid 14170] [client 157.90.182.30:51986] PHP Warning:  [mysql error 1028] Sort aborted: Query execution was interrupted (max_statement_time exceeded)\n\n            SELECT *\n            FROM images\n            WHERE id IN (413509,400218,400219,400217,400220,400221,438118,504697,413945,413510,415044,398727,438120,438121,473734,473735,473736,473737,473738,473739,473740,473741,473742,473743,473744,473745,473746,473713,473714,473721,473722,473723,473724,473725,473726,473727,473728,473729,473715,473730,473731,473732,473733,473716,473717,473718,473719,473720,413188,413946,413473,415045,398728,413189,413474,413947,415046,398729,413190,473192,413475,413948,415048,398730,413191,413476,413949,415049,398731,413192,472275,413477,413950,415050,398732,413193,413478,413951,415051,398733,413194,413952,413479,415052,398734,472277,413195,413480,413953,415053,398735,413196,472270,444134,444143,447741,451869,455573,462159,464697,469088,477462,261388,273000,286508,290488,400575,417692,426809,431728,438432,443374,450238,451041,454911,460859,473190,476063,476363,478423,480799,501233,4978 in /var/www/html/galerii/include/dblayer/functions_mysqli.inc.php on line 847
[Tue Mar 29 19:49:16.656255 2022] [php7:error] [pid 14170] [client 157.90.182.30:51986] PHP Fatal error:  Uncaught Error: Call to a member function fetch_assoc() on boolean in /var/www/html/galerii/include/dblayer/functions_mysqli.inc.php:211\nStack trace:\n#0 /var/www/html/galerii/themes/bootstrap_darkroom/include/themecontroller.php(190): pwg_db_fetch_assoc(false)\n#1 /var/www/html/galerii/include/functions_plugins.inc.php(264): BootstrapDarkroom\\ThemeController->getAllThumbnailsInCategory()\n#2 /var/www/html/galerii/picture.php(1029): trigger_notify('loc_end_picture')\n#3 {main}\n  thrown in /var/www/html/galerii/include/dblayer/functions_mysqli.inc.php on line 211

so, whatever it is doing there, it takes way too long. as once upon a time I had this other problem with megabytes long queries I just in case set mysql/mariadb query timeout to 10 seconds ( "max_statement_time = 10" in /etc/mysql/my.cnf ). Can you fix the query to not take as long as it takes?

Last edited by deemon (2022-03-29 20:16:59)

deemon · 2022-04-07 00:07:17

So what now?

The SQL query seems to be either cut off, it ends in like:
WHERE id IN (413509,400218,1234
without even the ending ")" for the "WHERE id IN ()" or it is actually a massive query once again including too many pictures (that should not happen) and actually is just cropped for the sake of error log?

How can we fix this? What causes this to happen in /var/www/html/galerii/themes/bootstrap_darkroom/include/themecontroller.php(190)

PHP Fatal error: Uncaught Error: Call to a member function fetch_assoc() on boolean in /var/www/html/galerii/include/dblayer/functions_mysqli.inc.php:211\nStack trace:\n#0 /var/www/html/galerii/themes/bootstrap_darkroom/include/themecontroller.php(190): pwg_db_fetch_assoc(false)\n#1 /var/www/html/galerii/include/functions_plugins.inc.php(264): BootstrapDarkroom\\ThemeController->getAllThumbnailsInCategory()\n#2 /var/www/html/galerii/picture.php(1029): trigger_notify('loc_end_picture')\n#3 {main}\n thrown in /var/www/html/galerii/include/dblayer/functions_mysqli.inc.php on line 211

erAck · 2022-04-07 01:12:22

deemon wrote:
The SQL query seems to be either cut off, it ends in like:
WHERE id IN (413509,400218,1234
without even the ending ")" for the "WHERE id IN ()" or it is actually a massive query once again including too many pictures (that should not happen) and actually is just cropped for the sake of error log?

I'd assume the latter. If it was a truncated query then already MySQL should bail out.

deemon · 2022-04-07 22:01:54

Alright. So how to fix this?

Soliidne · 2022-11-24 16:36:10

Hello

We are having the same bug and we are also using bootstrap_darkroom theme. Since our gallery has more than 700k images this bug creates a massive SQL query that halts and freezes the MySQL server. This of course causes the website to become unresponsive. Only solution that helps to solve it temporarily is to restart MySQL service entirely or kill the specific query from the database.

In our case I have noticed that robots who crawl our Piwigo gallery can trigger this by making query to picture.php?... I can reproduce this issue every time by creating a query like that, for example, "mygallery.org/picture.php?12". After that, this massive query is created, query itself is 43MB and contains all the picture ids from _image table.
But I can not reproduce it same way with another theme.

Deemon have you had any luck figuring out solution for this bug?

erAck · 2022-11-24 19:52:19

If with Bootstrap Darkroom you have the Slick Carousel enabled and huge albums, make sure to not enable lazyLoad method progressive, but ondemand instead. If that doesn't help try to disable the carousel completely.

Soliidne · 2022-11-25 08:58:15

Thank you for your suggestion.

lazyLoad method was already set to ondemand. I tried disabling carousel completely but the issue still remains.

When the server crashes I get the following PHP fatal error:

Code:

Fatal error: Uncaught Error: Call to a member function fetch_assoc() on boolean in /var/www/html/emil/include/dblayer/functions_mysqli.inc.php:211 Stack trace: #0 /var/www/html/emil/themes/bootstrap_darkroom/include/themecontroller.php(185): pwg_db_fetch_assoc(false) #1 /var/www/html/emil/include/functions_plugins.inc.php(264): BootstrapDarkroom\ThemeController->getAllThumbnailsInCategory() #2 /var/www/html/emil/picture.php(1033): trigger_notify('loc_end_picture') #3 {main} thrown in /var/www/html/emil/include/dblayer/functions_mysqli.inc.php on line 211

It seems that if you go on URL like /picture.php?photoId you get to the so called "root" category or album where all the public images are accessible, in our case 700k+ images. Then Bootstrap Darkroom theme calls for a function getAllThumbnailsInCategory(), located in /themes/bootstrap_darkroom/include/themecontroller.php on line 164 to get info for all the pictures in that category. And this creates massive SQL query that crashes the database.

I wonder if this function could be edited so that it would not try to get data for all the images at once for that category.

There is an if block in themecontroller.php starting at line 170:

if (!$page['items'] || ($page['section'] == 'categories' && !isset($page['category']) && !isset($page['chronology_field']) && !isset($page['flat']))) {
return;
}

Maybe we could add a condition to it ?

Soliidne · 2022-11-25 14:14:57

I think that I managed to find a solution to my problem. I found github issue on the same problem and applied the patch what tkuther suggested here [Github] piwigo-bootstrap-darkroom issue #195

I disabled Slick Carousel and Photoswipe before applying the patch. It seems that now when querying something like /picture.php?pictureId or /picture.php?/pictureId/categories this massive SQL query is not being created that clogs up the database.

erAck · 2022-11-26 03:10:47

Good find, thanks for providing the solution!

Piwigo.org

Announcement

#1 2022-02-23 20:31:46

[resolved] piwigo becomes unresponsive / white screen in browser.

#2 2022-02-28 07:55:26

Re: [resolved] piwigo becomes unresponsive / white screen in browser.

#3 2022-02-28 11:30:28

Re: [resolved] piwigo becomes unresponsive / white screen in browser.

#4 2022-02-28 13:45:42

Re: [resolved] piwigo becomes unresponsive / white screen in browser.

#5 2022-02-28 17:24:08

Re: [resolved] piwigo becomes unresponsive / white screen in browser.

Code:

Code:

#6 2022-03-29 16:49:14

Re: [resolved] piwigo becomes unresponsive / white screen in browser.

Code:

Code:

Code:

#7 2022-04-07 00:07:17

Re: [resolved] piwigo becomes unresponsive / white screen in browser.

#8 2022-04-07 01:12:22

Re: [resolved] piwigo becomes unresponsive / white screen in browser.

deemon wrote:

#9 2022-04-07 22:01:54

Re: [resolved] piwigo becomes unresponsive / white screen in browser.

#10 2022-11-24 16:36:10

Re: [resolved] piwigo becomes unresponsive / white screen in browser.

#11 2022-11-24 19:52:19

Re: [resolved] piwigo becomes unresponsive / white screen in browser.

#12 2022-11-25 08:58:15

Re: [resolved] piwigo becomes unresponsive / white screen in browser.

Code:

#13 2022-11-25 14:14:57

Re: [resolved] piwigo becomes unresponsive / white screen in browser.

#14 2022-11-26 03:10:47

Re: [resolved] piwigo becomes unresponsive / white screen in browser.

Board footer