Mailing-List: contact users-help@tomcat.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Tomcat Users List" <users@tomcat.apache.org>
Received-SPF: pass (nike.apache.org: domain of lists@nabble.com designates
 216.139.236.158 as permitted sender)
Message-ID: <25693459.post@talk.nabble.com>
Date: Thu, 1 Oct 2009 00:47:09 -0700 (PDT)
From: MartinOShea <appy74@dsl.pipex.com>
To: users@tomcat.apache.org
Subject: Re: Tomcat 6.X and MySQL connection pooling issue
In-Reply-To: <25677820.post@talk.nabble.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
References: <25677820.post@talk.nabble.com>


Changing  testOnBorrow = "true" has not changed the situation with the
connection pool failing  -can anyone suggest anything?

Thanks.

Martin.


MartinOShea wrote:
> 
> Hello
> 
> I wonder if anyone can advise me on this issue. I have a Tomcat 6.X Java /
> JSP application which uses connection pooling to access a MySQL database
> but, if the application is left for up to eight hours, one of the pages
> fails to display the contents of a dataset upon loading. 
> 
> Looking into the logs, I find that I have the following:
> 
> ERROR|29 09 2009|08 42
> 19|http-8080-4|myDataSharer.database_access.Database_Metadata_DBA| - Error
> getting types of columns of tabular Dataset 12
> 
> com.mysql.jdbc.CommunicationsException: Communications link failure due to
> underlying exception: 
> 
> ** BEGIN NESTED EXCEPTION ** 
> 
> java.io.EOFException
> 
> STACKTRACE:
> 
> java.io.EOFException
> 	at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:1956)
> 	at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2368)
> 	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2867)
> 	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1616)
> 	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1708)
> 	at com.mysql.jdbc.Connection.execSQL(Connection.java:3255)
> 	at
> com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1293)
> 	at
> com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1428)
> 	at
> org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93)
> 	at
> myDataSharer.database_access.Database_Metadata_DBA.getTabDSColumnTypes(Database_Metadata_DBA.java:364)
> 	at
> myDataSharer.database_access.Dataset_DBA.getTabDSAsHTMLTable(Dataset_DBA.java:1266)
> 
> And so on for several hundred lines.
> 
> Now the page in question is meant to display data from one of a number of
> tables in MySQL which vary in their column types. Each table forms a
> dataset which is created from data extracted from RSS feeds or an Excel /
> CSV file uploaded by a user, hence the varying table column types. 
> 
> So, in the absence of defined object types, what the application does is
> to look up the table's column types in a query
> (Database_Metadata_DBA.getTabDSColumnTypes) and then use the output from
> this to build a HTML table 
> (Dataset_DBA.getTabDSAsHTMLTable) which is returned to the servlet for
> displaying in a JSP. Each database operation uses code similar to the
> example below and these work perfectly normally. But I think what is
> happening after eight hours of inactivity, is that the system is trying to
> reuse a connection pool object that MySQL has closed down after its
> default eight hour period.
> 
> But I can't seem to resolve this problem and I wonder if anyone can
> advise?
> 
> The relevant part of my application's context.xml file is:
> 
> name = "jdbc/myDataSharer"
>     auth = "Container"
>     maxActive = "100"
>     maxIdle = "30"
>     maxWait = "10000"
>     username = "XXXX"
>     password = "XXXXXXXXXX"
>     driverClassName = "com.mysql.jdbc.Driver"
>     url = "jdbc:mysql://XXXXXXXXXX:XXXX/myDataSharer?autoReconnect=true"
>     logAbandoned = "true"
>     minEvictableIdleTimeMillis = "30000"
>     numTestsPerEvictionRun = "5"
>     removeAbandoned = "true"
>     removeAbandonedTimeout = "120"
>     testOnBorrow = "false"
>     testOnReturn = "false"
>     testWhileIdle = "true"
>     timeBetweenEvictionRunsMillis = "60000"
>     type = "javax.sql.DataSource"
>     validationQuery = "select now()" />
> 
> And my application uses servlets which generally have several database IO
> operations each of the form :
> 
> public static Dataset getDataset(int DatasetNo) 
> {    
> ConnectionPool_DBA pool = ConnectionPool_DBA.getInstance();    
> Connection connection = pool.getConnection();    
> PreparedStatement ps = null;    
> ResultSet rs = null;    
> String query = ("SELECT * " +                   
> "FROM Dataset " +                    
> "WHERE DatasetNo = ?;");    
> try {        
> ps = connection.prepareStatement(query);        
> ps.setInt(1, DatasetNo);        
> rs = ps.executeQuery();        
> if (rs.next()) 
> {            
> Dataset d = new Dataset();            
> d.setDatasetNo(rs.getInt("DatasetNo"));            
> d.setDatasetName(rs.getString("DatasetName"));            
> ...            
> }            
> return d;        
> }        
> else {            
> return null;        
> }    
> }    
> catch(Exception ex) 
> {        
> logger.error("Error getting Dataset " + DatasetNo + "\n", ex);                    
> return null;    
> }    
> finally 
> {        
> DatabaseUtils.closeResultSet(rs);        
> DatabaseUtils.closePreparedStatement(ps);        
> pool.freeConnection(connection);    
> }
> 
> Where class ConnectionPool_DBA is my own DBCP-based class. 
> 
> Apologies for the length of this message but any help would be much
> appreciated. Is there a MySQL setting which should be changed at all?
> 
> Thanks
> 
> Martin O'Shea.
> 

-- 
View this message in context: http://www.nabble.com/Tomcat-6.X-and-MySQL-connection-pooling-issue-tp25677820p25693459.html
Sent from the Tomcat - User mailing list archive at Nabble.com.


---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org