1

I have a strange problem connecting to publicly hosted static IP mysql server.

I am able to connect to the server successfully with MySQL Workbench and also on the local remote server through SSH. However the connection attempt to the mysql server from jdbc connecter(both through NetBeans and through the locally deployed code fails). On looking into the logs, the code is trying to connect with remote server considering the user credentials are of a local user instead of remote user. So the user exists on remote but java considers user@localhost while trying connection. The exact error appears as:

Caused by: java.sql.SQLException: Access denied for user 'user'@'c-24-130-52-20.hsd1.ca.comcast.net' (using password: YES) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1078) ~[mysql-connector-java-5.1.27.jar:na] at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4237) ~[mysql-connector-java-5.1.27.jar:na] at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4169) ~[mysql-connector-java-5.1.27.jar:na] at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:928) ~[mysql-connector-java-5.1.27.jar:na] at com.mysql.jdbc.MysqlIO.proceedHandshakeWithPluggableAuthentication(MysqlIO.java:1750) ~[mysql-connector-java-5.1.27.jar:na]

My connection string is :

jdbc:mysql://user:password@W.X.Y.Z/

Can someone please suggest how to mention the user so java considers it to be a remote user instead of local user on my machine?

user1242321
  • 1,578
  • 2
  • 18
  • 30
  • 2
    I think the address in the error indicates *your* machine, not the machine it is trying to connect. The identifier after "user" is identifying who is trying to connect. Are you sure connections to that server are allowed for all remote machines? – RealSkeptic Oct 05 '15 at 10:48
  • Oh yes, you are right. That was stupid of me to not get that at first instance. Thanks for pointing it out. Yes I have ensured that the remote access is possible to everyone from outside by setting the bindaddress to 0.0.0.0. I will edit the question to take out this silly confusion of compact. Thanks. – user1242321 Oct 05 '15 at 10:52
  • However now I am curious how to mention the local user of remote machine in the connection string? I assumed the user would by default be considered as the one on remote server like user@w.x.y.z instead the current way. I don't have a static ip on my broadband so granting my local user access on remote server won't work either. – user1242321 Oct 05 '15 at 10:56
  • 1
    It's not just the listen address. User privileges in MySQL are based on both user name and host they connect from, so jack@example.com is different than jack@foo.bar.com. See [privilege system](https://dev.mysql.com/doc/refman/5.0/en/privilege-system.html) in MySQL documentation. – RealSkeptic Oct 05 '15 at 10:58
  • @RealSkeptic so what IP do I provide in the "host they connect from?" since I don't have a static IP for my local machine? – user1242321 Oct 05 '15 at 11:28
  • 1
    I think the key here is to grant privileges to that user from all IPs. See [this question](http://stackoverflow.com/q/8348506/4125191). – RealSkeptic Oct 05 '15 at 11:47

2 Answers2

0

I don't know if this will solve your problem but you're much better off connecting using the API provided, rather than by constructing the connection String manually.

It will make it harder to make mistakes (which will be difficult to diagnose) and makes parameter substitution easier:

String username = "testuser";
String password = "test123";
String url = "jdbc:mysql://ip-address/databaseName";
Class.forName ("com.mysql.jdbc.Driver").newInstance ();
Connection conn = DriverManager.getConnection (url, username, password);
StuPointerException
  • 7,117
  • 5
  • 29
  • 54
  • Thanks for the input. But I am using the Play Framework and that internally connects the way you just mentioned. The purpose of mentioning the connection string was to convey the connection details. Netbeans connection attempt is failing too which I think should be pretty standard way. Surprisingly Mysql workbench is successfully connecting. So this would not help the issue much in this case. :-( – user1242321 Oct 05 '15 at 10:47
  • 1
    No need to call `Class.forName`. There's auto-discovery since JDBC 4. – RealSkeptic Oct 05 '15 at 10:49
0

The problem here was:

When MySQL Workbench was connecting, the connection was successful since the user was userName@W.X.Y.Z which had full access to DB. However when I was using java to connect, the user it was trying to connect with was userName@JavaServerIP, instead of userName@W.X.Y.Z. So the connection attempt was failing since the userName@JavaServerIP had no access. Granting such user access to the DB on mysql server resolved the issue for me. Here's the command that helped me resolve this:

GRANT ALL PRIVILEGES ON my_database.* TO 'my_user'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;
user1242321
  • 1,578
  • 2
  • 18
  • 30