Re: Access denied for root user

[Michael Stassen]

MueR wrote:
From: Michael Stassen [mailto:[EMAIL PROTECTED] 
Subj: Re: Access denied for root user

MueR wrote:
Hello,
I've recently had my HD crash, and have reinstalled everything.
Everything seems to work fine, apart from one thing. My MySQL server
has decided to randomly set a root password.
No, it didn't.  I know it seems that way, but I'm sure that's not what
happened.
I have tried logging in with no password, tried it with my old
password. Tried restarting the server with the --skip-grant-tables
command, I've tried to update the user table manually.
Everything seems to work out just fine, until the moment I attempt to
login using the root account. It will give me the error # 1045
(access denied for user [EMAIL PROTECTED]).
That's not the full error message, and you've tried a lot of things, so
it's hard to diagnose the problem.
The full error message also includes the using password...
Yes.  It helps to know whether it said "YES" or "NO", and which answer came 
with which attempt.

Nothing seems to work. Has anyone ever had this problem? Do you know
a solution to it? I am using MySQL v 3.23.49, on a Debian system.
I'd suggest carefully following the directions in the manual 
<http://dev.mysql.com/doc/mysql/en/Resetting_permissions.html>.  If
that doesn't work, paste what happens into your next post.
 
After starting the server with --skip-grant-tables:

utserver1:/var/run/mysqld# mysql -u root
Welcome to the MySQL monitor.
mysql> UPDATE mysql.user 
    -> SET Password=PASSWORD('xxxxx')
    -> WHERE User='root';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 3  Changed: 0  Warnings: 0`
OK, this worked.  You have 3 (why 3?) root users, based on "Rows matched". 
They all have the same password, which didn't change because it is already 
the same as what you tried to set it to here, based on "Changed".

You need to FLUSH PRIVILEGES or restart for this to take effect, though. 
Did you do so?  Did you test?

Or, the other way..
utserver1:/# mysqladmin -u root flush-privileges password "xxxxx"
This appears to have worked, as well (for user [EMAIL PROTECTED]).
utserver1:/# mysql -u root
ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password:
NO)
Good.  You set a password, so you can't get in without it.
utserver1:/# mysql -u root -p
Enter password:
ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password:
YES)
So none of the two ways described seem to work?
Hmmm...  Something is wrong.  The password you entered here doesn't match 
the password in the table.  You last set it with mysqladmin.  Does your 
password contain any special characters which your shell would have done 
something with?  A '$', for example?  If so, you'll have to escape them. 
Better to do it the first way (in the client), I think, so as to avoid this 
issue.

Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]