Hello,

With 1.5.3, I often get mysqli errors.
I get several errors in my debug log :

EXCEPTION (0): Call to a member function real_escape_string() on boolean in zp-core/functions-db-MySQLi.php on line 155

WARNING: mysqli_connect(): (42000/1203): User already has more than 'max_user_connections' active connections in zp-core/functions-db-MySQLi.php on line 29
mysqli_connect called from db_connect (functions-db-MySQLi.php [29])
from require_once (functions-basic.php [185])
from require_once (functions.php [18])

This happens when working on new albums/pictures, not while looking at pictures.
Am I alone, or do you also have some problems ?

Errors are generated when working with image cache.
Adding titles, comments, tags, does not generate mysqli errors.

My mariadb was automatically upgraded (version : 10.2.25) recently.
Could it be a reason ?

I will ask my host why they upgrade this db !

Did you try to lower the cache concurrency on the image options as I mentioned on another topic?

Also it is important what method for caching you are using (cURL or classic way).

Generally pre-caching is a massiv task causing lots of server load and that grows with the number and size (dimensions) of the images processed.

As mentioned before t is technically not necessary as Zenphoto will cache any image size when requested the first time. Even if that causes some delay for the first time visitor.

We don't/can't test MariaDB specifially but according to its info it should be generally compatible with the matching MySQL versions and not the problem here.

I just reduced the cache concurrency. And I'm not using cURL anymore. I will see with next photos I'll add.
Happy with sql version not being the problem too !
Thanks.

Btw, for your interest here is how the cacheManager is meant to work in cURL mode but sadly does not everywhere: https://www.zenphoto.org/screenshots/various/cachemanager-curl-mode.m4v.html

I can confirm this for MySQL.
Here are some common examples from my debug log:

WARNING: mysqli_connect(): (HY000/1203): User  already has more than 'max_user_connections' active connections in /zp-core/functions-db-MySQLi.php on line 29
mysqli_connect called from db_connect (functions-db-MySQLi.php [29])
from require_once (functions-basic.php [181])
from require_once (functions.php [18])
from include (index.php [14])
from index.php [31]

WARNING: mysqli_connect(): (HY000/1203): User  already has more than 'max_user_connections' active connections in /zp-core/functions-db-MySQLi.php on line 29
mysqli_connect called from db_connect (functions-db-MySQLi.php [29])
from require_once (functions-basic.php [181])
from require_once (functions.php [18])
from full-image.php [10]

WARNING: mysqli_connect(): (HY000/1203): User  already has more than 'max_user_connections' active connections in /zp-core/functions-db-MySQLi.php on line 29
mysqli_connect called from db_connect (functions-db-MySQLi.php [29])
from require_once (functions-basic.php [181])
from i.php [35]

These are from Zenphoto version 1.5.1.

Please first upgrade (current is 1.5.4) and also try the options mentioned above. Otherwise we need an exact description when and under what circumstances this happens like using the cacheManager with what settings, how many images, how large (dimensions NOT file size!) are used etc. Just the usual information.

I already upgraded to the latest version and will keep an eye on this.
We don't use cacheManager.

Have you considered using a Singleton pattern for the database connection to prevent situations where the connection isn't closed properly before creating a new connection?
Currently db_connect() does not check if $_zp_DB_connection is already set before calling mysqli_connect() again.

We will not use an actual singleton but the planned major release will have this area reworked a bit.

But I will review the missing check you mentioned as I had the impression there is a check with that global. That would probably be an oversight.

It would be interesting on what occasions you get this. But I see I think. At least the db_connect() functions of each db lib should check if the same db connection is already open with the global. I will take a look at this as soon as I can.

I'd be interested as well to know when this happens, but although it's happening quite often, I cannot see a regular pattern. Sometimes it's gone for quite some time before it shows up again in the logs.
So it might be the number of users concurrently using the site or certain usage patterns (like mass uploading or editing images) that triggers the problem.

Let me know if you find anything to reproduce it. I never saw this neither locally nor on our own site or demo.

Hello,

I also upgraded to 1.5.4 recently.
Errors are still present.
I upgraded php 7.1 to 7.2 -> same problem.

Problem is only with admin pages, albums settings.

I did several tests, problem only occurs on albums containing many pictures.
No mysqli errors with "small" albums".

I can reproduce the problem, so, if you want more info ...

Please post any info you can find. As mention I never saw this error and on my local install for instance I have lots of albums anad images for testing.

Simply said: If a mysql host, say, allows only 10 connections at the same time and you have 11 visitors at the time, that will exceed since every visitor has its own instance. According to the docs the default values is normally 151.
https://dev.mysql.com/doc/refman/8.0/en/too-many-connections.html (same for 5.x versions generally)

So if that is the case this is a server limitation we cannot do anything against. Even adding the probably missing checks discussed above will not help here then.

I only get mysqli errors with albums administration, otherwise, no mysql errors (never)

Starting point : I empty the debug log

I enter the albums page :

I check the logs :

I enter Nouveautés album :

I check the logs :

Nouveautés is a dynamic album.
With 1.5.1, no mysqli errors, only few pictures added since 1.5.3 and recently 1.5.4 upgrade.

O2switch is my host.

Here is the setting of this dynamic album :

words=*&searchfields=title&inalbums=0&inimages=1&unpublished=0

I also get errors with some other albums.
But most of them do not generate mysqli errors.

Please note I do not even have a look to images sub-page.

And displaying an admin album page that creates a mysqli problem is quite long, server seems busy.
I have to wait several seconds.
I do not have to wait so long with albums that do not generate mysqli errors until page is entirely displayed.

Two questions:

1. Do you have the search cache enabled on Options > Search? If you have large dynamic albums this is strongely recommended for performance. Dynamic albums are "saved searches" and therefore always more overhead than "real physical" albums.

2. Turn off Options > Gallery > Gallery behaviour > Show all subalbum thumbs as this may cause overhead as these have to be collected.

3. Turn off Options > Gallery > Gallery behaviour >Visual thumb selection. This will not work in all browser anyway as not all can display thumbs in the selector. And of course if these are loads of images that are even not cached yet it will also create unnecessary overhead. (This selector will be reworked in the future anyway).

I can only again tell that I never saw these and I have a large dynamic test album locally (contain hundreds of images as it just searches for all "jpg" files). Dynamic album handling to my memory did not change since 1.5.1 and even earlier at all.

Hello acrylian,

Some answers :

1 -> no, search cache was already disabled.
2 -> solved my problem : you are fantastic, it's not easy to guess what can be wrong with others.
3 -> I followed your advise and turned it off

Now, there is no delay when opening any admin album page.

I probably missed something when I enabled those settings one year ago !

Thank you very much.

Glad something worked out for you!

1 -> no, search cache was already disabled.

Actually I recommend to enable the search cache if you use dynamic albums extensively. It should help performance as the search queries do not need to be performed every time.

Regarding 2 I have to look if that is default or not. It does not hurt if you have a few images and few album levels. But the more you have the more it costs. As mentioned we will rework this selector completely which should solve this and 3 in one go.

Also could you two please check the functions-db-MySQLI.php file in the support build (will add PDO later if needed and the mysql lib is deprecated anyway…). I added a check so it does not re-connect if the global var is already set (it's either set or not, there are not several on one instance). I could not reproduce the issue so far but perhaps it helps…