Re: Connection is invalid -- how to check for this and get a valid connection

[Guy Rouillier]

On 5/10/2013 5:59 PM, ooper wrote:
> Occasionally I will get this error message:
>
> Error querying database. Cause: java.sql.SQLException: Error accessing
> PooledConnection. Connection is invalid.
>
> Restarting Tomcat fixes it, but obviously this is not a good solution.
> What is best practice for avoiding this error in MyBatis 3.1.1?

In your Tomcat Resource definition for the DataSource, specify a
validationQuery. Then each time Tomcat hands out a connection from the
pool, it will first run the validationQuery to ensure the connection is
still valid.

--
Guy Rouillier

[Brian Barnett]

Should a configuration like this solve the problem?

<environment id="production">
<transactionManager type="JDBC">
</transactionManager>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url"
value="jdbc:mysql://myserver.com/mydb"/>
<property name="username" value="myusername"/>
<property name="password" value="mypassword"/>
<property name="poolMaximumActiveConnections" value="30"/>
<property name="poolMaximumIdleConnections" value="5"/>
<property name="poolPingEnabled" value="true"/>
<property name="poolPingQuery" value="select 1"/>
</dataSource>
</environment>

--
You received this message because you are subscribed to the Google Groups
"mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an
email to mybatis-user...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

[Guy Rouillier]

No, that is setting up a MyBatis pooled connection. You said you are
using Tomcat. In that case, you should be using a Tomcat-configured
Resource for your connection pool; that would type="JNDI". Then in
your Tomcat server.xml, you'd have one or more resources defined like this:

<Resource
name="jdbc/myDB"
auth="Container"
type="javax.sql.DataSource"
driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://myserver.com/mydb"
username="myusername"
password="mypassword"
initialSize="0"
maxActive="30"
maxIdle="10"
validationQuery="select 1"
testOnBorrow="true"
removeAbandoned="true"
removeAbandonedTimeout="300"
logAbandoned="true"
/>

Guy Rouillier

[Brian Barnett]

Yes I am using Tomcat, but not necessarily for db connection pooling
functionality. I determined that the JDBC MyBatis connection pool as
configured below does not work. After a long period of time has elapsed
without db activity, I still get invalid connection errors.

Is there a MyBatis connection pool configuration that would fix this?
Otherwise, I suppose I will be forced to try the Tomcat approach as you have
suggested.

[Chris]

Have you confirmed that there isn't something else closing the connections on you?  Perhaps a firewall residing between the app and database servers?  Just a thought...

---

From: Brian Barnett <br...@fincalc.com>
To: mybati...@googlegroups.com
Sent: Monday, May 13, 2013 1:53 PM
Subject: RE: Connection is invalid -- how to check for this and get a valid connection

email to mybatis-user+unsub...@googlegroups.com.

For more options, visit https://groups.google.com/groups/opt_out.

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.

To unsubscribe from this group and stop receiving emails from it, send an email to mybatis-user+unsub...@googlegroups.com.

[Brian Barnett]

I’m not sure how I would check something like that. We do have a load balancer between app and db servers, but it has not been a problem for us in the past. We recently upgraded from an old, old version of MyBatis and in the upgrade process, I wanted to use MyBatis connection pooling where before we were using JNDI through Tomcat. There was not a problem with connections going stale before, so I suspect there is not something else closing connections on us.

 

Just hoping to solve this using MyBatis connection pooling…

To unsubscribe from this group and stop receiving emails from it, send an email to mybatis-user...@googlegroups.com.

[Chris]

I have seen similar cases where a firewall between an app server and DB caused problems with connections getting closed.  When you switched from using Tomcat to manage the pool, did you possibly upgrade a driver?  Could you do a test where you just put a simple java program on the app server, outside of the context of the application server, that opens connections to the database and keeps them open for an extended period?  It wouldn't have to do much, other than just execute some simple query at some interval that tries to mimic the application behavior you are seeing.  It might be useful to know for sure that the issue with DB connections is repeatable outside the context of any pool (or not repeatable, which would tell you for sure that the problem is code related and not environment related).

Either way, good luck.   These sorts of problems always seem to get resolved by some poor soul, late at night...  sigh.

Chris

---

From: Brian Barnett <br...@fincalc.com>
To: mybati...@googlegroups.com

Sent: Monday, May 13, 2013 2:21 PM