[Hibernate / Spring / Cluster / JDBC] Erreur "Too many connections"
Bonjour,
J'ai un problème dans le configuration de cluster au niveau de DataSource.
J'ai deux bases de données (MySQL) Replication Master Master.
Dans la configuration ci-dessous, sans cluster l'application fonctionne correctement.
Configuration de data-source:
Code:
1
2
3
4
5
6
7
| <bean id="mydataSource" class="org.apache.commons.dbcp.BasicDataSource">
<property name="driverClassName" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/zaas?autoReconnect=true"/>
<property name="username" value="root"/>
<property name="password" value="root"/>
<property name="validationQuery" value="Select 1"></property>
</bean> |
Une seule connexion est ouvert d'après la table PROCESSLIST de MySQL
Code:
1
2
3
4
5
6
7
8
| mysql> show PROCESSLIST;
+----+------+-----------------+------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------------+------+---------+------+-------+------------------+
| 50 | root | localhost:51096 | zaas | Sleep | 2 | | NULL |
| 51 | root | localhost | NULL | Query | 0 | NULL | show PROCESSLIST |
+----+------+-----------------+------+---------+------+-------+------------------+
2 rows in set (0.00 sec) |
Les problèmes commencent lors de l'ajout de la configuration de cluster comme suit :
Code:
1
2
3
4
5
6
7
| <bean id="mydataSource" class="org.apache.commons.dbcp.BasicDataSource">
<property name="driverClassName" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost,192.168.1.161:3306/zaas?autoCommit=true&autoReconnect=true&failOverReadOnly=false"/>
<property name="username" value="root"/>
<property name="password" value="root"/>
<property name="validationQuery" value="Select 1"></property>
</bean> |
Le problème est que pour chaque requête, une connexion sera ouverte comme le montre la table PROCESSLIST :
Code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
| mysql> show PROCESSLIST;
+----+------+-----------------+------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------------+------+---------+------+-------+------------------+
| 51 | root | localhost | NULL | Query | 0 | NULL | show PROCESSLIST |
| 53 | root | localhost:35600 | zaas | Sleep | 94 | | NULL |
| 54 | root | localhost:35609 | zaas | Sleep | 34 | | NULL |
| 55 | root | localhost:60089 | zaas | Sleep | 5 | | NULL |
| 56 | root | localhost:60093 | zaas | Sleep | 4 | | NULL |
| 57 | root | localhost:60094 | zaas | Sleep | 4 | | NULL |
| 58 | root | localhost:60095 | zaas | Sleep | 4 | | NULL |
| 59 | root | localhost:60096 | zaas | Sleep | 3 | | NULL |
| 60 | root | localhost:60097 | zaas | Sleep | 3 | | NULL |
| 61 | root | localhost:60098 | zaas | Sleep | 3 | | NULL |
| 62 | root | localhost:60099 | zaas | Sleep | 3 | | NULL |
| 63 | root | localhost:60100 | zaas | Sleep | 2 | | NULL |
| 64 | root | localhost:60101 | zaas | Sleep | 2 | | NULL |
+----+------+-----------------+------+---------+------+-------+------------------+
13 rows in set (0.00 sec) |
Si le nombre de connexions dépasse les 100 connexions (la valeur par défaut de MySQL), nous avons un problème de "Too many connection"
Si nous nous arrêtons la bases de données localhost, le serveur utilise la deuxième base de données mais avec une seule connexion ouverte comme le montre la table PROCESSLIST :
Code:
1
2
3
4
5
6
7
8
| mysql> show PROCESSLIST;
+-----+------+------------------------------+------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+------+------------------------------+------+---------+------+-------+------------------+
| 372 | root | wissem-desktop-2.local:47227 | zaas | Sleep | 36 | | NULL |
| 374 | root | wissem-desktop-2.local:47299 | NULL | Query | 0 | NULL | show PROCESSLIST |
+-----+------+------------------------------+------+---------+------+-------+------------------+
2 rows in set (0.00 sec) |
Avec une exception dans le log
Code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
| Thu Sep 13 11:24:15 CEST 2012 WARN: Connection to primary host failed
EXCEPTION STACK TRACE:
** BEGIN NESTED EXCEPTION **
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException
MESSAGE: Communications link failure
The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
STACKTRACE:
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
at sun.reflect.GeneratedConstructorAccessor71.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1118)
at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:343)
at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2308)
at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2122)
at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:774)
at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:49)
at sun.reflect.GeneratedConstructorAccessor62.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:375)
at com.mysql.jdbc.LoadBalancingConnectionProxy.createConnectionForHost(LoadBalancingConnectionProxy.java:320)
at com.mysql.jdbc.FailoverConnectionProxy.createPrimaryConnection(FailoverConnectionProxy.java:132)
at com.mysql.jdbc.FailoverConnectionProxy.invoke(FailoverConnectionProxy.java:122)
at $Proxy21.setAutoCommit(Unknown Source)
at org.apache.commons.dbcp.DelegatingConnection.setAutoCommit(DelegatingConnection.java:371)
at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.setAutoCommit(PoolingDataSource.java:328)
at org.hibernate.transaction.JDBCTransaction.toggleAutoCommit(JDBCTransaction.java:228)
at org.hibernate.transaction.JDBCTransaction.commitAndResetAutoCommit(JDBCTransaction.java:173)
at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:146)
at org.springframework.orm.hibernate3.HibernateTransactionManager.doCommit(HibernateTransactionManager.java:656)
at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:754)
at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:723)
at org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAfterReturning(TransactionAspectSupport.java:412)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:118)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
at $Proxy39.searchByType(Unknown Source)
at tn.alpha.zaas.web.admin.ListDomainBean.initDataTable2(ListDomainBean.java:266)
at tn.alpha.zaas.web.admin.ListDomainBean.init2(ListDomainBean.java:260)
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.springframework.beans.factory.annotation.InitDestroyAnnotationBeanPostProcessor$LifecycleElement.invoke(InitDestroyAnnotationBeanPostProcessor.java:340)
at org.springframework.beans.factory.annotation.InitDestroyAnnotationBeanPostProcessor$LifecycleMetadata.invokeInitMethods(InitDestroyAnnotationBeanPostProcessor.java:293)
at org.springframework.beans.factory.annotation.InitDestroyAnnotationBeanPostProcessor.postProcessBeforeInitialization(InitDestroyAnnotationBeanPostProcessor.java:130)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.applyBeanPostProcessorsBeforeInitialization(AbstractAutowireCapableBeanFactory.java:394)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1396)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:513)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:450)
at org.springframework.beans.factory.support.AbstractBeanFactory$2.getObject(AbstractBeanFactory.java:328)
at org.springframework.web.context.request.AbstractRequestAttributesScope.get(AbstractRequestAttributesScope.java:43)
at org.springframework.web.context.request.SessionScope.get(SessionScope.java:92)
at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:324)
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:189)
at org.springframework.context.support.AbstractApplicationContext.getBean(AbstractApplicationContext.java:1044)
at org.springframework.beans.factory.access.el.SpringBeanELResolver.getValue(SpringBeanELResolver.java:56)
at javax.el.CompositeELResolver.getValue(CompositeELResolver.java:53)
at com.sun.faces.el.FacesCompositeELResolver.getValue(FacesCompositeELResolver.java:64)
at org.apache.el.parser.AstIdentifier.getValue(AstIdentifier.java:45)
at org.apache.el.parser.AstValue.getValue(AstValue.java:86)
at org.apache.el.ValueExpressionImpl.getValue(ValueExpressionImpl.java:186)
at com.sun.facelets.el.TagValueExpression.getValue(TagValueExpression.java:71)
at javax.faces.component.UIOutput.getValue(UIOutput.java:173)
at com.sun.faces.renderkit.html_basic.MenuRenderer.getCurrentSelectedValues(MenuRenderer.java:651)
at com.sun.faces.renderkit.html_basic.MenuRenderer.renderOption(MenuRenderer.java:524)
at com.sun.faces.renderkit.html_basic.MenuRenderer.renderOptions(MenuRenderer.java:757)
at com.sun.faces.renderkit.html_basic.MenuRenderer.renderSelect(MenuRenderer.java:811)
at com.sun.faces.renderkit.html_basic.MenuRenderer.encodeEnd(MenuRenderer.java:335)
at javax.faces.component.UIComponentBase.encodeEnd(UIComponentBase.java:833)
at javax.faces.component.UIComponent.encodeAll(UIComponent.java:896)
at javax.faces.render.Renderer.encodeChildren(Renderer.java:137)
at javax.faces.component.UIComponentBase.encodeChildren(UIComponentBase.java:809)
at javax.faces.component.UIComponent.encodeAll(UIComponent.java:886)
at javax.faces.component.UIComponent.encodeAll(UIComponent.java:892)
at com.sun.facelets.FaceletViewHandler.renderView(FaceletViewHandler.java:571)
at org.ajax4jsf.application.ViewHandlerWrapper.renderView(ViewHandlerWrapper.java:100)
at org.ajax4jsf.application.AjaxViewHandler.renderView(AjaxViewHandler.java:176)
at com.sun.faces.lifecycle.RenderResponsePhase.execute(RenderResponsePhase.java:106)
at com.sun.faces.lifecycle.LifecycleImpl.phase(LifecycleImpl.java:251)
at com.sun.faces.lifecycle.LifecycleImpl.render(LifecycleImpl.java:144)
at javax.faces.webapp.FacesServlet.service(FacesServlet.java:245)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.ajax4jsf.webapp.BaseXMLFilter.doXmlFilter(BaseXMLFilter.java:206)
at org.ajax4jsf.webapp.BaseFilter.handleRequest(BaseFilter.java:290)
at org.ajax4jsf.webapp.BaseFilter.processUploadsAndHandleRequest(BaseFilter.java:388)
at org.ajax4jsf.webapp.BaseFilter.doFilter(BaseFilter.java:515)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at tn.alpha.zaas.web.util.filter.SessionTimeoutFilter.doFilter(SessionTimeoutFilter.java:62)
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:343)
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:355)
at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:97)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355)
at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:100)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355)
at org.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:78)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355)
at org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:54)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355)
at org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:35)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355)
at org.springframework.security.web.authentication.www.BasicAuthenticationFilter.doFilter(BasicAuthenticationFilter.java:177)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355)
at org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:188)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355)
at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:105)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355)
at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:79)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355)
at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:149)
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.StandardWrapperValve.invoke(StandardWrapperValve.java:230)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:175)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:104)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:261)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:844)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:581)
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447)
at java.lang.Thread.run(Thread.java:619)
Caused by: java.net.ConnectException: Connection refused
at java.net.PlainSocketImpl.socketConnect(Native Method)
at java.net.PlainSocketImpl.doConnect(PlainSocketImpl.java:333)
at java.net.PlainSocketImpl.connectToAddress(PlainSocketImpl.java:195)
at java.net.PlainSocketImpl.connect(PlainSocketImpl.java:182)
at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:366)
at java.net.Socket.connect(Socket.java:519)
at java.net.Socket.connect(Socket.java:469)
at java.net.Socket.<init>(Socket.java:366)
at java.net.Socket.<init>(Socket.java:209)
at com.mysql.jdbc.StandardSocketFactory.connect(StandardSocketFactory.java:256)
at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:292)
... 119 more
** END NESTED EXCEPTION ** |
Est-ce la bonne façon de configurer un cluster ?
L'url de configuration de DataSource : http://dev.mysql.com/doc/refman/5.0/...roperties.html
J'ai essayé plusieurs configurations :
- JNDI
- la configuration c3p0 comme suit:
Code:
1
2
3
4
5
6
7
8
9
10
11
| <bean id="mydataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
<property name="driverClass" value="com.mysql.jdbc.Driver"/>
<property name="jdbcUrl" value="jdbc:mysql://localhost:3306,192.168.1.161:3306/zaas?autoCommit=true&autoReconnect=true&autoReconnectForPools=true&failOverReadOnly=false"/>
<property name="user" value="root" />
<property name="password" value="root" />
<!-- these are C3P0 properties -->
<!-- property name="acquireIncrement" value="${acquireIncrement}" /_-->
<property name="minPoolSize" value="6" />
<property name="maxPoolSize" value="10" />
<property name="maxIdleTime" value="100" />
</bean> |
Quelqu'un saurait-il m'indiquer d'où peut venir le problème ?
Merci d'avance pour votre aide.
