Communications link failure

[Deparis Momo]

Hi

I hosted my first JEE web site but my mySQL connections are timing out after few minutes and I am getting this error message:

HTTP Status 500 - com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

type Exception report

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

description The server encountered an internal error that prevented it from fulfilling this request.

exception

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

Last packet sent to the server was 0 ms ago.
dao.OffreDaoImpl.lister(OffreDaoImpl.java:95)
filters.PrechargementFilter.doFilter(PrechargementFilter.java:96)
org.apache.catalina.filters.SetCharacterEncodingFilter.doFilter(SetCharacterEncodingFilter.java:108)

root cause

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

Last packet sent to the server was 0 ms ago.
sun.reflect.GeneratedConstructorAccessor28.newInstance(Unknown Source)
sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
java.lang.reflect.Constructor.newInstance(Constructor.java:525)
com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1074)
com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2985)
com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2871)
com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3414)
com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1936)
com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2060)
com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2542)
com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1734)
com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1885)
com.jolbox.bonecp.PreparedStatementHandle.executeQuery(PreparedStatementHandle.java:172)
dao.OffreDaoImpl.lister(OffreDaoImpl.java:90)
filters.PrechargementFilter.doFilter(PrechargementFilter.java:96)
org.apache.catalina.filters.SetCharacterEncodingFilter.doFilter(SetCharacterEncodingFilter.java:108)

root cause

java.io.EOFException: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.
com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:2431)
com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2882)
com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2871)
com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3414)
com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1936)
com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2060)
com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2542)
com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1734)
com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1885)
com.jolbox.bonecp.PreparedStatementHandle.executeQuery(PreparedStatementHandle.java:172)
dao.OffreDaoImpl.lister(OffreDaoImpl.java:90)
filters.PrechargementFilter.doFilter(PrechargementFilter.java:96)
org.apache.catalina.filters.SetCharacterEncodingFilter.doFilter(SetCharacterEncodingFilter.java:108)

I asked my hoster to increase the timeout value and here is his response:

Please accept our sincerest apologizes for misunderstanding.
Unfortunately due to shared hosting environment your request could not be accomplished - it will affect other customers, that share server resources for you. (10 min is max value for this parameter).
Since you strongly need some features - we can only suggest you considering to move to VPS or dedicated server where you will have full admin privileges and can do whatever you want without affecting of another customers.

As a solution, we would like to suggest you to add in your jdbc connection string following command:
&autoReconnect=true&failOverReadOnly=false&maxReconnects=10

In this case, after timeout, you will be reconnected again.

So I edited my config.properties file:

url = jdbc:mysql://localhost:3306/myDatabase&autoReconnect=true&failOverReadOnly=false&maxReconnects=10
driver = com.mysql.jdbc.Driv
username = xxxxxx
password = xxxxxx

But when I launched my application i got this error message:

INFO [org.netbeans.modules.tomcat5.TomcatManagerImpl]: TomcatManagerImpl connecting to: http://localhost:8084/manager/text/deploy?config=file%3A%2FC%3A%2FUsers%2FMo%2FAppData%2FLocal%2FTemp%2Fcontext138676520858696842.xml&path=/MonSite
INFO [org.netbeans.modules.j2ee.deployment.impl.TargetServer]: Cannot incrementally deploy to more than one target
INFO [org.netbeans.modules.j2ee.deployment.devmodules.api.Deployment]
The module has not been deployed.
See the server log for details.
[catch] at org.netbeans.modules.j2ee.deployment.devmodules.api.Deployment.deploy(Deployment.java:210)
at org.netbeans.modules.j2ee.ant.Deploy.execute(Deploy.java:111)
at org.apache.tools.ant.UnknownElement.execute(UnknownElement.java:291)
at sun.reflect.GeneratedMethodAccessor76.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at org.apache.tools.ant.dispatch.DispatchUtils.execute(DispatchUtils.java:106)
at org.apache.tools.ant.Task.perform(Task.java:348)
at org.apache.tools.ant.Target.execute(Target.java:392)
at org.apache.tools.ant.Target.performTasks(Target.java:413)
at org.apache.tools.ant.Project.executeSortedTargets(Project.java:1399)
at org.apache.tools.ant.Project.executeTarget(Project.java:1368)
at org.apache.tools.ant.helper.DefaultExecutor.executeTargets(DefaultExecutor.java:41)
at org.apache.tools.ant.Project.executeTargets(Project.java:1251)
at org.apache.tools.ant.module.bridge.impl.BridgeImpl.run(BridgeImpl.java:283)
at org.apache.tools.ant.module.run.TargetExecutor.run(TargetExecutor.java:541)
at org.netbeans.core.execution.RunClassThread.run(RunClassThread.java:153)

What can I do?

Thanks

[Deparis Momo]

It's ok now, I forgot to add the "?" in the url string. The correct url is:

url = jdbc:mysql://localhost:3306/myDatabase?autoReconnect=true&failOverReadOnly=false&maxReconnects=10

[Deparis Momo]

Hi

There's no compilation error but my mySQL connections are still timing out after 10 minutes and I am getting this error message above.

Can someone help me please?

[Claude Moore]

What do you mean with timeout? As far s I know, a connection timeout occurs when you are trying to connect to a db and the connect itself takes more than a given max amount of time to be performed I don't understand if you are successful in obtaining connections or not, or if those connections work for a while and then are dropped.

[Deparis Momo]

In fact in the index.jsp (which is the welcome page), there is a filter which connect to my database, collect and list data from the database. So I have no connection issue. But in the server I am located, my hoster setted the timeout value to 10 minutes. That's why after 10 minutes of inactitity, the mysql connections timed out.

[Claude Moore]

This means that you open a connection and use it all over your application, right? You should instead define and use a Connection Pool. You get a connection when you need it and,once used, you close it returning it to the pool.Normally connection pool implementations allow the developer to define a reuse and aging policy related to physical connection handled by the pool. This means that, for example, you may define that an "aged timeout" so that a connection is marked obsoleted when a customizable amount of time is passed after is creation. This way no matter if your hoster closes forcibly unused connections after 10 minutes: just define an aged timeout, and you will always get fresh connections.

[Deparis Momo]

I already used a connection pool. Here is my code:

/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
*/
package dao;
/**
*
* @author Mo
*/
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;
import com.jolbox.bonecp.BoneCP;
import com.jolbox.bonecp.BoneCPConfig;
public class DAOFactory {
private static final String FICHIER_PROPERTIES = "/dao/config.properties";
private static final String PROPERTY_URL = "url";
private static final String PROPERTY_DRIVER = "driver";
private static final String PROPERTY_NOM_UTILISATEUR = "nomutilisateur";
private static final String PROPERTY_MOT_DE_PASSE = "motdepasse";
/* package */BoneCP connectionPool = null;
/* package */DAOFactory( BoneCP connectionPool ) {
this.connectionPool = connectionPool;
}
/*
* Méthode chargée de récupérer les informations de connexion à la base de
* données, charger le driver JDBC et retourner une instance de la Factory
*/
public static DAOFactory getInstance() throws DAOConfigurationException {
Properties properties = new Properties();
String url;
String driver;
String nomUtilisateur;
String motDePasse;
BoneCP connectionPool = null;
ClassLoader classLoader = Thread.currentThread().getContextClassLoader();
InputStream fichierProperties = classLoader.getResourceAsStream( FICHIER_PROPERTIES );
if ( fichierProperties == null ) {
throw new DAOConfigurationException( "Le fichier properties " + FICHIER_PROPERTIES + " est introuvable." );
}
try {
properties.load( fichierProperties );
url = properties.getProperty( PROPERTY_URL );
driver = properties.getProperty( PROPERTY_DRIVER );
nomUtilisateur = properties.getProperty( PROPERTY_NOM_UTILISATEUR );
motDePasse = properties.getProperty( PROPERTY_MOT_DE_PASSE );
} catch ( FileNotFoundException e ) {
throw new DAOConfigurationException( "Le fichier properties " + FICHIER_PROPERTIES + " est introuvable.", e );
} catch ( IOException e ) {
throw new DAOConfigurationException( "Impossible de charger le fichier properties " + FICHIER_PROPERTIES, e );
}
try {
Class.forName( driver );
} catch ( ClassNotFoundException e ) {
throw new DAOConfigurationException( "Le driver est introuvable dans le classpath.", e );
}
try {
/*
* Création d'une configuration de pool de connexions via l'objet
* BoneCPConfig et les différents setters associés.
*/
BoneCPConfig config = new BoneCPConfig();
/* Mise en place de l'URL, du nom et du mot de passe */
config.setJdbcUrl( url );
config.setUsername( nomUtilisateur );
config.setPassword( motDePasse );
/* Paramétrage de la taille du pool */
config.setMinConnectionsPerPartition( 5 );
config.setMaxConnectionsPerPartition( 10 );
config.setPartitionCount( 2 );
/* Création du pool à partir de la configuration, via l'objet BoneCP */
connectionPool = new BoneCP( config );
} catch ( SQLException e ) {
e.printStackTrace();
throw new DAOConfigurationException( "Erreur de configuration du pool de connexions.", e );
}
/*
* Enregistrement du pool créé dans une variable d'instance via un appel
* au constructeur de DAOFactory
*/
DAOFactory instance = new DAOFactory( connectionPool );
return instance;
}
/* Méthode chargée de fournir une connexion à la base de données */
/* package */Connection getConnection() throws SQLException {
return connectionPool.getConnection();
}
/*
* Méthodes de récupération de l'implémentation des différents DAO (un seul
* pour le moment)
public EmployeurDao getEmployeurDao() {
return new EmployeurDaoImpl( this );
}*/
public CandidatDao getCandidatDao() {
return new CandidatDaoImpl( this );
}
public OffreDao getOffreDao() {
return new OffreDaoImpl( this );
}
/*
public CommandeDao getCommandeDao() {
return new CommandeDaoImpl( this );
}*/
}