Multiple Database Users Contribution

[Guest]

Problem:

 

High traffic volume results in 'max_questions' limit reached which for my host is '50000' per user.

 

Solution:

 

The ability to set into the configuration multiple database users to split the load on the database, most hosts will limit the number of questions per user to prevent database crash.

 

For those who are having this problem as I am, I recommend you set different database users for your catalog and for your admin. This will help a bit, and if you are doing a bunch of updates it won't affect customers viewing your site if you do reach the 'max_questions' for that user. Generally, the problem occurs for me when I have the website scanned for vulnerabilities. Or when a robot is indexing the site, I have had these errors occur in these situations.

 

I believe that this is a fairly simple contribution for someone with a bit more php knowledge than me.

 

Thank You,

 

PsiliPharm

[Guest]

Well, for all those who are interested. I have figured out how to do this myself, even with my limited knowledge of PHP it wasn't that hard. So, here it is...

 

First I edited the catalog/includes/configure.php by adding:

 

// define our database connection
 define('DB_SERVER', 'localhost'); // eg, localhost - should not be empty for productive servers
 define('DB_SERVER_USERNAME', 'db_username');
 define('DB_SERVER_USERNAME1', 'db_username1');
 define('DB_SERVER_USERNAME2', 'db_username2');
 define('DB_SERVER_USERNAME3', 'db_username3);
 define('DB_SERVER_PASSWORD', 'db_pass');
 define('DB_DATABASE', 'db_name');
 define('USE_PCONNECT', 'false'); // use persistent connections?
 define('STORE_SESSIONS', 'mysql'); // leave empty '' for default handler or set to 'mysql'

 

You may add as many database users as you wish, I left the default variable to make sure that other contributions such as SEO will be able to access the database as well, but most connections will be randomized between these other 3 usernames. (Remember to create these users with the same password for all four!)

 

Next, I edited the catalog/includes/functions/database.php by changing function tep_db_connect():

 

  function tep_db_connect($server = DB_SERVER, $username1 = DB_SERVER_USERNAME1, $username2 = DB_SERVER_USERNAME2, $username3 = DB_SERVER_USERNAME3, $password = DB_SERVER_PASSWORD, $database = DB_DATABASE, $link = 'db_link') {
global $$link;
$db_users = array($username1, $username2, $username3);
$username = $db_users[array_rand($db_users)];

if (USE_PCONNECT == 'true') {
  $$link = mysql_pconnect($server, $username, $password);
} else {
  $$link = mysql_connect($server, $username, $password);
}

if ($$link) mysql_select_db($database);

return $$link;
 }

 

This is how I changed the database.php file to randomize connections between multiple users, this will limit the number of queries per hour by each user. As I mentioned before, I also set a different user for the admin than the catalog, to avoid any updates or changes on the admin side affecting the catalog connection. Also, if you are using Froogle, BizRate, Yahoo! Auto Data Feed(s), use the admin username as well, as these scripts use alot of queries. You may also apply the above code changes to the admin with multiple database users for the admin section as well. I may create a contribution for this to provide this for everyone who needs it.

 

Enjoy,

 

PsiliPharm

[boxtel]

Well, for all those who are interested. I have figured out how to do this myself, even with my limited knowledge of PHP it wasn't that hard. So, here it is...

 

First I edited the catalog/includes/configure.php by adding:

 

// define our database connection
 define('DB_SERVER', 'localhost'); // eg, localhost - should not be empty for productive servers
 define('DB_SERVER_USERNAME', 'db_username');
 define('DB_SERVER_USERNAME1', 'db_username1');
 define('DB_SERVER_USERNAME2', 'db_username2');
 define('DB_SERVER_USERNAME3', 'db_username3);
 define('DB_SERVER_PASSWORD', 'db_pass');
 define('DB_DATABASE', 'db_name');
 define('USE_PCONNECT', 'false'); // use persistent connections?
 define('STORE_SESSIONS', 'mysql'); // leave empty '' for default handler or set to 'mysql'

 

You may add as many database users as you wish, I left the default variable to make sure that other contributions such as SEO will be able to access the database as well, but most connections will be randomized between these other 3 usernames. (Remember to create these users with the same password for all four!)

 

Next, I edited the catalog/includes/functions/database.php by changing function tep_db_connect():

 

  function tep_db_connect($server = DB_SERVER, $username1 = DB_SERVER_USERNAME1, $username2 = DB_SERVER_USERNAME2, $username3 = DB_SERVER_USERNAME3, $password = DB_SERVER_PASSWORD, $database = DB_DATABASE, $link = 'db_link') {
global $$link;
$db_users = array($username1, $username2, $username3);
$username = $db_users[array_rand($db_users)];

if (USE_PCONNECT == 'true') {
  $$link = mysql_pconnect($server, $username, $password);
} else {
  $$link = mysql_connect($server, $username, $password);
}

if ($$link) mysql_select_db($database);

return $$link;
 }

 

This is how I changed the database.php file to randomize connections between multiple users, this will limit the number of queries per hour by each user. As I mentioned before, I also set a different user for the admin than the catalog, to avoid any updates or changes on the admin side affecting the catalog connection. Also, if you are using Froogle, BizRate, Yahoo! Auto Data Feed(s), use the admin username as well, as these scripts use alot of queries. You may also apply the above code changes to the admin with multiple database users for the admin section as well. I may create a contribution for this to provide this for everyone who needs it.

 

Enjoy,

 

PsiliPharm

 

I would do a random define in configure.php and leave the function alone.

[Guest]

I would do a random define in configure.php and leave the function alone.

I will try that... but I'm not sure how to define it there. I did notice that the orders did not show up correctly and no confirmation e-mails went out after orders were placed.

[Guest]

Nevermind, I figured out how to do it with the following in the configure.php:

 

$db_users = array(DB_SERVER_USERNAME1, DB_SERVER_USERNAME2, DB_SERVER_USERNAME3, DB_SERVER_USERNAME4, DB_SERVER_USERNAME5, DB_SERVER_USERNAME6, DB_SERVER_USERNAME7, DB_SERVER_USERNAME8, DB_SERVER_USERNAME9);
 define('DB_SERVER_USERNAME',$db_user = $db_users[array_rand($db_users)]);

 

And removed the function changes, but I am still not getting orders or order confirmation e-mails.

 

Oh and I commented out the original define username.

[Guest]

If I could figure out how to use just one user for the order process that would be fine since most of the queries would be split among many users.

[Guest]

If I could figure out how to use just one user for the order process that would be fine since most of the queries would be split among many users.

I really need help with this one!! I haven't been able to figure it out, I've been racking my brain!!

 

Please, if anybody has any ideas, let me know what they are!!

 

Thanks,

 

PsiliPharm

[Guest]

NOBODY IS WILLING TO HELP ME?!?!?

[Guest]

Please, somebody help me with this one!!

[esearing]

Have you tried changing the user when the error occurs? Not sure of the syntax but it would look something like...

 

if errno= 1226 {

define('DB_SERVER_USERNAME', 'db_username1');

define('DB_SERVER_PASSWORD', 'password1');

} else {

define('DB_SERVER_USERNAME', 'db_username0');

define('DB_SERVER_PASSWORD', 'password0');

}

This is just a variation of what you were doing so it may not be any better. I wonder if you have separate passwords for each user preventing emails from sending?

 

 

also try modifying your robots.txt file on your home page and set the robots to off in admin - Configure - Sessions

[esearing]

This Works! Thanks to PsiliPharm.

 

Create additional users in yor mySQL database.

(if using ipowerwebs vdeck, select hostManger - databases - setup)

 

in catalog/includes/config.php

 

replace or comment out:

define('DB_SERVER_USERNAME', 'user1');

 

with:

$db_users = array('user1','user2', 'user3');

define('DB_SERVER_USERNAME',$db_user = $db_users[array_rand($db_users)]);

 

Save and test.

 

I did not have email issues once I turned on allow email in configuration email options.

[MarcoZorro]

Surely a better option would be to either

 

a) Get a webhost that doesnt limit the number of queries or

B) Fix the code so that the number of queries is reduced

 

Both of these seem much more logical than the mess you are attempting :thumbsup: