You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by bca <be...@gmail.com> on 2007/03/22 19:50:24 UTC
Re: Connection closing after timeout on tomcat
Eric:
were you able to get this issue resolved? if so, what did you do to get it
resolved?
I am having the same issue as well..
appreciate your help.
thanks ..
Eric Bauld wrote:
>
> The SQL Map used to be accessed as a single static object but I ended up
> changing it when the connection was closing to try to fix the problem.
> I'm going to leave it the way it is right now to see if the connection
> timeouts are fixed. Once I know they are I will go back to something
> like this
>
> /**
> * Will setup the SqlMapper, if already setup will do nothing
> */
> public static void setIBATIS(String iBATIS) {
> if(sqlMap == null){
> SQLMapper.iBATIS = iBATIS;
> setup();
> }
> }
>
> Thanks a bunch everyone, I hope this thing is fixed now.
>
> Chris Lamey wrote:
>> Hello,
>>
>> For what it's worth, sqlMapClients are thread-safe, so you can construct
>> one at webapp init time and share it between all servlets. Creating a
>> new sqlMapClient on every HTTP request is heavyweight and not needed.
>> Also, the way SQLMapper is written, you could have different threads
>> coming in and getting each other sqlMapClients, which wouldn't be a big
>> deal in this case, but is something to note.
>>
>> Cheers,
>> Chris
>>
>> On Fri, 2006-08-25 at 13:18 -0600, Eric Bauld wrote:
>>
>>> Unsure about using DBCP so probably not. I am creating a new instance of
>>> a SqlMapClient each time a servlet is run.
>>>
>>> iBatis 2.1.7
>>> apache-tomcat 5.5.7
>>> mysql 4.1.11
>>>
>>> The servlets are accessed from a php script and the output is passed
>>> through apache as a excell file. Its not used that often.
>>>
>>> Each servlet executes this
>>> SQLMapper.setIBATIS( sysprops.getProperty( "ibatis" ) );
>>>
>>> And everything that uses ibatis accesses the SqlMapClient does so via
>>> SQLMapper.getSqlMapInstance()
>>>
>>>
>>> And SQLMapper is as follows
>>>
>>>
>>> package rp.broker;
>>>
>>> import java.io.Reader;
>>> import java.sql.SQLException;
>>>
>>>
>>> import com.ibatis.common.resources.Resources;
>>> import com.ibatis.sqlmap.client.SqlMapClient;
>>> import com.ibatis.sqlmap.client.SqlMapClientBuilder;
>>>
>>>
>>>
>>> public class SQLMapper {
>>> private static SqlMapClient sqlMap;
>>> private static String iBATIS;
>>>
>>> private static void setup(){
>>> try {
>>>
>>> String resource = SQLMapper.getIBATIS();
>>> Reader reader = Resources.getResourceAsReader(resource);
>>> sqlMap = null;
>>> SQLMapper.sqlMap =
>>> SqlMapClientBuilder.buildSqlMapClient(reader);
>>> } catch (Exception e) {
>>> //An error at this point is unrecoverable, so this should
>>> die
>>> e.printStackTrace();
>>> throw new RuntimeException(
>>> "Error initializing SQLMapper class.Cause : " +e);
>>> }
>>> }
>>>
>>> public static SqlMapClient getSqlMapInstance() throws SQLException {
>>>
>>> return sqlMap;
>>> }
>>>
>>> public static void close(){
>>> }
>>>
>>> public static void setIBATIS(String iBATIS) {
>>> SQLMapper.iBATIS = iBATIS;
>>> setup();
>>> }
>>> public static String getIBATIS() {
>>> return iBATIS;
>>> }
>>> }
>>>
>>> Kris Schneider wrote:
>>>
>>>> On 8/25/06, Eric Bauld <ba...@cpsc.ucalgary.ca> wrote:
>>>>
>>>>> Im having a heck of a time finding the right way to fix this.
>>>>> I have a servlet on a tomcat server, it uses ibatis. It works great as
>>>>> long as it is being used but when it sits overnight, the connection
>>>>> times out and the next person to use it will get an error. But then it
>>>>> will work for every request thereafter.
>>>>> I get this exception after it has been sitting.
>>>>>
>>>>> java.sql.SQLException: No operations allowed after connection closed
>>>>>
>>>>> Anyone know of a way to test this to see if the connection has closed
>>>>> ?
>>>>>
>>>> If you're using DBCP for your connection pooling, see this:
>>>>
>>>> http://jakarta.apache.org/commons/dbcp/configuration.html
>>>>
>>>> Specifically the testOnBorrow and validationQuery params.
>>>>
>>>>
>>>>> I could just put a bogus query + error catch to "wake up" the
>>>>> connection, but its not a very proper fix.
>>>>>
>>>>> - Eric
>>>>>
>
>
>
--
View this message in context: http://www.nabble.com/Connection-closing-after-timeout-on-tomcat-tf2166355.html#a9621570
Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
Re: Connection closing after timeout on tomcat
Posted by bca <be...@gmail.com>.
hmm ok.. i'll try the autoReconnect=true. .
i'm not using dbcp though... just jdbc.. simple..
<transactionManager type="JDBC">
<dataSource type="SIMPLE">
<property name="JDBC.Driver" value="${driver}"/>
<property name="JDBC.ConnectionURL" value="${jdbcURL}"/>
<property name="JDBC.Username" value="${username}"/>
<property name="JDBC.Password" value="${password}"/>
<property name="Pool.PingQuery" value="select 1 from
fdblseason"/>
<property name="Pool.PingEnabled" value="true"/>
<property name="Pool.PingConnectionsOlderThan" value="3600000"/>
<property name="Pool.PingConnectionsNotUsedFor" value="3600000"/>
</dataSource>
</transactionManager>
Jerome Gagner wrote:
>
> To ammend my previous email, the the reason you need ?autoReconnect=true
> with MySQL is because the server explicitly closes the connection after a
> period of inactivity, which causes a problem with pooling, as DBCP will
> keep
> a few connections open depending on your configuration. The working after
> a
> refresh can be explained by DBCP throwing away the connection, and
> creating
> a new one.
>
> On 3/22/07, bca <be...@gmail.com> wrote:
>>
>>
>> Eric:
>>
>> were you able to get this issue resolved? if so, what did you do to get
>> it
>> resolved?
>>
>> I am having the same issue as well..
>>
>> appreciate your help.
>>
>> thanks ..
>>
>>
>> Eric Bauld wrote:
>> >
>> > The SQL Map used to be accessed as a single static object but I ended
>> up
>> > changing it when the connection was closing to try to fix the problem.
>> > I'm going to leave it the way it is right now to see if the connection
>> > timeouts are fixed. Once I know they are I will go back to something
>> > like this
>> >
>> > /**
>> > * Will setup the SqlMapper, if already setup will do nothing
>> > */
>> > public static void setIBATIS(String iBATIS) {
>> > if(sqlMap == null){
>> > SQLMapper.iBATIS = iBATIS;
>> > setup();
>> > }
>> > }
>> >
>> > Thanks a bunch everyone, I hope this thing is fixed now.
>> >
>> > Chris Lamey wrote:
>> >> Hello,
>> >>
>> >> For what it's worth, sqlMapClients are thread-safe, so you can
>> construct
>> >> one at webapp init time and share it between all servlets. Creating a
>> >> new sqlMapClient on every HTTP request is heavyweight and not needed.
>> >> Also, the way SQLMapper is written, you could have different threads
>> >> coming in and getting each other sqlMapClients, which wouldn't be a
>> big
>> >> deal in this case, but is something to note.
>> >>
>> >> Cheers,
>> >> Chris
>> >>
>> >> On Fri, 2006-08-25 at 13:18 -0600, Eric Bauld wrote:
>> >>
>> >>> Unsure about using DBCP so probably not. I am creating a new instance
>> of
>> >>> a SqlMapClient each time a servlet is run.
>> >>>
>> >>> iBatis 2.1.7
>> >>> apache-tomcat 5.5.7
>> >>> mysql 4.1.11
>> >>>
>> >>> The servlets are accessed from a php script and the output is passed
>> >>> through apache as a excell file. Its not used that often.
>> >>>
>> >>> Each servlet executes this
>> >>> SQLMapper.setIBATIS( sysprops.getProperty( "ibatis" ) );
>> >>>
>> >>> And everything that uses ibatis accesses the SqlMapClient does so via
>> >>> SQLMapper.getSqlMapInstance()
>> >>>
>> >>>
>> >>> And SQLMapper is as follows
>> >>>
>> >>>
>> >>> package rp.broker;
>> >>>
>> >>> import java.io.Reader;
>> >>> import java.sql.SQLException;
>> >>>
>> >>>
>> >>> import com.ibatis.common.resources.Resources;
>> >>> import com.ibatis.sqlmap.client.SqlMapClient;
>> >>> import com.ibatis.sqlmap.client.SqlMapClientBuilder;
>> >>>
>> >>>
>> >>>
>> >>> public class SQLMapper {
>> >>> private static SqlMapClient sqlMap;
>> >>> private static String iBATIS;
>> >>>
>> >>> private static void setup(){
>> >>> try {
>> >>>
>> >>> String resource = SQLMapper.getIBATIS();
>> >>> Reader reader = Resources.getResourceAsReader(resource);
>> >>> sqlMap = null;
>> >>> SQLMapper.sqlMap =
>> >>> SqlMapClientBuilder.buildSqlMapClient(reader);
>> >>> } catch (Exception e) {
>> >>> //An error at this point is unrecoverable, so this should
>> >>> die
>> >>> e.printStackTrace();
>> >>> throw new RuntimeException(
>> >>> "Error initializing SQLMapper class.Cause : " +e);
>> >>> }
>> >>> }
>> >>>
>> >>> public static SqlMapClient getSqlMapInstance() throws
>> SQLException
>> {
>> >>>
>> >>> return sqlMap;
>> >>> }
>> >>>
>> >>> public static void close(){
>> >>> }
>> >>>
>> >>> public static void setIBATIS(String iBATIS) {
>> >>> SQLMapper.iBATIS = iBATIS;
>> >>> setup();
>> >>> }
>> >>> public static String getIBATIS() {
>> >>> return iBATIS;
>> >>> }
>> >>> }
>> >>>
>> >>> Kris Schneider wrote:
>> >>>
>> >>>> On 8/25/06, Eric Bauld <ba...@cpsc.ucalgary.ca> wrote:
>> >>>>
>> >>>>> Im having a heck of a time finding the right way to fix this.
>> >>>>> I have a servlet on a tomcat server, it uses ibatis. It works great
>> as
>> >>>>> long as it is being used but when it sits overnight, the connection
>> >>>>> times out and the next person to use it will get an error. But then
>> it
>> >>>>> will work for every request thereafter.
>> >>>>> I get this exception after it has been sitting.
>> >>>>>
>> >>>>> java.sql.SQLException: No operations allowed after connection
>> closed
>> >>>>>
>> >>>>> Anyone know of a way to test this to see if the connection has
>> closed
>> >>>>> ?
>> >>>>>
>> >>>> If you're using DBCP for your connection pooling, see this:
>> >>>>
>> >>>> http://jakarta.apache.org/commons/dbcp/configuration.html
>> >>>>
>> >>>> Specifically the testOnBorrow and validationQuery params.
>> >>>>
>> >>>>
>> >>>>> I could just put a bogus query + error catch to "wake up" the
>> >>>>> connection, but its not a very proper fix.
>> >>>>>
>> >>>>> - Eric
>> >>>>>
>> >
>> >
>> >
>>
>> --
>> View this message in context:
>> http://www.nabble.com/Connection-closing-after-timeout-on-tomcat-tf2166355.html#a9621570
>> Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
>>
>>
>
>
--
View this message in context: http://www.nabble.com/Connection-closing-after-timeout-on-tomcat-tf2166355.html#a9624003
Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
Re: Connection closing after timeout on tomcat
Posted by Jerome Gagner <ph...@gmail.com>.
To ammend my previous email, the the reason you need ?autoReconnect=true
with MySQL is because the server explicitly closes the connection after a
period of inactivity, which causes a problem with pooling, as DBCP will keep
a few connections open depending on your configuration. The working after a
refresh can be explained by DBCP throwing away the connection, and creating
a new one.
On 3/22/07, bca <be...@gmail.com> wrote:
>
>
> Eric:
>
> were you able to get this issue resolved? if so, what did you do to get
> it
> resolved?
>
> I am having the same issue as well..
>
> appreciate your help.
>
> thanks ..
>
>
> Eric Bauld wrote:
> >
> > The SQL Map used to be accessed as a single static object but I ended up
> > changing it when the connection was closing to try to fix the problem.
> > I'm going to leave it the way it is right now to see if the connection
> > timeouts are fixed. Once I know they are I will go back to something
> > like this
> >
> > /**
> > * Will setup the SqlMapper, if already setup will do nothing
> > */
> > public static void setIBATIS(String iBATIS) {
> > if(sqlMap == null){
> > SQLMapper.iBATIS = iBATIS;
> > setup();
> > }
> > }
> >
> > Thanks a bunch everyone, I hope this thing is fixed now.
> >
> > Chris Lamey wrote:
> >> Hello,
> >>
> >> For what it's worth, sqlMapClients are thread-safe, so you can
> construct
> >> one at webapp init time and share it between all servlets. Creating a
> >> new sqlMapClient on every HTTP request is heavyweight and not needed.
> >> Also, the way SQLMapper is written, you could have different threads
> >> coming in and getting each other sqlMapClients, which wouldn't be a big
> >> deal in this case, but is something to note.
> >>
> >> Cheers,
> >> Chris
> >>
> >> On Fri, 2006-08-25 at 13:18 -0600, Eric Bauld wrote:
> >>
> >>> Unsure about using DBCP so probably not. I am creating a new instance
> of
> >>> a SqlMapClient each time a servlet is run.
> >>>
> >>> iBatis 2.1.7
> >>> apache-tomcat 5.5.7
> >>> mysql 4.1.11
> >>>
> >>> The servlets are accessed from a php script and the output is passed
> >>> through apache as a excell file. Its not used that often.
> >>>
> >>> Each servlet executes this
> >>> SQLMapper.setIBATIS( sysprops.getProperty( "ibatis" ) );
> >>>
> >>> And everything that uses ibatis accesses the SqlMapClient does so via
> >>> SQLMapper.getSqlMapInstance()
> >>>
> >>>
> >>> And SQLMapper is as follows
> >>>
> >>>
> >>> package rp.broker;
> >>>
> >>> import java.io.Reader;
> >>> import java.sql.SQLException;
> >>>
> >>>
> >>> import com.ibatis.common.resources.Resources;
> >>> import com.ibatis.sqlmap.client.SqlMapClient;
> >>> import com.ibatis.sqlmap.client.SqlMapClientBuilder;
> >>>
> >>>
> >>>
> >>> public class SQLMapper {
> >>> private static SqlMapClient sqlMap;
> >>> private static String iBATIS;
> >>>
> >>> private static void setup(){
> >>> try {
> >>>
> >>> String resource = SQLMapper.getIBATIS();
> >>> Reader reader = Resources.getResourceAsReader(resource);
> >>> sqlMap = null;
> >>> SQLMapper.sqlMap =
> >>> SqlMapClientBuilder.buildSqlMapClient(reader);
> >>> } catch (Exception e) {
> >>> //An error at this point is unrecoverable, so this should
> >>> die
> >>> e.printStackTrace();
> >>> throw new RuntimeException(
> >>> "Error initializing SQLMapper class.Cause : " +e);
> >>> }
> >>> }
> >>>
> >>> public static SqlMapClient getSqlMapInstance() throws SQLException
> {
> >>>
> >>> return sqlMap;
> >>> }
> >>>
> >>> public static void close(){
> >>> }
> >>>
> >>> public static void setIBATIS(String iBATIS) {
> >>> SQLMapper.iBATIS = iBATIS;
> >>> setup();
> >>> }
> >>> public static String getIBATIS() {
> >>> return iBATIS;
> >>> }
> >>> }
> >>>
> >>> Kris Schneider wrote:
> >>>
> >>>> On 8/25/06, Eric Bauld <ba...@cpsc.ucalgary.ca> wrote:
> >>>>
> >>>>> Im having a heck of a time finding the right way to fix this.
> >>>>> I have a servlet on a tomcat server, it uses ibatis. It works great
> as
> >>>>> long as it is being used but when it sits overnight, the connection
> >>>>> times out and the next person to use it will get an error. But then
> it
> >>>>> will work for every request thereafter.
> >>>>> I get this exception after it has been sitting.
> >>>>>
> >>>>> java.sql.SQLException: No operations allowed after connection closed
> >>>>>
> >>>>> Anyone know of a way to test this to see if the connection has
> closed
> >>>>> ?
> >>>>>
> >>>> If you're using DBCP for your connection pooling, see this:
> >>>>
> >>>> http://jakarta.apache.org/commons/dbcp/configuration.html
> >>>>
> >>>> Specifically the testOnBorrow and validationQuery params.
> >>>>
> >>>>
> >>>>> I could just put a bogus query + error catch to "wake up" the
> >>>>> connection, but its not a very proper fix.
> >>>>>
> >>>>> - Eric
> >>>>>
> >
> >
> >
>
> --
> View this message in context:
> http://www.nabble.com/Connection-closing-after-timeout-on-tomcat-tf2166355.html#a9621570
> Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
>
>