Cannot connect to MySQL with JDBC - Connection Timeout - Ubuntu 9.04

I am running Ubuntu and am ultimately trying to connect Tomcat to my MySQL database using JDBC.

It has worked previously but after a reboot the instance now fails to connect.

• Both Tomcat 6 and MySQL 5.0.75 are on the same machine
• Connection string: jdbc:mysql:///localhost:3306
• I can connect to MySQL on the command line using the mysql command
• The my.cnf file is pretty standard (Available on request) has bind address: 127.0.0.1
• I cannot Telnet to the MySQL port despite netstat saying MySQL is listening
开发者_JAVA技巧
• I have one IpTables rule to forward 80 -> 8080 and no firewall I'm aware of.

I'm pretty new to this and I'm not sure what else to test. I don't know whether I should be looking in etc/interfaces and if I did what to look for. It's weird because it used to work but after a reboot it's down so I must have changed something.... :).

I realise a timeout indicates the server is not responding and I assume it's because the request isn't actually getting through. I installed MySQL via apt-get and Tomcat manually.

MySqld processes

root@88:/var/log/mysql#  ps -ef | grep mysqld
root     21753     1  0 May27 ?        00:00:00 /bin/sh /usr/bin/mysqld_safe
mysql    21792 21753  0 May27 ?        00:00:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-external-locking --port=3306 --socket=/var/run/mysqld/mysqld.sock
root     21793 21753  0 May27 ?        00:00:00 logger -p daemon.err -t mysqld_safe -i -t mysqld
root     21888 13676  0 11:23 pts/1    00:00:00 grep mysqld

Netstat

root@88:/var/log/mysql# netstat -lnp | grep mysql
tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      21792/mysqld
unix  2      [ ACC ]     STREAM     LISTENING     1926205077 21792/mysqld        /var/run/mysqld/mysqld.sock

Toy Connection Class

root@88:~# cat TestConnect/TestConnection.java

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class TestConnection {

  public static void main(String args[]) throws Exception {
    Connection con = null;

    try {
      Class.forName("com.mysql.jdbc.Driver").newInstance();
      System.out.println("Got driver");
      con = DriverManager.getConnection(
                "jdbc:mysql:///localhost:3306",
                "uname", "pass");
      System.out.println("Got connection");

      if(!con.isClosed())
        System.out.println("Successfully connected to " +
          "MySQL server using TCP/IP...");

    } finally {
        if(con != null)
          con.close();
    }
  }
}

Toy Connection Class Output

Note: This is the same error I get from Tomcat.

root@88:~/TestConnect# java -cp mysql-connector-java-5.1.12-bin.jar:. TestConnection
Got driver
                Exception in thread "main" com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet sent successfully to the server was 1 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:1122)            
        at TestConnection.main(TestConnection.java:14)
Caused by: 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:1122)
        at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:344)
        at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2181)
        ... 12 more
Caused by: java.net.ConnectException: Connection timed out
        at java.net.PlainSocketImpl.socketConnect(Native Method)
        ... 13 more

Telnet Output

root@88:~/TestConnect# telnet localhost 3306
Trying 127.0.0.1...
telnet: Unable to connect to remote host: Connection timed out

Netstat shows that MySQL is indeed listening on port 3306 on all local addresses. Since telnet cannot connect to 127.0.0.1 there is a firewall that stops your you - try running sudo ufw default allow to effectivly disable it, or sudo ufw allow 3306 and see if it makes any changes.

What does your /etc/hosts file look like? Do you have an entry like the following?

127.0.0.1   localhost

Can you try to telnet:

telnet 127.0.0.1 3306 or telnet 127.0.0.1:3306

or JDBC connect to:

jdbc:mysql://127.0.0.1:3306

The connection string ought to be

jdbc:mysql://localhost:3306

instead of

jdbc:mysql:///localhost:3306

Also on unix/linux, by default the client/server communication is done via the unix socket file and not via tcp/ip. Hence you are not able to telnet to port 3306 on your local machine.

Were you always using the same signature for the getConnection method, or is that one of the things you changed before it stopped working? (The '///' in the string suggestions it was a recent addition.)

If this is new code, you might try the following connection string:

String dbUrl = "jdbc:mysql://localhost:3306/[db]?user=[user]&password=[password]"; Connection conn = DriverManager.getConnection(dbUrl);

This is the example format on the mysql.com documentation page:

http://dev.mysql.com/doc/refman/5.0/es/connector-j-usagenotes-basic.html