9

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

3
  • Are you sure you set the password? Try SET PASSWORD FOR 'edward'@'%' = PASSWORD( 'YOU PASSWORD HERE' ). Jun 26, 2012 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, 2012 at 14:09
  • same thing is happenning to me, and the password was set correctly on the create user command Dec 14, 2016 at 22:13

3 Answers 3

6

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.

3
  • 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, 2012 at 14:20
  • 4
    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, 2012 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. Jun 26, 2012 at 14:34
0

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

3
  • 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, 2012 at 14:12
  • So sorry: I messed up should have been *.*, not * - I edited my answer.
    – Eugen Rieck
    Jun 26, 2012 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 Jun 26, 2012 at 14:23
0

Same problem couldn't figure it out for nothing. 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 didn't have this problem.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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