Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

In phpmyadmin I created a user called "edward" with password and all privileges:

enter image description here

Yet when I try to log in via phpmyadmin, it says "Cannot log in to the MySQL Server".

When I try to log in on the command line (Windows 7), it won't allow me either: enter image description here

But if I login in without a password, I get in:

enter image description here

and shows me only two of the tables.

I'm sure the password is correct and I've created multiple users which all can't log in with their passwords no matter how many rights I give them.

How can I get this user to be able to log in only with password and see all databases?


Addendum:

Here is a screenshot from phpmyadmin showing that "edward" has just as many rights as "root":

enter image description here

share|improve this question
    
Are you sure you set the password? Try SET PASSWORD FOR 'edward'@'%' = PASSWORD( 'YOU PASSWORD HERE' ). –  SkyDan Jun 26 '12 at 14:07
    
yes, I logged in as root and set the password again with that command, but I still can't log in as edward, as if there is some kind of global setting against anyone logging in except the root, I installed this with xampp –  Edward Tanguay Jun 26 '12 at 14:09
add comment

migrated from stackoverflow.com Jun 26 '12 at 14:21

This question came from our site for professional and enthusiast programmers.

3 Answers

up vote 5 down vote accepted

Because you granted it access without password... If a user has no password, logging in with one will always fail.

Set the password for user, only then you'll be able to use -p

In addition, you might want to remove the Any user.

share|improve this answer
    
I granted it a password in phpmyadmin (as shown in the screen shot above), and a at the command prompt I logged in again and granted it a password with set password for 'edward'@'%' = password('test'); but still can't log in with password, it's as if the password is not being set, but is showing as being set –  Edward Tanguay Jun 26 '12 at 14:20
3  
your screenshot shows % host, however, you're accessing it from localhost, which makes a difference. You probably have other grant there, which matches anyone on localhost, without password. user with specified host is stronger definition –  poncha Jun 26 '12 at 14:24
2  
ok, I changed it from % to localhost and now it I can log in, and also, as you said, if I remove the "any user" entry, I can use % in edward, thanks. –  Edward Tanguay Jun 26 '12 at 14:34
add comment

Same problem couldnt figure it out for nothin. Erased the database, removed the directory and reinstalled on Centos 6.3. The command line shows some data that is then scrolled quickly that solved the problem for me. Pasted below. PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! To do so, start the server, then issue the following commands:

/usr/bin/mysqladmin -u root password 'new-password' /usr/bin/mysqladmin -u root -h cch-web-centos password 'new-password'

Alternatively you can run: /usr/bin/mysql_secure_installation

The last one is what worked for me. I entered a root password and it actually WORKED. Entering a root password using mysqladmin without running that script gave me the same errors Edward reported. Run the script, answer the questions, now using the root password works and logging in without the password is denied. This one drove me crazy as well. I've installed mysql lots of times and this is the first time I've had this problem. Not sure if it is a yum/centos thing as on Ubuntu and Fedora I didnt have this problem.

share|improve this answer
add comment

Most possibly you have granted privileges on the server, but not on the databases. Try GRANT ALL ON *.* TO 'edward' IDENTIFIED BY 'password'

share|improve this answer
    
I logged in as root and set the password with that command for the database "test" and "edward" but I still can't log in as edward with its password –  Edward Tanguay Jun 26 '12 at 14:12
    
So sorry: I messed up should have been *.*, not * - I edited my answer. –  Eugen Rieck Jun 26 '12 at 14:15
    
that still doesn't work, as is show in the screen above, the user edward has all rights, as much as root actually –  Edward Tanguay Jun 26 '12 at 14:23
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.