How to correct the time out problem?

classic Classic list List threaded Threaded
11 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

How to correct the time out problem?

陈抒
Hi,
   I get this error message after my service runs for some hours.
Throwable occured in ResultFileHandler of Portage service:
### Error querying database.  Cause: com.mysql.jdbc.exceptions.
jdbc4.CommunicationsException: The last packet successfully received from the server was37995 seconds ago.The last packet sent successfully to the server was 37995 seconds ago, which  is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.
### The error may involve iBATIS3.Xml.UserMapper.searchEmailTask-Inline
### The error occurred while setting parameters
### Cause: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was37995 seconds ago.The last packet sent successfully to the server was 37995 seconds ago, which  is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.
  
    I am using iBatis3.The following is my configuration file.
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//ibatis.apache.org//DTD Config 3.0//EN"
"http://ibatis.apache.org/dtd/ibatis-3-config.dtd">
<configuration>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://10.23.1.155:3306/bulkupload" />
                <property name="username" value="freebird"/>
                <property name="password" value="770328"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="iBATIS3/Xml/user.map.xml" />
    </mappers>
</configuration>

   How to correct it?
Thanks.




陈抒
Best regards
http://blog.csdn.net/sheismylife
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: How to correct the time out problem?

Martin Ellis-4
On 9 May 2010 07:43, 陈抒 <[hidden email]> wrote:
> You should consider either expiring and/or testing
> connection validity before use in your application, increasing the server
> configured values for client timeouts, or using the Connector/J connection
> property 'autoReconnect=true' to avoid this problem.

The error message you've posted lists three ways to correct the problem.

The third is probably the easiest to try, it's a one line change: just add
?autoReconnect=true to your JDBC URI.

Martin

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: How to correct the time out problem?

陈抒
After adding ?autoReconnect=true,I got the following error message.


[Level:ERROR] [Time:2010-05-10 22:30:27,103] [Thread: pool-1-thread-1] [Class:PortageService] [Method:?] [Line:?]
Throwable in ResultFileHandler.run:
### Error updating database.  Cause: java.lang.NullPointerException
### The error may exist in iBATIS3/Xml/user.map.xml
### The error may involve iBATIS3.Xml.UserMapper.addMailRequest
### The error occurred while executing an update
### Cause: java.lang.NullPointerException
org.apache.ibatis.exceptions.IbatisException:
### Error updating database.  Cause: java.lang.NullPointerException
### The error may exist in iBATIS3/Xml/user.map.xml
### The error may involve iBATIS3.Xml.UserMapper.addMailRequest
### The error occurred while executing an update
### Cause: java.lang.NullPointerException
        at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:8)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:100)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:87)
        at freebird.business.MailManager.addMailRequest(MailManager.java:18)
        at freebird.helper.MailHelper.sendMailRequestToUser(MailHelper.java:68)
        at com.exactor.cn.portageservice.ResultFileHandler.handleCorrectResultFile(ResultFileHandler.java:99)
        at com.exactor.cn.portageservice.ResultFileHandler.run(ResultFileHandler.java:123)
        at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
        at java.lang.Thread.run(Thread.java:619)
Caused by: java.lang.NullPointerException
        at freebird.business.Email.getReceiverList(Email.java:105)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:597)
        at org.apache.ibatis.reflection.invoker.MethodInvoker.invoke(MethodInvoker.java:22)
        at org.apache.ibatis.reflection.wrapper.BeanWrapper.getBeanProperty(BeanWrapper.java:133)
        at org.apache.ibatis.reflection.wrapper.BeanWrapper.get(BeanWrapper.java:27)
        at org.apache.ibatis.reflection.MetaObject.getValue(MetaObject.java:103)
        at org.apache.ibatis.builder.xml.dynamic.DynamicContext.<init>(DynamicContext.java:29)
        at org.apache.ibatis.builder.xml.dynamic.DynamicSqlSource.getBoundSql(DynamicSqlSource.java:21)
        at org.apache.ibatis.mapping.MappedStatement.getBoundSql(MappedStatement.java:198)
        at org.apache.ibatis.executor.statement.BaseStatementHandler.<init>(BaseStatementHandler.java:45)
        at org.apache.ibatis.executor.statement.PreparedStatementHandler.<init>(PreparedStatementHandler.java:16)
        at org.apache.ibatis.executor.statement.RoutingStatementHandler.<init>(RoutingStatementHandler.java:27)
        at org.apache.ibatis.session.Configuration.newStatementHandler(Configuration.java:226)
        at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:27)
        at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:72)
        at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:43)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:98)
        ... 8 more

陈抒
Best regards
http://blog.csdn.net/sheismylife


On Mon, May 10, 2010 at 12:03 AM, Martin Ellis <[hidden email]> wrote:
On 9 May 2010 07:43, 陈抒 <[hidden email]> wrote:
> You should consider either expiring and/or testing
> connection validity before use in your application, increasing the server
> configured values for client timeouts, or using the Connector/J connection
> property 'autoReconnect=true' to avoid this problem.

The error message you've posted lists three ways to correct the problem.

The third is probably the easiest to try, it's a one line change: just add
?autoReconnect=true to your JDBC URI.

Martin

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]


Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: How to correct the time out problem?

陈抒
Hello:
   Who did solve this problem before?Can I add some properties to fix this.
       <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://192.168.0.110:3306/bulkupload?autoReconnect=true" />
                <property name="username" value="freebird"/>
                <property name="password" value="770328"/>
                <property name="poolPingQuery" value="select * from jobstatus"/>
                <property name="poolPingEnabled" value="true"/>
            </dataSource>
        </environment>

陈抒
Best regards
http://blog.csdn.net/sheismylife


On Wed, May 12, 2010 at 9:31 AM, 陈抒 <[hidden email]> wrote:
After adding ?autoReconnect=true,I got the following error message.


[Level:ERROR] [Time:2010-05-10 22:30:27,103] [Thread: pool-1-thread-1] [Class:PortageService] [Method:?] [Line:?]
Throwable in ResultFileHandler.run:
### Error updating database.  Cause: java.lang.NullPointerException
### The error may exist in iBATIS3/Xml/user.map.xml
### The error may involve iBATIS3.Xml.UserMapper.addMailRequest
### The error occurred while executing an update
### Cause: java.lang.NullPointerException
org.apache.ibatis.exceptions.IbatisException:
### Error updating database.  Cause: java.lang.NullPointerException
### The error may exist in iBATIS3/Xml/user.map.xml
### The error may involve iBATIS3.Xml.UserMapper.addMailRequest
### The error occurred while executing an update
### Cause: java.lang.NullPointerException
        at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:8)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:100)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:87)
        at freebird.business.MailManager.addMailRequest(MailManager.java:18)
        at freebird.helper.MailHelper.sendMailRequestToUser(MailHelper.java:68)
        at com.exactor.cn.portageservice.ResultFileHandler.handleCorrectResultFile(ResultFileHandler.java:99)
        at com.exactor.cn.portageservice.ResultFileHandler.run(ResultFileHandler.java:123)
        at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
        at java.lang.Thread.run(Thread.java:619)
Caused by: java.lang.NullPointerException
        at freebird.business.Email.getReceiverList(Email.java:105)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:597)
        at org.apache.ibatis.reflection.invoker.MethodInvoker.invoke(MethodInvoker.java:22)
        at org.apache.ibatis.reflection.wrapper.BeanWrapper.getBeanProperty(BeanWrapper.java:133)
        at org.apache.ibatis.reflection.wrapper.BeanWrapper.get(BeanWrapper.java:27)
        at org.apache.ibatis.reflection.MetaObject.getValue(MetaObject.java:103)
        at org.apache.ibatis.builder.xml.dynamic.DynamicContext.<init>(DynamicContext.java:29)
        at org.apache.ibatis.builder.xml.dynamic.DynamicSqlSource.getBoundSql(DynamicSqlSource.java:21)
        at org.apache.ibatis.mapping.MappedStatement.getBoundSql(MappedStatement.java:198)
        at org.apache.ibatis.executor.statement.BaseStatementHandler.<init>(BaseStatementHandler.java:45)
        at org.apache.ibatis.executor.statement.PreparedStatementHandler.<init>(PreparedStatementHandler.java:16)
        at org.apache.ibatis.executor.statement.RoutingStatementHandler.<init>(RoutingStatementHandler.java:27)
        at org.apache.ibatis.session.Configuration.newStatementHandler(Configuration.java:226)
        at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:27)
        at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:72)
        at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:43)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:98)
        ... 8 more
On Mon, May 10, 2010 at 12:03 AM, Martin Ellis <[hidden email]> wrote:
On 9 May 2010 07:43, 陈抒 <[hidden email]> wrote:
> You should consider either expiring and/or testing
> connection validity before use in your application, increasing the server
> configured values for client timeouts, or using the Connector/J connection
> property 'autoReconnect=true' to avoid this problem.

The error message you've posted lists three ways to correct the problem.

The third is probably the easiest to try, it's a one line change: just add
?autoReconnect=true to your JDBC URI.

Martin

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]



Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: How to correct the time out problem?

陈抒
In reply to this post by 陈抒
Hello:
   Who did solve this problem before?Can I add some properties to fix this.

       <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://192.168.0.110:3306/bulkupload?autoReconnect=true" />

                <property name="username" value="freebird"/>
                <property name="password" value="770328"/>
                <property name="poolPingQuery" value="select * from jobstatus"/>
                <property name="poolPingEnabled" value="true"/>
            </dataSource>
        </environment>
陈抒
Best regards
http://blog.csdn.net/sheismylife


On Wed, May 12, 2010 at 9:31 AM, 陈抒 <[hidden email]> wrote:
After adding ?autoReconnect=true,I got the following error message.


[Level:ERROR] [Time:2010-05-10 22:30:27,103] [Thread: pool-1-thread-1] [Class:PortageService] [Method:?] [Line:?]
Throwable in ResultFileHandler.run:
### Error updating database.  Cause: java.lang.NullPointerException
### The error may exist in iBATIS3/Xml/user.map.xml
### The error may involve iBATIS3.Xml.UserMapper.addMailRequest
### The error occurred while executing an update
### Cause: java.lang.NullPointerException
org.apache.ibatis.exceptions.IbatisException:
### Error updating database.  Cause: java.lang.NullPointerException
### The error may exist in iBATIS3/Xml/user.map.xml
### The error may involve iBATIS3.Xml.UserMapper.addMailRequest
### The error occurred while executing an update
### Cause: java.lang.NullPointerException
        at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:8)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:100)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:87)
        at freebird.business.MailManager.addMailRequest(MailManager.java:18)
        at freebird.helper.MailHelper.sendMailRequestToUser(MailHelper.java:68)
        at com.exactor.cn.portageservice.ResultFileHandler.handleCorrectResultFile(ResultFileHandler.java:99)
        at com.exactor.cn.portageservice.ResultFileHandler.run(ResultFileHandler.java:123)
        at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
        at java.lang.Thread.run(Thread.java:619)
Caused by: java.lang.NullPointerException
        at freebird.business.Email.getReceiverList(Email.java:105)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:597)
        at org.apache.ibatis.reflection.invoker.MethodInvoker.invoke(MethodInvoker.java:22)
        at org.apache.ibatis.reflection.wrapper.BeanWrapper.getBeanProperty(BeanWrapper.java:133)
        at org.apache.ibatis.reflection.wrapper.BeanWrapper.get(BeanWrapper.java:27)
        at org.apache.ibatis.reflection.MetaObject.getValue(MetaObject.java:103)
        at org.apache.ibatis.builder.xml.dynamic.DynamicContext.<init>(DynamicContext.java:29)
        at org.apache.ibatis.builder.xml.dynamic.DynamicSqlSource.getBoundSql(DynamicSqlSource.java:21)
        at org.apache.ibatis.mapping.MappedStatement.getBoundSql(MappedStatement.java:198)
        at org.apache.ibatis.executor.statement.BaseStatementHandler.<init>(BaseStatementHandler.java:45)
        at org.apache.ibatis.executor.statement.PreparedStatementHandler.<init>(PreparedStatementHandler.java:16)
        at org.apache.ibatis.executor.statement.RoutingStatementHandler.<init>(RoutingStatementHandler.java:27)
        at org.apache.ibatis.session.Configuration.newStatementHandler(Configuration.java:226)
        at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:27)
        at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:72)
        at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:43)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:98)
        ... 8 more
On Mon, May 10, 2010 at 12:03 AM, Martin Ellis <[hidden email]> wrote:
On 9 May 2010 07:43, 陈抒 <[hidden email]> wrote:
> You should consider either expiring and/or testing
> connection validity before use in your application, increasing the server
> configured values for client timeouts, or using the Connector/J connection
> property 'autoReconnect=true' to avoid this problem.

The error message you've posted lists three ways to correct the problem.

The third is probably the easiest to try, it's a one line change: just add
?autoReconnect=true to your JDBC URI.

Martin

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]



Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: How to correct the time out problem?

陈抒
In reply to this post by Martin Ellis-4
Hi,
'?autoReconnect=true' can't solve this problem.

I add the following properties.Are they helpful for this problem?
<property name="poolPingQuery" value="select * from jobstatus"/>
<property name="poolPingEnabled" value="true"/>

陈抒
Best regards
http://blog.csdn.net/sheismylife


On Mon, May 10, 2010 at 12:03 AM, Martin Ellis <[hidden email]> wrote:
On 9 May 2010 07:43, 陈抒 <[hidden email]> wrote:
> You should consider either expiring and/or testing
> connection validity before use in your application, increasing the server
> configured values for client timeouts, or using the Connector/J connection
> property 'autoReconnect=true' to avoid this problem.

The error message you've posted lists three ways to correct the problem.

The third is probably the easiest to try, it's a one line change: just add
?autoReconnect=true to your JDBC URI.

Martin

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]


Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: How to correct the time out problem?

Martin Ellis-4
In reply to this post by 陈抒
On 12 May 2010 02:31, 陈抒 <[hidden email]> wrote:
> Caused by: java.lang.NullPointerException
>         at freebird.business.Email.getReceiverList(Email.java:105)

What caused this NullPointerException?
Is it really related to the connection option?

Martin

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: How to correct the time out problem?

陈抒
I don't know.
But I took one hour to test my application again,it works fine if the 'time out' problem doesn't occurs.
Now I add two properties,let's wait and see if it can solve this or not.
<property name="poolPingQuery" value="select * from jobstatus"/>
<property name="poolPingEnabled" value="true"/>

陈抒
Best regards
http://blog.csdn.net/sheismylife


On Wed, May 12, 2010 at 6:46 PM, Martin Ellis <[hidden email]> wrote:
On 12 May 2010 02:31, 陈抒 <[hidden email]> wrote:
> Caused by: java.lang.NullPointerException
>         at freebird.business.Email.getReceiverList(Email.java:105)

What caused this NullPointerException?
Is it really related to the connection option?

Martin

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]


Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: How to correct the time out problem?

François Schiettecatte
陈抒

Not sure which RDBMS you are using. Ideally you want to set the idle connection timeout in your pool to a value smaller than the idle connection timeout on the server end, the errors you were seeing suggests that the server has dropped the connection while the pool thinks it is still ok. From my limited experience 'autoReconnect=true' is not recommended, here is an excerpt from the MySQL documentation:

----
Should the driver try to re-establish stale and/or dead connections? If enabled the driver will throw an exception for a queries issued on a stale or dead connection, which belong to the current transaction, but will attempt reconnect before the next query issued on the connection in a new transaction. The use of this feature is not recommended, because it has side effects related to session state and data consistency when applications don't handle SQLExceptions properly, and is only designed to be used when you are unable to configure your application to handle SQLExceptions resulting from dead and stale connections properly. Alternatively, investigate setting the MySQL server variable "wait_timeout" to some high value rather than the default of 8 hours.
----

What you are doing will work but there is overhead associated with it, and you might want use a query like 'select 1' rather than hitting the jobstatus table. And you might want to read up on 'poolPingConnectionsNotUsedFor' on page 20 of the documentation, the default value is 0 which means the connection is pinged before every use.

I would recommend you check into these parameters to see if you can do away with the ping query:

                <property name="poolMaximumActiveConnections" value="10" />
                <property name="poolMaximumIdleConnections" value="5" />
                <property name="poolMaximumCheckoutTime" value="20000" />
                <property name="poolTimeToWait" value="20000" />

Cheers

François

On May 12, 2010, at 6:57 AM, 陈抒 wrote:

> I don't know.
> But I took one hour to test my application again,it works fine if the 'time out' problem doesn't occurs.
> Now I add two properties,let's wait and see if it can solve this or not.
> <property name="poolPingQuery" value="select * from jobstatus"/>
> <property name="poolPingEnabled" value="true"/>
>
> 陈抒
> Best regards
> http://blog.csdn.net/sheismylife
>
>
> On Wed, May 12, 2010 at 6:46 PM, Martin Ellis <[hidden email]> wrote:
> On 12 May 2010 02:31, 陈抒 <[hidden email]> wrote:
> > Caused by: java.lang.NullPointerException
> >         at freebird.business.Email.getReceiverList(Email.java:105)
>
> What caused this NullPointerException?
> Is it really related to the connection option?
>
> Martin
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>
>


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: How to correct the time out problem?

陈抒
Hello:
Thanks a lot!
I am worrying about the overhead,because I don't know what iBatis3 will do when I enable poolPingQuery. 
I will try your idea soon.Thanks again!


陈抒
Best regards
http://blog.csdn.net/sheismylife


2010/5/12 François Schiettecatte <[hidden email]>
陈抒

Not sure which RDBMS you are using. Ideally you want to set the idle connection timeout in your pool to a value smaller than the idle connection timeout on the server end, the errors you were seeing suggests that the server has dropped the connection while the pool thinks it is still ok. From my limited experience 'autoReconnect=true' is not recommended, here is an excerpt from the MySQL documentation:

----
Should the driver try to re-establish stale and/or dead connections? If enabled the driver will throw an exception for a queries issued on a stale or dead connection, which belong to the current transaction, but will attempt reconnect before the next query issued on the connection in a new transaction. The use of this feature is not recommended, because it has side effects related to session state and data consistency when applications don't handle SQLExceptions properly, and is only designed to be used when you are unable to configure your application to handle SQLExceptions resulting from dead and stale connections properly. Alternatively, investigate setting the MySQL server variable "wait_timeout" to some high value rather than the default of 8 hours.
----

What you are doing will work but there is overhead associated with it, and you might want use a query like 'select 1' rather than hitting the jobstatus table. And you might want to read up on 'poolPingConnectionsNotUsedFor' on page 20 of the documentation, the default value is 0 which means the connection is pinged before every use.

I would recommend you check into these parameters to see if you can do away with the ping query:

               <property name="poolMaximumActiveConnections" value="10" />
               <property name="poolMaximumIdleConnections" value="5" />
               <property name="poolMaximumCheckoutTime" value="20000" />
               <property name="poolTimeToWait" value="20000" />

Cheers

François

On May 12, 2010, at 6:57 AM, 陈抒 wrote:

> I don't know.
> But I took one hour to test my application again,it works fine if the 'time out' problem doesn't occurs.
> Now I add two properties,let's wait and see if it can solve this or not.
> <property name="poolPingQuery" value="select * from jobstatus"/>
> <property name="poolPingEnabled" value="true"/>
>
> 陈抒
> Best regards
> http://blog.csdn.net/sheismylife
>
>
> On Wed, May 12, 2010 at 6:46 PM, Martin Ellis <[hidden email]> wrote:
> On 12 May 2010 02:31, 陈抒 <[hidden email]> wrote:
> > Caused by: java.lang.NullPointerException
> >         at freebird.business.Email.getReceiverList(Email.java:105)
>
> What caused this NullPointerException?
> Is it really related to the connection option?
>
> Martin
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>
>


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]


Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: How to correct the time out problem?

François Schiettecatte
陈抒

It is all explained on page 20 of the user guide:

        http://svn.apache.org/repos/asf/ibatis/java/ibatis-3/trunk/doc/en/iBATIS-3-User-Guide.pdf

the relevant section is:

• poolPingQuery – The Ping Query is sent to the database to validate that a connection is in good working order and is ready to accept requests. The default is "NO PING QUERY SET", which will cause most database drivers to fail with a decent error message.

• poolPingEnabled – This enables or disables the ping query. If enabled, you must also set the poolPingQuery property with
a valid SQL statement (preferably a very fast one). Default: false.

• poolPingConnectionsNotUsedFor – This configures how often the poolPingQuery will be used. This can be set to match the typical timeout for a database connection, to avoid unnecessary pings. Default: 0 (i.e. all connections are pinged every time – but only if poolPingEnabled is true of course).


Basically, if poolPingConnectionsNotUsedFor = 0 then the ping query will be sent before every time the connection is used, and if poolPingConnectionsNotUsedFor > 0 then the ping query will be sent if the connection has been idle for at least that amount of time (you should set this to a smaller value than your timeout, which looks like it is set to 38000 seconds or 10.5 hours).

Cheers

François

On May 12, 2010, at 8:18 AM, 陈抒 wrote:

> Hello:
> Thanks a lot!
> I am worrying about the overhead,because I don't know what iBatis3 will do when I enable poolPingQuery.  
> I will try your idea soon.Thanks again!
>
>
> 陈抒
> Best regards
> http://blog.csdn.net/sheismylife
>
>
> 2010/5/12 François Schiettecatte <[hidden email]>
> 陈抒
>
> Not sure which RDBMS you are using. Ideally you want to set the idle connection timeout in your pool to a value smaller than the idle connection timeout on the server end, the errors you were seeing suggests that the server has dropped the connection while the pool thinks it is still ok. From my limited experience 'autoReconnect=true' is not recommended, here is an excerpt from the MySQL documentation:
>
> ----
> Should the driver try to re-establish stale and/or dead connections? If enabled the driver will throw an exception for a queries issued on a stale or dead connection, which belong to the current transaction, but will attempt reconnect before the next query issued on the connection in a new transaction. The use of this feature is not recommended, because it has side effects related to session state and data consistency when applications don't handle SQLExceptions properly, and is only designed to be used when you are unable to configure your application to handle SQLExceptions resulting from dead and stale connections properly. Alternatively, investigate setting the MySQL server variable "wait_timeout" to some high value rather than the default of 8 hours.
> ----
>
> What you are doing will work but there is overhead associated with it, and you might want use a query like 'select 1' rather than hitting the jobstatus table. And you might want to read up on 'poolPingConnectionsNotUsedFor' on page 20 of the documentation, the default value is 0 which means the connection is pinged before every use.
>
> I would recommend you check into these parameters to see if you can do away with the ping query:
>
>                <property name="poolMaximumActiveConnections" value="10" />
>                <property name="poolMaximumIdleConnections" value="5" />
>                <property name="poolMaximumCheckoutTime" value="20000" />
>                <property name="poolTimeToWait" value="20000" />
>
> Cheers
>
> François
>
> On May 12, 2010, at 6:57 AM, 陈抒 wrote:
>
> > I don't know.
> > But I took one hour to test my application again,it works fine if the 'time out' problem doesn't occurs.
> > Now I add two properties,let's wait and see if it can solve this or not.
> > <property name="poolPingQuery" value="select * from jobstatus"/>
> > <property name="poolPingEnabled" value="true"/>
> >
> > 陈抒
> > Best regards
> > http://blog.csdn.net/sheismylife
> >
> >
> > On Wed, May 12, 2010 at 6:46 PM, Martin Ellis <[hidden email]> wrote:
> > On 12 May 2010 02:31, 陈抒 <[hidden email]> wrote:
> > > Caused by: java.lang.NullPointerException
> > >         at freebird.business.Email.getReceiverList(Email.java:105)
> >
> > What caused this NullPointerException?
> > Is it really related to the connection option?
> >
> > Martin
> >
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail: [hidden email]
> > For additional commands, e-mail: [hidden email]
> >
> >
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>
>


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Loading...