Page 1 of 2 12 LastLast
Results 1 to 10 of 19
  1. #1
    Join Date
    May 2007
    Posts
    124
    Plugin Contributions
    0

    Default User already has more than 'max_user_connections

    ZC-1.5.5e, PHP 5.6.32, Database patch level 1.5.5

    I get this log error once daily(
    User already has more than 'max_user_connections)
    I spoke with my Server Host, My max db user connections is set at 50 per User. My understanding is that it is based on a concurrent basis. So there would have to be >= 50 db connections within milliseconds.
    I read the previous posts regarding 'Rotating db users' .

    *********************************************************
    Code:
    // Enter your 4 database usernames here.
    // If you have only two, then repeat them: first, second, first, second
     $dbuser[] = 'username1';
     $db_pwd[] = 'password1';
     
     $dbuser[] = 'username2';
     $db_pwd[] = 'password1';
     
     $dbuser[] = 'username3';
     $db_pwd[] = 'password1';
     
     $dbuser[] = 'username4';
     $db_pwd[] = 'password1';
    
    
    // NO NEED TO DO ANYTHING IN THIS SECTION:
     // calculate the current "minute"
     $current_minute = date('i');
     // calculate which "quarter of the hour" we're in
     $db_user_active = (int)($current_minute/15);
     define('DB_SERVER_USERNAME', $dbuser[$db_user_active]);
     define('DB_SERVER_PASSWORD', $db_pwd[$db_user_active]);
     //echo $db_user_active; 
    
    *************************************************************

    Based on the above code it seems that this, 50 or greater simultaneous db connections log error, would still occur. since each different db user would still
    be the designated user for 15 minutes. if the concurrency occurred at the second the switch to the next user the max could be increased to 100 theoretically.
    After doing some searching on this, I came up with the following code :

    ************************************************************

    Code:
    $dbusers = array(
    array('user' => 'mysql_username_1', 'password' => 'mysql_password_1') // First MySQL user/password combination
    , array('user' => 'mysql_username_2', 'password' => 'mysql_password_2') // Second MySQL user/password combination
    , array('user' => 'mysql_username_3', 'password' => 'mysql_password_3') // Third MySQL user/password combination
    );
    $mysql_user = $dbusers[rand(0, count($dbusers) - 1)];
    $config['MasterServer']['username'] = $mysql_user['user'];
    $config['MasterServer']['password'] = $mysql_user['password'];
    ****************************************************************
    The No. of db users could be increased beyond 3


    Everytime a page is opened, 1 of the defined username/password combinations will be choosen at random, and by this reducing the number of connections for each user.

    My questions are:
    1) Will this code work?
    If it does work this could eliminate those max user connections.





  2. #2
    Join Date
    May 2007
    Posts
    124
    Plugin Contributions
    0

    Default Re: User already has more than 'max_user_connections

    To rotate randomly, I'm trying the following:
    Code:
    $dbusers = array(
    array('user' => 'mysql_username_1', 'password' => 'mysql_password_1') // First MySQL user/password combination
    , array('user' => 'mysql_username_2', 'password' => 'mysql_password_2') // Second MySQL user/password combination
    , array('user' => 'mysql_username_3', 'password' => 'mysql_password_3') // Third MySQL user/password combination
    );
    $mysql_user = $dbusers[rand(0, count($dbusers) - 1)];
    define('DB_SERVER_USERNAME', $mysql_user['user'];
    define('DB_SERVER_PASSWORD', $mysql_user['password'];

  3. #3
    Join Date
    Jan 2004
    Posts
    66,407
    Blog Entries
    7
    Plugin Contributions
    81

    Default Re: User already has more than 'max_user_connections

    That sort of concept can work.

    But the real root issue is that you've outgrown your server's (or the hosting plan's) capabilities.
    Now is the time to investigate a server without such limits.
    .

    Zen Cart - putting the dream of business ownership within reach of anyone!
    Donate to: DrByte directly or to the Zen Cart team as a whole

    Remember: Any code suggestions you see here are merely suggestions. You assume full responsibility for your use of any such suggestions, including any impact ANY alterations you make to your site may have on your PCI compliance.
    Furthermore, any advice you see here about PCI matters is merely an opinion, and should not be relied upon as "official". Official PCI information should be obtained from the PCI Security Council directly or from one of their authorized Assessors.

  4. #4
    Join Date
    Jul 2012
    Posts
    16,778
    Plugin Contributions
    17

    Default Re: User already has more than 'max_user_connections

    Further, while practicing this will reduce the clashing of multiple user connections (though certainly 50 does seem to be a small number), one should try to look at what is causing that to occur from the onset...

    There could be a poorly written script that could be causing the issue of using all of the connections. This could just be helping it run a little longer or by using more server resources.
    ZC Installation/Maintenance Support <- Site
    Contribution for contributions welcome...

  5. #5
    Join Date
    May 2007
    Posts
    124
    Plugin Contributions
    0

    Default Re: User already has more than 'max_user_connections

    Thanks for the input.
    As far as figuring out what the cause is;

    i've been recently inundated with this Romanian bot (100's of sessions daily): IP Address 185.206.81.10Host: 185.206.81.10 every day.

    The following are a few sessions from the User Tracker Mod:


    Code:
    Session ID	 User Shopping Cart 
    Guest, hsa8l0af3rficsevqvbu4mqr35DeleteView	
    Empty Cart
    Click Count:	1	
    Start Time:	19:12:41	Idle Time:	00:10:23
    End Time:	19:12:41	Total Time:	00:00:00
    Country:	Romania Romania
    IP Address:	185.206.81.10
    Host:	185.206.81.10
    Originating URL:	/mysite/toys/tv_movie_and_character_toys?zenid=6i3ouklnofd5ogsbcg7cpgcbn4&pid=04 
    
    
    Session ID	 User Shopping Cart 
    Guest, gad0nrause6vcpe9572nnl0gm0DeleteView	
    Empty Cart
    Click Count:	1	
    Start Time:	19:08:10	Idle Time:	00:14:54
    End Time:	19:08:10	Total Time:	00:00:00
    Country:	Romania Romania
    IP Address:	185.206.81.10
    Host:	185.206.81.10
    Originating URL:	/mysite/?amp%3Bamp%3Bamp%3Bamp%3Bamp%3Bmanufacturers_id=68&amp%3Bamp%3Bamp%3Bamp%3Bamp%3Bzenid=6i3ouklnofd5ogsbcg7cpgcbn4&review=362 
    
    
    Session ID	 User Shopping Cart 
    Guest, e55qdtfv75t141snvnd9c8kcn7DeleteView	
    Empty Cart
    Click Count:	1	
    Start Time:	18:51:02	Idle Time:	00:32:02
    End Time:	18:51:02	Total Time:	00:00:00
    Country:	Romania Romania
    IP Address:	185.206.81.10
    Host:	185.206.81.10
    Originating URL:	/mysite/?amp%3Bamp%3Bamp%3Bamp%3Bamp%3Bamp%3Bamp%3Bamp%3Bmanufacturers_id=68&amp%3Bamp%3Bamp%3Bamp%3Bamp%3Bamp%3Bamp%3Bamp%3Bzenid=6i3ouklnofd5ogsbcg7cpgcbn4&pid=108 
    
    
    Session ID	 User Shopping Cart 
    Guest, nsifem6betp9r9kopua5si8e52DeleteView	
    Empty Cart
    Click Count:	1	
    Start Time:	18:45:51	Idle Time:	00:37:13
    End Time:	18:45:51	Total Time:	00:00:00
    Country:	Romania Romania
    IP Address:	185.206.81.10
    Host:	185.206.81.10
    Originating URL:	/mysite/?amp%3Bamp%3Bamp%3Bamp%3Bamp%3Bamp%3Bmanufacturers_id=68&amp%3Bamp%3Bamp%3Bamp%3Bamp%3Bamp%3Bzenid=6i3ouklnofd5ogsbcg7cpgcbn4&con=74

    Could this Bot be responsible for the DB hits over 50?
    How do you know if this is a Good bot or Bad one?
    Could the 'Bad Bot Block' mod help in this instance?

    Any help would be appreciated

  6. #6
    Join Date
    Jan 2004
    Posts
    66,407
    Blog Entries
    7
    Plugin Contributions
    81

    Default Re: User already has more than 'max_user_connections

    Even if it's a good bot, if you're not expecting customers from the audience that that bot's search-index-results will normally serve, then it's prudent to block it.

    You can google several things:
    - lists of good and bad bots
    - using robots.txt rules to block (friendly) bots that you wish to not index your site

    And you can use a firewall or a plugin to do the blocking.
    ie: if the undesired bot is always visiting from a single (or group of) IP address, the most efficient way is to use the server firewall to block it, cuz then it never even hits your site. One can also use some hosting control panels to block undesired IP addresses (in the background it drops those IPs into a .htaccess file).
    Using a plugin won't reduce db connections because the hits will actually get to your store and the store will have to do the ignoring after the db lookup.
    .

    Zen Cart - putting the dream of business ownership within reach of anyone!
    Donate to: DrByte directly or to the Zen Cart team as a whole

    Remember: Any code suggestions you see here are merely suggestions. You assume full responsibility for your use of any such suggestions, including any impact ANY alterations you make to your site may have on your PCI compliance.
    Furthermore, any advice you see here about PCI matters is merely an opinion, and should not be relied upon as "official". Official PCI information should be obtained from the PCI Security Council directly or from one of their authorized Assessors.

  7. #7
    Join Date
    May 2007
    Posts
    124
    Plugin Contributions
    0

    Default Re: User already has more than 'max_user_connections

    Many thanks
    The Hosting Control Panel 'IP Address Deny Manager'
    is a great way denying a range of IP addresses.

  8. #8
    Join Date
    Aug 2007
    Location
    Gijón, Asturias, Spain
    Posts
    2,636
    Plugin Contributions
    30

    Default Re: User already has more than 'max_user_connections

    Just moved hosts and have discovered the new one has max users set to 25 (old hosting was 0/unlimited).
    I assume this is very low indeed...indicating a server squeezed to the max.

    Testing locally all is fine for 2 and above so I assume scripts are ok.
    Steve
    github.com/torvista: Spanish Language Pack, Google reCaptcha, Structured Data, Multiple Copy-Move-Delete, Image Checker, BackupMySQL Admin/Auto...

  9. #9
    Join Date
    Aug 2007
    Location
    Gijón, Asturias, Spain
    Posts
    2,636
    Plugin Contributions
    30

    Default Re: User already has more than 'max_user_connections

    I would be interested to know what value others have if they are using shared servers (where there is no user control over this parameter/it's set by the hosting).

    This is shown in the ZC Admin->Tools->Server/Version Info, search for "max_user_connections".
    Steve
    github.com/torvista: Spanish Language Pack, Google reCaptcha, Structured Data, Multiple Copy-Move-Delete, Image Checker, BackupMySQL Admin/Auto...

  10. #10
    Join Date
    Jul 2012
    Posts
    16,778
    Plugin Contributions
    17

    Default Re: User already has more than 'max_user_connections

    Quote Originally Posted by torvista View Post
    I would be interested to know what value others have if they are using shared servers (where there is no user control over this parameter/it's set by the hosting).

    This is shown in the ZC Admin->Tools->Server/Version Info, search for "max_user_connections".
    I've seen 100 on a shared server.
    ZC Installation/Maintenance Support <- Site
    Contribution for contributions welcome...

 

 
Page 1 of 2 12 LastLast

Similar Threads

  1. v154 Concurrent admin access / more than one simultaneous user
    By Haggis in forum Customization from the Admin
    Replies: 2
    Last Post: 1 May 2016, 09:38 PM
  2. Replies: 7
    Last Post: 9 Jul 2015, 10:04 PM
  3. Why have more than one admin user?
    By edwinlloyd in forum Basic Configuration
    Replies: 4
    Last Post: 26 Aug 2010, 11:00 AM
  4. How to set up different shipping options than what UPS has already?
    By bnieukirk in forum Built-in Shipping and Payment Modules
    Replies: 0
    Last Post: 6 Sep 2009, 06:03 AM
  5. Customer bought more than the stock has, how?
    By yellow1912 in forum General Questions
    Replies: 3
    Last Post: 26 Jan 2007, 03:41 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
disjunctive-egg
Zen-Cart, Internet Selling Services, Klamath Falls, OR