Home› General support

mysqli errors

ctdlg Member
in General support

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 ?

Comments

  • ctdlg Member
    edited June 2019

    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 !

  • acrylian Administrator, Developer

    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.

  • acrylian Administrator, Developer

    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.

  • ctdlg Member

    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.

  • acrylian Administrator, Developer

    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

  • Tobias Member

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

    WARNING: mysqli_connect(): (HY000/1203): User <user_id> already has more than 'max_user_connections' active connections in <zenphoto_path>/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 <user_id> already has more than 'max_user_connections' active connections in <zenphoto_path>/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 <user_id> already has more than 'max_user_connections' active connections in <zenphoto_path>/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.

  • acrylian Administrator, Developer

    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.

  • Tobias Member

    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.

  • acrylian Administrator, Developer
    edited June 2019

    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.

  • acrylian Administrator, Developer
    edited June 2019

    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.

  • Tobias Member

    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.

  • acrylian Administrator, Developer

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

  • ctdlg Member

    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 ...

  • acrylian Administrator, Developer

    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.

  • ctdlg Member

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

    Starting point : I empty the debug log
    logs

    I enter the albums page :

    albums

    I check the logs :

    logs

    I enter Nouveautés album :

    Nouveautes

    I check the logs :

    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.

  • ctdlg Member

    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.

  • acrylian Administrator, Developer

    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.

  • ctdlg Member

    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.

  • acrylian Administrator, Developer
    edited June 2019

    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…

  • Tobias Member

    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.

    I checked and our MySQL server allows 151 connections. So that does not seem to be the issue.

    Also we do not use dynamic albums as of now and both options you mentioned are disabled.

  • acrylian Administrator, Developer

    Or do you have that many visitors that you manage 151 at one time actually? If the traffic load exceeds the server capability we can of course not do much ;-)

    But regardless please try the file above…

  • MarkRH Member

    Oh yeah.. I get that max_user_connections error when viewing the sub-albums of an album that had 23 albums in it. My max_user_connections value is set to 15 on my server. Seems related to the thumbnail generation as on the first view, a couple are not created.

    In my case Options > Gallery > Gallery behaviour > Show all subalbum thumbs was already unchecked. I'll have to install 1.5.4 and go from there. I do have "Cache processing worker limit" set to 3.

  • acrylian Administrator, Developer

    @MarkRH Please also try the change in the support build to see if that helps. But 15 is really pretty low, isn't it?

  • acrylian Administrator, Developer

    We meanwhile found at least why the issue seems new to you. It was supressed via @ formerly so it was already there but you just didn't get to see it.

  • Tobias Member
    edited July 2019

    I have tested the support build since June 23rd now and the error did not occur again on my site.

    That being said I found one very similar warning message in the debug log:

    {19018:Thu, 11 Jul 2019 22:09:41 GMT}
WARNING: mysqli_connect(): (08004/1040): Too many connections in <zenphoto_path>/zp-core/functions-db-MySQLi.php on line 30
mysqli_connect called from db_connect (functions-db-MySQLi.php [30])
from require_once (functions-basic.php [185])
from require_once (functions.php [18])
from include (index.php [14])
from index.php [44]

    Edit: This might very well be some hiccup on the hoster end. So unless someone else experiences similar warnings, please don't overrate this.

  • acrylian Administrator, Developer
    edited July 2019

    I would really good to know when on what page this happens. As said I cannot reproduce this neither locally nor on our own live sites.

    Did you limit the image processor concurrency on the option?

  • Tobias Member

    I'll keep an eye on it, but currently I don't have clue either.

Sign In or Register to comment.