473,324 Members | 2,473 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

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

MySQL Privileges Problem

jepler
4
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
Oct 10 '06 #1
4 7362
ronverdonk
4,258 Expert 4TB
What is the exact shell command you use doing the terminal logon?

Ronald :cool:
Oct 11 '06 #2
jepler
4
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
jepler
4
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
ronverdonk
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

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

Similar topics

1
by: Jordy | last post by:
Environment: Sun servers running solaris 2.8 Php 4.3.6 Apache 1.3.29 Mysql 4.1.1 phpMyAdmin 2.6.0-alpha1 phpAds 2.0 PhpMyadmin and phpAds don't succeed to connect the MySql database when
0
by: Ola Ogunneye | last post by:
--=_7B255FAE.1F7E0C30 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit Hello all, Let me start out by saying that I am still a newbie. I just freshly installed...
0
by: Kenneth Illingsworth | last post by:
--=_603E7359.5B3A569C Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: quoted-printable Content-Disposition: inline I suspect that this is some kind of issue with...
9
by: Sheldon | last post by:
Hi, I have a mysql setting problem that is driving me mad. Hopefully someone can give some advice: On the local network I have windows 2000/iis5 machine running our intranet site, with php...
4
by: Adam Smith | last post by:
I have a dedicated server running 'FreeBSD 4.9 STABLE' at a hosting site. They have done some default installations, presumably from the CVS ports package ??. Herein lies the problem, "I do not...
5
by: MLH | last post by:
I'm supposed to set a password for the MySQL root user. The output of mysql_install_db instructed me to run the following commands... /usr/bin/mysqladmin -u root -h appserver password mynwewpasswd...
4
by: Robert Blackwell | last post by:
A while ago I had some help from here to make a batch file that would dump my db. Everything was working just fine until 2 days ago and I just found out. I checked my backup folder and found...
1
by: razedk | last post by:
After creating a new user and executing "mysqladmin flush-privileges" I have lost all my privileges, I cant connect as root or any other user. Any idea why ? What can I do to restore or...
6
Atli
by: Atli | last post by:
This is an easy to digest 12 step guide on basics of using MySQL. It's a great refresher for those who need it and it work's great for first time MySQL users. Anyone should be able to get...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

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.