472,126 Members | 1,521 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,126 software developers and data experts.

MySQL Privileges Problem

I find myself in a circular reference loop that I can't seem to resolve. I recently upgraded from MySQL 4.0.x to MySQL 4.1.21 on OS 10.3.9 Server. Then, I *thought* I set my open master root password correctly using usr/local/mysql/bin/mysqladmin -u root password new_password and /usr/local/mysql/bin/mysqladmin -u root -h `hostname` password "newpasswd" . After setting my master root password I then connected via phpMyAdmin 2.5.4 as root@localhost, recreated the databases and then dumped the data from the old databases present in my 4.0.x MySQL build into my new databases in my 4.2.1 build. I then set up my users as they were present in the 4.0.x with the same privileges via phpMyAdmin.

Everything seemed to be working fine. My web apps are connecting, queries are working properly and everything seems rosy. BUT! I keep receiving an error message when trying to connect to mysql via terminal shell (ERROR 1045: Access denied for user 'root'@'localhost' (using password: NO) which seems odd because I'm able to connect using phpMyAdmin and I set the open password up right after I did my install. I'm also receiving the error: 'Client does not support authentication protocol requested by server; consider upgrading MySQL client'.

From the research I've done, the second error is related to the password problem that I should be able to fix from the instructions here http://dev.mysql.com/doc/refman/5.0/en/old-client.html . However, I can't fix this because I'm getting an access denied 1045 error mentioned above. To fix that problem using a solution from here http://dev.mysql.com/doc/refman/5.0/...ss-denied.html , I need to be able to access mysql via the client but I can't because of the password problem.

Any ideas would be greatly appreciated.


Oct 10 '06 #1
4 7243
4,258 Expert 4TB
What is the exact shell command you use doing the terminal logon?

Ronald :cool:
Oct 11 '06 #2
What is the exact shell command you use doing the terminal logon?

Ronald :cool:

[127:~/] user% mysql -u root -p
Enter password: xxxxxx
ERROR 1251: Client does not support authentication protocol requested by server; consider upgrading MySQL client

this is the message I receive from the shell prompt...
Oct 11 '06 #3
I think I finally solved my problem.

I initially secured the MySQL root user password using the following in the shell:

/usr/local/mysql/bin/mysqladmin -u root password new_password
/usr/local/mysql/bin/mysqladmin -u root -h `hostname` password "newpasswd"

This set the root user's password to the longer hashed version in my mysql user table. I then connect via phpMyAdmin without a problem because phpMyAdmin was able to read the longer hashed passwords. I then set up the rest of my users using phpMyAdmin, which created hashed passwords that were too long for my older mysql client to use.

I then discovered a problem with a remote client on a separate server that could not connect to my new database because the internal client could not understand the new password scheme to authenticate this remote user. I kept getting the message that I needed to upgrade my MySQL client. To fix the connection problem, I learned that I needed to reset that user's passwords using the OLD_PASSWORD command accessible through the terminal, but I couldn't connect in the terminal to do this because my root password had already been set to the longer hashed version. To my knowledge, you cannot use the default options in phpMyAdmin to invoke the OLD_PASSWORD command; this can only be done via command line. This was the cause of my vicious cycle.

I ended up creating a user via phpMyAdmin with FULL privileges on the mysql user tables WITHOUT a password. I then connected to the mysql user tables via command line (I did not receive the mysql upgrade error this time because this user didn't have a password) and invoked the OLD_PASSWORD command on all my users to set their passwords back to pre-4.1 style. Lastly, I exited the mysql.user, reconnected via root and then deleted this temporary super user from my privileges table. All is well.
Oct 11 '06 #4
4,258 Expert 4TB
Good you found it yourself, because I must say: I had no idea.
Anyway, thanks for the explanation to our forum members!

Ronald :cool:
Oct 11 '06 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by Ola Ogunneye | last post: by
reply views Thread by Kenneth Illingsworth | last post: by
9 posts views Thread by Sheldon | last post: by
4 posts views Thread by Robert Blackwell | last post: by
1 post views Thread by razedk | last post: by
6 posts views Thread by Atli | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.