Remote connection to mysql

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

I'm having some problem connecting to a remote MySQL server. It's on my LAN. I'm just trying to make a simple connection from any host, like such:

use DBI;

my $dbh = DBI->connect(
      'dbi:mysql:tickets@webhost.mydomain.org:3306', 
      'someuser', 'somepass', {
       RaiseError => 1, AutoCommit => 1
   }
);
[download]

I receive identical errors from several host running various version of Perl:

DBI connect('tickets@webhost.mydomain.org:3306','someuser',...) failed
+: Can't connect to MySQL server on '3306' (60) at test9 line 3
[download]

I know the server is configured properly. Port 3306 is open to all hosts on my LAN. I can also access and administer the database with an ODBC GUI (using the same username and password from the script). I've seen many other posts, plus stuff on google, but I'm at a loss. Can someone help? Some extra info:

Server is MySQL 4.1.5 on BSD
Client is Xp with actvestate 5.8.6, DBI DBI 1.48, DBD-mysql 2.9006
[download]

Thanks much.

Comment on Remote connection to mysql Select or Download Code

Replies are listed 'Best First'.
Re: Remote connection to mysql by eXile (Priest) on Apr 23, 2005 at 19:15 UTC
Hi, I haven't seen that syntax before to connect to dbi before (the 'dbi:mysql:tickets@webhost.mydomain.org:3306' part), I normally use something like: `$dsn="DBI:mysql:database=$database;host=$hostname;port=$port"; $dbh=DBI->connect($dsn, $user, $password);` [download] (taken straight from the DBD::mysql manpage)	[reply] [d/l]
Re^2: Remote connection to mysql by mhearse (Chaplain) on Apr 23, 2005 at 19:36 UTC
Thanks for your reply. That works perfectly. I was using the syntax from the DBI man page. I guess the DBD::mysql requires a different syntax. Lesson learned.	[reply]
Re^3: Remote connection to mysql by Joost (Canon) on Apr 23, 2005 at 20:50 UTC
Yes, DBI datasource/connection strings are driver dependent, so you should always look up the syntax in the corresponding DBD::xxx manpage. "What should it profit a man, if he should win a flame war, yet lose his cool?"	[reply]
Re: Remote connection to mysql by bofh_of_oz (Hermit) on Apr 23, 2005 at 19:12 UTC
If you want to do something simple with mySQL, you might want to consider using Net::MySQL module. Here is a snippet from my (working) code: `use Net::MySQL; my $mysql = Net::MySQL->new( hostname => 'localhost', database => 'mytestdb', user => 'mysql', password => 'mysql' ); ... my $SQL = "select * from table1 where field1 = 1"; $mysql->query($SQL);` [download] Hope this helps :) -------------------------------- An idea is not responsible for the people who believe in it...	[reply] [d/l]
Re^2: Remote connection to mysql by BUU (Prior) on Apr 23, 2005 at 20:16 UTC
Why on earth would you want to use this 'Net::Mysql' instead of the extremely well tested, perfectly working, standard module DBI?	[reply]
Re^3: Remote connection to mysql by bofh_of_oz (Hermit) on Apr 23, 2005 at 21:55 UTC
Umm... simply because I was first introduced to Net::MySQL and noone has pointed me towards DBI until now... Judging by the downvotes I got for my post, I guess it's time to fill the gap and familiarize myself with the standard way of doing things :) -------------------------------- An idea is not responsible for the people who believe in it...	[reply]