Hello everyone,
First I would like to apologise for this long mail but I really need
some advices on a web application I am currently working on. This
application makes extensive use of DBI and interacts with many
databases at the same time.
I will try to resume my setup the best way I can, English is not my
native language so I hope everyone will understand me well even if my
English is not perfect :P
Server:
Apache 1.3.29 + mod_perl 1.29 + Mason 1.26
Perl 5.85 / DBI 1.45 + DBD::mysql-2.9004 + Apache::DBI 0.94
This is a multi-client web application, each client can have more than
one user, there is a 'master' database which is global to everyone and
contain among other things a 'users' table used to store credentials
of the users.
Here is the complex part: each client has 2 databases
('client-clientname' and 'archives-clientname') which are specific to
the client. So basically, each client has access to three databases,
the master and the 2 client specific database.
At the moment, I use a global hash to aggregate all my dbh, and I am
not sure if this is the right way to do it. The master dbh is
initiated in my handler.pl, while the two client specific database
connections are initiated after the user log-in to the web
application.
Here are the relevant portion of my handler.pl
use Apache::DBI;
use DBI;
â
# DSN declaration
my $dsn = "DBI:$db_type:host=127.0.0.1;dbname=master";
# Debug mode
$Apache::DBI::DEBUG = 2;
# Connect to master database
Apache::DBI->connect_on_init( $dsn, 'db_user', 'db_pass' ) or die
"Cant connect to master database: $DBI::errstr!\n";
#Apache::DBI->setPingTimeOut( $dsn, 0 );
sub handler
{
# Get the Apache request object
my $r = shift;
# Only handle text
return -1 if $r->content_type and $r->content_type !~ m|^text/|i;
# (re)-Connect to the master database
$HTML::Mason::Commands::dbh{'master'} = DBI->connect($dsn, 'db_user',
'db_pass') or die "Cannot connect to master database:
$DBI::errstr!\n";
# Return to the browser
return $ah->handle_request($r);
}
The application is used by the users to move data between is two
client specific databases.
Here an example:
1. A user log-in, the credentials are taken from the 'master' database
and the user is authenticated, I use Apache::Session::MySQL and
cookies to store session information (user_id, client_id and othersâ).
2. If the user is authenticated, I initiate a connection to the
client-specific databases based on the value of client_id. User now
have access to the three databases, all dbh are stored in a global
%dbh hash.
3. User move data between is two table, (clientdb => archivesdb).
This setup is working but it is giving me some problems:
Since the Apache::DBI module don't share dbh between the apache child
processes, each user might end up using a total of 11 connections! One
for the master and five for each client specific databases (my setup
use 5 apache child processes, the apache default).
The Apache::DBI recycle the 'master' dbh without problem but a new
connection is initiated to each client specific database at every user
request, thus not recycling it. I have seen a patch
(http://mathforum.org/epigone/modperl/stroblangnerm) for Apache::DBI
but I am not sure if I should use it.
Here is a sanitized sample of my error_log with $Apache::DBI::DEBUG =
2 in my handler.pl :
16629 Apache::DBI new connect to
'host=127.0.0.1;dbname=masterdb_userdb_passAutoCommit=1PrintError=1Username=db_user'
20332 Apache::DBI new connect to
'host=127.0.0.1;dbname=masterdb_userdb_passAutoCommit=1PrintError=1Username=db_user'
29081 Apache::DBI new connect to
'host=127.0.0.1;dbname=masterdb_userdb_passAutoCommit=1PrintError=1Username=db_user'
12403 Apache::DBI new connect to
'host=127.0.0.1;dbname=masterdb_userdb_passAutoCommit=1PrintError=1Username=db_user'
7125 Apache::DBI new connect to
'host=127.0.0.1;dbname=masterdb_userdb_passAutoCommit=1PrintError=1Username=db_user'
24346 Apache::DBI new connect to
'host=127.0.0.1;dbname=masterdb_userdb_passAutoCommit=1PrintError=1Username=db_user'
20332 Apache::DBI already connected to
'host=127.0.0.1;dbname=masterdb_userdb_passAutoCommit=1PrintError=1Username=db_user'
20332 Apache::DBI already connected to
'host=127.0.0.1;dbname=masterdb_userdb_passAutoCommit=1PrintError=1Username=db_user'
20332 Apache::DBI already connected to
'host=127.0.0.1;dbname=masterdb_userdb_passAutoCommit=1PrintError=1Username=db_user'
20332 Apache::DBI new connect to
'host=127.0.0.1;dbname=client-client1db_userdb_passAutoCommit=1PrintError=1Username=db_user'
20332 Apache::DBI new connect to
'host=127.0.0.1;dbname=archives-client1db_userdb_passAutoCommit=1PrintError=1Username=db_user'
20332 Apache::DBI already connected to
'host=127.0.0.1;dbname=masterdb_userdb_passAutoCommit=1PrintError=1Username=db_user'
26657 Apache::DBI new connect to
'host=127.0.0.1;dbname=masterdb_userdb_passAutoCommit=1PrintError=1Username=db_user'
20332 Apache::DBI already connected to
'host=127.0.0.1;dbname=masterdb_userdb_passAutoCommit=1PrintError=1Username=db_user'
20332 Apache::DBI already connected to
'host=127.0.0.1;dbname=client-client1db_userdb_passAutoCommit=1PrintError=1Username=db_user'
20332 Apache::DBI already connected to
'host=127.0.0.1;dbname=archives-client1db_userdb_passAutoCommit=1PrintError=1Username=db_user'
29081 Apache::DBI already connected to
'host=127.0.0.1;dbname=masterdb_userdb_passAutoCommit=1PrintError=1Username=db_user'
29081 Apache::DBI already connected to
'host=127.0.0.1;dbname=masterdb_userdb_passAutoCommit=1PrintError=1Username=db_user'
29081 Apache::DBI new connect to
'host=127.0.0.1;dbname=client-client1db_userdb_passAutoCommit=1PrintError=1Username=db_user'
29081 Apache::DBI new connect to
'host=127.0.0.1;dbname=archives-client1db_userdb_passAutoCommit=1PrintError=1Username=db_user'
20332 Apache::DBI already connected to
'host=127.0.0.1;dbname=masterdb_userdb_passAutoCommit=1PrintError=1Username=db_user'
29081 Apache::DBI already connected to
'host=127.0.0.1;dbname=masterdb_userdb_passAutoCommit=1PrintError=1Username=db_user'
29081 Apache::DBI already connected to
'host=127.0.0.1;dbname=client-client1db_userdb_passAutoCommit=1PrintError=1Username=db_user'
29081 Apache::DBI already connected to
'host=127.0.0.1;dbname=archives-client1db_userdb_passAutoCommit=1PrintError=1Username=db_user'
20332 Apache::DBI already connected to
'host=127.0.0.1;dbname=masterdb_userdb_passAutoCommit=1PrintError=1Username=db_user'
So basically I am wondering if I should modify my application behaviour.
I know that with DBI I can initiate a connection to the server instead
of a database if I don't specify a tablename in my DSN and modify my
code to use SQL query like the following:
SELECT * FROM database.table â
Instead of specifying a database in my DSN (like I do now) and using
query like this:
SELECT * FROM table â
This approach could also allow me to move data between my databases in
a more 'sexy' way using the new mysql 4.1 'INSERT â SELECT' syntax,
like this:
INSERT INTO archives-clientname.table (*) SELECT * FROM client-clientname.table
Since this would require a lot of change to my code, I am wondering if
I am heading in the right direction or if there a better solution
(modyfing my Apache::DBI setup or application behaviour). I am also
wondering if modifying my code to connect directly to the server and
use database.table syntax would cause security issues since it would
require me to use a mysql user who has access to all databases.
Any idea, tips, pointers or recommendations will be much appreciated ï
It is the first multi-databases application I am writing and I am
still learning!
And thanks in advance !
Ps: I apologise for my not-so-perfect English, if something is not
clear or if I need to provide more information's about my setup, just
tell me.
