DBI / mySQL: Access denied for user 'myusername'@'localhost' (using password: YES)

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

When I run this code (assume database, user, pass are defined and correct):

use DBI;
print "Content-Type: text/html\n\n ";
my $dbh = DBI->connect("DBI:mysql:$database", $user, $pass) or print "
+<BR>Couldn't connect to database: " . DBI->errstr;
[download]

I get the following error:

Couldn't connect to database: Access denied for user 'myusername'@'localhost' (using password: YES)

From looking around, I find that this is caused by mySQL updating its password encryption and leaving DBI lagging behind. What I want to know is, is there a way to connect to my mySQL server anyway using DBI? Or should I switch to Mysql.pm (no user input is used in the queries, security isn't a major concern - but I'd need a sample of code to use)? Or do I have to figure out how to get a new version of DBI installed on my hosting account? Usually I'd bypass all the trouble by using PHP (EVIL EVIL FLAME FLAME), but in this case I need a script I can call from shell, so PHP isn't going to cut it. And in case anyone asks, I did try Super Search first - it just turned up a million posts on mySQL, unrelated to my problem.

Comment on DBI / mySQL: Access denied for user 'myusername'@'localhost' (using password: YES) Download Code

Replies are listed 'Best First'.
Re: DBI / mySQL: Access denied for user 'myusername'@'localhost' (using password: YES) by perrin (Chancellor) on Mar 07, 2006 at 05:36 UTC
There's no trick here -- the permissions are setup incorrectly for this user. Try it from the shell. MySQL has a somewhat confusing distinction about users who can connect over a local socket. See the MySQL docs on creating user accounts for more help.	[reply]
Re: DBI / mySQL: Access denied for user 'myusername'@'localhost' (using password: YES) by hesco (Deacon) on Mar 07, 2006 at 09:37 UTC
Although you clearly state that we should "assume database, user, pass are defined and correct," that is not what your error message is telling us. DBI->errstr should return the error from the database engine, itself. Try, from a prompt, the command line client: `$ mysql -u myusername -p` [download] Your grant statement must grant specific privileges on certain tables to a user @ a domain, identified by 'a password'. If you have root on the db engine, you can use show fields from mysql.tablename and select from mysql.* to see what the mysql installation thinks its valid users are. Do these tokens work at a cli client? -- Hugh	[reply] [d/l]
Re: DBI / mySQL: Access denied for user 'myusername'@'localhost' (using password: YES) by idle (Friar) on Mar 07, 2006 at 07:15 UTC
There is really known problem Client does not support authentication protocol. But in that case there should be another error, yours clearly say that you have unsufficient rights to connect to db.	[reply]
Re^2: DBI / mySQL: Access denied for user 'myusername'@'localhost' (using password: YES) by jhourcle (Prior) on Mar 07, 2006 at 12:39 UTC
If the error idle linked to is the problem that TedPride is having, the solution is to install mySQL 5 locally, so you have the correct shared libraries, and then reinstall DBD::mysql. However, I'd be interested to know the success/failure case of trying to connect using the given credentials from the command line, as I find that's the most common problem given that error message.	[reply]
Re^2: DBI / mySQL: Access denied for user 'myusername'@'localhost' (using password: YES) by Anonymous Monk on Mar 24, 2019 at 06:17 UTC
Have you got solution. I am also facing same problem. Please share solution.	[reply]
Re^3: DBI / mySQL: Access denied for user 'myusername'@'localhost' (using password: YES) by marto (Cardinal) on Mar 24, 2019 at 06:59 UTC
Way back in 2006, OP wrote this. If you have a different issue post a question. See also How do I post a question effectively?.	[reply]
Re: DBI / mySQL: Access denied for user 'myusername'@'localhost' (using password: YES) by TedPride (Priest) on Mar 07, 2006 at 15:08 UTC
Well, you guys were right - the password was incorrect. Someone set it to something different from the main account password. Thanks for eliminating the impossible and telling me how to check the possible :)	[reply]


P is for Practical
	PerlMonks