Multiple database connections

This is an archive of the phpBB 2.0.x support forum. Support for phpBB2 has now ended.
Forum rules
Following phpBB2's EoL, this forum is now archived for reference purposes only.
Please see the following announcement for more information: viewtopic.php?f=14&t=1385785
Locked
nfs
Registered User
Posts: 49
Joined: Mon May 16, 2005 10:04 pm

Multiple database connections

Post by nfs »

The search facility seems to be timing out on me, so I guess I'll just ask the world.

I administer a small board (up 13 months, ~900 users, ~45000 messages so far) and database size is getting to be an issue. Yes, it's the wordmatch table that's the problem and, no, given what I've read here, I don't think I want to install full text searching at the moment.

The problem arises because the host I'm using has set an arbitrary limit of 100Mb on database size (and we're north of 70Mb at the moment, going up about 2Mb a week). I can have 100 databases, i.e. I can use up to 10Gb in total, but only cut up into chunks.

Should I change hosts? Probably, but that's a hassle too.

So, in the spirit of exploring kludges, and because I have 9.9Gb available to me - just not in very usable form - I'd like to know whether anyone has a clever solution that will let me shift wordmatch into another database, connecting one instance of phpBB to both the original database - which keeps the other 30 or so tables - and the new database - wordmatch only - simultaneously.

Alternatively, how about a seamless way of shifting from one database to another depending on which forum is in view?

P.S. This is not an April Fools joke. I'd like some good suggestions or pointers.
User avatar
Lumpy Burgertushie
Registered User
Posts: 68175
Joined: Mon May 02, 2005 3:11 am
Contact:

Post by Lumpy Burgertushie »

something like that might be possilbe. it would take someone much smarter than me to figure out how ( not very hard to find that person :lol: )

anyway, I would check with your host and see if you have a total of 100 MB of database space to use with up to 10 databases or if it is like you say, 10 100 MB databases .


anyway, yes, I would consider a new host, that is a very small limit. most hosts allow you a set amount of space for your web hosting account, and that includes your web site and your database(s).

I have never had an account in over 10 years onlin, that limited my database space separate from my web site space.
I'm baaaaaccckkkk. still doing work on donation basis. PM your needs.

Premium phpBB 3.3 Styles by PlanetStyles.net

If nobody is in the forest, does a tree really fall?
nfs
Registered User
Posts: 49
Joined: Mon May 16, 2005 10:04 pm

Post by nfs »

Well, it turned out to be simpler than I thought it would be.
  • 4 added lines in config.php to define parameters for the second DB
  • 4 added lines in includes/db.php to make the second connection
  • minor changes in search.php and includes/functions_search.php to pick the right connection for queries against SEARCH_WORD and SEARCH_MATCH
It works like a dream. It splits the space usage almost perfectly in half.

It's probably bought me a year. Maybe by then the host will have come to their senses.

8)
User avatar
gussie
Registered User
Posts: 92
Joined: Wed Nov 27, 2002 1:26 am

Post by gussie »

nfs wrote: Well, it turned out to be simpler than I thought it would be.
  • 4 added lines in config.php to define parameters for the second DB
  • 4 added lines in includes/db.php to make the second connection
  • minor changes in search.php and includes/functions_search.php to pick the right connection for queries against SEARCH_WORD and SEARCH_MATCH
It works like a dream. It splits the space usage almost perfectly in half.

It's probably bought me a year. Maybe by then the host will have come to their senses.

8)

Would you share with us what kind of code we should use to make this happen?

Cheers
Res tantum valet quantum vendi potest
Do not hire Christian Bullock - he will not finish the job and he will not return your money.
espicom
Registered User
Posts: 17905
Joined: Wed Dec 22, 2004 1:14 am
Location: Woodstock, IL

Post by espicom »

If the databases can as the same user, you do not have to deal with forcing PHP to make separate connections. You can assign tables to different databases, and have PHPBB work with them, by adjusting constants.php to include the database name as part of the "address" of the table.

NOTE: I have tested NONE of this directly, but have used similar code in other projects. The code presented below may contain bugs. Back up all files before doing ANY modifications and testing. Never test on a live system. This code is presented only as a guide to implementing something silly like this... Use at your own risk. Your mileage will vary. Tax, title, and Doc fees not included. No photons were harmed in the preduction of this code, but many electrons were seriously inconvenienced.

Let's make some changes to a typical config.php script, that starts out like this:

Code: Select all

<?php


// phpBB 2.x auto-generated config file
// Do not change anything in this file!

$dbms = 'mysql';

$dbhost = '192.168.1.2';
$dbname = 'my_database';
$dbuser = 'my_username';
$dbpasswd = 'my_password';

$table_prefix = 'phpbb_';

define('PHPBB_INSTALLED', true);

?>
Let's add an array to hold database names that we have available to us, all of which must be under the control of "my_username":

Code: Select all

<?php


// phpBB 2.x auto-generated config file
// Do not change anything in this file!

$dbms = 'mysql';

$dbhost = '192.168.1.2';
$dbname = 'DataBase1';
$dbuser = 'my_username';
$dbpasswd = 'my_password';

$table_prefix = 'phpbb_';
$db_array = array( 
	'auth_access' => "DataBase1", 'user_group' => "DataBase1", 'groups' => "DataBase1", 'banlist' => "DataBase1", 
	'categories' => "DataBase1", 'config' => "DataBase1", 'confirm' => "DataBase1", 'disallow' => "DataBase1",
	'forum_prune' => "DataBase1", 'forums' => "DataBase1", 'posts' => "DataBase2", 'posts_text' => "DataBase2", 
	'privmsgs' => "DataBase2", 'privmsgs_text' => "DataBase2", 'ranks' => "DataBase1", 'search_results' => "DataBase3", 
	'search_wordlist' => "DataBase3", 'search_wordmatch' => "DataBase3", 'sessions' => "DataBase1", 
	'sessions_keys' => "DataBase1", 'smilies' => "DataBase1", 'themes' => "DataBase1", 'themes_name' => "DataBase1", 
	'topics' => "DataBase2", 'topics_watch' => "DataBase2", 'users' => "DataBase1", 'vote_desc' => "DataBase2", 
	'vote_results' => "DataBase2", 'vote_voters' => "DataBase2", 'words' => "DataBase3");

define('PHPBB_INSTALLED', true);

?>
I've got three databases: "DataBase1" has the "configuration" information, and the users. "DataBase2" contains the topics, posts, and related tables. "DataBase3" has all the search stuff.

constants.php is the other place to make changes. The tables section would look like this:

Code: Select all

// Table names
define('CONFIRM_TABLE', $db_array['confirm']. '.' . $table_prefix .'confirm');
define('AUTH_ACCESS_TABLE', $db_array['auth_access']. '.' . $table_prefix .'auth_access');
define('BANLIST_TABLE', $db_array['banlist']. '.' . $table_prefix .'banlist');
define('CATEGORIES_TABLE', $db_array['categories']. '.' . $table_prefix .'categories');
define('CONFIG_TABLE', $db_array['config']. '.' . $table_prefix .'config');
define('DISALLOW_TABLE', $db_array['disallow']. '.' . $table_prefix .'disallow');
define('FORUMS_TABLE', $db_array['forums']. '.' . $table_prefix .'forums');
define('GROUPS_TABLE', $db_array['groups']. '.' . $table_prefix .'groups');
define('POSTS_TABLE', $db_array['posts']. '.' . $table_prefix .'posts');
define('POSTS_TEXT_TABLE', $db_array['posts_text']. '.' . $table_prefix .'posts_text');
define('PRIVMSGS_TABLE', $db_array['privmsgs']. '.' . $table_prefix .'privmsgs');
define('PRIVMSGS_TEXT_TABLE', $db_array['privmsgs_text']. '.' . $table_prefix .'privmsgs_text');
define('PRIVMSGS_IGNORE_TABLE', $db_array['privmsgs_ignore']. '.' . $table_prefix .'privmsgs_ignore');
define('PRUNE_TABLE', $db_array['forum_prune']. '.' . $table_prefix .'forum_prune');
define('RANKS_TABLE', $db_array['ranks']. '.' . $table_prefix .'ranks');
define('SEARCH_TABLE', $db_array['search_results']. '.' . $table_prefix .'search_results');
define('SEARCH_WORD_TABLE', $db_array['search_wordlist']. '.' . $table_prefix .'search_wordlist');
define('SEARCH_MATCH_TABLE', $db_array['search_wordmatch']. '.' . $table_prefix .'search_wordmatch');
define('SESSIONS_TABLE', $db_array['sessions']. '.' . $table_prefix .'sessions');
define('SESSIONS_KEYS_TABLE', $db_array['sessions_keys']. '.' . $table_prefix .'sessions_keys');
define('SMILIES_TABLE', $db_array['smilies']. '.' . $table_prefix .'smilies');
define('THEMES_TABLE', $db_array['themes']. '.' . $table_prefix .'themes');
define('THEMES_NAME_TABLE', $db_array['themes_name']. '.' . $table_prefix .'themes_name');
define('TOPICS_TABLE', $db_array['topics']. '.' . $table_prefix .'topics');
define('TOPICS_WATCH_TABLE', $db_array['topics_watch']. '.' . $table_prefix .'topics_watch');
define('USER_GROUP_TABLE', $db_array['user_group']. '.' . $table_prefix .'user_group');
define('USERS_TABLE', $db_array['users']. '.' . $table_prefix .'users');
define('WORDS_TABLE', $db_array['words']. '.' . $table_prefix .'words');
define('VOTE_DESC_TABLE', $db_array['vote_desc']. '.' . $table_prefix .'vote_desc');
define('VOTE_RESULTS_TABLE', $db_array['vote_results']. '.' . $table_prefix .'vote_results');
define('VOTE_USERS_TABLE', $db_array['vote_voters']. '.' . $table_prefix .'vote_voters');
Yes, it's complex. I would have done the defines differently, but I was trying to keep things in the same general format as PHPBB coding standards. What this will present to the SQL queries is a database.table addressing format, so queries of the users table would become (in our sample) "SELECT * FROM DataBase1.phpbb_users" instead of the standard "SELECT * FROM phpbb_users".

If the different databases have to have different users, then yes, it gets to be a mess that requires more than one database connection.
Jeff
Fixing 1016/1030/1034 Errors | (obsolete link) | MySQL 4.1/5.x Client Error | phpBBv2 Logo in ACP
Support requests via PM are ignored!
"To be fully alive is to feel that everything is possible." - Eric Hoffer
User avatar
gussie
Registered User
Posts: 92
Joined: Wed Nov 27, 2002 1:26 am

Post by gussie »

Awesome. Thank you for the quick reply :)
Res tantum valet quantum vendi potest
Do not hire Christian Bullock - he will not finish the job and he will not return your money.
espicom
Registered User
Posts: 17905
Joined: Wed Dec 22, 2004 1:14 am
Location: Woodstock, IL

Post by espicom »

gussie wrote: Awesome. Thank you for the quick reply :)


The advantage of years of practice in code re-use and a good editor.... :wink:
Jeff
Fixing 1016/1030/1034 Errors | (obsolete link) | MySQL 4.1/5.x Client Error | phpBBv2 Logo in ACP
Support requests via PM are ignored!
"To be fully alive is to feel that everything is possible." - Eric Hoffer
nfs
Registered User
Posts: 49
Joined: Mon May 16, 2005 10:04 pm

Post by nfs »

gussie wrote:
nfs wrote:Well, it turned out to be simpler than I thought it would be.
  • 4 added lines in config.php to define parameters for the second DB
  • 4 added lines in includes/db.php to make the second connection
  • minor changes in search.php and includes/functions_search.php to pick the right connection for queries against SEARCH_WORD and SEARCH_MATCH
It works like a dream. It splits the space usage almost perfectly in half.

It's probably bought me a year. Maybe by then the host will have come to their senses.

8)

Would you share with us what kind of code we should use to make this happen?

Cheers

config.php

Code: Select all

Add four lines

$wmhost = ...;
$wmname = ...;
$wmuser = ...;
$wmpasswd = ...;
includes/db.php

Code: Select all

Add just before end of file:

// Make a second database connection for wordlist and wordmatch tables.
$wmdb = new sql_db($wmhost, $wmuser, $wmpasswd, $wmname, false);
if (!$wmdb->db_connect_id)
{
  message_die(CRITICAL_ERROR, "Could not connect to the database");
}
In search.php (sorry about this diff format, I'm in a rush)

Code: Select all

<                                                                       AND w.word_common <> 1";
---
>                                                                       AND w.word_common <> 1";
>                                                       $which_db = $wmdb;

316c342,343
<                                                               WHERE " . $search_match;
---
>                                                               WHERE " . $search_match;
>                                                       $which_db = $db;
319c345
<                                               if ( !($result = $db->sql_query($sql)) )
---
>                                               if ( !($result = $which_db->sql_query($sql)) )
325c351
<                                               while( $temp_row = $db->sql_fetchrow($result) )
---
>                                               while( $temp_row = $which_db->sql_fetchrow($result) )
357c383
<                                               $db->sql_freeresult($result);
---
>                                               $which_db->sql_freeresult($result);

In includes/functions_search.php:

Code: Select all

107c107
<       global $db, $phpbb_root_path, $board_config, $lang;
---
>       global $db, $wmdb, $phpbb_root_path, $board_config, $lang;
170c170
<                               if ( !($result = $db->sql_query($sql)) )
---
>                               if ( !($result = $wmdb->sql_query($sql)) )
175c175
<                               while ( $row = $db->sql_fetchrow($result) )
---
>                               while ( $row = $wmdb->sql_fetchrow($result) )
207c207
<                                               if( !$db->sql_query($sql) )
---
>                                               if( !$wmdb->sql_query($sql) )
232c232
<                       if ( !$db->sql_query($sql) )
---
>                       if ( !$wmdb->sql_query($sql) )
249c249
<                       if ( !$db->sql_query($sql) )
---
>                       if ( !$wmdb->sql_query($sql) )
269c269
<       global $db;
---
>       global $db, $wmdb;
307c307
<               if ( !($result = $db->sql_query($sql)) )
---
>               if ( !($result = $wmdb->sql_query($sql)) )
313c313
<               while ( $row = $db->sql_fetchrow($result) )
---
>               while ( $row = $wmdb->sql_fetchrow($result) )
317c317
<               $db->sql_freeresult($result);
---
>               $wmdb->sql_freeresult($result);
324c324
<                       if ( !$db->sql_query($sql) )
---
>                       if ( !$wmdb->sql_query($sql) )
331c331
<                       if ( !$db->sql_query($sql) )
---
>                       if ( !$wmdb->sql_query($sql) )
343c343
<       global $db;
---
>       global $wmdb;
355c355
<                       if ( $result = $db->sql_query($sql) )
---
>                       if ( $result = $wmdb->sql_query($sql) )
358c358
<                               while ( $row = $db->sql_fetchrow($result) )
---
>                               while ( $row = $wmdb->sql_fetchrow($result) )
368c368
<                               if ( $result = $db->sql_query($sql) )
---
>                               if ( $result = $wmdb->sql_query($sql) )
371c371
<                                       while ( $row = $db->sql_fetchrow($result) )
---
>                                       while ( $row = $wmdb->sql_fetchrow($result) )
380c380
<                                               if ( !$db->sql_query($sql) )
---
>                                               if ( !$wmdb->sql_query($sql) )
385c385
<                                               $words_removed = $db->sql_affectedrows();
---
>                                               $words_removed = $wmdb->sql_affectedrows();
405c405
<                       if ( !$db->sql_query($sql) )
---
>                       if ( !$wmdb->sql_query($sql) )
410c410
<                       $words_removed = $db->sql_affectedrows();
---
>                       $words_removed = $wmdb->sql_affectedrows();
417c417
<       if ( !$db->sql_query($sql) )
---
>       if ( !$wmdb->sql_query($sql) )
498c498
Locked

Return to “2.0.x Support Forum”