473,888 Members | 1,447 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 7407
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
1871
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
2990
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 includes insert = privileges?
9
2528
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 reliase this isn't secure but its only temporary for testing)
4
5857
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 --force"
5
4324
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 allowed to connect to this MySQL server' Another command I'm supposed to run also resulted in an...
4
3706
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 been doing just fine for the last few weeks. Quote:
1
6181
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
38547
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 command line interface
0
9961
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
11181
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. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10778
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 captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10886
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 most users, this new feature is actually very convenient. If you want to control the update process,...
0
10439
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9597
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
5819
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 the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
6014
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4642
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

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.