Connecting to mysql using DBI and a non-standard socket

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

I've been bashing my head against this wall for two days and making no progress. I have a mysql database on a local Linux server and I'm trying to connect using DBI. The socket location is nonstandard. Instead of "/var/lib/mysql/mysql.sock", it's "/usr/kasenna/mysql/tmp/mysql.sock".

From the command line, I can connect using DBI:

mysql -S /usr/kasenna/mysql/tmp/mysql.sock -u root --password=password.

When I try to duplicate that using DBI:

my $dbh = DBI->connect('dbi:mysql:database=nemo;socket=/usr/kasenna/mysql/tmp/mysql.sock', 'nemo','password')
or

my %attr;
$attr{socket} = "/usr/kasenna/mysql/tmp/mysql.sock";
my $dbh = DBI->connect('dbi:mysql:nemo', 'nemo', 'password', \%attr);
[download]

I get the following error:
Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

I'm pretty sure that the problem stems from trying to pass the non-standard socket location, but I can't figure out how to pass that in the connect statement.

-Logan
"What do I want? I'm an American. I want more."

Comment on Connecting to mysql using DBI and a non-standard socket Select or Download Code

Replies are listed 'Best First'.
Re: Connecting to mysql using DBI and a non-standard socket by gellyfish (Monsignor) on Jul 05, 2005 at 19:42 UTC
The option is `mysql_socket` so you will want something like: `my $dbh = DBI->connect('dbi:mysql:database=nemo;mysql_socket=/usr/kase +nna/mysql/tmp/mysql.sock', 'nemo','password');` [download] If it's not described in your DBD::mysql manpage then it is likely that the feature isn't supported in the version you have. /J\	[reply] [d/l] [select]
Re^2: Connecting to mysql using DBI and a non-standard socket by logan (Curate) on Jul 06, 2005 at 15:37 UTC
That's the one. I was referring to the socket as `socket` instead of `mysql_socket`. Thanks. -Logan "What do I want? I'm an American. I want more."	[reply] [d/l] [select]
Re: Connecting to mysql using DBI and a non-standard socket by Cody Pendant (Prior) on Jul 05, 2005 at 23:24 UTC
Call me Mr State-the-Obvious, but in your first example which you say works, you're connecting as root, and in the next one which doesn't work, you're connecting as another user. Have we at least checked it the other way around? ($_='kkvvttuu bbooppuuiiffss qqffssmm iibbddllffss') =~y~b-v~a-z~s; print	[reply]
Re: Connecting to mysql using DBI and a non-standard socket by Codon (Friar) on Jul 05, 2005 at 20:04 UTC
Why do you need connect via a the socket file? Presumably your mysql instance is listening for connections on a particular port number (default is 3306). In which case, you could use the command line `mysql -h localhost -P <port> -u root --password=password nemo` [download] which would translate in DBI to `my $dbh = DBI->connect("DBI:mysql:database=nemo;host=localhost;port=$p +ort", 'root', 'password', \%attr);` [download] Ivan Heffner Sr. Software Engineer, DAS Lead WhitePages.com, Inc.	[reply] [d/l] [select]
Re^2: Connecting to mysql using DBI and a non-standard socket by gellyfish (Monsignor) on Jul 05, 2005 at 20:18 UTC
You might not want to have your `mysqld` listening on a TCP socket if there is no need for a client on another host to access the database. It makes for a more secure installation for one thing. /J\	[reply] [d/l]
Re: Connecting to mysql using DBI and a non-standard socket by shiza (Hermit) on Jul 05, 2005 at 20:58 UTC
You could also symlink it: `ln -s /usr/kasenna/mysql/tmp/mysql.sock /var/lib/mysql/mysql.sock` [download] May be a bit of a hack but it works. :)	[reply] [d/l]
Re^2: Connecting to mysql using DBI and a non-standard socket by gellyfish (Monsignor) on Jul 05, 2005 at 21:16 UTC
The chances are that the reason that the `mysqld` uses that location in the first place is that the user it runs as does not have adequate permissions in `/var/lib/mysql` in the first place. /J\	[reply] [d/l] [select]
Re^3: Connecting to mysql using DBI and a non-standard socket by shiza (Hermit) on Jul 05, 2005 at 21:25 UTC
That is true. Although, I have experienced the same situation due to the fact that Fedora(Yum) still installs MySQL 3.23. That's a different issue though. :)	[reply]

Back to Seekers of Perl Wisdom