Problems with Net::MySQL...

[chriswaltham]

I'm trying to port a web-based application from a Solaris 2.6 box to
Mac OS X Server 10.4.8. The Solaris box was running perl 5.6.1 and
MySQL 4.0.18, the OS X box is running perl 5.6.2 and MySQL 4.0.18. Both
are running Net::MySQL 0.009. The trouble is, the Solaris box will
connect successfully to the MySQL DB but the OS X box will not.

FYI, I installed perl-5.6.2 on OS X in /usr/local/perl in order to
preserve the install that comes with the OS itself.

This is the code:

#!/usr/bin/perl -w
use Net::MySQL;

my $mysql = Net::MySQL->new(
database => 'mysql',
user => 'root',
password => 'secret'
);

# SLECT example
$mysql->query(q{SELECT * FROM user});
my $record_set = $mysql->create_record_iterator;
while (my $record = $record_set->each) {
printf "First column: %s Next column: %s\n",
$record->[0], $record->[1];

}

$mysql->close;

And this is the error it yields:

[root@csc-web2 admin]# perl test.pl
Access denied for user: '@localhost' to database '!' at test.pl line 5

I am pretty new to perl, so I'm not quite sure how to debug this. The
username and password are definitely correct, but the most troubling
thing is that the error message says that the perl file is trying to
connect to the database named '!', not the database named 'mysql'.
Anyone have any ideas on how I can debug this?

Thanks,

Chris

 

[Michael]

I'm trying to port a web-based application from a Solaris 2.6 box to
Mac OS X Server 10.4.8. The Solaris box was running perl 5.6.1 and
MySQL 4.0.18, the OS X box is running perl 5.6.2 and MySQL 4.0.18. Both
are running Net::MySQL 0.009. The trouble is, the Solaris box will
connect successfully to the MySQL DB but the OS X box will not.

FYI, I installed perl-5.6.2 on OS X in /usr/local/perl in order to
preserve the install that comes with the OS itself.

This is the code:

#!/usr/bin/perl -w
use Net::MySQL;

my $mysql = Net::MySQL->new(
database => 'mysql',
user => 'root',
password => 'secret'
);

# SLECT example
$mysql->query(q{SELECT * FROM user});
my $record_set = $mysql->create_record_iterator;
while (my $record = $record_set->each) {
printf "First column: %s Next column: %s\n",
$record->[0], $record->[1];

}$mysql->close;

And this is the error it yields:

[root@csc-web2 admin]# perl test.pl
Access denied for user: '@localhost' to database '!' at test.pl line 5

I am pretty new to perl, so I'm not quite sure how to debug this. The
username and password are definitely correct, but the most troubling
thing is that the error message says that the perl file is trying to
connect to the database named '!', not the database named 'mysql'.
Anyone have any ideas on how I can debug this?

Are you certain that the database server is up and running and can be
accessed, independently of your script?

You should be able to type the following in a terminal window and get
access to your database:
mysql -u root -p

Does the mysql user "root" have permission to read the tables? I always
try to do what I need on the command line first and then translate that
into Perl -- the error messages from the command line are slightly less
cryptic

Regards,
Michael
http://www.perlcircus.org/

 

[chriswaltham]

Are you certain that the database server is up and running and can be
accessed, independently of your script?

You should be able to type the following in a terminal window and get
access to your database:
mysql -u root -p

Does the mysql user "root" have permission to read the tables? I always
try to do what I need on the command line first and then translate that
into Perl -- the error messages from the command line are slightly less
cryptic

Yep, I can successfully execute the command from the MySQL console with
the same credentials, just not using the Net::MySQL script :-(


Chris

 

[Michael]

Yep, I can successfully execute the command from the MySQL console with
the same credentials, just not using the Net::MySQL script :-(

hmmmm, okay, but that error message is weird. Why is it saying your
username is '' and your database is '!' right? You say you're using a
perl installed under local but you shebang is "/usr/bin/perl". Are you
sure you're using the perl you think you are?

Try this (in a terminal) and see if it makes any difference:
# /usr/local/perl test.pl

Regards,
Michael

 

[chriswaltham]

hmmmm, okay, but that error message is weird. Why is it saying your
username is '' and your database is '!' right? You say you're using a
perl installed under local but you shebang is "/usr/bin/perl". Are you
sure you're using the perl you think you are?

But that's the thing -- in my test.pl I define the username as root,
the password as secret and the database as mysql. Yet when I run the
script, it gets confused and doesn't know what database it's supposed
to use... :-\

Try this (in a terminal) and see if it makes any difference:
# /usr/local/perl test.pl

Sorry, the shebang is really /usr/local/perl/bin/perl (which is 5.6.2).
The OS ships with 5.8.6 which is installed in /usr (thus it's shebang
is /usr/bin/perl). I have tried installing Net::MySQL in both, with no
success.


Chris

 

[Michael]

the password as secret and the database as mysql. Yet when I run the

(Another stab in the dark here From the Net::MySQL docs:

unixsocket - Path of the UNIX socket where MySQL daemon. default is
/tmp/mysql.sock.
Supposing hostname is omitted, it will connect by UNIX Socket.

Since you are not specifying a hostname, it appears that Net::MySQL is
trying to access /tmp/mysql.sock? Does that file exist? You could
eliminate this possibility by specifying a hostname:

my $mysql = Net::MySQL->new(
hostname => '127.0.0.1',
database => 'mysql',
user => 'root',
password => 'secret'
);

But I'm no expert in Net::MySQL. I've always used DBD::MySQL via the
DBI interface. That looks like this:

use DBI;

$dbname = "mysql";
$dbhostname = "localhost";
$dbport = 3306;

$dsn = "DBI:mysql:$dbname:$dbhostname:$dbport";

$dbuser = "root";
$dbpassword = "secret";

$dbh = DBI->connect($dsn, $dbuser, $dbpassword);

Maybe your problems will go away with a different module? The DBD
modules are very stable and I've never had any problem with them on my
Mac. At least you might get a better error message if it still isn't
working! Just a suggestion.

Regards,
Michael

 

[chriswaltham]

(Another stab in the dark here From the Net::MySQL docs:

unixsocket - Path of the UNIX socket where MySQL daemon. default is
/tmp/mysql.sock.
Supposing hostname is omitted, it will connect by UNIX Socket.

Yep, /tmp/mysql.sock definitely exists:

srwxrwxrwx 1 mysql wheel 0 Nov 13 10:51 /tmp/mysql.sock=

Since you are not specifying a hostname, it appears that Net::MySQL is
trying to access /tmp/mysql.sock? Does that file exist? You could
eliminate this possibility by specifying a hostname:

my $mysql = Net::MySQL->new(
hostname => '127.0.0.1',
database => 'mysql',
user => 'root',
password => 'secret'
);

I've tried using the IP address of the box, using 127.0.0.1, using
localhost, and just using the socket. They all fail :-(

But I'm no expert in Net::MySQL. I've always used DBD::MySQL via the
DBI interface. That looks like this:

use DBI;

$dbname = "mysql";
$dbhostname = "localhost";
$dbport = 3306;

$dsn = "DBI:mysql:$dbname:$dbhostname:$dbport";

$dbuser = "root";
$dbpassword = "secret";

$dbh = DBI->connect($dsn, $dbuser, $dbpassword);

Maybe your problems will go away with a different module? The DBD
modules are very stable and I've never had any problem with them on my
Mac. At least you might get a better error message if it still isn't
working! Just a suggestion.

I wouldn't mind using DBD, but unfortunately this module is pretty
large & I don't think I know enough perl to go through it & rewrite all
the calls from Net::MySQL to DBD::mysql...


Chris

 

[chriswaltham]

Well I see it, but I don't believe it!

I removed Net::MySQL 0.09 and replaced it with Net::MySQL 0.08. And
everything works now.

[root@csc-web2 admin]# /usr/local/perl/bin/perl test.pl
First column: localhost Next column: root
First column: csc-web2.pressherald.com Next column: root
First column: localhost Next column:
First column: csc-web2.pressherald.com Next column:
First column: localhost Next column: cscuser
First column: 172.16.1.179 Next column: cscuser
First column: * Next column: cscuser

*sigh*

I'll try email the author of the module (again) to see what he thinks.
He never replied when I was trying to get it started in the first
place. Thanks for your help, Michael!


Chris

 

[Michael]

I wouldn't mind using DBD, but unfortunately this module is pretty
large & I don't think I know enough perl to go through it & rewrite all
the calls from Net::MySQL to DBD::mysql...

If you can use the same credentials to successfully access the database
via the mysql CLI, then logically the problem must be in the module,
right? Simplifying the problem a bit: what do you get when you enter
the absolute minimal script possible in the terminal (should be all on
one line with no breaks)?

/usr/bin/perl -MNet::MySQL -e'print
Net::MySQL->new(database=>"mysql",user=>"root",password=>"secret");'

 

[Michael]

Well I see it, but I don't believe it!

I removed Net::MySQL 0.09 and replaced it with Net::MySQL 0.08. And
everything works now.

Heh. Would you believe that was going to be the very next thing I
suggested? ;-) Anyway, I'm just glad you got it going finally. I hate
to hear of anyone trying to move to the Most Sweet Mac platform and
having problems.

Regards,
Michael

 

[chriswaltham]

Heh. Would you believe that was going to be the very next thing I
suggested? ;-) Anyway, I'm just glad you got it going finally. I hate
to hear of anyone trying to move to the Most Sweet Mac platform and
having problems.

Regards,
Michael

Thanks again for your help, Michael. Yep, I'm a big Mac fan, so it was
great to get this one solved! I was beginning to tear my hair out. Now
I just have to make sure it works against perl-5.8.6, as well as the
webserver built into OS X Server (apache 2.x?).

I'll try the script you pasted with both Net::MySQL 0.08 and 0.09, and
send both to the maintainer. I think it's a bug somewhere.
Interestingly enough, the script failed completely when connecting to a
4.x or 5.0 MySQL server, which limits where we can go in the future...


Chris

 

[Mark Clements]

Thanks again for your help, Michael. Yep, I'm a big Mac fan, so it was
great to get this one solved! I was beginning to tear my hair out. Now
I just have to make sure it works against perl-5.8.6, as well as the
webserver built into OS X Server (apache 2.x?).

I'll try the script you pasted with both Net::MySQL 0.08 and 0.09, and
send both to the maintainer. I think it's a bug somewhere.
Interestingly enough, the script failed completely when connecting to a
4.x or 5.0 MySQL server, which limits where we can go in the future...

Does this bug seem relevant to the original problem?

#20160: Cannot connect to a Mysql 3.23.47 in Net::MySQL 0.09

https://rt.cpan.org/Ticket/Display.html?id=20160

There is a comment:

You need to keep working with version 0.08.
0.09 is updated using the new algorithme which is needed from mysql4.1
onwards.

Mark