0

I have an account with a Java PaaS (CloudBees) that hosts their platform off of Amazon EC2 instances. My account comes with access to a MySQL server instance and I am getting a bizarre error that only occurs when I try to log into the MySQL server from a client on my linux (Ubuntu 12.04 desktop) machine, but not my Windows 7 machine.

Here is a screenshot of the error when I try to log in with MySQL Workbench on my Ubuntu machine:

enter image description here

It's important to note that I have another MySQL client (Squirrel SQL) installed on this machine, that throw the same exact error when I try to log in to my dl_web account. Squirrel SQL is a pure Java application, and I have tried to check that MySQL Workbench and Squirrel SQL do not share any common dependencies and couldn't find any (but I could be wrong).

However, when I try to log in with a MySQL Workbench instance living on my Windows 7 machine, I can log in just fine.

Given the list of 4 things to check from that error message:

  1. Yes, of course MySQL server is running on the EC2 instance; otherwise CloudBees would be out of business and I wouldn't be able to log in from my Windows 7 machine.
  2. 3306 is the correct port - I verified this with CloudBees support staff.
  3. Not sure about this one - perhaps the MySQL server instance is somehow configured to refuse connections from linux clients?!?!
  4. The password I'm using is correct - I have checked and re-checked and re-re-checked.

So the only 2 theories I have so far are:

  • The MySQL server instance is somehow configured to refuse connections from Linux clients (is this even possible?); or
  • The MySQL Workbench and Squirrel SQL instances on my Ubuntu machine are sharing some common dependency that is buggy or misconfigured; this is the more likely of the two scenarios, but I have been unsuccessful pegging down what this dependency is...

Also - note the exact error message that I get from both MySQL Workbench and Squirrel SQL:

Access denied for user 'dl_web'@'%' to database...

My user is dl_web...not 'dl_web'@'%'!!! Bizarre, right?!

Any ideas? Thanks in advance!

IAmYourFaja
  • 55,468
  • 181
  • 466
  • 756
  • 1
    have you configured the user security so the user such as 'billybob'@'fios-1-2-13-10.midatlantic.net' or whatever is allowed access to the db ? – Drew May 25 '13 at 12:25
  • Thanks @DrewPierce (+1) - no I haven't set any of that up, and was hoping that kind of configuration would just come "out of the box". But it still doesn't explain why my Windows machine would be able to login. I really think this is a linux dependency issue. Thanks again! – IAmYourFaja May 25 '13 at 12:40
  • 2
    select * from mysql.user – Drew May 25 '13 at 12:42
  • Thanks again @DrewPierce but I'm not following you here...in order to run that query, I'd have to be logged in right? **I can't even log in from my linux machine**! – IAmYourFaja May 25 '13 at 12:44
  • u have no access whatsoever from any machine ? – Drew May 25 '13 at 12:56
  • From my Windows 7 machine, it's giving me a denied user for `dl_web@someserver.example.com`... (this is probably a shared MySQL instance that CloudBees has put me on, so no special privs/access)...any ideas? – IAmYourFaja May 25 '13 at 13:03
  • 1
    three things and i am sure 2 are already done 1) port 3306 is open 2) mysql is bound on 0.0.0.0 and not just 127.0.0.1, and 3) you issue mysql GRANT commands, yes, using that bizarre user123@'some silly address' with password='somepassword' granting access onto some tables – Drew May 25 '13 at 13:06
  • http://www.debuntu.org/how-to-create-a-mysql-database-and-set-privileges-to-a-user/ – Drew May 25 '13 at 13:07
  • Thanks again (+1) but still not quite following you: are you saying I need to check and verify that these 3 things have been done? If so, I know #1 is done, not sure about #2 and have no idea how to check for #3... – IAmYourFaja May 25 '13 at 13:07
  • And again, @DrewPierce - **can you explain why I can login from Windows and not Linux?** – IAmYourFaja May 25 '13 at 13:08
  • are they on the same IP address ? or is windows back at your office and Linux is an instance running in AWS. you did not provide that detail (that I can see) – Drew May 25 '13 at 13:10
  • Yes they are on the same IP, though indirectly. The linux box is a physical desktop; the Windows 7 machine is actually a VM (Vbox) with host-only networking turned on; which if I understand correctly, means it uses the same IP as the host. – IAmYourFaja May 25 '13 at 13:11
  • 1
    http://pastie.org/7957450 – Drew May 25 '13 at 13:17
  • 1
    run ifconfig and ipconfig, you may find that in the virtualized environment your ip addresses are different. they certainly are for me running VMWARE underneath windows7 (same box). like different subnets. all it takes is for the host to be a little different and that could do it too – Drew May 25 '13 at 13:20
  • Again, I don't have permission to run `select * from mysql.user`, so I can't look for login attempts. Also I verified that both machines (physical and virtual alike) have the same ip. Here's a question: when I do try to run `select * from mysql.user`, I get that `SELECT command denied to user dl_web@someserver.example.com for table user` error. In reality (I can't show the full server name for security reasons, `server.example.com` seems to be a Time Warner cable (my ISP) machine at `nycap.res.rr.com`. Why is MySQL matching my user name `dl_web` with some TW cable machine? – IAmYourFaja May 25 '13 at 13:27
  • that is just the bizarre thing that you have to get used to when trying to get a user to connect to mysql up on something like AWS. it is not an issue back on an intranet / dns inside a company, but the thing that has to happen over the internet. that was why i was talking about fios-34-3-4-23-4- blah blah blah string because that is my hostname as far as AWS is concerned – Drew May 25 '13 at 13:30
  • let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/30618/discussion-between-drew-pierce-and-iamyourfaja) – Drew May 25 '13 at 13:35

1 Answers1

1

You appear to be in a hosted environment within AWS where you do not have access to root or to view grants on tables, but this may help someone else and show a few steps.

first, if I go into sqlyog (or toad or mysql workbench) and try to connect to AWS mysql, i get the following error:

http://img836.imageshack.us/img836/4594/out1.GIF

so the issue is root@pool-72-93-207-216.bstnma.fios.verizon.net

so i get into mysql at root (yes I know you might not be able to do this !)..

and I perform the following

mysql> use test;


mysql> grant all privileges on test.* to 'root'@'pool-72-93-207-216.bstnma.fios.verizon.net'  identified by 'mypassword123z';




mysql> select user,host from mysql.user;
+------------------+--------------------------------------------+
| user             | host                                       |
+------------------+--------------------------------------------+
| foo_user         | %                                          |
| root             | 127.0.0.1                                  |
| foo_user         | 72.93.207.216                              |
| foo_user2        | 72.93.207.216                              |
| root             | ::1                                        |
|                  | domU-12-31-38-04-B2-89                     |
| root             | domU-12-31-38-04-B2-89                     |
|                  | localhost                                  |
| debian-sys-maint | localhost                                  |
| phpmyadmin       | localhost                                  |
| root             | localhost                                  |
| root             | pool-72-93-207-216.bstnma.fios.verizon.net |
+------------------+--------------------------------------------+

mysql> SHOW GRANTS FOR 'root'@'localhost';
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*4F4504C8CA8D22648E58B5092F653457698A9EBE' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION                                                                           |
+----------------------------------------------------------------------------------------------------------------------------------------+

mysql> show grants for 'root'@'pool-72-93-207-216.bstnma.fios.verizon.net';
+----------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@pool-72-93-207-216.bstnma.fios.verizon.net                                                                                   |
+----------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'root'@'pool-72-93-207-216.bstnma.fios.verizon.net' IDENTIFIED BY PASSWORD '*CACE4BB4D0A5D265BA7FCBEED600DC8976884A43' |
| GRANT ALL PRIVILEGES ON `test`.* TO 'root'@'pool-72-93-207-216.bstnma.fios.verizon.net'                                                      |
+----------------------------------------------------------------------------------------------------------------------------------------------+

then I go back to sqlyog (or toad or mysql workbench) and log in with root and password mypassword123z and I am in.

in addition you can create an secure SSH proxy tunnel thru port 22 and your AWS pem file, using like Putty.

Drew
  • 24,851
  • 10
  • 43
  • 78