-1

Seems to be a well known issue with connecting to mysql and the @localhost:

Connects when:

mysql -uadmin2 -p

Does not connect when (duplicate @localhost)

$ mysql -uadmin2@localhost -p
Enter password: 
ERROR 1045 (28000): Access denied for user 'admin2@localhost'@'localhost' (using password: YES)

Dumped below is the mysql.user for the particular user.

mysql> select * from mysql.user where user='admin2'\G
*************************** 1. row ***************************
                  Host: localhost
                  User: admin2
              Password: *ECED01ACC1794471BCD067ECFEF085F99EDBFA23
           Select_priv: Y
           Insert_priv: Y
           Update_priv: Y
           Delete_priv: Y
           Create_priv: Y
             Drop_priv: Y
           Reload_priv: Y
         Shutdown_priv: Y
          Process_priv: Y
             File_priv: Y
            Grant_priv: Y
       References_priv: Y
            Index_priv: Y
            Alter_priv: Y
          Show_db_priv: Y
            Super_priv: Y
 Create_tmp_table_priv: Y
      Lock_tables_priv: Y
          Execute_priv: Y
       Repl_slave_priv: Y
      Repl_client_priv: Y
      Create_view_priv: Y
        Show_view_priv: Y
   Create_routine_priv: Y
    Alter_routine_priv: Y
      Create_user_priv: Y
            Event_priv: Y
          Trigger_priv: Y
Create_tablespace_priv: Y
              ssl_type: 
            ssl_cipher: 
           x509_issuer: 
          x509_subject: 
         max_questions: 0
           max_updates: 0
       max_connections: 0
  max_user_connections: 0
                plugin: 
 authentication_string: 
1 row in set (0.00 sec)

Screenshot showing error from dbVisualizer:

dbVisualizer connection info

I tried what was recommended on their forum which was this where someone else complained the same

grant all privileges on *.* to 'admin3'@'%' identified by 'mypass' with grant option;

but worked for them but not me!

FWIW its MAMP running version 5.5.33 of mysql. I've flushed privileges, tried using the ip address, left field blank (as per screenshot), looked at a similar solution here and am out of ideas now.

Anyone got any further suggestions then I will be very grateful.

Thanks

Kevin

Community
  • 1
  • 1
KevinY
  • 1,229
  • 1
  • 12
  • 27

1 Answers1

1

The duplicate username isn't useful information, you just can't set the user hostname at the command line, that's not how it works. It's set according to the hostname you're connecting from. Which makes sense, you wouldn't want me to try to connect to your server from my computer across the internet with 'admin2@localhost' and get in.

One thing that might be at play is MySQL has different permissions for connecting via localhost (which is done via a socket file on the local computer) versus 127.0.0.1 (which is done over the network with TCP), and you may have to add permissions explicitly for a user at localhost in addition to % (my recollection there is fuzzy):

grant all privileges on *.* to 'admin3'@'localhost' identified by 'mypass' with grant option;

Also, I just noticed you're granting privs to admin3 but trying to login with admin2 - if that's what you're doing in reality, you'll definitely have problems.

Other thoughts: does admin2 have rights to the database you're trying to connect to? I notice you're using a non-standard port (8889), but not specifying that in your command-line connections (although it might be in some config files), but the fact that you're getting "Access Denied" rather than a couldn't connect suggests that's not the issue.

Double-check the password, check your logs, try other clients, but this isn't any "famous" issue I don't think.

See this SO question for more info on localhost vs 127.0.0.1.

Community
  • 1
  • 1
cincodenada
  • 2,877
  • 25
  • 35
  • As mentioned in the message I can see the duplicate @localhost - you didn't read the question properly - in line with that, the dumped mysql.user shows the same user - the same one in the screenshot error message too! I created a number of user accounts trying to solve this problem - admin3 is just one of them. You have done nothing to help solve the issue (which I feel is sufficiently documented) and I don't think you marking it down is reasonable because you didn't read the question properly!!! – KevinY Apr 23 '14 at 02:18
  • I didn't vote you down, others did. I've edited my answer a bit - I see now you did mention the duplicate localhost, but that's not useful, since it's just using the cli improperly. I still don't know why there are issues, I've added some other notes. – cincodenada Apr 23 '14 at 02:49