Access denied in remote mysql connection

[wes.tibular]

Forgive me if this is not the appropriate forum for this question.

I have installed DBI, and DBD::mysqlpp (couln't get DBD::mysql to
compile) on my system (with the requisite Net::MySql). When i connect
to my local mysql database, things are fine.

When i attempt to connect to a remote mysql database, i get:

DBI connect('database=ossinv;host=remotehost','username',...) failed:
Access denied for user: '@localhost' (Using password: NO) at /usr/
perl5/site_perl/5.8.4/DBD/mysqlPP.pm line 109
at ./test2.pl line 9
cannot open ossinv: at ./test2.pl line 9.

The relevant code is:

#!/usr/bin/perl
#
# test.pl
#
#

use DBI;

$dbh_OSS = DBI->connect
('DBI:mysqlPP:database=ossinv;host=remotehost','username','password')
or die "cannot open ossinv:$!";
$SQL = "select samid, turnover_complete from iso where host like
'%73odsp%'";
# print "$SQL<BR>\n\n";
$sth_UE = $dbh_UE->prepare($SQL);
$sth_UE->execute();

while ((@row) = $sth_UE->fetchrow_array) {
print "$row[0]:\n";
foreach $item (@row) {
print "$item\t";
}
print "\n";
}

If i use mysql itself (mysql -h remotehost -u username) it connects
just fine, so i know the username and password work across my network.

I have verified that the username and password are being passed to
mysqlpp, which passes it in turn to Net:MySql, but the remote server
does not seem to receive either the username (Access denied for user:
'@localhost' ) or the password (Using password: NO). This same code
opens a local database just fine (with appropriate changes to the
host, username, and password values).

Aside from the -w, and strict stuff, what am i missing?

 

[Tim Greer]

Forgive me if this is not the appropriate forum for this question.

I have installed DBI, and DBD::mysqlpp (couln't get DBD::mysql to
compile) on my system (with the requisite Net::MySql). When i connect
to my local mysql database, things are fine.

When i attempt to connect to a remote mysql database, i get:

DBI connect('database=ossinv;host=remotehost','username',...) failed:
Access denied for user: '@localhost' (Using password: NO) at /usr/

That seems to indicate there was no username passed. Also, it says
"localhost", yet you said this fails when you try to connect remotely.
Localhost is local (not remote), so your problem and question are
confusing. Anyway, if the script connects fine locally, then it's
simply a matter of passing the right, existing host and database names
and login credentials, which probably isn't a Perl related question.

 

[wes.tibular]

That seems to indicate there was no username passed. Also, it says
"localhost", yet you said this fails when you try to connect remotely.
Localhost is local (not remote), so your problem and question are
confusing.  

@localhost is a redacted host name. it is what the remote connection
is returning in the error message

Anyway, if the script connects fine locally, then it's
simply a matter of passing the right, existing host and database names
and login credentials, which probably isn't a Perl related question.

It's a question about what the perl modules DBD::mysqlpp and
Net::Mysql are doing that leads it to not pass what i give them. In
the code, the username and password are being passed to mysqlpp, and i
have verified that Net:Mysql gets them, I have no control over what
happens from there, but it seems that the module is dropping it. Was
hoping to hear from someone using these modules that has seen the
problem before and can clue me in.

Thanks for the response.

 

[Tim Greer]

@localhost is a redacted host name. it is what the remote connection
is returning in the error message

Be sure to paste the actual error message, or are you saying this very
literally was, showing @localhost with no username? That just doesn't
seem right is all.

It's a question about what the perl modules DBD::mysqlpp and
Net::Mysql are doing that leads it to not pass what i give them. In
the code, the username and password are being passed to mysqlpp, and i
have verified that Net:Mysql gets them, I have no control over what
happens from there, but it seems that the module is dropping it. Was
hoping to hear from someone using these modules that has seen the
problem before and can clue me in.

Thanks for the response.

Try using RaiseError, such as:

my $dbh = DBI->connect("dbi:mysqlPP:database=$db;host=$host",
$user, $password, { RaiseError => 1 }
);

That example, such as the one you posted as trying, seems to be correct.
If you can connect using mysql on the command line, then it should
work, provided it uses the default port (3306), if it's something else,
just pass the port= value.

Examples from the docs:

my $dsn = "dbi:mysqlPP:$database";
my $dsn = "dbi:mysqlPP:database=$database;host=$hostname";
my $dsn = "dbi:mysqlPP:database=$database;host=$hostname;port=$port";

my $dbh = DBI->connect($dsn, $user, $password);


So:

my $dbh = DBI->connect($dsn, $user, $password, { RaiseError => 1 });

Just to confirm, you can definitely connect from the command line with:

mysql -hHOST -uUSER database -pPASSWORD

from that system to the remote server?

<Also, please don't quote signatures, especially below your own text and
at the end of the post>

 

[wes.tibular]

Be sure to paste the actual error message, or are you saying this very
literally was, showing @localhost with no username?  That just doesn't
seem right is all.

Thank you for your patience. I have to redact certain information,
but probably should have used '@example.com' instead of '@localhost'
to avoid misinterpretation.

That said, yes, the actual error message said "Access denied for user:
'@example.com' " with no username. It appears that something between
the sample program and the network scrubbed the username and
password. I verified it was getting as far as into Net::Mysql, but
could not follow past that.

And, yes, that doesn't seem right to me either.

Try using RaiseError, such as:

my $dbh = DBI->connect("dbi:mysqlPP:database=$db;host=$host",
   $user, $password, { RaiseError => 1 }
);

Sadly, this did not provide any different result than previous
attemtps without RaiseError

That example, such as the one you posted as trying, seems to be correct.
If you can connect using mysql on the command line, then it should
work, provided it uses the default port (3306), if it's something else,
just pass the port= value.

Examples from the docs:

my $dsn = "dbi:mysqlPP:$database";
my $dsn = "dbi:mysqlPP:database=$database;host=$hostname";
my $dsn = "dbi:mysqlPP:database=$database;host=$hostname;port=$port";

my $dbh = DBI->connect($dsn, $user, $password);

So:

my $dbh = DBI->connect($dsn, $user, $password, { RaiseError => 1 });

Just to confirm, you can definitely connect from the command line with:

mysql -hHOST -uUSER database -pPASSWORD

from that system to the remote server?

Yes. I can confirm that.



Thanks Tim. I will keep working on it.

 

[Xho Jingleheimerschmidt]

Thank you for your patience. I have to redact certain information,
but probably should have used '@example.com' instead of '@localhost'
to avoid misinterpretation.

That said, yes, the actual error message said "Access denied for user:
'@example.com' " with no username. It appears that something between
the sample program and the network scrubbed the username and
password. I verified it was getting as far as into Net::Mysql, but
could not follow past that.

mysql has a rather bizarre permissions system. If the mysql.user table
has an entry for both wildcard username at specified host
(%@example.com) and a specified username at wildcard host (user@%), it
could that it thinks you are trying to log you on as %@example.com even
though it told it to use a specific username. Since the password you
are supplying is for user@%, not for %@example.com, it gives an
incorrect password error.

Xho