Glassfish4 and MySQL 5.5.38 remote server: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

Question

I have 2 amazon ec2 instances. On the first one I have my application running on a Glassfish4 application server, while on the second instance I have mysql installed. I have to connect the to tier. I have configured mysql as a remote server, setting the bind-address=0.0.0.0 in etc/mysql/my.conf and restarting mysql. Then I create a user@ in mysql and granted all the privilegies on the created database to this user.

On the application tier I create the jdbc connection pool using the following glassfish command:

create-jdbc-connection-pool --datasourceclassname com.mysql.jdbc.jdbc2.optional.MysqlDataSource --restype javax.sql.DataSource --property portNumber=3306:password=<PASSWORD>:user=<USER>:serverName=<MYSQL-SERVER-IP>:databaseName=<DATABASE-NAME> <CONNECTION-POOL-NAME>    

Then I deploy my application on Glassfish4.

In my application I use the following method:

public Connection getConnection() {
    Connection c = null;
    try {
        Class.forName("com.mysql.jdbc.Driver");
        c = (Connection) DriverManager.getConnection(connectionString,username,password);
    } catch (SQLException e) {
        e.printStackTrace();
    }catch (ClassNotFoundException e) {
        e.printStackTrace();
    }
    return c;
}    

Also my application has a particular feature. I handle some database entities using JPA 2.0 while some others directly using SQL. So I also have a persistence.xml in which I specify the following property:

<property name="javax.persistence.url" value="jdbc:mysql://<MYSQL-SERVER-IP>:3306/<DATABASE-NAME>"/>    

The url in the persistence.xml property is the same connectionString user in the getConnection() method. In the persistence.xml I also specify the username and password to access the database.

My application home shows two button, one to initialize the database with the entities managed by the JPA and another to inizialize the database with the remaining entities using SQL. The first one works successfully and the database tables are created on the remote mysql server. But when I click the second button it throws the CommunicationsException.

I read a lot on the web and also on stackoverflow about this problem and I see there may be multiple causes. In particupar I read the following discussion:

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

and tried to fix almost all the possible causes, but anything seems to work.

In my opinion it should be something with the JDBC driver. I also tried to put the mysql JDBC connector in the glassfish4/glassfish/lib folder but doesn't work. Also putting it in the application classpath doesn't work.

Excuse me for my english and if I forgot to specify something in the question. I hope my question is in accordance with the rules of stackoverflow since I have just signed up and this is my first post.

Thanks in advance for your help.

Answer 1

Just few tips based on your question:

Then I create a user@ in mysql and granted all the privilegies on the created database to this user.

MySQL authentication mechanism validates the combination of both user+host, so you need to be sure the host you are tryining to connect from is allowed to do so. For example, a very common mistake is to create the user like this:

CREATE USER 'user'@'localhost' IDENTIFIED BY 'password';

If you try to connect to MySQL from other host then you won't succeed. See MySQL - Adding user account for further details.

---

On the application tier I create the jdbc connection pool [...]

It looks ok, but you should also make this pool available as a JDBC resource with a proper JNDI name (i.e.: jdbc/ConnectionPoolName, so then you can use a DataSource directly in your application by using injection. See Administering JDBC Resources. This is valid if you use both JPA and JDBC.

---

Also my application has a particular feature. I handle some database entities using JPA 2.0 while some others directly using SQL.

If you use JPA the resource must be set in persistence.xml:

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.1" xmlns="http://xmlns.jcp.org/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd">
  <persistence-unit name="MyPU" transaction-type="JTA">
    <jta-data-source>jdbc/ConnectionPoolName</jta-data-source>
    <properties />
  </persistence-unit>
</persistence>

If you use plain JDBC API then a DataSource can be injected by using @Resource in a bean like this:

@Stateless
public class MyJdbcBean {

    @Resource(lookup = "jdbc/ConnectionPoolName")
    private DataSource dataSource;

    public Connection getConnection() {
        if (dataSource != null) {
            return dataSource.getConnection();
        } else {
            Exception ex = new ExceptionInInitializerError("Couldn't initialize data source!");
            Logger.getLogger(MyJdbcBean.class.getName()).log(Level.SEVERE, ex.getMessage(), ex);
            throw ex;
        }
    }
}

---

I also tried to put the mysql JDBC connector in the glassfish4/glassfish/lib folder but doesn't work. Also putting it in the application classpath doesn't work.

In my experience JDBC connector must be placed in domain's lib/ext folder, i.e.: glassfish-4.0/glassfish/domains/domain1/lib/ext.