I have a small problem connecting to a mysql database using perl scripts
and the DBI module. When I try to issue a DBI->connect() from within the
script, I get an error (it's below). I think the problem is that my mysql
socket file is in a weird place. I get an identical error when I try to
connect from the command line without specifying a socket file location.
The database was compiled from source with the following options:
./configure --prefix=/usr2/db/mysql --localstatedir=/usr2/db/mysql/data
--with-unix-socket-path=/usr2/db/mysql
It's running on a Pentium II 300 and Red Hat 5.2 (kernel 2.0.36).
For some reason, the socket file likes to live in the directory just above
where I told it to be.
Here's my test script (nothing mysterious here -- it came out of the DBI
perldoc):
#!/usr/bin/perl -w
use DBI;
$user ="root";
$pass="testpass";
$database="testdb";
$dsn = "DBI:mysql:$database";
$dbh = DBI->connect ($dsn,$user,$pass,'');
if ( !defined $dbh ) {
die "Cannot connect to MySQL server: $DBI::errstr\n";
}
#Disconnect from the db
$dbh->disconnect;
Here's what I get when I run it:
# ./testdb.pl
Can't connect to local MySQL server (21) at ./testdb.pl line 9
Cannot connect to MySQL server: Can't connect to local MySQL server (21)
Now, here's the interesting part. When I saw the above error, I was
scratching my head for a while. I've been using the mysql monitor for a
few days on the new db with no troubles (I've already put in a few records
by hand, selected stuff a few times, etc.). But one time when I was
connecting from the command line, I forgot to specify the socket file, like
so (I'm in /usr2/db/mysql):
# bin/mysql --user=root -p
Enter password: testpass
ERROR 2002: Can't connect to local MySQL server (21)
And the error looked very familiar. But connecting normally works fine:
(again in /usr2/db/mysql):
# bin/mysql --socket=/usr2/db/mysqld.sock --user=root -p
Enter password: testpass
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 3.22.21
Type 'help' for help.
mysql> quit
Here are my mysql variables (from `libexec/mysqld variables`):
basedir: /usr2/db/mysql/
datadir: /usr2/db/
tmpdir: /tmp/
language: /usr2/db/mysql/share/mysql/english/
pid file: /usr2/db/lazlo.qualcomm.com.pid
TCP port: 3306
Unix socket: /usr2/db/mysqld.sock
system locking is not in use
Possible variables for option --set-variable (-O) are:
back_log current value: 5
connect_timeout current value: 5
delayed_insert_timeout current value: 300
delayed_insert_limit current value: 100
flush_time current value: 0
join_buffer current value: 131072
key_buffer current value: 8388600
long_query_time current value: 10
max_allowed_packet current value: 1048576
max_connections current value: 100
max_connect_errors current value: 10
max_delayed_insert_threads current value: 20
delayed_queue_size current value: 1000
max_join_size current value: 4294967295
max_sort_length current value: 1024
net_buffer_length current value: 16384
record_buffer current value: 131072
sort_buffer current value: 2097144
table_cache current value: 64
tmp_table_size current value: 1048576
thread_stack current value: 65536
wait_timeout current value: 28800
And I've read the DBI perldoc, various man pages, newsgroups, mailing list
archives, and anything else I could find, but I can't for the life of me
figure out how to specify a socket file from within the DBI connect()
call. In fact, I'm only guessing that doing so will solve my troubles (it
seems to me that since specifying it from a command line works, doing so
from within a perl script will work as well). I've even tried using a
shell script to see if it works, and it does -- as long as I specify a
socket file location.
Ideally, I'd have the mysql socket file and data and such in locations such
that I could connect without having to specify a socket file location
(because it's getting to be a pain). But I can't figure out how to do
this. I don't know why the compile didn't set those options, except for
maybe that a previous install used the locations thigns are currently in
(is there a mysql.conf file somewhere that I can use to override these
values?). I've tried starting mysqld with custom options, but it doesn't
seem to take (and I can't start the db with safe_mysqld either).
Does anyone have a fix for me? Pointers to a place that has a fix? I'm a
bit new to mysql (as you can no doubt tell), so even a good tutorial might
help (although I've read a few of the most populars ones already).
I'm totally baffled (and I have a deadline approaching soon --
naturally). I'd really appreciate any help. Thanks in advance!
-B
Bill Rhodes
x16002 AA-202M
