473,549 Members | 3,749 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

MySQL Privileges Problem

jepler
4 New Member
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'@'localho st' (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 7381
ronverdonk
4,258 Recognized Expert Specialist
What is the exact shell command you use doing the terminal logon?

Ronald :cool:
Oct 11 '06 #2
jepler
4 New Member
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 New Member
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 Recognized Expert Specialist
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
931
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
1852
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 mysql version 4.0.13. The following are the steps that I took.
0
2965
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 privileges. However, the = account I am using has been given 'ALL' privileges to the database I am = trying to insert to. Am I save to assume that ALL...
9
2512
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 and mysql working ok. Anonymous access is disabled, and root has been assigned a password and given full access to all tables from all hosts (i...
4
5820
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 know" what they have done! At first it was apparent that MySQL was installed mysql_install_db ; GRANT TABLES etc. ??? so I ran "mysql_install_db...
5
4291
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 I did. It did not work. Here's the error: /usr/bin/mysqladmin: connect to server at 'appserver' failed error: 'Host 'appserver.crci.com' is not...
4
3687
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 that starting on 4/25/2006 the dump files are empty and only shows a few lines of comments or something instead of creating a normal dump as it had...
1
6153
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 recreate my privileges ?
6
38471
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 through this without much trouble. Programming knowledge is not required. Index What is SQL? Why MySQL? Installing MySQL. Using the MySQL...
0
7723
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
7962
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7480
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
1
5373
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
3504
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3486
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1949
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1063
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
769
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

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.