Piwigo.org

Crythes · 2024-12-19 10:09:21

Hello/Hi/Greetings,

In a private album users gets an SQL error at the bottom of the page:

Fatal error: Uncaught mysqli_sql_exception: Out of range value for column 'category_id' at row 1 in /srv/hfr/include/dblayer/functions_mysqli.inc.php:134 Stack trace: #0 /srv/hfr/include/dblayer/functions_mysqli.inc.php(134): mysqli->query() #1 /srv/hfr/include/functions.inc.php(541): pwg_query() #2 /srv/hfr/index.php(728): pwg_log() #3 {main} thrown in /srv/hfr/include/dblayer/functions_mysqli.inc.php on line 134

The query is:

!-- SQL Query:
UPDATE piwigo_user_infos
SET last_visit = NOW(),
lastmodified = lastmodified
WHERE user_id = 890
-->

I assume that this is because the category_id that is being written to piwigo_history table is 38200. Is that normal?

Without actually messing up with the database I feel the easiest solution would be to turn off user history tracking for the moment, but I can't seem to find an option to do that...? Any other suggestions?

EDIT: SOLUTION: change category_id attribute to UNSIGNED in piwigo_history table, like it is in the other ones...

I am using version 14.5. I don't know if it's fixed in 15. But that leads me to another question - does that mean that max number of albums we can have is 65535? And generally, why is it not just INT but SMALLINT instead?

Last edited by Crythes (2024-12-19 10:23:48)

plg · 2024-12-19 10:29:21

That's a bug: piwigo_history.category_id should indeed be a "smallint(5) unsigned default NULL". So you have to run this SQL query :

Code:

ALTER TABLE piwigo_history CHANGE category_id category_id smallint(5) unsigned default NULL;

does that mean that max number of albums we can have is 65535?

It does.

Now that in Piwigo 15, we collect anymous data about Piwigo installations, I can tell you that over 18k installations, we have 2 with more than 65k albums. It means they have manually increased the size of the piwigo_categories.id column (and other piwigo_*.category_id related columns).

The biggest number of albums is 104744. I don't understand how a single photo gallery can have so many albums. Do you?

Crythes · 2024-12-19 10:44:59

The biggest number of albums is 104744. I don't understand how a single photo gallery can have so many albums. Do you?

Yes, maybe... We have around 52K albums already. Our use case is very specific and maybe unusual. We have a massive database of around 25K micro-CT scans. We use Piwigo as a visual database for these scans. But we organize them usually by curatorial institutions, the site the specimens actually come from, and then we might organize them by species, e.g. a gorilla foot bone from the Berlin Zoo that originated from Congo. So in our case we might end up with many more thousands of subalbums.

I have a feeling that we may also need to change the attributes of all the category_id columns in the future. But that further complicates the maintenance of our Piwigo installation since we have already made some changes to website files.

plg · 2024-12-19 19:46:45

In Piwigo 15, Linty has heavily improved the speed of the album manager. I consider that before this version 15, the album manager was too slow for normal use beyond 10k albums. I wonder how slow it was with 38k albums!!!

Don't you find the album manager very slow with version 14.5.0? You should really give a try to 15, just for this very specific improvement.

Crythes · 2025-04-17 13:25:31

I have not yet upgraded to the latest version, but it's good to know!

If we change the the attributes of all the category_id columns in the database will these be overwritten by future piwigo updates? But I am really curious if there is actually a need to use SMALLINT instead of INT? Can a change to INT be pushed with a Piwigo update?

Piwigo.org

Announcement

#1 2024-12-19 10:09:21

Fatal MYSQL error: Out of range value for column 'category_id'

#2 2024-12-19 10:29:21

Re: Fatal MYSQL error: Out of range value for column 'category_id'

Code:

#3 2024-12-19 10:44:59

Re: Fatal MYSQL error: Out of range value for column 'category_id'

#4 2024-12-19 19:46:45

Re: Fatal MYSQL error: Out of range value for column 'category_id'

#5 2025-04-17 13:25:31

Re: Fatal MYSQL error: Out of range value for column 'category_id'

Board footer