MySQL Privileges Problem

[jepler]

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.

Thanks,

-Jim

[ronverdonk]

What is the exact shell command you use doing the terminal logon?

Ronald :cool:

[jepler]

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...

[jepler]

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.

[ronverdonk]

Good you found it yourself, because I must say: I had no idea.
Anyway, thanks for the explanation to our forum members!

Ronald :cool: