MySQL 4.1 + Tomcat 5.5 + CVS version: Connection reset

[Migrated From Jforum.net]

When I run the CVS version on tomcat 5.5 and Mysql 4.1, the datasource loses it's connection every night and doesn't recover from it. Each morning I have to restart the tomcat server.
After a quick look in the code I have fixed the problem with following pieces of code in de C3P0PooledConnection:

public Connection getConnection()
	{
		try {
			Connection conn = this.ds.getConnection();
			if (!testConnection(conn)) {
				realReleaseAllConnections();
				init();
			}
			return conn;
		}
		catch (Exception e) {
			throw new DatabaseException(e);
		}
	}

	private boolean testConnection(Connection conn) {
		boolean retValue = false;
		PreparedStatement pstmt = null;
		ResultSet resultSet = null;
		try {
			pstmt = conn.prepareStatement("select * from jforum_users");
			resultSet = pstmt.executeQuery();
			retValue = true;
		} catch(Exception e) {
			logger.warn("Connection has timed out");
		} finally {
			if (resultSet != null) {
				try {
					resultSet.close();
				} catch (Exception e) {}
			}
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (Exception e) {}
			}
		}
		return retValue;
	}

Now I'm going to test the connection before returning it to the application. If it fails, I'm refreshing the entire connection pool.

[Edit[Sorry, this is in the wrong subforum, can you move it to the correct one. Thanks[/Edit]
[originally posted on jforum.net by mortis__rigor]

[Migrated From Jforum.net]

You don't need this code hack. C3P0 already have these configuration options for you.
Open the file WEB-INF/classes/c3p0.properties and add this:

c3p0.preferredTestQuery = select 1
c3p0.testConnectionOnCheckin = true

This will test the connection every time.

Also, please note that this is mysql's behaviour, that, by default, kills all connections that were inactive for 8 hours.
For that, add he following option to c3p0.properties:

c3p0.idleConnectionTestPeriod = <time in seconds>

Note that, if you use this option, you don't need the first two

Rafael
[originally posted on jforum.net by Rafael Steil]

[Migrated From Jforum.net]

Ok, thank you. I will check it out.
[originally posted on jforum.net by mortis__rigor]

[Migrated From Jforum.net]

I'm sorry too that those sollutions didn't fixed the problem.
However, I did some code reading and did find the solution. I added the following parameter to the configuration file:
database.ping.delay=<time in seconds>
[originally posted on jforum.net by mortis__rigor]