Connecting to remote db with DBI

dana has asked for the wisdom of the Perl Monks concerning the following question:

Hi,

I'm trying to connect to a MySQL database that is located on a server, but I think I am having problems with the connect statement. The program appears to hang (see output; I have to kill the process), so I turned on tracing. My code:

use strict;
use warnings;
use DBI;

my $db = 'homo_sapiens_core_42_';
my $host = '192.168.1.200';
my $user = 'xx';
my $pass = 'yy';

DBI->trace(1);
print "HERE\n";

my $dbh = DBI->connect("DBI:mysql:$db;host=$host", 
                       $user, $pass, { RaiseError => 1 } ) or
   die ( "Couldn't connect to database: " . DBI->errstr );

print "DONE\n";
[download]

The output:

    DBI 1.54-ithread default trace level set to 0x0/1 (pid 25943) at q
+uery_ensembl.pl line 23
HERE
    -> DBI->connect(DBI:mysql:homo_sapiens_core_42_;host=192.168.1.200
+, xx, ****, HASH(0x9fc8c28))
    -> DBI->install_driver(mysql) for linux perl=5.008005 pid=25943 ru
+id=56 euid=56
       install_driver: DBD::mysql version 4.004 loaded from /usr/lib/p
+erl5/site_perl/5.8.5/i386-linux-thread-m
ulti/DBD/mysql.pm
    <- install_driver= DBI::dr=HASH(0xa063d18)
    !! warn: 0 CLEARED by call to connect method
[download]

I looked through the DBI documentation and several Tutorials (DBI recipes, The fine art of database programming, Reading from a database, and Tricks with DBI), but I couldn't find anything that seemed to address this issue. I also tried several google searches.

I can connect to the database via the command line, so I don't think it is a problem with the server or the database itself.

$ /usr/local/mysql-standard-5.0.27-linux-i686-glibc23/bin/mysql -u xx 
+-h HOST -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3100 to server version: 5.0.27-standard
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>
mysql> use homo_sapiens_core_42_
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed

[deleted test select query]

SELECT query successful.
[download]

Your wisdom and insights are appreciated. Thanks.

Comment on Connecting to remote db with DBI Select or Download Code

Replies are listed 'Best First'.
Re: Connecting to remote db with DBI by ferreira (Chaplain) on Mar 23, 2007 at 20:02 UTC
It looks like you're making a mistake when building the DSN: constrast yours (`"DBI:mysql:$db;host=$host"`) and the one provided in the documentation of DBD::mysql: `use DBI; $dsn = "DBI:mysql:database=$database;host=$hostname;port=$port";` [download] You're missing the `"database="` before `$db`.	[reply] [d/l] [select]
Re^2: Connecting to remote db with DBI by dana (Monk) on Mar 23, 2007 at 21:16 UTC
Thank you. I needed the 'database=' in the statement. I also had to use the hostname rather than the IP address, however this may be a network issue. My final connection code looks like the following: my $dsn = "DBI:mysql:database=$db;host=$host"; my $dbh = DBI->connect( $dsn, $user, $pass, { RaiseError => 1 }) or die ( "Couldn't connect to database: " . DBI->errstr );	[reply]
Re^3: Connecting to remote db with DBI by Anonymous Monk on May 20, 2015 at 21:21 UTC
Worked the first try !!	[reply]
Re: Connecting to remote db with DBI by dragonchild (Archbishop) on Mar 23, 2007 at 20:00 UTC
`my $dbh = DBI->connect("DBI:mysql:database=$db;host=$host", $user, $pass, { RaiseError => 1 } ) or die ( "Couldn't connect to database: " . DBI->errstr );` [download] My criteria for good software: Does it work? Can someone else come in, make a change, and be reasonably certain no bugs were introduced?	[reply] [d/l]
Ojiwa Janga-Kenya: Connecting to remote db with DBI by Anonymous Monk on May 14, 2007 at 11:39 UTC
Hi Perl forks Your problem is inside the DBI. Your DBD is corrupted or incomplete. Install perl DBD for mysql. I assure you this works.	[reply]


laziness, impatience, and hubris
	PerlMonks