Atomikos Forum

Hi all,

I'm getting this kind of error, likely due to the timeout of mysql connection.
I use the com.atomikos.jdbc.AtomikosDataSourceBean so I guess that the maintenanceInterval is set to 60 secs by default. So why the connection isn't been ripped by the pool and remain active until the server drop it?

Thanks a lot,

Giancarlo Frison

ver: 3.6.6

com.atomikos.datasource.pool.CreateConnectionException: Error executing testQuery
        at com.atomikos.jdbc.AtomikosXAPooledConnection.testUnderlyingConnection(AtomikosXAPooledConnection.java:101)
        at com.atomikos.datasource.pool.AbstractXPooledConnection.createConnectionProxy(AbstractXPooledConnection.java:43)
        at com.atomikos.datasource.pool.ConnectionPool.borrowConnection(ConnectionPool.java:136)
        at com.atomikos.jdbc.AbstractDataSourceBean.getConnection(AbstractDataSourceBean.java:292)
        at com.atomikos.jdbc.AbstractDataSourceBean.getConnection(AbstractDataSourceBean.java:344)
        at org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:111)



Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 80,303,261 milliseconds ago.  The last packet sent successfully to the server was 80,303,261 milliseconds ago. 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 report the configuration raise this problem:

    <bean id="ds" class="com.atomikos.jdbc.AtomikosDataSourceBean"
        init-method="init" destroy-method="close"
        p:xaDataSource-ref="singleds" p:uniqueResourceName="ds"
        p:testQuery="select 1" p:maxPoolSize="${jdbc.maxPoolSize}" p:minPoolSize="${jdbc.minPoolSize}"/>        
    <bean id="oldSingle" class="com.mysql.jdbc.jdbc2.optional.MysqlXADataSource"
        p:user="${jdbc.old.username}" p:password="${jdbc.old.password}" p:url="${jdbc.old.url}"  p:autoReconnect="true"/>


any idea about it?
Thanks,

Is this merely in our logs or is it an exception at the app-level?

Compares only on logs.
It seems that the sql test "select 1" it's not performed at maintenance interval, whereas after the connection timeout throwing this exception.
The application isn't affected by any error because when the connection is borrowed the sql test fails then an other connection would be picked up.

I have this same problem as well. The issue is that there seems to be no way to "ping" to "refresh" the connections in the pool every n seconds, hence the timeout from the mysql end.

Apparently Ibatis connection pool provides facilities for refreshing the connection as follows:

<transactionManager type="JDBC">
<dataSource type="SIMPLE">
<property value="${driver}" name="JDBC.Driver"/>
<property value="${url}" name="JDBC.ConnectionURL"/>
<property value="${username}" name="JDBC.Username"/>
<property value="${password}" name="JDBC.Password"/>
<property name="Pool.PingQuery" value="select 1"/>
<property name="Pool.PingEnabled" value="true"/>
<property name="Pool.PingConnectionsOlderThan" value="3600000"/> <!-- 1 hr -->
<property name="Pool.PingConnectionsNotUsedFor" value="10000"/> <!-- ping db 10 sec -->
</dataSource>
</transactionManager>

Please refer to the below URL for moree details:

http://mysqlpreacher.com/wordpress/2010/06/mysql-jdbc-connector-autoreconnecttrue/

Is there any such "ping" or "refresh" facility in the atomikos connection pool as mentioned above?

I sniffed the tcp traffic to mysql and I've seen no activity nor sql test nor new connections. It's odd because in the ConnectionPool init method a periodic task should refresh connections:

        maintenanceTimer = new PooledAlarmTimer ( maintenanceInterval * 1000 );
        maintenanceTimer.addAlarmTimerListener(new AlarmTimerListener() {
            public void alarm(AlarmTimer timer) {
                shrinkPool();
                reapPool();
            }
        });

I tried to debug the application but I noticed a missing synchronization between classes and sources. I'm using the maven version 3.6.6.

Any help? Thank you

3.7.0M5 should be better in terms of correspondence with the sources...

The pool does _not_ check on maintenance intervals, rather on per-borrow-occasions. So this error in the logs does not seem like a problem at first sight...

Thank you Guy,

So is there any trick in order to skirt this warning message in the logs and let the pool refreshing the connections?

Thanks again,

Not at this time. Would that be important?

I think it is important.

The pool should provides always fresh and ready connections instead of create them (because of timeout for example) 'on the fly' when required. I think it's important to check regularly the sql test, and recreate them after configuration elapsed time.

Regards,

Thanks, feature request created...

Hi Guy,

If you agree I'd contribute for this feature.

You're more than welcome to do so - thanks!

Do you want me to send the paperwork with detailed instructions?

Could you give me the access to the repository so I can you post the updates?

Hi,

Gladly, if you are willing to submit signed documents as outlined in http://www.atomikos.com/Main/FrequentlyAskedQuestions

Just let me know,

Thanks

Hi,

Any update on this? Shall we set up contributor access for you?

Guy