The last packet successfully received from the server was 2,907,745 milliseconds ago. The last packet sent successfully to the server was 0 milliseconds ago.

[sailin...@gmail.com]

Hi all,I get the com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure error in my project. 
env: 
the mybatis version is 3.2.8. 
the mysql connector version is 5.1.35. 
the mysqld version is 5.5.53. 

this below is the mybatis config.I set the ping query, but It may not work. 
url = jdbc:mysql://127.0.0.1:3306/tuning_new?autoReconnect=true 
<environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${username}"/>
                <property name="password" value="${password}"/>
                <property name="poolMaximumActiveConnections" value="20"/>
                <property name="poolMaximumIdleConnections" value="20"/>
                <property name="poolPingQuery" value="/* ping */ SELECT 1"/>
                <property name="poolPingEnabled" value="true"/>
                <property name="poolPingConnectionsNotUsedFor" value="10000"/>
            </dataSource>
        </environment>
    </environments>

the mysql variables is below 
mysql> show variables like "%timeout%" 
    -> ; 
+----------------------------+----------+ 
| Variable_name              | Value    | 
+----------------------------+----------+ 
| connect_timeout            | 10       | 
| delayed_insert_timeout     | 300      | 
| innodb_lock_wait_timeout   | 50       | 
| innodb_rollback_on_timeout | OFF      | 
| interactive_timeout        | 28800    | 
| lock_wait_timeout          | 31536000 | 
| net_read_timeout           | 30       | 
| net_write_timeout          | 60       | 
| slave_net_timeout          | 3600     | 
| wait_timeout               | 28800    | 
+----------------------------+----------+ 
10 rows in set (0.01 sec) 

the tomcat web application only use in daytime, not use in night. 
in every morning, I first use, it will get this error. 
The last packet successfully received from the server was 3,146,142 milliseconds ago.  The last packet sent successfully to the server was 0 milliseconds ago.
### The error may exist in mappers/Snapshot.xml 
### The error may involve defaultParameterMap 
### The error occurred while setting parameters 
### SQL: SELECT ss.thumbnail FROM Snapshot AS ss         WHERE ss.id IN         (SELECT DISTINCT * FROM (         SELECT ss.id FROM Snapshot AS ss WHERE ss.user_id = ?         UNION         SELECT sf.snapshot_id_name FROM snapshot_feed AS sf WHERE sf.to_id = ?         UNION         SELECT ss.id FROM Snapshot AS ss WHERE ss.status = 0)AS a)         AND ss.judgeThemeId = ? AND (ss.status = 0 OR ss.status = 1)         ORDER BY ss.ts DESC limit 1 
### Cause: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure 

The last packet successfully received from the server was 3,146,142 milliseconds ago.  The last packet sent successfully to the server was 0 milliseconds ago.
        at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:26) 
        at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:111) 
        at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:102) 
        at org.apache.ibatis.session.defaults.DefaultSqlSession.selectOne(DefaultSqlSession.java:66) 
        at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:68) 
        at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:52) 
        at com.sun.proxy.$Proxy57.getLatestThumbnail(Unknown Source) 
        at com.mininglamp.tuning.console.application.SnapshotManager.getLatestThumbnail(SnapshotManager.java:103) 
        at com.mininglamp.tuning.console.service.SnapshotService.getLatestThumbnail(SnapshotService.java:515) 
        at sun.reflect.GeneratedMethodAccessor242.invoke(Unknown Source) 
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) 
        at java.lang.reflect.Method.invoke(Method.java:498) 
        at org.apache.cxf.service.invoker.AbstractInvoker.performInvocation(AbstractInvoker.java:180) 
        at org.apache.cxf.service.invoker.AbstractInvoker.invoke(AbstractInvoker.java:96) 
        at org.apache.cxf.jaxrs.JAXRSInvoker.invoke(JAXRSInvoker.java:200) 
        at org.apache.cxf.jaxrs.JAXRSInvoker.invoke(JAXRSInvoker.java:99) 
        at org.apache.cxf.interceptor.ServiceInvokerInterceptor$1.run(ServiceInvokerInterceptor.java:59) 
        at org.apache.cxf.interceptor.ServiceInvokerInterceptor.handleMessage(ServiceInvokerInterceptor.java:96) 
        at org.apache.cxf.phase.PhaseInterceptorChain.doIntercept(PhaseInterceptorChain.java:308) 
        at org.apache.cxf.transport.ChainInitiationObserver.onMessage(ChainInitiationObserver.java:121) 
        at org.apache.cxf.transport.http.AbstractHTTPDestination.invoke(AbstractHTTPDestination.java:251) 
        at org.apache.cxf.transport.servlet.ServletController.invokeDestination(ServletController.java:234) 
        at org.apache.cxf.transport.servlet.ServletController.invoke(ServletController.java:208) 
        at org.apache.cxf.transport.servlet.ServletController.invoke(ServletController.java:160) 
        at org.apache.cxf.transport.servlet.CXFNonSpringServlet.invoke(CXFNonSpringServlet.java:180) 
        at org.apache.cxf.transport.servlet.AbstractHTTPServlet.handleRequest(AbstractHTTPServlet.java:293) 
        at org.apache.cxf.transport.servlet.AbstractHTTPServlet.doGet(AbstractHTTPServlet.java:217) 
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:624) 
        at org.apache.cxf.transport.servlet.AbstractHTTPServlet.service(AbstractHTTPServlet.java:268) 
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303) 
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) 
        at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52) 
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241) 
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) 
        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:218) 
        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:122) 
        at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:505) 
        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:169) 
        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103) 
        at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:956) 
        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116) 
        at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:442) 
        at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1082) 
        at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:623) 
        at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:316) 
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) 
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) 
        at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) 
        at java.lang.Thread.run(Thread.java:745) 
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure 

The last packet successfully received from the server was 3,146,142 milliseconds ago.  The last packet sent successfully to the server was 0 milliseconds ago.
        at sun.reflect.GeneratedConstructorAccessor135.newInstance(Unknown Source) 
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) 
        at java.lang.reflect.Constructor.newInstance(Constructor.java:423) 
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:389) 
        at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1038) 
        at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3422) 
        at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3322) 
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3762) 
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2435) 
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2582) 
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2535) 
        at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1911) 
        at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1203) 
        at sun.reflect.GeneratedMethodAccessor102.invoke(Unknown Source) 
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) 
        at java.lang.reflect.Method.invoke(Method.java:498) 
        at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:62) 
        at com.sun.proxy.$Proxy54.execute(Unknown Source) 
        at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:59) 
        at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:73) 
        at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:60) 
        at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:267) 
        at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:137) 
        at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:96) 
        at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:77) 
        at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:108) 
        ... 47 more 
Caused by: java.io.EOFException: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost. 
        at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:2914) 
        at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3332) 
        ... 67 more

[Carsten Langsdorf]

Hi,

I just had a look at this. Although I am not very proficient with batis yet, I spotted a few things I would like to comment on, hoping it might help.

Let's have a look at the generated SQL statement, which I reformatted a bit for the sake of readability:

SELECT ss.thumbnail FROM Snapshot AS ss        
WHERE ss.id IN (
 SELECT DISTINCT * FROM (        
 SELECT ss.id FROM Snapshot AS ss WHERE ss.user_id = ?        
 UNION        
 SELECT sf.snapshot_id_name FROM snapshot_feed AS sf WHERE sf.to_id = ?        
 UNION        
 SELECT ss.id FROM Snapshot AS ss WHERE ss.status = 0)
 AS a)        
AND ss.judgeThemeId = ?
AND (ss.status = 0 OR ss.status = 1)        
ORDER BY ss.ts DESC limit 1

Using an SQL query like this is not merely asking for trouble, it is almost guaranteed to cause severe issues - even if you were not running MySQL in server mode and Tomcat. In your environment, it's likely to blow either server up, or maybe even both.

Here's why:

1. You are nesting SELECTs 3 levels deep. From long time personal experience, I have adopted the practice to never go more than 2 levels deep, and that only if absolutely needed and never without reconsidering better alternatives.
2. To make things a lot worse, the innermost level is not even a singular query, but contains 3 SELECTs, and even combines them via UNION. UNIONs are dangerous and should be avoided as much as possible.
3. Also questionable, concerning the UNIONs: Are ss.id and sf.snapshot_id_name type compatible?
4. You are using the same table alias (ss) 3 times, 2 times alone on the deepest nesting level. You are really asking for big trouble here.
5. And you do all this only to fetch 1 field from 1 single row. Can you guarantee there will be at least 1, or is an empty result set possible?

Honestly, and even if it might not make you happy, all this is a sign of poor database or application design, or even both. And definetely they don't match very well, to say the least.

If I had to deal with this, I would check/fix/redesign the following issues, in order:

1. Type compatibility of ss.id and sf.snapshot_id_name. In theory you should get an error message in case of incompatibility, but in practice you might not, especially considering all the layers you are running through (database/web server etc.). Checking the server logs (if available) might provide some insight, though.
2. The table alias 'ss' - avoid using any table alias for more than 1 context. 
3. The UNIONs. Avoid them - there are always alternatives.
4. Try using your target attribute instead of the '*' in your 'SELECT DISTINCT *'. Or, even better, consider removing it altogether, if possible. 
5. Reduce the query nesting depth.
6. Rethink the overall query design.
7. As a second-to-last resort, a database redesign might be necessary. Consider optimized helper views, for example.
8. Last resort: application redesign.

Hope it helps, good luck, keep us updated, and don't be shy to ask for more help, if needed.

Carsten

P.S.:

On the funny side, I think every reasonable database engine should reply to such a query with a message like

Indignantly, the database shakes its virtual head and asks 'Are you kidding me?'

[Iwao AVE!]

Hi,

This error is caused by various reasons, but there was a fix possibly related to your issue. 

https://github.com/mybatis/mybatis-3/issues/748

You should try 1) updating MyBatis to 3.4.2 or 2) using some third party datasource implementation (e.g. DBCP, c3p0, HikariCP, etc.) and see if it helps.

Regards,

Iwao

--
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+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.