Accessing a remote MySQL database using JAVA

[LianaN]

Hi guys!

I'm trying to access a remote database (MySQL) that is installed on my home computer. The code is:

public void initDB(String ipaddress, String databasename, String login, String pass) {
    try {
      Class.forName("com.mysql.jdbc.Driver");
      db =  (Connection) DriverManager.getConnection("jdbc:mysql://" + ipaddress + "/" + databasename, login, pass);
      statement = (Statement) db.createStatement();
      System.out.println("Database is connected.");
    } catch (Exception e) {
      System.out.println("Could not initialize the database.");
      e.printStackTrace();
    }
  }

This code works perfectly with the local database (when the code is executed from my home computer). Now I'm trying to run the code from another computer, and I cannot connect to the database. Where could be the problem?

Some details about the database: I'm using Wamp server. To allow a remote access, I've opened the file phpmyadmin.conf in C:\WAMP\ALIAS and replaced Allow statement with Allow from all. Also, I've created new user and I've given the value of % for the Host. That is all I've done. Maybe, I missed something, don't know...

Below you may see an error message:

Could not initialize the database.
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
        at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1118)
        at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:343)
        at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2308)
        at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2122)
        at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:774)
        at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:49)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
        at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:375)
        at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:289)
        at java.sql.DriverManager.getConnection(DriverManager.java:582)
        at java.sql.DriverManager.getConnection(DriverManager.java:185)
        at SystClasses.QueryTableModel.initDB(QueryTableModel.java:88)
        at SystClasses.QueryTableModel.<init>(QueryTableModel.java:36)
        at SystClasses.FilterClass.initComponents(FilterClass.java:67)
        at SystClasses.FilterClass.<init>(FilterClass.java:51)
        at SystClasses.Form.createContacts(Form.java:280)
        at SystClasses.Form.createBaseRight(Form.java:196)
        at SystClasses.Form.createGUIforAdmin(Form.java:111)
        at SystClasses.Form.<init>(Form.java:84)
        at SystClasses.Form.main(Form.java:356)
Caused by: java.net.ConnectException: Connection timed out: connect
        at java.net.PlainSocketImpl.socketConnect(Native Method)
        at java.net.PlainSocketImpl.doConnect(PlainSocketImpl.java:333)
null
        at java.net.PlainSocketImpl.connectToAddress(PlainSocketImpl.java:195)
        at java.net.PlainSocketImpl.connect(PlainSocketImpl.java:182)
        at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:366)
        at java.net.Socket.connect(Socket.java:529)
        at java.net.Socket.connect(Socket.java:478)
        at java.net.Socket.<init>(Socket.java:375)
        at java.net.Socket.<init>(Socket.java:218)
        at com.mysql.jdbc.StandardSocketFactory.connect(StandardSocketFactory.java:256)
        at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:292)
        ... 22 more

Please, help me with some advise. Thanks!

[masijade]

Firewal, firewall, firewall. You might have opened the "phpadmin" port (whatever port that is), but you seemingly have not opened the standard DB port, and your firewall simply drops packets.

Also, don't forget to properly configure your grants.

[LianaN]

Thank you. I suppose that I've configured grants properly. It seems that the problem is really connected with opening the standard DB port. Hmm, I'm not very good in this topic... I've found the following http://msdn.microsoft.com/en-us/library/ms175043.aspx. Is it exactly what you mean?

Firewal, firewall, firewall. You might have opened the "phpadmin" port (whatever port that is), but you seemingly have not opened the standard DB port, and your firewall simply drops packets.

Also, don't forget to properly configure your grants.

[masijade]

hey, i also doing the same process. i have created mysql database using my sql i have connect this with jdbc by connector/j but when i execute this it shows error

C:\Documents and Settings\prudenforce\Desktop\servlets-examples>java GetAllRows
Exception in thread "main" java.lang.NoClassDefFoundError: GetAllRows
Caused by: java.lang.ClassNotFoundException: GetAllRows
at java.net.URLClassLoader$1.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at java.net.URLClassLoader.findClass(Unknown Source)
at java.lang.ClassLoader.loadClass(Unknown Source)
at sun.misc.Launcher$AppClassLoader.loadClass(Unknown Source)
at java.lang.ClassLoader.loadClass(Unknown Source)
at java.lang.ClassLoader.loadClassInternal(Unknown Source)
Could not find the main class: GetAllRows. Program will exit.
please help me to solve my problem.

Different problem. Start your own thread and learn about classpaths.

[masijade]

Thank you. I suppose that I've configured grants properly. It seems that the problem is really connected with opening the standard DB port. Hmm, I'm not very good in this topic... I've found the following http://msdn.microsoft.com/en-us/library/ms175043.aspx. Is it exactly what you mean?

That's the process, but make sure you configure for a MySQL port and not an SQL Server port (which is what's described there). Also, you do know that it is extremely unsafe to expose these ports, right?

[LianaN]

Yes, I know that exposing ports is unsafe. But how could I remotely access my MySQL database without exposing them? I guess it is impossible, is it?

[masijade]

Set up a web service? And access the db through "proxy". In any case, MySQL can at least do SSL, so make sure to, at the very least, use that, however, I do not know if the authentication attempt is already encrypted, or maybe the channel is only encrypted after connection/authentication has been made. In which case, it doesn't do much good anymore.

[LianaN]

I've solved the problem. I've just uninstalled Wamp Server and installed MySQL Community Server using MSI Installer. Then I've created a database, a table and a user. Finally, I've granted all privileges to that user:

grant all on database_name.* to user_name@'ip_address_of_user' identified by 'password';

Everything works correctly. Thanks!