SQLSTATE[28000] [1045]

Question

SQLSTATE[28000] [1045] Access denied for user 'XXXXX'@'localhost' (using password: YES)

I created this user with appropriate privileges in phpMyAdmin.

I've read MySQL ERROR 1045 (28000): Access denied for user 'bill'@'localhost' (using password: YES) and have questions:

1) Will the error message be the same if I have a user name but the password is different or will it give me a different error (methinks the error is the same regardless) but need to be sure.

2) Could this issue have anything whatsoever to do with too little hard drive space?

3) GRANT ALL PRIVILEGES ONXXXXXXX_%.* TO 'XXXXXXX'@'localhost'WITH GRANT OPTION; I ran this query, this user has full privleges:

REVOKE ALL PRIVILEGES ON  `XXXXXXX_%` . * FROM  'XXXXXXX'@'localhost';

REVOKE GRANT OPTION ON  `XXXXXXX_%` . * FROM  'XXXXXXX'@'localhost';

GRANT ALL PRIVILEGES ON  `XXXXXXX_%` . * TO  'XXXXXXX'@'localhost';

Was this the right thing to do?

4) I have access to the app/etc/local.xml file, what - if anything should I check in there to see if I'm on the right track? I see the user name appropriately listed there but... I cannot seem to find a reference to the livedb for dbname anywhere in phpMyAdmin, where could I look?

 <connection>
                <host><![CDATA[localhost]]></host>
                <username><![CDATA[XXXXXXX]]></username>
                <password><![CDATA[XXXXXXXXXX]]></password>
                <dbname><![CDATA[XXXXX_livedb]]></dbname>
                <initStatements><![CDATA[SET NAMES utf8]]></initStatements>
                <model><![CDATA[mysql4]]></model>
                <type><![CDATA[pdo_mysql]]></type>
                <pdoType><![CDATA[]]></pdoType>

Answer 1

I'd start by comparing the password for the user.

SELECT * FROM mysql.user WHERE User = 'XXXXX' AND Host = 'localhost' ;

Make sure that row exists, and the plugin column shows mysql_native_password and password_expired shows N.

If all of that is okay, then compare the password values. To get a hash value to compare, you can run this:

 SELECT PASSWORD('mysecret')

And then compare the returned value to the value stored in the Password column of the row in the mysql.user table. If the hash values are identical, then 'mysecret' is a valid password for that user.

---

If that specific row doesn't exist in the mysql.user table, note that an anonymous user at localhost (''@localhost) will take precedence over a XXXXX@'%' account. (And this is kind of confusing to the uninitiated.)

In MySQL, a user is identified by two things: User and Host. When you login to MySQL, the MySQL server has a specific order it looks for a matching row in the mysql.user table. First, it looks for an exact match on user and host...

User='XXXXX' Host='localhost'

If found, that's the user... it will check the password (or whatever authentication mechanism, to see if connection is allowed.) If no match is found, then MySQL server checks for an anonymous user

User='' Host='localhost'

And if no match is found there, then it will check:

User='XXXXX' Host='%'