Hiya. I'm using red5 1.0.0rc1 to create an online game. I'm connecting to a MySQL database using a jdbc mysql connector v5.1.12
it seems that after several hours of idle my application can continue running queries because the connection to the db got closed and i have to restart the application.
how can I resolve the issue ?
Kfir
The MySQL JDBC driver has an autoreconnect feature that can be helpful on occasion; see Driver/Datasource Class Names, URL Syntax and Configuration Properties for Connector/J, and read the caveats.
In my experience the best solution is to change the MySQL configuration properties to set the session idle timeout to a really large number.
Do you have a validationQuery defined (like select 1)? If not, using a validation query would help.
Well, you reopen the connection.
Connection pools (which are highly recommended, BTW, and if you run Java EE your container - Tomcat, JBoss, etc - can provide a javax.sql.DataSource through JNDI which can handle pooling and more for you) validate connections before handing them out by running a very simple validation query (like SELECT 1 or something). If the validation query doesn't work, it throws away the connection and opens a new one.
Increasing the connection or server timeout tends to just postpone the inevitable.
I had the Same issue for my application and I have removed the idle time out tag Thats it
It really worked fine try this, I was using the Jboss server, in that i have made the following change in mysql-ds.xml file.
Let me know if you have any more doubts
The normal JDBC idiom is to acquire and close the Connection (and also Statement and ResultSet) in the shortest possible scope, i.e. in the very same try-finally block of the method as you're executing the query. You should not hold the connection open all the time. The DB will timeout and reclaim it sooner or later. In MySQL it's by default after 8 hours.
To improve connecting performance you should really consider using a connection pool, like c3p0 (here's a developer guide). Note that even when using a connection pool, you still have to write proper JDBC code: acquire and close all the resources in the shortest possible scope. The connection pool will in turn worry about actually closing the connection or just releasing it back to pool for further reuse.
Here's a kickoff example how your method retrieving a list of entities from the DB should look like:
public List<Entity> list() throws SQLException {
// Declare resources.
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
List<Entity> entities = new ArrayList<Entity>();
try {
// Acquire resources.
connection = database.getConnection();
statement = connection.createStatement("SELECT id, name, value FROM entity");
resultSet = statement.executeQuery();
// Gather data.
while (resultSet.next()) {
Entity entity = new Entity();
entity.setId(resultSet.getLong("id"));
entity.setName(resultSet.getString("name"));
entity.setValue(resultSet.getInteger("value"));
entities.add(entity);
}
} finally {
// Close resources in reversed order.
if (resultSet != null) try { resultSet.close(); } catch (SQLException logOrIgnore) {}
if (statement != null) try { statement.close(); } catch (SQLException logOrIgnore) {}
if (connection != null) try { connection.close(); } catch (SQLException logOrIgnore) {}
}
// Return data.
return entities;
}