Authentication failing in Perl DBI script

resetreset · 11-18-2008, 01:26 AM

Hi all,
I'm trying to access a MySQL database within a Perl program, which goes like this:

#! /usr/bin/perl

use DBI ;
use strict ;

use CGI ;
my ($cgi) = new CGI ;

my ($dsn)="DBI:mysql:dr:localhost";
my ($user_name) = "fubar" ;
my ($password) = "secret" ;
my ($dbh,$sth) ;
my (@ary) ;

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

, and on MySQL, at the command prompt, I've typed this:

grant all on *.* to fubar identified by "secret" ;

However when I run the program, I get this error:
DBI connect('dr:localhost','fubar',...) failed: Access denied for user 'fubar'@'localhost' (using password: YES) at ./test.cgi line 19

Can someone help me out?

Thanks.

chrism01 · 11-18-2008, 01:51 AM

When you grant access, you need to specify a host: http://dev.mysql.com/doc/refman/5.0/en/grant.html

resetreset · 11-18-2008, 01:54 AM

But if I don't specify one, my MySQL book tells me that it's supposed to take "localhost" by default - and the error message seems to corroborate this.......?

chrism01 · 11-18-2008, 06:22 PM

My std sub looks like this

Code:

#! /usr/bin/perl -w

use DBI ;
use strict ;

sub db_connect
{
    my (
        $db,    # database to connect to
        $dsn,   # data source name
        );

    # Create data source string and connect ...
    $dsn = "DBI:mysql:".
           "database=".$cfg::params{'DB_NAME'}.";".
           "host=".$cfg::params{'DB_HOST'}.";".
           "port=".$cfg::params{'DB_PORT'};
    $cfg::dbh = DBI->connect( $dsn, $cfg::params{'DB_USER'},
                                $cfg::params{'DB_PASSWD'},
                                {RaiseError => 0, AutoCommit => 1} );

    if( $DBI::errstr)
    {
        exit_with_error($DBI::errstr);
    }
}

note that the delimiters are colon ( : ) and semi-colon ( ; ) in different places. You've only used colons.
you may want to RaiseError => 1. I always handle all the erroys 'manually'/programmatically inside the code, as my progs run without user interaction, hence my exit_with_error() sub.
I've always used host specifier, but as you say, re-reading the link, it appears its not strictly necessary, although it is for me on production systems.
You 'might' need to flush privileges : http://dev.mysql.com/doc/refman/5.0/en/flush.html
Probably also a good idea to check the grant: http://dev.mysql.com/doc/refman/5.0/en/show-grants.html

resetreset · 11-19-2008, 09:01 AM

Hey I put in the "localhost" and it works! Thanks a lot!