Symptom
Below two examples of issues faced are described:
Example 1
There are some issues on the production system with MySql. The SAP Commerce servers (cluster with 2 nodes) often have connection errors to the database (which is on another server on the same network).
Which results in stack traces like:
|
2013-08-12 01:04:01,658 ERROR JaloSession: (master/s18193766952080,USERUID:anonymous) usa_store WS-00000000000140638559 [[restjersey]] Servlet.service() for servlet restjersey threw exception
de.hybris.platform.servicelayer.search.exceptions.FlexibleSearchException: SQL search error : Communications link failure
Last packet sent to the server was 0 ms ago. query = 'SELECT item_t0.PK FROM carts item_t0, users item_t1 WHERE ( item_t0.UserPK = item_t1.PK AND item_t1.p_customerid = ? AND item_t0.Code = ?) AND ((item_t0.TypePkString IN (?,?) AND item_t1.TypePkString=? ))', values = [anonymous, 118391968879, 8796115435602, 8796094201938, 8796094038098]
at de.hybris.platform.servicelayer.search.impl.DefaultFlexibleSearchService$2.execute(DefaultFlexibleSearchService.java:406)
at de.hybris.platform.servicelayer.search.impl.DefaultFlexibleSearchService$2.execute(DefaultFlexibleSearchService.java:1)
at de.hybris.platform.servicelayer.session.impl.DefaultSessionService.executeInLocalView(DefaultSessionService.java:88)
at de.hybris.platform.servicelayer.search.impl.DefaultFlexibleSearchService.getJaloResult(DefaultFlexibleSearchService.java:385)
at de.hybris.platform.servicelayer.search.impl.DefaultFlexibleSearchService.search(DefaultFlexibleSearchService.java:171)
...
at sun.reflect.GeneratedMethodAccessor930.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at com.sun.jersey.server.impl.model.method.dispatch.AbstractResourceMethodDispatchProvider$ResponseOutInvoker._dispatch(AbstractResourceMethodDispatchProvider.java:175)
at com.sun.jersey.server.impl.model.method.dispatch.ResourceJavaMethodDispatcher.dispatch(ResourceJavaMethodDispatcher.java:67)
at com.sun.jersey.server.impl.uri.rules.HttpMethodRule.accept(HttpMethodRule.java:163)
at com.sun.jersey.server.impl.uri.rules.SubLocatorRule.accept(SubLocatorRule.java:105)
at com.sun.jersey.server.impl.uri.rules.RightHandPathRule.accept(RightHandPathRule.java:111)
at com.sun.jersey.server.impl.uri.rules.SubLocatorRule.accept(SubLocatorRule.java:105)
at com.sun.jersey.server.impl.uri.rules.RightHandPathRule.accept(RightHandPathRule.java:111)
at com.sun.jersey.server.impl.uri.rules.SubLocatorRule.accept(SubLocatorRule.java:105)
at com.sun.jersey.server.impl.uri.rules.RightHandPathRule.accept(RightHandPathRule.java:111)
at com.sun.jersey.server.impl.uri.rules.ResourceClassRule.accept(ResourceClassRule.java:71)
at com.sun.jersey.server.impl.uri.rules.RightHandPathRule.accept(RightHandPathRule.java:111)
at com.sun.jersey.server.impl.uri.rules.RootResourceClassesRule.accept(RootResourceClassesRule.java:63)
at com.sun.jersey.server.impl.application.WebApplicationImpl._handleRequest(WebApplicationImpl.java:689)
at com.sun.jersey.server.impl.application.WebApplicationImpl.handleRequest(WebApplicationImpl.java:647)
at com.sun.jersey.server.impl.application.WebApplicationImpl.handleRequest(WebApplicationImpl.java:638)
at com.sun.jersey.spi.container.servlet.WebComponent.service(WebComponent.java:309)
at com.sun.jersey.spi.container.servlet.ServletContainer.service(ServletContainer.java:425)
at com.sun.jersey.spi.container.servlet.ServletContainer.service(ServletContainer.java:590)
[8/13/13 2:57:41 PM] Erik Wennerberg: at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
...
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:366)
at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:109)
at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:83)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:378)
at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:97)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:378)
at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:100)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:378)
at org.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:78)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:378)
at org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:54)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:378)
at org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:35)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:378)
at org.springframework.security.web.authentication.www.BasicAuthenticationFilter.doFilter(BasicAuthenticationFilter.java:177)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:378)
at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:79)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:378)
at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:167)
at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:237)
at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:167)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
...
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:554)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:298)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:859)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:588)
at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:396)
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:662)
Caused by: de.hybris.platform.jalo.flexiblesearch.FlexibleSearchException: SQL search error : Communications link failure
|
From the monitoring tools no downtime was noticed with the database that lasted more than 5 minutes.
These are the mysql timeout settings:
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| connect_timeout | 10 |
| delayed_insert_timeout | 300 |
| innodb_lock_wait_timeout | 50 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 28800 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| slave_net_timeout | 3600 |
| table_lock_wait_timeout | 50 |
| wait_timeout | 28800 |
+----------------------------+-------+ |
Here is the db config:
db.pool.maxActive=90
db.pool.maxIdle=90
db.url=jdbc:mysql://192.168.108.9/benefit_hybris?useConfigs=maxPerformance&characterEncoding=utf8
db.driver=com.mysql.jdbc.Driver
db.username=hybris
db.password=qwgGdKU0
db.tableprefix=
mysql.optional.tabledefs=CHARSET=utf8 COLLATE=utf8_bin
mysql.tabletype=InnoDB |
The transaction isolation level has been changed from REPEATABLE READ to READ COMMITTED, but it hasn't helped much.
The tenant.restart.on.connection.error=false property has been set, which avoids Hybris crashing when it looses the DB connection.
A lot of "unable to create a DB connection. pool returned NULL without further error." errors are still being experienced, and the DBA didn't see the DB going down at any time.
It has happened that the DB connection was lost for a whole day, which is quite problematic.
Example 2
We have problems with the database reconnect after small I/O outages. Please find attached the stack trace.
|
INFO | jvm 1 | main | 2013/09/17 15:58:43.026 | [0;31mERROR [TP-Processor287] [10.16.176.21] [Utilities] invalid db connection params oracle.jdbc.driver.OracleDriver::jdbc:oracle:thin:@(DESCRIPTION=(LOAD_BALANCE=ON)(FAILOVER=ON)(ENABLE=BROKEN)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=164.139.43.202)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=164.139.43.203)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=HYBRIS_PP2.de.madm.net)(FAILOVER_MODE=(TYPE=SELECT)(METHOD=BASIC)(RETRIES=36)(DELAY=5))))... - check platform properties (or database)!
INFO | jvm 1 | main | 2013/09/17 15:58:43.026 | [mjava.lang.Error: connection was NULL, this should NEVER occur. (txbound: true)
INFO | jvm 1 | main | 2013/09/17 15:58:43.026 | at de.hybris.platform.jdbcwrapper.DataSourceImpl.assertNotNull(DataSourceImpl.java:444)
INFO | jvm 1 | main | 2013/09/17 15:58:43.026 | at de.hybris.platform.jdbcwrapper.DataSourceImpl.getFromPool(DataSourceImpl.java:416)
INFO | jvm 1 | main | 2013/09/17 15:58:43.026 | at de.hybris.platform.jdbcwrapper.DataSourceImpl.doGetConnection(DataSourceImpl.java:397)
INFO | jvm 1 | main | 2013/09/17 15:58:43.026 | at de.hybris.platform.jdbcwrapper.DataSourceImpl.getConnection(DataSourceImpl.java:388)
INFO | jvm 1 | main | 2013/09/17 15:58:43.026 | at de.hybris.platform.jdbcwrapper.DataSourceImpl.getConnection(DataSourceImpl.java:342)
INFO | jvm 1 | main | 2013/09/17 15:58:43.026 | at de.hybris.platform.util.Utilities.isSystemInitialized(Utilities.java:1324)
INFO | jvm 1 | main | 2013/09/17 15:58:43.026 | at de.hybris.platform.util.Utilities.isSystemInitialized(Utilities.java:1304)
INFO | jvm 1 | main | 2013/09/17 15:58:43.026 | at de.hybris.platform.jalo.JaloConnection.isSystemInitialized(JaloConnection.
|
Read more...
Environment
Product
hybris Commerce Suite 4.4 ; hybris Commerce Suite 4.5 ; hybris Commerce Suite 4.6 ; hybris Commerce Suite 4.7 ; hybris Commerce Suite 4.8
Keywords
Stale transaction, connection loss, connection pool, thread pool, bound connection, commerce
, KBA , CEC-SCC-PLA-PL , Platform , Bug Filed
About this page
This is a preview of a SAP Knowledge Base Article. Click
more to access the full version on SAP for Me (Login
required).
Search for additional results
Visit SAP Support Portal's SAP Notes and KBA Search.
/support/notes/service/sap_logo.png){kind=logo}
SAP Knowledge Base Article - Preview/support/notes/service/facebook.svg)
/support/notes/service/twitter.svg)
/support/notes/service/youtube.svg)
/support/notes/service/linkedin.svg)
/support/notes/service/instagram2.svg)