2729638 - Unable to execute long-running queries through VirtualJDBC after upgrade to 6.3

After upgrading hybris to 6.3, it was observed that long-running queries through VirtualJDBC are failing using the default configuration.

After exactly 5 minutes of the query executing, the below error is observed in the logs:

2017-07-24 16:15:28,220 * 404742 [GET_INVENTORY_DATA_404742] SQLException when closing statement
java.sql.SQLException: java.sql.SQLException: No operations allowed after connection closed.
at de.simplicit.vjdbc.util.SQLExceptionHelper.wrapSQLException(SQLExceptionHelper.java:44)
at de.simplicit.vjdbc.util.SQLExceptionHelper.wrap(SQLExceptionHelper.java:25)
at de.simplicit.vjdbc.server.command.CommandProcessor.process(CommandProcessor.java:165)
at de.hybris.platform.virtualjdbc.servlet.MethodBasedRequestDispatcher.executeCommand(MethodBasedRequestDispatcher.java:78)
...

Other potentially related errors include - but may not be limited to - the following:

2018-03-01 12:02:45,475 ERROR [hybrisHTTP19] [172.25.129.38] [ConnectionImpl] transaction of unbinding connection 32726() has not been ended - will rollback automatically
2018-03-01 12:02:46,956 ERROR [hybrisHTTP34] [172.25.129.38] [ConnectionImpl] transaction of unbinding connection 32726() has not been ended - will rollback automatically
2018-03-01 12:02:47,974 ERROR [commons-pool-EvictionTimer] [ConnectionImpl] transaction of unbinding connection 32726() has not been ended - will rollback automatically

INFO | jvm 1 | main | 2017/08/04 09:07:51.917 |SEVERE: Servlet.service() for servlet [vjdbcServlet] in context with path [/virtualjdbc] threw exception
INFO | jvm 1 | main | 2017/08/04 09:07:51.917 | java.lang.IllegalArgumentException: There is not any matching connection information for given connection uid <23>
INFO | jvm 1 | main | 2017/08/04 09:07:51.917 | at com.google.common.base.Preconditions.checkArgument(Preconditions.java:122)
INFO | jvm 1 | main | 2017/08/04 09:07:51.917 | at de.hybris.platform.virtualjdbc.filter.VjdbcConnectionUidCache.get(VjdbcConnectionUidCache.java:37)
INFO | jvm 1 | main | 2017/08/04 09:07:51.917 | at de.hybris.platform.virtualjdbc.filter.VjdbcConnectionFilter.prehandleProcessRequest(VjdbcConnectionFilter.java:162)
INFO | jvm 1 | main | 2017/08/04 09:07:51.917 | at de.hybris.platform.virtualjdbc.filter.VjdbcConnectionFilter.doPreRequest(VjdbcConnectionFilter.java:103)
INFO | jvm 1 | main | 2017/08/04 09:07:51.917 | at de.hybris.platform.util.RootRequestFilter.doFilter(RootRequestFilter.java:857)
INFO | jvm 1 | main | 2017/08/04 09:07:51.917 | at 
...

INFO | jvm 1 | main | 2017/08/04 15:06:12.109 | SEVERE: Servlet.service() for servlet [vjdbcServlet] in context with path [/virtualjdbc] threw exception
INFO | jvm 1 | main | 2017/08/04 15:06:26.437 | SEVERE: Servlet.service() for servlet [vjdbcServlet] in context with path [/virtualjdbc] threw exception
INFO | jvm 1 | main | 2017/08/04 15:06:32.046 | SEVERE: Servlet.service() for servlet [vjdbcServlet] in context with path [/virtualjdbc] threw exception
...


Caused by: de.hybris.platform.jalo.flexiblesearch.FlexibleSearchException: SQL search error - enable the property ‘flexible.search.exception.show.query.details’ for more details[HY-0]
INFO | jvm 1 | main | 2017/07/24 08:05:09.232 | at de.hybris.platform.jalo.flexiblesearch.internal.FlexibleSearchExecutor.execute(FlexibleSearchExecutor.java:172)
INFO | jvm 1 | main | 2017/07/24 08:05:09.232 | at de.hybris.platform.jalo.flexiblesearch.FlexibleSearch.executeSearch(FlexibleSearch.java:1771)...
INFO | jvm 1 | main | 2017/07/24 08:05:09.232 | ... 15 moreINFO | jvm 1 | main | 2017/07/24 08:05:09.232 | 
Caused by:com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 7,200,042 milliseconds ago. 
The last packet sent successfully to the server was 7,200,040 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.

This issue has been observed after migrating to hybris Commerce Suite version 6.3 from an earlier version.

• hybris
• commerce
• vjdbc
• virtual jdbc
• CloverETL
• Jaspersoft Studio
• DbVisualizer 
• occ
• long running