excessive mysqld usage on big piwigo instances #1226

New Issue

Closed

opened 2026-10-05 00:36:36 +02:00 by adam · 9 comments

adam commented 2026-10-05 00:36:36 +02:00

Owner

Copy link

Copy source

adding to this forum topic:
https://piwigo.org/forum/viewtopic.php?id=31229

It seems that on some cases Piwigo goes mad and tries to do like 1.5MB mysql query (included in the attachment.)
when the url is like
https://-filtered-/picture.php?/478517
or
https://-filtered-/picture.php?/478517/categories

The picture even HAS CATEGORY, but when someone tampers with the URL and removes it there from there...

it's a 7zip file, not a zip, because github doesn't allow to upload .7z, but 7z file was like 126k, whereas actual zip was 532k.
dafuq.7z.zip

It's like serious DDOS vulnerability in Piwigo 11.4.x
And then when someone lets a crawler bot / script loose into this url that just starts "clicking" the next and previous arrow urls under there it's like game over for the site. It just kills MySQL, 100% CPU usage for hours;
When it was accidental or something, then restarting mysql service helps to stop this, until next bot comes along. Or even site own admin browsers prefetch or whatever.

adding to this forum topic: https://piwigo.org/forum/viewtopic.php?id=31229 It seems that on some cases Piwigo goes mad and tries to do like 1.5MB mysql query (included in the attachment.) when the url is like https://-filtered-/picture.php?/478517 or https://-filtered-/picture.php?/478517/categories The picture even HAS CATEGORY, but when someone tampers with the URL and removes it there from there... it's a 7zip file, not a zip, because github doesn't allow to upload .7z, but 7z file was like 126k, whereas actual zip was 532k. [dafuq.7z.zip](https://github.com/Piwigo/Piwigo/files/6444037/dafuq.7z.zip) It's like serious DDOS vulnerability in Piwigo 11.4.x And then when someone lets a crawler bot / script loose into this url that just starts "clicking" the next and previous arrow urls under there it's like game over for the site. It just kills MySQL, 100% CPU usage for hours; When it was accidental or something, then restarting mysql service helps to stop this, until next bot comes along. Or even site own admin browsers prefetch or whatever. <!-- github-sync-id:879667899 -->

adam closed this issue 2026-10-05 00:36:36 +02:00

adam commented 2026-10-05 00:36:37 +02:00

Author

Owner

Copy link

Copy source

can you show us the SQL queries directly here, simply by replacing the long list of ids with "[...]" ?

can you show us the SQL queries directly here, simply by replacing the long list of ids with "[...]" ? <!-- github-comment-id:834760232 -->

adam commented 2026-10-05 00:36:37 +02:00

Author

Owner

Copy link

Copy source

Hello,

I'm also experiencing this issue. When I skip to the next image it takes
very long to load the page. I switched on SQL traces and could also see a
statement with a list of many, many ids. I would be very interested in
getting this fixed.

Please let me know if you want me to do some tests and traces for you. I'm
still on 2.10.2 with my production site, but my sandbox running 11.4.0 has
the same issue and a similar amount of pictures (5k) and albums (300).

Best regards

Jens

Am 7. Mai 2021 22:12:03 schrieb D33M0N @.***>:

adding to this forum topic:
https://piwigo.org/forum/viewtopic.php?id=31229
It seems that on some cases some piwigo goes mad and tries to do like 1.5MB
mysql query (included in the attachment.)
when the url is like
https:///picture.php?/478517
or
https:///picture.php?/478517/categoriesThe picture even HAS CATEGORY, but
when someone tampers with the URL and removes it there from there...
it's a 7zip file, not a zip, because github doesn't allow to upload .7z,
but 7z file was like 126k, whereas actual zip was 532k.
dafuq.7z.zip
It's like serious DDOS vulnerability in Piwigo 11.4.x
—
You are receiving this because you are subscribed to this thread.
Reply to this email directly, view it on GitHub, or unsubscribe.

Hello, I'm also experiencing this issue. When I skip to the next image it takes very long to load the page. I switched on SQL traces and could also see a statement with a list of many, many ids. I would be very interested in getting this fixed. Please let me know if you want me to do some tests and traces for you. I'm still on 2.10.2 with my production site, but my sandbox running 11.4.0 has the same issue and a similar amount of pictures (5k) and albums (300). Best regards Jens Am 7. Mai 2021 22:12:03 schrieb D33M0N ***@***.***>: > > adding to this forum topic: > https://piwigo.org/forum/viewtopic.php?id=31229 > It seems that on some cases some piwigo goes mad and tries to do like 1.5MB > mysql query (included in the attachment.) > when the url is like > https:///picture.php?/478517 > or > https:///picture.php?/478517/categoriesThe picture even HAS CATEGORY, but > when someone tampers with the URL and removes it there from there... > it's a 7zip file, not a zip, because github doesn't allow to upload .7z, > but 7z file was like 126k, whereas actual zip was 532k. > dafuq.7z.zip > It's like serious DDOS vulnerability in Piwigo 11.4.x > — > You are receiving this because you are subscribed to this thread. > Reply to this email directly, view it on GitHub, or unsubscribe. <!-- github-comment-id:834784433 -->

adam commented 2026-10-05 00:36:38 +02:00

Author

Owner

Copy link

Copy source

can you show us the SQL queries directly here, simply by replacing the long list of ids with "[...]" ?

[1] 
SELECT @@SESSION.sql_mode
(this query time : 0.000 s)
(total SQL time  : 0.000 s)
(total time      : 0.001 s)
(num rows        : 1 )

[2] 

SELECT param, value
 FROM config
 
;
(this query time : 0.000 s)
(total SQL time  : 0.000 s)
(total time      : 0.001 s)
(num rows        : 96 )

[3] 

SELECT data
  FROM sessions
  WHERE id = 'BC00ittjaljbss6q9d4ofm69g3rab2'
;
(this query time : 0.000 s)
(total SQL time  : 0.000 s)
(total time      : 0.001 s)
(num rows        : 1 )

[4] 

SELECT * FROM plugins
  WHERE state='active'
(this query time : 0.000 s)
(total SQL time  : 0.000 s)
(total time      : 0.002 s)
(num rows        : 4 )

[5] 

SELECT id AS id
     , username AS username
     , password AS password
     , mail_address AS email
  FROM users
  WHERE id = '51'
(this query time : 0.000 s)
(total SQL time  : 0.001 s)
(total time      : 0.003 s)
(num rows        : 1 )

[6] 

SELECT
    ui.*,
    uc.*,
    t.name AS theme_name
  FROM user_infos AS ui
    LEFT JOIN user_cache AS uc ON ui.user_id = uc.user_id
    LEFT JOIN themes AS t ON t.id = ui.theme
  WHERE ui.user_id = 51
;
(this query time : 0.000 s)
(total SQL time  : 0.001 s)
(total time      : 0.003 s)
(num rows        : 1 )

[7] 

SELECT *
  FROM user_infos
  WHERE user_id = 2
;
(this query time : 0.000 s)
(total SQL time  : 0.001 s)
(total time      : 0.003 s)
(num rows        : 1 )

[8] 

SELECT id,uppercats,commentable,visible,status,global_rank
  FROM image_category
    INNER JOIN categories ON category_id = id
  WHERE image_id = 478517
AND (id NOT IN (53143,53186,53308,53983,54195,54236,54237,54238,54239,54240,54241,54242,54243,54244,54245,54246,54247,54248,54249,54250,54251,54252,54253,54254,54255,54256,54257,54258,54259,54260,54261,54262,54263,54264,54265,54266,54267,54268,54269,54270,54271,54272,54273,54274,54275,54276,54277,54278,54279,54280,54281,54282,54283,54284,54285,54286,54287,54288,54289,54290,54291,54292,54820,55482,56054,56210,56244,57826,57911,59738))
;
(this query time : 0.000 s)
(total SQL time  : 0.001 s)
(total time      : 0.032 s)
(num rows        : 1 )

[9] 

SELECT *
  FROM images
  WHERE id IN (478517,478516,413509,478518,326189)
;
(this query time : 0.000 s)
(total SQL time  : 0.001 s)
(total time      : 0.033 s)
(num rows        : 5 )

[10] 

SELECT t.*, count(*) AS counter
  FROM image_tag
    INNER JOIN tags t ON tag_id = id
  WHERE image_id IN (478517)
  GROUP BY t.id
  ORDER BY NULL
(this query time : 0.000 s)
(total SQL time  : 0.001 s)
(total time      : 0.033 s)
(num rows        : 0 )

[11] 

SELECT id, name, permalink
  FROM categories
  WHERE id IN (60287)
(this query time : 0.000 s)
(total SQL time  : 0.001 s)
(total time      : 0.033 s)
(num rows        : 1 )

[12] 

SELECT 
  id, name, permalink, nb_images, global_rank,
  date_last, max_date_last, count_images, count_categories
FROM categories INNER JOIN user_cache_categories
  ON id = cat_id and user_id = 51
WHERE 
(id_uppercat is NULL)
;
(this query time : 0.027 s)
(total SQL time  : 0.028 s)
(total time      : 0.077 s)
(num rows        : 4219 )

[13] 

SELECT
    c.id,
    c.uppercats,
    count(*) AS counter
  FROM image_category
    INNER JOIN categories c ON category_id = id
  WHERE image_id IN (ABSURDLY_MASSIVE_LIST_OF_EVERY_PICTURE_ID)
    AND (category_id NOT IN (53143,53186,53308,53983,54195,54236,54237,54238,54239,54240,54241,54242,54243,54244,54245,54246,54247,54248,54249,54250,54251,54252,54253,54254,54255,54256,54257,54258,54259,54260,54261,54262,54263,54264,54265,54266,54267,54268,54269,54270,54271,54272,54273,54274,54275,54276,54277,54278,54279,54280,54281,54282,54283,54284,54285,54286,54287,54288,54289,54290,54291,54292,54820,55482,56054,56210,56244,57826,57911,59738))
  GROUP BY c.id
  ORDER BY NULL
(this query time : 23.292 s)
(total SQL time  : 23.320 s)
(total time      : 23.403 s)
(num rows        : 5502 )

[14] 

SELECT
    id,
    name,
    permalink,
    id_uppercat,
    uppercats,
    global_rank
  FROM categories
  WHERE id IN (ANOTHER_LONG_LIST_but_much_smaller)
;
(this query time : 0.011 s)
(total SQL time  : 23.331 s)
(total time      : 23.428 s)
(num rows        : 5756 )

[15] 

SELECT mail_address
  FROM users
  WHERE id = 1
;
(this query time : 0.000 s)
(total SQL time  : 23.331 s)
(total time      : 23.526 s)
(num rows        : 1 )

> can you show us the SQL queries directly here, simply by replacing the long list of ids with "[...]" ? ``` [1] SELECT @@SESSION.sql_mode (this query time : 0.000 s) (total SQL time : 0.000 s) (total time : 0.001 s) (num rows : 1 ) [2] SELECT param, value FROM config ; (this query time : 0.000 s) (total SQL time : 0.000 s) (total time : 0.001 s) (num rows : 96 ) [3] SELECT data FROM sessions WHERE id = 'BC00ittjaljbss6q9d4ofm69g3rab2' ; (this query time : 0.000 s) (total SQL time : 0.000 s) (total time : 0.001 s) (num rows : 1 ) [4] SELECT * FROM plugins WHERE state='active' (this query time : 0.000 s) (total SQL time : 0.000 s) (total time : 0.002 s) (num rows : 4 ) [5] SELECT id AS id , username AS username , password AS password , mail_address AS email FROM users WHERE id = '51' (this query time : 0.000 s) (total SQL time : 0.001 s) (total time : 0.003 s) (num rows : 1 ) [6] SELECT ui.*, uc.*, t.name AS theme_name FROM user_infos AS ui LEFT JOIN user_cache AS uc ON ui.user_id = uc.user_id LEFT JOIN themes AS t ON t.id = ui.theme WHERE ui.user_id = 51 ; (this query time : 0.000 s) (total SQL time : 0.001 s) (total time : 0.003 s) (num rows : 1 ) [7] SELECT * FROM user_infos WHERE user_id = 2 ; (this query time : 0.000 s) (total SQL time : 0.001 s) (total time : 0.003 s) (num rows : 1 ) [8] SELECT id,uppercats,commentable,visible,status,global_rank FROM image_category INNER JOIN categories ON category_id = id WHERE image_id = 478517 AND (id NOT IN (53143,53186,53308,53983,54195,54236,54237,54238,54239,54240,54241,54242,54243,54244,54245,54246,54247,54248,54249,54250,54251,54252,54253,54254,54255,54256,54257,54258,54259,54260,54261,54262,54263,54264,54265,54266,54267,54268,54269,54270,54271,54272,54273,54274,54275,54276,54277,54278,54279,54280,54281,54282,54283,54284,54285,54286,54287,54288,54289,54290,54291,54292,54820,55482,56054,56210,56244,57826,57911,59738)) ; (this query time : 0.000 s) (total SQL time : 0.001 s) (total time : 0.032 s) (num rows : 1 ) [9] SELECT * FROM images WHERE id IN (478517,478516,413509,478518,326189) ; (this query time : 0.000 s) (total SQL time : 0.001 s) (total time : 0.033 s) (num rows : 5 ) [10] SELECT t.*, count(*) AS counter FROM image_tag INNER JOIN tags t ON tag_id = id WHERE image_id IN (478517) GROUP BY t.id ORDER BY NULL (this query time : 0.000 s) (total SQL time : 0.001 s) (total time : 0.033 s) (num rows : 0 ) [11] SELECT id, name, permalink FROM categories WHERE id IN (60287) (this query time : 0.000 s) (total SQL time : 0.001 s) (total time : 0.033 s) (num rows : 1 ) [12] SELECT id, name, permalink, nb_images, global_rank, date_last, max_date_last, count_images, count_categories FROM categories INNER JOIN user_cache_categories ON id = cat_id and user_id = 51 WHERE (id_uppercat is NULL) ; (this query time : 0.027 s) (total SQL time : 0.028 s) (total time : 0.077 s) (num rows : 4219 ) [13] SELECT c.id, c.uppercats, count(*) AS counter FROM image_category INNER JOIN categories c ON category_id = id WHERE image_id IN (ABSURDLY_MASSIVE_LIST_OF_EVERY_PICTURE_ID) AND (category_id NOT IN (53143,53186,53308,53983,54195,54236,54237,54238,54239,54240,54241,54242,54243,54244,54245,54246,54247,54248,54249,54250,54251,54252,54253,54254,54255,54256,54257,54258,54259,54260,54261,54262,54263,54264,54265,54266,54267,54268,54269,54270,54271,54272,54273,54274,54275,54276,54277,54278,54279,54280,54281,54282,54283,54284,54285,54286,54287,54288,54289,54290,54291,54292,54820,55482,56054,56210,56244,57826,57911,59738)) GROUP BY c.id ORDER BY NULL (this query time : 23.292 s) (total SQL time : 23.320 s) (total time : 23.403 s) (num rows : 5502 ) [14] SELECT id, name, permalink, id_uppercat, uppercats, global_rank FROM categories WHERE id IN (ANOTHER_LONG_LIST_but_much_smaller) ; (this query time : 0.011 s) (total SQL time : 23.331 s) (total time : 23.428 s) (num rows : 5756 ) [15] SELECT mail_address FROM users WHERE id = 1 ; (this query time : 0.000 s) (total SQL time : 23.331 s) (total time : 23.526 s) (num rows : 1 ) ``` <!-- github-comment-id:834818841 -->

adam commented 2026-10-05 00:36:39 +02:00

Author

Owner

Copy link

Copy source

Might as well add, that for now I am using a safeguard in my MariaDB /etc/mysql/my.cnf under [mysqld] section:
max_statement_time = 5

(for actual MySQL you need to use max_execution_time = 5000 instead (mysql value in milliseconds, whereas in MariaDB in seconds)

Might as well add, that for now I am using a safeguard in my MariaDB `/etc/mysql/my.cnf` under `[mysqld]` section: `max_statement_time = 5` (for actual MySQL you need to use `max_execution_time = 5000` instead (mysql value in milliseconds, whereas in MariaDB in seconds) <!-- github-comment-id:834825170 -->

adam commented 2026-10-05 00:36:40 +02:00

Author

Owner

Copy link

Copy source

in [Admin>Configuration>Menu] deactivate the
« related albums » menu for all user status.

Pierrick LE GALL

in [Admin>Configuration>Menu] deactivate the « related albums » menu for all user status. -- Pierrick LE GALL <!-- github-comment-id:835247912 -->

adam commented 2026-10-05 00:36:40 +02:00

Author

Owner

Copy link

Copy source

in [Admin>Configuration>Menu] deactivate the
« related albums » menu for all user status.

Thank you!

You can't "deactivate" it, but only hide it?
I mean, am I in the right spot -- admin.php?page=menubar
Configuration -> Menus -> Related albums (mbRelatedCategories) -> Hide [ v ] (checkbox)

> in [Admin>Configuration>Menu] deactivate the > « related albums » menu for all user status. Thank you! You can't "deactivate" it, but only hide it? I mean, am I in the right spot -- admin.php?page=menubar Configuration -> Menus -> Related albums (mbRelatedCategories) -> Hide [ v ] (checkbox) <!-- github-comment-id:835327209 -->

adam commented 2026-10-05 00:36:41 +02:00

Author

Owner

Copy link

Copy source

Yes, "hide" is the same as "deactivate".

I think you have asked Piwigo to show the menubar on the page of the photo, meaning Piwigo will calculate the related albums. If you (or a robot) use the canonical URL:

<link rel=canonical href="picture.php?/4">

Then Piwigo will search related albums to ALL photos of the gallery (because you're not in an album or in a tag...) and that makes a huge SQL query, very long to compute. That is directly related to #1392

Yes, "hide" is the same as "deactivate". I think you have asked Piwigo to show the menubar on the page of the photo, meaning Piwigo will calculate the related albums. If you (or a robot) use the canonical URL: ``` <link rel=canonical href="picture.php?/4"> ``` Then Piwigo will search related albums to ALL photos of the gallery (because you're not in an album or in a tag...) and that makes a huge SQL query, very long to compute. That is directly related to #1392 <!-- github-comment-id:838148372 -->

adam commented 2026-10-05 00:36:42 +02:00

Author

Owner

Copy link

Copy source

I think Piwigo should even not compute the related albums on the picture.php page. It's not useful at this point.

I think Piwigo should even not compute the related albums on the picture.php page. It's not useful at this point. <!-- github-comment-id:841117047 -->

adam commented 2026-10-05 00:36:42 +02:00

Author

Owner

Copy link

Copy source

related to #1392

related to #1392 <!-- github-comment-id:1081913622 -->

Sign in to join this conversation.

No Branch/Tag Specified

Branches Tags

master

issue-2560-people

translation

16.x

feat/embedding-based-contextual-tagging

issue-2576-search-include-ai-in-search-results

feat/search-ocr-ai

issue-2391-update-menu-design

15.x

detect-container

issue-2386-update-filters-users-activity

issue-2362-add-filters-options-in-admin

issue-2367-imagemagick7

issue-2225-quicksearch-help

14.x

issue-2164-BatchManagerUnitMode-Redesign

13.x

12.x

issue-1657-SVG_Support

issue-1703-historySearchMethod

11.x

2.10

2.9

2.8

2.7

2.5

2.6

2.4

2.3

2.2

2.1

2.0

1.7

1.3

1.6

1.5

1.4

16.4.0

16.3.0

16.2.0

16.1.0

16.0.0

16.0.0RC3

16.0.0RC2

15.7.0

16.0.0RC1

16.0.0beta1

16.0.0beta2

15.6.0

15.5.0

15.4.0

15.3.0

15.2.0

15.1.0

15.0.0

15.0.0beta3

15.0.0beta2

15.0.0beta1

14.5.0

14.4.0

14.3.0

14.2.0

14.1.0

14.0.0

14.0.0RC2

14.0.0RC1

14.0.0beta3

14.0.0beta2

13.8.0

14.0.0beta1

13.7.0

13.6.0

13.5.0

13.4.0

13.3.0

13.2.0

13.1.0

13.0.0

13.0.0RC4

13.0.0RC3

13.0.0RC2

13.0.0RC1

12.3.0

13.0.0beta2

13.0.0beta1

12.2.0

12.1.0

12.0.0

12.0.0RC2

12.0.0RC1

12.0.0beta2

12.0.0beta1

11.5.0

11.4.0

11.3.0

11.2.0

11.1.0

11.0.0

11.0.0beta5

2.11.0beta4

2.11.0beta3

2.11.0beta2

2.11.0beta1

2.10.2

2.10.1

2.10.0

2.10.0RC1

2.10.0beta2

2.10.0beta1

2.9.5

2.9.4

2.9.3

2.9.2

2.9.1

2.9.0

2.9.0RC2

2.9.0RC1

2.9.0beta2

2.8.6

2.8.5

2.8.4

2.9.0beta1

2.8.3

2.8.2

2.8.1

2.8.0

2.8.0RC2

2.8.0RC1

2.7.4

2.7.3

2.6.5

2.5.6

2.5.5

2.6.4

2.7.2

2.7.1

2.7.0

2.7.0RC2

2.7.0RC1

2.7.0beta2

2.6.3

2.7.0beta1

2.6.2

2.5.4

2.6.1

2.6.0

2.6.0RC2

2.6.0RC1

2.6.0beta2

2.5.3

2.5.2

2.6.0beta1

2.5.1

2.5.0

2.5.0RC2

2.4.7

2.5.0RC1

2.4.6

2.5.0beta3

2.5.0beta2

2.5.0beta1

2.4.5

2.4.4

2.4.3

2.4.2

2.4.1

2.4.0

2.3.5

2.4.0RC3

2.3.4

2.4.0RC2

2.4.0RC1

2.3.3

2.4.0beta2

2.4.0beta1

2.3.2

2.3.1

2.3.0

2.3.0RC2

2.3.0RC1

2.2.5

2.3.0beta3

2.2.4

2.3.0beta2

2.2.3

2.2.2

2.3.0beta1

2.2.1

2.2.0

2.2.0RC4

2.2.0RC3

2.2.0RC2

2.1.6

2.2.0RC1

2.1.5

2.1.4

2.1.3

2.1.2

2.1.1

2.1.0

2.0.10

2.1.0RC3

2.1.0RC2

2.1.0RC1

2.0.9

2.0.8

2.0.7

2.0.6

2.0.5

2.0.4

2.0.3

2.0.2

2.0.1

2.0.0

2.0.0RC4

1.7.3

2.0.0RC3

2.0.0RC2

2.0.0RC1

1.7.2

1.7.1

1.7.0

1.7.0RC2

1.7.0RC1

1.6.2

1.6.1

1.6.0

1.6.0RC2

1.6.0RC1

1.5.2

1.5.1

1.5.0

1.5.0RC2

1.5.0RC1

1.4.1

1.4.0

1.4.0RC3

1.4.0RC2

1.3.4

1.4.0RC1

1.3.3

1.3.2

1.3.1

1.3.0

1.3.0RC2

1.3.0RC1

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

Subscribe

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: adam/Piwigo#1226