Connecting to remote db with DBI

[dana]

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";

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

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.

Your wisdom and insights are appreciated. Thanks.

[ferreira]

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";

You're missing the "database=" before $db.

[dana]

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 );

[Anonymous Monk]

Worked the first try !!

[dragonchild]

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

[Anonymous Monk]

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.